Learning SSAS with Me – Fact Relationship Type

Introduction

After a long time I decide to write something which is very important related to SSAS. I decided to write several articles which are most important in SSAS and need clear understanding of It before building any cube.

In this article we are going to cove a complete understanding of Fact Relationship Type in SSAS. E hope this article is interesting to all of you and provides a solid knowledge of Fact Relationship Type.

Before going to it, we assume that the reader have full understanding of Data Source (DS), Data Source View (DSV), CUBE, Dimension, Dimensional Attributes, Facts, measures and measures group. The reader also needs to understand the different type of schema like Star schema and Snow flex schema.

Data warehouse

 Here we are creating a simple data warehouse to establish and understand relationship.

CREATE TABLE [dbo].[DIMProduct]

  (

    ProductID        INT           NOT NULL PRIMARY KEY,

    ProductName      VARCHAR(50)   NOT NULL,

    ProductGroupID   INT           NOT NULL

  );

GO

INSERT INTO [dbo].[DIMProduct]

   (ProductID, ProductName, ProductGroupID)

VALUES

(1, 'Tooth Paste', 101),

(2, 'Tooth Brush', 101),

(3, 'Saving Loation', 102),

(4, 'Savink Kits', 102);

GO

CREATE TABLE [dbo].[DIMProductGroup]

  (

    ProductGroupID   INT           NOT NULL PRIMARY KEY,

    ProductGroupName VARCHAR(50)   NOT NULL

  );

GO

INSERT INTO [dbo].[DIMProductGroup]

   (ProductGroupID, ProductGroupName)

VALUES

(101, 'Dental Product'),

(102, 'Saving Product');

CREATE TABLE [dbo].[FACTSalesOrder]

  (

       SalesOrderNo  CHAR(50)      NOT NULL,

       OrderLineNo   INT           NOT NULL,

       ProductID     INT           NOT NULL,

       QtySold              INT           NOT NULL,

       ProductRate   DECIMAL(18,2) NOT NULL,

       Amount               AS (QtySold*ProductRate)

  );

GO

ALTER TABLE  [dbo].[FACTSalesOrder]

ADD CONSTRAINT PK_FACTSalesOrder PRIMARY KEY(SalesOrderNo, OrderLineNo);

INSERT INTO [dbo].[FACTSalesOrder]

       (SalesOrderNo, OrderLineNo, ProductID, QtySold, ProductRate)

VALUES

('SO-1', 1, 1, 50, 125),

('SO-1', 2, 2, 60, 250),

('SO-1', 3, 3, 10, 650),

('SO-2', 1, 3, 20, 650),

('SO-2', 2, 4, 10, 980);

SELECT * FROM [dbo].[DIMProduct];

ProductID   ProductName                                        ProductGroupID

----------- -------------------------------------------------- --------------

1           Tooth Paste                                        101

2           Tooth Brush                                        101

3           Saving Loation                                     102

4           Savink Kits                                        102

SELECT * FROM [dbo].[DIMProductGroup];

ProductGroupID       ProductGroupName

--------------       --------------------------------------------------

101                  Dental Product

102                  Saving Product

SELECT * FROM [dbo].[FACTSalesOrder];

SalesOrderNo	OrderLineNo	ProductID	QtySold	ProductRate	Amount
SO-1	1	1	50	125	6250
SO-1	2	2	60	250	15000
SO-1	3	3	10	650	6500
SO-2	1	3	20	650	13000
SO-2	2	4	10	980	9800

Understanding Fact Relationship Type

What we understand a CUBE is nothing but relationship between Dimension and Measure Groups. We have to understand the relationship very well to make a perfect CUBE.

There are 6 type of relationship exists, mentioned bellow and we have to understand one by one

o   No Relationship

o   Regular

o   Fact

o   Referenced

o   Many-to-Many

o   Data Mining

No Relationship

The No Relationship means there is no relation between Dimension and Measure groups.

To understand it, let’s take an example.

We have a Dimension of Employee with attributes Employee ID and Employee name and we have a measure group called Sales which conations measures named Order Id and Sales amount. So there is no relationship exists between the Employee Dimension and Sales measures Group.

To avoid browsing unrelated cube objects we can use the measures group property named IgnoreUnrelatedDimensions.

Regular

The Regular relationship is the most common type of relation. It is one-to-many relation. In our example the relationship between DIMProduct and FACTSalesOrder has one-to-many relation.

Dimension Table : DIMProduct

ProductID	ProductName	ProductGroupID
1	Tooth Paste	101
2	Tooth Brush	101
3	Saving Loation	102
4	Savink Kits	102

Here the all the product have the Unique value and there is no Duplicate or NULL value exists. The Primary Key Exists on Product ID columns.

Fact Table : FACTSalesOrder

IDNo	SalesOrderNo	ProductID	QtySold	ProductRate	Amount
1	101	1	50	125.00	6250.00
2	101	2	60	250.00	15000.00
3	101	3	10	650.00	6500.00
4	102	3	20	650.00	13000.00
5	102	4	10	980.00	9800.00

The Dimension table named DIMProduct is a Foreign Key Relation with Fact Table Named FACTSalesOrder. The relationship is One-to-Many. That means One product can be exists into multiple Sales Order.

If we go to the SQL Statement

SELECT b.ProductID,

       b.ProductName,

       SUM(a.Amount) AS Amount

