Prior to the advent of cable, when you brought home a new television, the first order of business was to manually tune in one of the few existing local channels. To accomplish this you manipulated the dials, rabbit-ear antennae positioning, and other controls to eventually obtain an optimal picture, audio, and vertical hold configuration. The process of designing a data warehouse using Analysis Services 2005 is similar to this. Analysis Services 2005 provides you with various wizards that help you build the initial framework, just like the rotary tuner on the television got you close to the desired channel. With the basic infrastructure in place, some fine-tuning can optimize the initial framework to your needs. In fact, you saw this approach in the previous chapter when you learned about creating data sources and DSVs. Likewise, here you learn creating dimensions using the Dimension Wizard and then using the Dimension Designer to fine-tune the dimension based on your business needs.
Cubes are made of dimensions and measures where the measures are aggregated along each dimension. Without an understanding of dimensions and how measures are aggregated along dimensions, you can't create and exploit the power of cubes, so let's jump right in learning about building and viewing dimensions. Once the dimensions are created they need to be added to the cube and the right relationship type between the fact data and dimension needs to be defined. Analysis Services 2005 supports six relationship types which you learn in this chapter and Chapter 8. In addition you learn the attributes and hierarchies that form an integral part of dimensions. You learn to model the Time dimension and Parent-Child dimensions in Analysis Services 2005 which are unique from regular dimensions and quite often found in many data warehouses. Finally, you find out how to process and browse the dimensions.
Dimensions help you to define the structure of your cube so as to facilitate effective data analysis. Specifically, dimensions provide you with the capability of slicing data within a cube, and these dimensions can be built from one or more dimension tables. As you learned in Chapter 1, your data warehouse can be designed as a star or snowflake schema. In a star schema, dimensions are created from single tables that are joined to a fact table. In a snowflake schema, two or more joined dimension tables are used to create dimensions where one of the tables is joined to the fact table. You create both of these dimension types in this chapter.
You also learned in Chapters 1 and 3 that each dimension contains objects called hierarchies. In Analysis Services 2005 you have two types of hierarchies to contend with: the attribute hierarchy, which corresponds to a single column in a relational table, and multilevel hierarchies, which are derived from two or more attribute hierarchies where each attribute is a level in the multi-level hierarchy. A typical example of an attribute hierarchy would be zip code in a Dim Geography dimension, and a typical example for a multilevel hierarchy would be Country-State-City-Zip Code also in a Geography dimension. In everyday discussions of multilevel hierarchies, most people leave off the "multilevel" and just call them "hierarchies."
For the exercises in this chapter, you use the project you designed in Chapter 2. If you don't happen to have the project handy, you can download it from http://www.wrox.com. If you download or not, you will still need to add the Geography Dimension (dbo.DimGeography) to the DSV. To add this dimension to your DSV follow the steps below.
Double click on the DSV named "AdventureWorksDW.dsv" in Solution Explorer.
Click on the "Add/Remove Objects" icon (top left icon in the DSV Designer) as shown in Figure 5-1.
In the Available objects list, select dbo.DimGeography and click on the > (right arrow) button as shown in Figure 5-2. This will move the Geography dimension into Included objects lists in the Add/Remove tables dialog. Click OK to continue.
Now you are ready to explore use of the Dimension Wizard in Analysis Services 2005. The Dimension Wizard is capable of scanning the dimension table(s), detecting relationships between columns within the table, and suggesting hierarchies for the formation of the dimension. The following exercise shows you how to create a dimension using the Geography table in the Adventure Works DW data source. To do so, follow the steps below:
Launch the Dimension Wizard by right-clicking Dimensions in the Solution Explorer and selecting New Dimension… as shown in Figure 5-3. If the welcome screen of the Dimension Wizard opens up, click Next.
You will see the Select Build Method screen, as shown in Figure 5-4. You can create dimensions from a data source or from pre-existing templates provided by Analysis Services. In this chapter we're concerned with creating dimensions from a data source; dimensions created from templates are detailed in Chapter 8. By default the Dimension Wizard uses the "Auto build" cube option to create dimensions from data sources. When the Auto build option is enabled, the wizard retrieves a sample of data, detects relationships, and then suggests potential hierarchies for that dimension; but it is up to you to evaluate the Auto build. If you expect to create your own hierarchies and fine tune them then you can disable the Auto build option in this page. You can also choose to create attributes and hierarchies or just attributes by changing the selection in the drop-down list. For now, accept the default options and click Next.
In the Select Data Source View page (shown in Figure 5-5) you need to select the DSV for creating the dimension. By default the first DSV in your project is selected. Because your current project has only one DSV the Adventure Works DW DSV is selected. Click the Next button to process to the next page of the Dimension Wizard.
On the dimension type selection page of the Dimension Wizard (see Figure 5-6) you see three options: Standard Dimension, Time Dimension, and Server Time Dimension. A Standard dimension can be modified to become any sophisticated dimensional variant and makes for a great generic starting point. A Time dimension, on the other hand, is a unique type of dimension typically created from a table that contains time information such as year, semester, quarter, month, week, and date. A Time dimension is unique because its members are fixed (a year always has 12 months in it) and typical business analyses are performed over time. Due to the uniqueness of the Time dimension and how it is used for business analysis, there are special MDX functions that can be used along with time dimension. Furthermore, aggregation of data on a time dimension does not have to be a garden variety aggregation like sum or count.
Most business decision makers want to analyze their data across a time dimension to understand the month with maximum sales for a quarter or other time frame. Analysis Services provides you a distinct way to aggregate measure data across a time dimension; this is done with semi-additive measures. You learn more about semi-additive measures in Chapter 9. In a Time dimension, several hierarchies are commonly used, such as fiscal year and calendar year, for example; both of which can be built automatically. And without any associated tables in the data source either! To do so you need to use the Server time dimension. You learn about Server time dimension in Chapter 8. Select the Standard Dimension option and click Next.
In the "Select the Main Dimension Table" screen, you need to select the main table from which the dimension is to be designed. If a dimension is to be created from a star schema, the dimension is created from the single pertinent table. A snowflake schema dimension actually contains several tables, one of which is the primary table of the dimension. This primary table is chosen as the main table in the "Main table" selection screen of the Dimension Wizard. Select the DimGeography table from the Main table drop-down list as shown in Figure 5-7 and click Next.
At this point, the Dimension Wizard analyzes the DSV to detect any outward-facing relationships from the DimGeography table. An outward-facing relationship is a relationship between the DimGeography table and another table, such that a column in the DimGeography table is a foreign key related to another table. Figure 5-8 shows that the wizard detected an outward relationship between the DimGeography table and the DimSalesTerritory table. If you want to model the selected main table as a star-schema table, deselect any of the tables shown on that screen. You can try this out in the Geography dimension you are creating now. Make sure the DimSalesTerritory table is deselected and click Next.
As shown in Figure 5-9, the Dimension Wizard selects the columns of all the table(s) that have been selected for the dimension you're creating. Each selected column on this screen results in an equivalent attribute being created in the new dimension. Even though you are building a dimension here, this dimension is going to be part of a cube (which is described by the Unified Dimensional Model). The UDM combines the best of the relational and OLAP worlds — one of the important uses of the relational model is the ability to query each column for reporting purposes. The columns in the relational table are transformed to attributes of a dimension that can then be used for querying from the UDM. Select all the attributes and click Next.
Analysis Services 2005 is aware of some common dimension types often used in business intelligence applications. These common dimension types have dimension attributes associated with them that typically form levels of a hierarchy within the dimension. In the Dimension Wizard you can choose the dimension type and specify the columns in the table that correspond to the dimension attributes. In the Specify Dimension Type drop-down menu shown in Figure 5-10, the default dimension type selected is Regular, though several other types are supported, some of which can be seen in the figure. The Regular dimension type does not have predefined dimension attributes. If you click a different dimension type such as Organization or Promotion you will see the dimension attributes that are commonly associated with it. You can select the dimension attributes and specify the column in the data source that corresponds to them. When you complete the wizard, these dimension attributes will be defined and you will be able to see them in the Dimension Designer. For designing the Geography dimension, select the Regular dimension type and click Next.
The next screen in the Dimension Wizard is to define Parent-Child relationship within a dimension. A commonly used example of a Parent-Child relationship is readily seen in any organizational chart; it is the relationship between managers and their direct reports. You learn more about Parent-Child relationships later in this chapter. There is no Parent-Child relationship in the DimGeography table. You don't need to make any change here, so click Next.
On the Detecting Hierarchies screen, the wizard gets a sample of the data from the data source and scans for relationships between the columns in the table. If one-to-many relationships are detected within this sample, the wizard suggests hierarchies. The attributes that form the different levels within the hierarchies will be detected and shown to you on the following screen. The wizard gives you the option of including or excluding the detected hierarchies; in this example the Dimension Wizard does not detect any such hierarchies (see feedback from the Dimension Wizard after it analyzed the DimGeography table as shown in Figure 5-11). Hence the following screen does not have any hierarchies. Click Next on this screen, and click Next again on the following screen of the wizard.
The final screen of the Dimension Wizard summarizes all the attributes and hierarchies that you selected (see Figure 5-12). If there were hierarchies included, you will see another folder called Hierarchies under the dimension in this screen. Click the Finish button.
The wizard has created the dimension object Dim Geography and opens it up within the Dimension Designer. Congratulations!!! You have successfully created your first dimension using the Dimension Wizard. Next, you learn the various parts of the Dimension Designer and how to enhance the dimension as per your business needs.