Learning SSAS with Me –Role Playing Dimension

Introduction

There are another concept of Dimension is called Role Playing dimension. A single dimension is playing different type of role depending on scenario.

In this article we are trying understand the concept behind the Role Playing Dimension in my own way.

Hope it will be interesting.

To understand it properly, we are taking a multi-dimensional scenario.

Case Scenario

We have a dimension table and a Fact table (not a proper Dimension or Fact table used for example purpose only)

Dimension Table:DIMTime

DateKey (Pk)	MonthKey	EngMonth	Year
2017-01-01	1	January	2017
2017-01-02	1	January	2017
2017-01-03	1	January	2017
2017-01-04	1	January	2017
2017-01-05	1	January	2017
2017-01-06	1	January	2017
2017-01-07	1	January	2017
2017-01-08	1	January	2017
2017-01-09	1	January	2017
2017-01-10	1	January	2017

Fact Table:FACTSalesDetails

OrderNo	OrderDate (Fk)	SaleDate (Fk)	ShippingDate (Fk)	Amount
Ord-1	2017-01-01	2017-01-02	2017-01-03	120000.00
Ord-2	2017-01-02	2017-01-03	2017-01-04	45000.00
Ord-3	2017-01-04	2017-01-05	2017-01-06	900000.00

The relationship exists in Fact and Dimension Table.
The Primary key of Dimension named [DIMTime].[DateKey]have foreign key relation with all the foreign key of fact table.

The relationship is defined as:

[DIMTime].[DateKey]àFk Relation à [FACTSalesDetails].[OrderDate]

[DIMTime].[DateKey] àFk Relation à [FACTSalesDetails].[SaleDate]

[DIMTime].[DateKey] àFk Relation à [FACTSalesDetails].[ShippingDate]

Hope you understand the case scenario.

How the Role Playing Dimension Generated

Now we are creating a Database Dimension by using our dimension table named DimTime. When we are going to add this Database Dimension in our Cube Dimension the single Dimension table have showing three copy. One for Order Date, One for Sale Date and One for Shipping Date.

CREATE TABLE [dbo].[DIMTime]

  (

      [DateKey]   DATE         NOT NULL PRIMARY KEY,

      [MonthKey]  INT          NOT NULL,

      [EngMonth]  VARCHAR(50)  NOT NULL,

      [Year]      INT          NOT NULL

  );

GO

CREATE TABLE [dbo].[FACTSalesDetails]

  (

      [OrderNo]      VARCHAR(50)     NOT NULL,

      [OrderDate]    DATE            NOT NULL, 

      [SaleDate]     DATE            NOT NULL,

      [ShippingDate] DATE            NOT NULL,

      [Amount]       DECIMAL(18,2)   NOT NULL

  );

GO

ALTER TABLE [dbo].[FACTSalesDetails]

ADD CONSTRAINT Fk_OrderDate FOREIGN KEY([OrderDate])

REFERENCES [dbo].[DIMTime]([DateKey]);

GO

ALTER TABLE [dbo].[FACTSalesDetails]

ADD CONSTRAINT Fk_SaleDate FOREIGN KEY([SaleDate])

REFERENCES [dbo].[DIMTime]([DateKey]);

GO

ALTER TABLE [dbo].[FACTSalesDetails]

ADD CONSTRAINT Fk_ShippingDate FOREIGN KEY([ShippingDate])

REFERENCES [dbo].[DIMTime]([DateKey]);

GO

INSERT INTO [dbo].[DIMTime]

VALUES

('2017-01-01',1,'January',2017),

('2017-01-02',1,'January',2017),

('2017-01-03',1,'January',2017),

('2017-01-04',1,'January',2017),

('2017-01-05',1,'January',2017),

('2017-01-06',1,'January',2017),

('2017-01-07',1,'January',2017),

('2017-01-08',1,'January',2017),

('2017-01-09',1,'January',2017),

('2017-01-10',1,'January',2017);

GO

INSERT INTO [dbo].[FACTSalesDetails]

VALUES

('Ord-1','2017-01-01','2017-01-02','2017-01-03',120000.00),

('Ord-2','2017-01-02','2017-01-03','2017-01-04',45000.00),

('Ord-3','2017-01-04','2017-01-05','2017-01-06',900000.00);

GO

Why the Role Playing Dimension Generated

If we look at the case scenario, we can understand it properly.

The Dimension Table (DimTime) single columns (Datekey) has the relationship with three different columns of Fact Table (FACTSalesDetails) named Order Date, Sale Date and Shipping Date.

So, one Date key columns of Dimension Table is specified for three roll of Fact Table.

Role-1:

Dimension Table have to work with Fact Table to satisfy all the Query related to Sales Order Date.

Role-2:

Dimension Table have to work with Fact Table to satisfy all the Query related to Sales Date.

Role-3:

Dimension Table have to work with Fact Table to satisfy all the Query related to Sales Shipping Date.

So a single dimension is playing a multiple role.

If we look at the SQL Query

FROM [FACTSalesDetails]

INNER JOIN [DIMTime] ON [FACTSalesDetails].[OrderDate]= [DIMTime].[DateKey]
INNER JOIN [DIMTime] ON [FACTSalesDetails].[SaleDate]= [DIMTime].[DateKey]
INNER JOIN [DIMTime] ON [FACTSalesDetails].[ShippingDate] = [DIMTime].[DateKey]

Hope you understand it.

Posted by: MR. JOYDEEP DAS