Working with Analysis Services Cubes in SQL Server
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
After you have populated your dimensional database (as discussed in the previous article in this series), you're ready to build Analysis Services cubes (Analysis Services is free when you purchase SQL Server 2000). The tool that lets you build and manage cubes with Analysis Services is called Analysis Manager, which is similar to Enterprise Manager. The language used to query Analysis Services cubes is called MDX (Multi Dimensional eXpressions). Although simple SELECT statements in MDX resemble SQL SELECT statements, don't expect many similarities between these two languages. Although you can navigate data within the cube directly from Analysis Manager, if you build custom applications based on Analysis Services you might wish to test your MDX queries through a tool called MDX Sample Application.
Creating cubes with Analysis Manager is very simple; by clicking through a few wizard screens, you can build a decent foundation. Fortunately, there are numerous articles and tutorials that show you how to build cubes with Analysis Manager. Instead of writing yet another tutorial on building cubes, I want to show you some of the advanced functionality available with Analysis Manager; to someone who hasn't spent many hours working with this tool, such functionality might not be immediately apparent. I will show you how to pivot cube data based on a different set of dimensions and measures. In addition, I'll show you how to write MDX queries to accomplish the same functionality within the MDX sample application.
I will also discuss the calculated members and member properties; both of these features can be used very effectively for performing calculations on cube data, as well as making the output of MDX queries more meaningful.
Terminology
Before you dive into the details of Analysis Manager, you need to be familiar with the terms used within this tool. Throughout this article, I'll be using examples from the Foodmart2000 sample database that comes with Analysis Services 2000.
Analysis Services dimensions are based on dimension tables in the SQL Server database and describes the fact in some way. For example, the sales cube includes product, time, gender, promotion media, and other dimensions that let you slice the sales data accordingly. Within Analysis Services, you can have either shared or private dimensions—shared dimensions can be used within multiple cubes, whereas private dimensions are specific to a particular cube.
Dimension levels are columns of the dimension table that are used for breaking down report data. For example, the product dimension within the sales cube has the product family, product department, product category, product subcategory, brand name and product name levels. Similarly the time dimension has levels of year, quarter and month.
Dimension members are individual values within a dimension level. For example, the time dimension has members 1997 and 1998 in the year level. Product family level has drink, food, and nonconsumable members.
Measure is a (typically) numeric fact presented on the report or analyzed through an OLAP tool. Sales cube has the following measures: unit sales, store cost, store sales, sales count, and store sales net.
A calculated member is a special type of measure, derived from other existing measures, which can potentially involve calculations within the cube.
A member property is an attribute of a dimension member that you expose within your cube to provide additional information to your users. Store name level of the store dimension contains three member properties: store manager, store square feet, and store type.
Aggregations are precalculated summary values that are generated each time a cube is processed. Aggregations make end-user queries very fast; however, the more aggregations you design the longer it takes to process the cubes. Any structural changes to the cube require full processing of the cube, which involves rebuilding aggregations. Because some cubes take a very long time to process, you need to be careful about changing dimension structures or adding measures to a processed cube.