How to Create an SSAS 2008 Hierarchy


Let me explain the with a simple date dimension example first. Let me also assume you are familiar on how to create a data source, then a data source view, and then create a cube with cube wizard. Then comes the attribute part. In SSAS 2008 BI Studio, only the key column will come by default as an attribute for a dimension. So we can see sk_DateID as a single dimension. Double clickon the date dimension from the solution explorer, then you may have three panes ( in a tree view) . Attribute , Hierarchy and Data source view.

In the data source view, all the available columns from the dimension will be displayed. Right click on any columns and click –New attribute from Column.This will create an attribute to the dimension. We can see it gets added in the attribute pane. Lets add MonthName, YearNumber And QuarterNumber .Right Click on the MonthName attribute – properties. Change the Key column attribute. Add MonthNumber and YearNumber to the collection. Change the Namecolumn to MonthName and Value column to monthNumber.

Rightclick on the quarter attribute , Change the key column to QuarterNumber and YearNumber. Name column and Value Column to QuarterNumber. So now we have set the Attributes. Next step is to create attribute relationship. Take the “Attribute relationship” tab. Rt click on the sk_DateID . Click on New relationship, Select DateValue from the rightside combobox in the dialogue box. Give Ok.

Right click on DateValue capsule box – New relationship, Select MonthNumber, Repeat the same for linking MonthNumber with quarterNumber and QuarterNumber with YearNumber. Finally the attribute relationship page will like below image.

Now go back to dimenion Structure tab. Drag Year attribute to the hierarchy tab, Then drag quarter just below, then month, then date. Now we are ready with data dimension with a simple hierarchy and attributes.

Advertisements

One thought on “How to Create an SSAS 2008 Hierarchy

  1. doesn’t this give a Duplicate attribute error for Quarter? Quarter No would repeat for months Jan, Feb, March – 1 for e.g. ?

    Reply

Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s