Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Defining Role Playing Dimensions for SQL Server Analysis Services


By:   |   Read Comments (1)   |   Related Tips: > Analysis Services Dimensions

Problem

In the webinar I presented entitled Taking on your First SQL Server Business Intelligence Project, a couple of questions from the audience asked about defining and reusing dimensions, particularly the date dimension. These reusable dimensions are also known as role-playing dimensions because the single dimension can play multiple roles in the Analysis Services cube.

Solution

Fortunately, the solution to this problem is simple, and as an added bonus, we can also reuse a dimension's defined hierarchies. Reuse will also save time and effort when developing new or modifying existing Analysis Services solutions. For this tip, I will be using objects provided with the Adventure Works DW 2012 Multidimensional Standard Edition tutorial.

In the cube's Data Source View within the Analysis Services Visual Studio project, we need to make sure that relationships between fact table foreign key columns and dimension table primary key columns are defined. If a primary/foreign key relationship is already defined between the source database tables, then the relationship will be defined automatically in the cube's data source view. The image below shows the three relationships defined between the date columns in the FactInternetSales and DimDate tables in the Adventure Works DW.dsv data source view.

Defining Role Playing Dimensions in SQL Server Analysis Services

We can see these relationships defined in the AdventureWorksDW2012 database using the Object Explorer in SQL Server Management Studio.

Object Explorer in SQL Server Management Studio

If the relationship is not defined in the database, then the relationship can be specified in the data source view as shown below.

the relationship can be specified in the data source view

Existing relationships can also be edited in the data source view.

Existing relationships can also be edited in the data source view

We will now create our own example. First we will create and populate a small fact table using the T-SQL provided below.

USE [AdventureWorksDW2012]
GO
Drop TABLE [dbo].[FactMSSQLTipsExample]
go
CREATE TABLE [dbo].[FactMSSQLTipsExample]
(
   [ExampleKey] [int] NOT NULL,
   [SalesAmount] [money] NOT NULL,
   [OrderDateKey] [int] NOT NULL,
   [ShipDateKey] [int] NOT NULL,
   [DueDateKey] [int] NOT NULL,
   CONSTRAINT [PK_FactMSSQLTipsExample_ExampleKey] PRIMARY KEY CLUSTERED 
   (
      [ExampleKey] ASC
   )
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
   ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FactMSSQLTipsExample] WITH CHECK 
ADD CONSTRAINT [FK_FactMSSQLTipsExample_OrderDateKey_DimDate_DateKey] FOREIGN KEY([OrderDateKey])
REFERENCES [dbo].[DimDate] ([DateKey])
GO
ALTER TABLE [dbo].[FactMSSQLTipsExample] 
CHECK CONSTRAINT [FK_FactMSSQLTipsExample_OrderDateKey_DimDate_DateKey]
GO
ALTER TABLE [dbo].[FactMSSQLTipsExample]  WITH CHECK 
ADD CONSTRAINT [FK_FactMSSQLTipsExample_ShipDateKey_DimDate_DateKey] FOREIGN KEY([ShipDateKey])
REFERENCES [dbo].[DimDate] ([DateKey])
GO
ALTER TABLE [dbo].[FactMSSQLTipsExample] 
CHECK CONSTRAINT [FK_FactMSSQLTipsExample_ShipDateKey_DimDate_DateKey]
GO
ALTER TABLE [dbo].[FactMSSQLTipsExample]  WITH CHECK 
ADD CONSTRAINT [FK_FactMSSQLTipsExample_DueDateKey_DimDate_DateKey] FOREIGN KEY([DueDateKey])
REFERENCES [dbo].[DimDate] ([DateKey])
GO
ALTER TABLE [dbo].[FactMSSQLTipsExample] 
CHECK CONSTRAINT [FK_FactMSSQLTipsExample_DueDateKey_DimDate_DateKey]
GO
insert into dbo.FactMSSQLTipsExample values (1,14.99,20100101,20100102,20100107)
insert into dbo.FactMSSQLTipsExample values (2,24.99,20100202,20100203,20100208)
insert into dbo.FactMSSQLTipsExample values (3,34.99,20100303,20100304,20100309)
insert into dbo.FactMSSQLTipsExample values (4,44.99,20100404,20100405,20100410)
insert into dbo.FactMSSQLTipsExample values (5,54.99,20100505,20100506,20100511)
insert into dbo.FactMSSQLTipsExample values (6,64.99,20100606,20100607,20100612)
GO

