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.
We can see these relationships defined in the AdventureWorksDW2012 database using the 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.
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.
After creating the table FactMSSQLTipsExample, we can 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.
Now we can use the Cube Wizard to create a new cube.
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.
On the Select Measures page, we will 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.
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.
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.
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.
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.
The Calendar and Fiscal hierarchies we see when we browse the cube, are the same dimensional hierarchies that are defined in Visual Studio.
Next Steps
