Sunday 19 March 2017

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

4 comments:

  1. Can anyone identify what is fact-less fact table from this post.

    ReplyDelete
  2. Factauthorbook is a factless fact table. Thanks joydeep for publishing nicely topic on ssas.its very helpful for ssas guys.

    ReplyDelete
  3. Hi Sir, is this related to Tabular modeling or MDX ..?

    ReplyDelete