Microsoft Office Tutorials and References
In Depth Information
Table 1-1. OLTP vs. OLAP
Online Transaction Processing System
Online Analytical Processing System
Used for real-time data access.
Data might exist in more than one table.
Optimized for faster transactions.
Transactional databases include Add, Update,
and Delete operations.
Not built for running complex queries.
Line-of-business (LOB) and
enterprise-resource-planning (ERP) databases
use this model.
Tools: SQL Server Management Studio (SSMS).
Follows database (DB) normalization rules.
Relational database.
Holds key data.
Fewer indexes and more joins.
Query from multiple tables.
Used for online or historical data.
Used for analysis and drilling down into data.
Data might exist in more than one table.
Optimized for performance and details in querying the data.
Read-only database.
Built to run complex queries.
Analytical databases such as Cognos, Business Objects,
and so on. use this model.
Tools: SQL Server Analysis Services (SSAS).
Relaxes DB normalization rules.
Relational database.
Holds key aggregated data.
Relatively more indexes and fewer joins.
Query might run on fewer tables.
You’re probably already wondering how you can take your OLTP database and convert it to an OLAP database
so that you can run some analyses on it. Before we explain that, it’s important to know a little more about OLAP and
its structure.
The Unified Dimensional Model and Data Cubes
Data cubes are more sophisticated OLAP structures that will solve the preceding concern. Despite the name,
cubes are not limited to a cube structure. The name is adopted just because cubes have more dimensions than
rows and columns in tables. Don’t visualize cubes as only 3-dimensional or symmetric; cubes are used for their
multidimensional value. For example, an airline company might want to summarize revenue data by flight, aircraft,
route, and region. Flight, aircraft, route, and region in this case are dimensions. Hence, in this scenario, you have
a 4-dimensional structure (a hypercube) at hand for analysis.
A simple cube can have only three dimensions, such as those shown in Figure 1-5 , where X is Products, Y is
Region, and Z is Time.
Search JabSto ::

Custom Search