Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 3, 2002

Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube - Page 4

By William Pearson


Making the Cube Multi-Dimensional: Creating Meaningful Perspectives in our Cube

Each of the _id columns we saw in the last dialog box represented dimensions -- in the form of key columns that might be joined to dimension tables from the fact table in the MyFoodMart star schema. These joins provide a means of allowing us to pull in more details about the dimensions, and, in effect, act as the "link" between fact and dimensions. Much power lies in this concept, and a more in-depth look at the relational concepts surrounding, and the physical attributes and capabilities of, joins might be of interest at another time. Suffice it to say that we now have the opportunity to select existing dimensions -- or to even create new ones -- thus allowing us significant flexibility within the standard operations of the Cube Wizard -- or more accurately, within a second, "child" wizard, the Dimension Wizard.

From the "Select Dimensions" dialog that appeared when we clicked Next at the measures selection step shown above (Illustration 11), we will now click New Dimension at the dialog box we encounter next, shown below (Illustration 12). This results in a launch of the Dimension Wizard, as shown immediately next (Illustration 13), whose Welcome dialog can also be skipped going forward (with the check box), should we choose to short circuit the Welcome dialog later.



Illustration 12: Click New Dimension to Start the Dimension Wizard




Illustration 13: The Welcome Dialog for the Dimension Wizard


Creating a Dimension from a Star Schema: A Single Dimension Table

We click Next for now, and arrive at a dialog that asks how we want to create the current dimension. Important, contextually sensitive information is displayed for each of the five possible options. For purposes of our examination of the basics, we'll accept the default, Star Schema, as shown below in Illustration 14.



Illustration 14: The Dimension Wizard offers Five Options for Dimension Creation


Clicking Next at this juncture brings us to the initial prompt to Select the Dimension Table, from which we will select region. Many attributes of the region dimension appear as details in the right pane, as shown below in Illustration 15, and, as with the Fact Table Selection dialog in earlier steps, we can verify our choices by reviewing a snapshot of actual data using the Browse Data... button.



Illustration 15: We Select the Region Table as a Source for our First Dimension


We click Next, and the Wizard prompts us to Select the Levels for ... the Dimension we have chosen, when such levels exist. We can see that various hierarchical levels might be of interest within the region dimension. We select five for this dimension, sales_country, sales_region, sales_state_province, sales_district, and sales_city, then we attempt to rank them in what we think is the logical hierarchy, for optimal reporting purposes, as shown below in Illustration 16.



Illustration 16: At the Prompt, we select sales_country, sales_region, sales_state_province, sales_district, and sales_city


Attempting to rank these in logical order, from summarized to detailed, might be a bit confusing. Knowing the data well is the best defense, but say we weren't sure and made an attempt to move district above province (there are fewer districts than provinces, as the former are, in reality, a subset of the latter). The Wizard warns us that the arrangement might not be the most logical, and provides a valuable tip regarding the best order for reporting purposes, as depicted in Illustration 17. We decide to follow the implied advice, and abandon the contemplated re-sort with a click of the Yes button. The Wizard places levels in the order that appears most logical (small to larger, from the top down, in this case) as a result.



Illustration 17: The Dimension Wizard Offers Advice regarding potential errors in logic


Once we are returned to the Dimension Selection dialog, clicking Next takes us through the Specification of Member Keys dialog (see Illustration 18) -- which broaches topics beyond the scope of our simple overview -- then through the Advanced Options dialog (Illustration 19), a topic for discussion later in our series, which we will leave at default setpoints.



Illustration 18: The Specification of Member Key Columns dialog




Illustration 19: The Select Advanced Options dialog of the Dimension Wizard


We next arrive at the Finish the Dimension Wizard dialog, a preview point, where we name the Dimension "Region", simply enough. Using the "+" sign to the left of the topmost level of the dimension tree to expand the levels listed, we can preview the hierarchies that will be created (and check our design, to some extent), as shown in the following picture (Illustration 20). We will leave the rest of the setpoints at default for now, and click Finish.



Illustration 20: The Finish the Dimension Wizard Dialog


We return to the Cube Wizard, where we see immediately that Region appears in the list of Cube Dimensions, as shown below (Illustration 21).



Illustration 21: Back at the Cube Wizard, we see Region as a Listed Dimension


Page 5: Creating a Dimension from a Snowflake Schema




MS SQL Archives

Comment and Contribute

 


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

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM