Monday, 20 March 2017

Learning SSAS with Me - Attributes Hierarches

Introduction
Here we are going to discuss about another important tropic of SSAS called attributes Hierarchies.

As we all know that the Dimension have Attributes and we can make hierarchies on it. The question that developer ask is when and how we made it.

We have to understand the purpose of making Attributes Hierarchies and also know the type of Attribute Hierarchies that we made.

There are lot of types of Attribute Hierarches, if you consult any books but we preferred to broadly classified them into two categories

o   Natural Hierarches
o   Non-Natural Hierarchies

In our view this two type is enough to understand the SSAS Hierarchies.
Before going to further discussion let’s understand why we use Attribute Hierarchies and how it help us in terms of SSAS Cube Querying by MDX query.

What the Use of Dimension Attributes Hierarches
As we all know that the CUBE is a combination of Dimension, Dimension Attributes and relationship with Measures Group measures.

Cube Definition:

Dimension Attributes ß[Relation ] à Measure Group Measures

Note: To know more about relation, please refer to my previous article named:
Learning SSAS with Me – Fact Relationship Type

When we make a query in CUBE we used MDX query or retrieve data from CUBE, Attribute Hierarchies improve the performance of data retrieval.

Now we have to understand HOW?

For that we are considering a Dimension named Date Dimension. We hope that everyone must know about Date Dimension and why this special dimension is used.
So, we have One Fact table related to Sales Order Details and a Date Dimension.


Fact Table:FACTSalesOrderDetails

SalesOrderNo
SOLineNo
OrderDateKey
OrderDate
ItemCode
ItemRate
ITemQty
Amount
SO-1
1
20170101
2017-01-01
101
500.00
10
5000.00
SO-1
2
20170101
2017-01-01
102
700.00
20
14000.00
SO-1
3
20170101
2017-01-01
103
200.00
30
6000.00
SO-2
1
20170205
2017-02-05
102
700.00
12
8400.00
SO-2
2
20170205
2017-02-05
103
200.00
15
3000.00
SO-3
1
20170307
2017-03-07
104
150.00
11
1650.00
SO-3
2
20170307
2017-03-07
101
500.00
23
11500.00
SO-3
3
20170307
2017-03-07
102
700.00
60
42000.00
SO-3
4
20170307
2017-03-07
103
200.00
12
2400.00

Dimension Table: Date Dimension

Which contains Date Key, Month, Quarter and Year

Now think about the situation where we are using the MDX query to retrieve the total sales amount for the Year Wise, Quarter Wise or Month Wise.

What it exactly do –Basically it use the Date Key to retrieve records and make aggregate of facts.
·         If we make Query for Year wise, it retrieve all records by Date Key wise and make aggregate.
·         If we make Query for Quarter wise, it retrieve all records by Date Key wise and make aggregate.
·         If we make Query for Month wise, it retrieve all records by Date Key wise and make aggregate.

For all the cases it directly access the Date Key and with huge volume of records the MDX query takes Date (Performance of MDX Query or Data retrieval is decreased).
So it basically decrees the performance of Query and hence the Attribute Hierarchies comes into the picture.

The Logical Structure of Attribute Hierarches:

Date key
Aggregate - Month-1
Aggregate - Quarter-1
Aggregate -Year
Date key
Aggregate - Month-2
Date key
Aggregate - Month-3
Date key
Aggregate - Month-4
Aggregate - Quarter-2
Date key
Aggregate - Month-5
Date key
Aggregate - Month-6
Date key
Aggregate - Month-7
Aggregate - Quarter-3
Date key
Aggregate - Month-8
Date key
Aggregate - Month-9
Date key
Aggregate - Month-10
Aggregate - Quarter-4
Date key
Aggregate - Month-11
Date key
Aggregate - Month-12

If we look at the above diagram, we can see the

·         Month is the Total Aggregate result of specified date key (Combining 29/28/30/31 day key according to month).
·         Quarter is the Total Aggregate result of specified Month (Combining 3 month).
·         Year is the Total Aggregate result of specified Quarter (Combining 4 Quarter)
So if the Hierarchies exists no need to combining all the date key to get the result for Year/Quarter/Month. For a Year we just simply make the summation Aggregation of 4 Quarter.

So, we understand that the Hierarchies is used to increase the performance of Data retrieval from Cube.


Now we are going to discuss about the Type of Hierarchies.

Type of Attribute Hierarchies
As we told before, the beginning of the article that in our brad classification we can classified Attribute Hierarchies into 2 types.

o   Natural Hierarchies
o   Non-Natural Hierarchies.

Let’s go one by one to understand it properly.

The Natural Hierarches
Here we already define the Natural Hierarches. The definition that we think which best match is

The Natural Hierarches is the Hierarches where all the Attributes are tightly related. We can see it in Dimension named Date dimension

Date key à Month à Quarter à Year

If we go to the another example to understand it, let’s see the Geography Hierarches
City Name à State Name à Country Name

The level of the Attributes Hierarchies can be varies.

One-Level Attribute Hierarchies: Where the Attribute is Non-Aggregatable. It is always natural in nature because it is nothing to relate.

Two-Level Attribute Hierarchies: It is marked as Aggregatable. First level is ALL level and it’s relates to any other Attributes.

Variable numbers of Level: it natural as well and called Parent-Child



The Non-Natural Hierarches
It is just opposite to the Natural Hierarchies where attributes are not at all tightly related. If we take an example of Gender and Age of Dimension Employee.

This type of Hierarchies is built only for display purpose and it decreases the performance of data retrieval from Cube.


Employee
Gender
Age
Designation


Now it’s time to implement it

We have to create a Date Dimension to demonstrate it properly.


Script to Generate Date Dimension Table Objects
There is a good article to create Date Dimension
Create and Populate Date Dimension for Data Warehouse

Now Create the Dimension
Here we are using named query in data source view

SELECT  [DateKey], [Date], [DayName], [Month], [MonthName],
        [Quarter], [QuarterName], [Year], [YearName]
FROM   [dbo].[DimDate];













Hope you understand it.



Posted by: MR. JOYDEEP DAS

1 comment:

  1. Good to hear from you on SSAS. will lean another tools

    ReplyDelete