FROM   [dbo].[FACTSalesOrder] AS a

       INNER JOIN [dbo].[DIMProduct] AS b

             ON a.ProductID=b.ProductID

GROUP BY b.ProductID,

         b.ProductName;

ProductID   ProductName                                        Amount

----------- -------------------------------------------------- ---------

1           Tooth Paste                                        6250.00

2           Tooth Brush                                        15000.00

3           Saving Loation                                     19500.00

4           Savink Kits                                        9800.00

Fact

It is little bit confusing. The fact relationship is also used when the measure group is also used as dimension.

To understand it properly, we are going to an example. Please look at the fact table

SalesOrderNo	OrderLineNo	ProductID	QtySold	ProductRate	Amount
SO-1	1	1	50	125	6250
SO-1	2	2	60	250	15000
SO-1                         \	3	3	10	650	6500
SO-2	1	3	20	650	13000
SO-2	2	4	10	980	9800

Here the Sales Order Number from Fact table we can use like a Dimension table. What we want to mean that there is no separate table for Sales Order but we need to make a Dimension using our existing Fact table named FACTSalesOrder.

So Our New Dimension tables look like

SalesOrderNo	OrderLineNo
SO-1	1
SO-1	2
SO-1	3
SO-2	1
SO-2	2

If we go to the SQL Statement

SELECT a.SalesOrderNo,

       SUM(a.Amount) AS Amount

FROM   [dbo].[FACTSalesOrder] AS a

       INNER JOIN (SELECT SalesOrderNo, OrderLineNo

                      FROM   [dbo].[FACTSalesOrder]) AS b

             ON a.SalesOrderNo=b.SalesOrderNo

                   AND a.OrderLineNo=b.OrderLineNo

GROUP BY a.SalesOrderNo;

           

SalesOrderNo                                       Amount

-------------------------------------------------- ---------------------------------------

SO-1                                               27750.00

SO-2                                               22800.00

Referencing

In this relation the specified dimension is not directly related to Measure group or fact table. We are taking an example to understand it.

Here the Dimension name DIMProductGroup is not Directly related with Fact table named FACTSaleOrder. It is related by another dimension named DIMProduct.

If we go to the SQL Statement

SELECT b.ProductName,

       c.ProductGroupName,

       SUM(a.Amount) AS Amount

FROM   [dbo].[FACTSalesOrder] AS a

       INNER JOIN [dbo].[DIMProduct] AS b ON a.ProductID=b.ProductID

          INNER JOIN [dbo].[DIMProductGroup] AS c ON b.ProductGroupID=c.ProductGroupID

GROUP BY b.ProductName,

         c.ProductGroupName;

ProductName	ProductGroupName	Amount
Tooth Brush	Dental Product	15000.00
Tooth Paste	Dental Product	6250.00
Saving Loation	Saving Product	19500.00
Savink Kits	Saving Product	9800.00

Many-to-Many

Using a Many-to-Many relationship is done when a data warehouse design implements a bridge table between dimensions to appropriately represent all combinations of data. 

Generally there are more than one fact table is used to established the relationship. To understand it properly, we are taking an simple example of Author and Published book

Dimension Name: DIMAuthor

AuthorID	AuthorName
1	Priya Bannerjee
2	Deblina Bhat
3	Joydeep Das

Dimension Name: DIMBook

BookID	BookName
101	Lets Learn VC++
102	All about SSIS
103	Basic IT

Fact Table: FACTBookSales

BookID	SalesAmt
101	2500
102	5000
103	200

Fact Tbale: FACTAuthorBook

AuthorID	BookID
1	101
2	102
3	103
1	102
2	103

Now What the Relation is

Database Structure

CREATE TABLE DIMAuthor

    (

          AuthorID     INT PRIMARY KEY,

          AuthorName   VARCHAR(50)  NOT NULL

    );

INSERT INTO DIMAuthor

VALUES

(1,    'Priya Bannerjee'),

(2,    'Deblina Bhat'),

(3,    'Joydeep Das');

CREATE TABLE DIMBook

     (BookID    INT PRIMARY KEY, 

      BookName  VARCHAR(50) NOT NULL);

INSERT INTO DIMBook

VALUES

(101, 'Lets Learn VC++'),

(102, 'All about SSIS'),

(103, 'Basic IT');

CREATE TABLE FACTAuthorBook

    (AuthorID INT NOT NULL,

     BookID   INT NOT NULL);

-- FK Creation

ALTER TABLE FACTAuthorBook

ADD CONSTRAINT FK_AuthorID FOREIGN KEY(AuthorID)

REFERENCES DIMAuthor(AuthorID);

ALTER TABLE FACTAuthorBook

ADD CONSTRAINT FK_BookID FOREIGN KEY(BookID)

REFERENCES DIMBook(BookID);

INSERT INTO FACTAuthorBook

VALUES

(1,    101),

(2,    102),

(3,    103),

(1,    102),

(2,    103);

CREATE TABLE FACTBookSales

   (BookID    INT           NOT NULL,

    SalesAmt  DECIMAL(18,2) NOT NULL);

-- FK Creation

ALTER TABLE FACTBookSales

ADD CONSTRAINT FK_BookIDBookSales FOREIGN KEY(BookID)

REFERENCES DIMBook(BookID);

INSERT INTO  FACTBookSales

VALUES

(101,  2500),

(102,  5000),

(103,  200);

DSV Structure

Now Create the Dimension for DIMAuthor and DIMBook

Posted by: MR. JOYDEEP DAS