Browse DevX
Sign up for e-mail newsletters from DevX


An Introduction to OLAP in SQL Server 2005 : Page 3

Get a preview of the upcoming SQL Server 2005's Business Intelligence suite and find out about the major OLAP components of Analysis Services.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Working with Analysis Services
After identifying the dimensions and measures you wish to analyze, you can use Analysis Services to construct an OLAP cube.

Analysis Services has built-in wizards that make the actual process of creating dimensions fairly easy, especially if you're already familiar with SQL 2000's version, although SQL Server 2005's version does add one additional step—you must create a Data Source View to import your database objects.

Figure 6. SQL vs. MDX: The figure compares data extraction using SQL vs. MDX.
Just as you use SQL to query relational databases, you use MDX to query a multidimensional cube (see Figure 6). For those who are eager to interrogate the cube without learning MDX, there is an Excel Pivot Table add-in that provides a drag and drop query interface. This interface generates MDX and queries the cube on behalf of the user and as a special bonus the results are displayed in Excel!

You use MDX used to create "calculated measures" that would be too complex or impossible to do in SQL. For example, suppose the VP of Sales wants to know what the average sales price of each product is. Unfortunately, average sales price is not a measure in the Sales cube; however, Store Sales and Sales Count are available. Because you can calculate Average Sales Price by dividing Store Sales by Sales Count, you can calculate the measure (ergo the name "calculated measure") by using MDX. Here's the MDX code.

WITH MEMBER Measures.[Average Sale Price] AS 'Measures.[Store Sales] / Measures.[Sales Count]' SELECT { Measures.[Average Sale Price] } ON COLUMNS, { Product.CHILDREN } ON ROWS FROM Sales

Luckily, some third party tools let users create calculated measures that may have been intentionally omitted from the original cube design, such as commission or bonus calculations.

Cube Browser
After creating the cube, you need a cube browser to connect to the cube and display the data. Cube browsers usually provide user-friendly tree-structured dimension filters and/or drag and drop interfaces that allow end users to interrogate the cube. You can set up pre-defined queries, or allow ad hoc querying by letting users combine the various measures with dimensions.

For example, suppose you want to create a report that shows Revenue by Sales Territory by Product. Because dimensions are hierarchical, you can obtain the details of a dimension by drilling down. This usually involves clicking on the dimension (for example, clicking on Sales Territory may reveal each store's level in that dimension).

Dimensions can have multiple levels (such as year, quarter, and month). Users can mix and match members within the same dimension. Furthermore, some cube browsers enable developers to export cube browsers as a Web part that they can then easily include in a portal site or digital dashboard.

There are three basic types of cube browsers:

  • Office Web components such as the Excel Pivot Table
  • Third-party applications such ProClarity
  • Custom-built applications
Some OLAP developers find debugging cube design and validating data using pivot tables much easier than performing the same tasks using the native Analysis Services screen.

To sum up, here's the process in a nutshell.

  • Determine the required dimensions and measures.
  • Use Data Transformation Services to extract data from your source databases, transforming the data as needed, and loading the finished data into the cube.
  • Use the BI Workbench's Analysis Services wizards to build the measures, dimensions, and schema.
  • Provide cube browsers for your users so they can select and view reports. If necessary, write MDX queries or use automated tools, such as Excel Pivot Tables to query the cube.
Hopefully, this primer has whetted your OLAP appetite and given you the confidence to start creating OLAP cubes yourself. A good way to get started is to use the sample Foodmart or Adventure Works databases that ship with SQL Server 2005.

Gail Tieh Monaco is a Project Leader with Citigate Hudson's Pervasive Business Intelligence team. Gail's expertise includes process improvement through the use of technology. Gail holds an MBA in Information Systems and BA in Economics from Baruch College of City University of New York. She currently serves on the Board of New York Software Industry Association (NYSIA) and is also the Special Interest Group Leader of NYSIA's Database Professionals Council.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date