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 5

By William Pearson


Creating a Dimension from a Snowflake Schema: Multiple, Related Dimension Tables

Since the product_id key in our fact table is joined to a series of Product Dimension tables, and because each of these tables contains columns that are needed to define the respective levels of a multiple-tier hierarchy, we will need to approach this slightly more complex scenario from the perspective of a snowflake schema dimension. With the exception of defining joins, and a few additional steps, the Dimension Wizard certainly comes to our assistance in making the process straightforward and efficient.

We begin, once more, by clicking the New Dimension button in the Cube Wizard, where we left it in the previous example. We click Next at the Dimension Wizard's Welcome screen, and then select the Snowflake Schema radio button at the Choose How You Want to Create the Dimension dialog, as shown below in Illustration 22. We are given additional information about the choice we have made (in the Description area at the bottom of the dialog), and then we click Next.



Illustration 22: Choosing Snowflake Schema at the Choose How You Want to Create the Dimension Dialog


We will select the product and product_class tables, as shown below (Illustration 23), then click Next to continue to the Create and Edit Joins dialog, where we can see the joins (relationships) that exist between the tables we have selected.



Illustration 23: Selecting the product and product_class Tables to Define Dimensions




Illustration 24: The Create and Edit Joins Dialog, where we can Review / Edit Table Relationships


The join between the two tables we have chosen appears appropriate at the product_class_id field, as shown in Illustration 24 above. Data types and other considerations need to be reviewed, or problems will almost certainly result later, but in our simple scenario, the join depicted is as it should be. (For more information on deleting or creating new joins, or on the criteria that dictate correct definition and placement of these relationships, see the online documentation, as well as the Analysis Services volume of the MSSQL Server Reference Library, for starters). Click Next to move forward.

We again select levels for the dimension at the next dialog, with warnings again given if we seem to be placing the levels out of order. We appropriately order them as shown below (Illustration 25) from most summarized to most detailed, from top to bottom product_category, product_subcategory, and brand_name.



Illustration 25: Selecting Levels for the Product Dimension


We click Next, again skipping the Specify Member Key Columns for our present purposes, as well as passing over Advanced Options as beyond our immediate scope in this session.

Finally, we arrive at the Finish ... dialog, and enter Product in the Dimension Name box, and leave the other options with defaults selected, as shown in Illustration 26.



Illustration 26: Finishing the Dimension Design for the Product Dimension


After clicking Finish, we again arrive at the Cube Wizard dialog, where, as illustrated below (Illustration 27), we see the Product Dimension is now a member of the Cube Dimensions list.



Illustration 27: The Product Dimension now appears in the Cube Wizard Dimension List


Page 6: Adding a Time Dimension




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