After executing the above code, Object Explorer in SQL Server Management Studio gives us a visual representation of the new table and its keys.

Object Explorer in SQL Server Management Studio gives us a visual representation of the new table and its keys

After creating the table FactMSSQLTipsExample, we can add the table to the existing data source view in Visual Studio.

add the table to the existing data source view in Visual Studio

Because we defined our foreign keys in the script used to create the table, the relationships are automatically defined in the Analysis Services project data source view.

the relationships are automatically defined in the Analysis Services project data source view

Now we can use the Cube Wizard to create a new cube.

Now we can use the Cube Wizard to create a new cube

Select "Use existing tables" and then click on Next.

Select "Use existing tables" and then click on Next

On the Select Measure Group Tables page, we will choose FactMSSQLTipsExample and then click on Next.

choose FactMSSQLTipsExample and then click on Next

On the Select Measures page, we will choose the Sales Amount and the Count.

choose the Sales Amount and the Count

On the Select Existing Dimensions page, we will choose the existing Date dimension that already has the Fiscal and Calendar hierarchies defined. Click on Next.

choose the existing Date dimension that already has the Fiscal and Calendar hierarchies defined

The Completing the Wizard page of the Cube Wizard allows us to name the cube and to verify the measures and dimensions to be used in the cube.

name the cube and to verify the measures and dimensions to be used

Looking at the Cube Structure tab in Visual Studio for the cube we just created, we can see the three role-playing dimensions for the dates and their hierarchies.

we can see the three role-playing dimensions for the dates and their hierarchies

Looking at the Dimension Usage tab in Visual Studio for our MSSQLTipsExample cube, we can see the how the date dimension is being reused for Order, Due and Ship Dates.

we can see the how the date dimension is being reused for Order, Due and Ship Dates

After deploying and processing the cube, we can browse the cube in SQL Server Management Studio. In the example below, we are slicing our measures by the Order Date's Fiscal Quarter.

browse the cube in SQL Server Management Studio

The Calendar and Fiscal hierarchies we see when we browse the cube, are the same dimensional hierarchies that are defined in Visual Studio.

The Calendar and Fiscal hierarchies we see when we browse the cube
Next Steps
  • Experiment with reusing other dimensions such as employee and geography.
  • Read through our Analysis Services Tutorial.
  • Bookmark the SQL Server Analysis Services Glossary for future reference.


  • Last Update:






    About the author
    MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

    View all my tips


     









    Post a comment or let the author know this tip helped.

    All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

    *Name    *Email    Notify for updates 


    Get free SQL tips:

    *Enter Code refresh code     



    Wednesday, September 30, 2015 - 7:50:45 AM - Martin Chaine Back To Top

    Hi,

    I am able to use my dimension as a role-playing dimension but if I create a dimension from my Fact table I have a strange issue. I think I know the cause but I can't find how to achieve what I need.

    My FactTransfers has these keys:

    WarehouseFromKey
    WarehouseToKey

    In my DSV, they are both linked to DimWarehouse. In my cube I can easily define DimWarehouse as a role-playing dimension and everything works fine. For some reasons I don't want to have a "Warehouse From" and a "Warehouse To" dimension in my cube. I already have a "Transfer Detail" dimension (built from FactTransfers) containing TransferNo,WarehouseFrom and WarehouseTo.

    If I don't set a NameColumn on WarehouseFrom and WarehouseTo, the keys are shown and it works fine. If I set their NameColumn to be DimWarehouse.Description, it always show the one related to WarehouseFrom. It looks like despite my key being FactTransfers.WarehouseToKey, the NameColumn always use FactTransfers.WarehouseFromKey to go get the warehouse description from DimWarehouse.

    Example:

    If I don't set a NameColumn, it shows this:

    WarehouseFrom = 10 (which is FactTransfers.WarehouseFromKey). DimWarehouse.Description for 10 is "3rd street warehouse"
    WarehouseTo = 29 (which is FactTransfers.WarehouseToKey). DimWarehouse.Description for 29 is "1st avenue warehouse"

    But if I set WarehouseFrom.NameColumn and WarehouseTo.NameColumn to DimWarehouse.Description, it will show this:

    WarehouseFrom = 3rd street warehouse
    WarehouseTo = 3rd street warehouse

    I understand how role-playing dimension works inside a cube but when defining attributes inside a dimension I want the NameColum to show the corresponding warehouse description but since both keys are referencing to DimWarehouse.WarehouseKey, it seems to always use FactTransfers.WarehouseFromKey.


    Learn more about SQL Server tools