Preparing Data for Analysis with OLAP
After data is imported to the Data Warehouse SQL Server database, it must be prepared for analysis so business managers can run reports against it. To prepare data for reporting, the system administrator runs a DTS task that exports a selected subset of data from the SQL Server database to the OLAP database. In the OLAP database, the data is stored in multidimensional cubes.
By storing data in OLAP cubes, instead of in relational tables in SQL Server, the Data Warehouse can retrieve data for reporting purposes more quickly. The data can be retrieved from the cubes faster because it is aggregated. That is, OLAP is typically much faster than SQL Server because the OLAP data is aggregated and the SQL Server data contains all the details. For example, by using OLAP you can run a report that lists users who visit your site based on the time of their visit and on the ASP page that they access first. It would be extremely difficult to run such a report against a large SQL Server database.
In multidimensional cubes, data is grouped in two kinds of structures:
- Measures. The numeric values that are analyzed.
- Dimensions. A business entity, such as color, size, product, or time. For example, you would use the color dimension to contrast how many red products and blue products were sold, and the size dimension to contrast how many large and small products were sold.
It is the relationship between the dimension (for example, color) and measure (for example, number of products sold) structures that provides the basis for your reports about user activity.
The following figure illustrates the dimensions and measures in a report.
.gif "An illustration of a report with callouts indicating measures and dimensions")
See Also
Using the Data Warehouse in Web Site Management
Overview of Importing Data to the Data Warehouse
Copyright © 2005 Microsoft Corporation.
All rights reserved.