In this article, I am going to walk you through the steps in creating a date hierarchy in SQL Server Analysis Services 2008. I am using the AdventureWorksDW2008R2 database if you are going to follow along. I am assuming that you have already created your shared data source, your data source view and built your cube using the FactInternetSales fact table and all related tables. If you’ve done this, fire up your Analysis Services project in Business Intelligence Development Studio (BIDS) and let’s get crackalackin!
Open up your Date Dimension. You should have something similar to what I have below.
Next, drag the columns CalendarYear, CalendarQuarter, EnglishMonthName and FullDateAlternateKey into the Attributes area as shown in the green square below.
Now that you have your Attributes dragged over, what we want to do is make these names a little easier on the eyes. Slowly click twice on each of the columns and rename them to Year, Quarter, Month and Date (Full Date Alternate Key). You should see something similar to the below screenshot.
Alternatively, you could have selected the attribute above, hit F4 to show the properties window and altered the Name property. Now is the fun part. Let’s create the hierarchy. First off, a hierarchy allows the users consuming the cube to drag over one object that will encase all of the attributes in the hierarchy with the relationship drill down already completed. What we need to do is drag the columns over from the Attribute section shown in the red square above into the Hierarchies pane shown in the oranges square. The results should look something like the below.
I did alter the name of the hierarchy by slowly clicking it. This will give it a little better name as a reference. Now what we need to do is click on the Attribute Relationships tab as shown in the highlighted section above. By clicking on the Attribute Relationship tab, what we are going to do is create the relationship between child and parent. To do this, all you have to do is drag the parent onto the child all the way up the top. In other words, when you drag Date on top of Month, you’ll see something like shown below.
Next, drag Month on top of Quarter, then Quarter on top of Year. You should have something similar to the below screenshot.
This concludes this article on creating a date hierarchy using SSAS 2008.
Until next time, “keep your ear to the grindstone” – Good Will Hunting
Brian K. McDonald, MCDBA, MCSDBusiness Intelligence Consultant – Pragmatic Works Consulting
Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network
Convert with DTS xChange | Develop with BI xPress | Process with TaskFactory | Document with BI Documenter