Microsoft SQL Server Knowledge Bank: Learning SSAS with Me

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

http://sqlknowledgebank.blogspot.in/2017/03/learning-ssas-with-me-fact-relationship.html

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.