Home > SSAS > SSAS #23 – Implement dimensional hierarchy in SSAS 2008

SSAS #23 – Implement dimensional hierarchy in SSAS 2008

Let’s continue from my previous post, SSAS #22 – Dimensional Hierarchy Contained in a Single Dimensional Table (Star Schema).

I need to assume that you have done all the following so far:

  • Created an Analysis Services Project in BIDS
  • Created a Data Source to point to your star schema relational database (with proper connection string and impersonation information)
  • Created a Data Source View with the fact table(s) and dimension table(s) you need for your project.

Step 1 – Create a dimension in Dimension Wizard

In my example, I need to create a dimension DimTreatment, with one key and 2 attributes.

image

Right click on Dimensions folder, and select New Dimension…

image

Dimension Wizard will pop up. Select Use an existing table to create the dimension.

image

Step 2 – Select a Key column and a Name Column

In the next Specify Source Information window, select your dimension table. The key column is automatically picked up by the wizard because the key column is defined in the DSV. The key column is also the default for the Name column. Because I do not have a description column for the key column, so leave the key column as the default for the name column.

image

Step 3 – Select dimension attributes

In the Select Dimension Attributes window, make sure you select all the attributes you need from your dimension table.

image

Step 4 – Dimension Structure Tab – Showing Attributes without Hierarchies

This is the final result from the Dimension Wizard. It shows all the dimensional attributes you have selected. No hierarchical structures yet.

If you are a very observant person, you will see a blue squiggly under the name of the dimension. Pointing the cursor to it.

"Create hierarchies in non-parent child dimensions."

image

This is one of the many Best Practice Warnings in SSAS 2008. The warning is saying that you need to create some sort of hierarchies in your dimension table. As a best practice, that is.

Step 5 – Create a hierarchy

We are happy to oblige in the case. We do need a hierarchy between attributes, Treatment and TreatmentType. We also hope that the blue squiggly will go away,

Simply drag attribute TreatmentType to the Hierarchies tab, and Treatment as well. Also rename the hierarchy name to something meaningful rather than the default Hierarchy.

image 

You will probably get annoyed now, because we are getting more blue squiggly now. This one is under the name of the hierarchy.

“Attribute relationships do not exist between one or more levels in this hierarchy. This may result in decreased query performance.”

This Best Practice Warning is telling us to create attribute relationships.

Before I take a break, I just want to show you a snapshot of the new visual Attribute Relationships tab in the Dimension Designer in SSAS 2008.

image

I’ll continue in the next blog post to discuss why we need to create attribute relationships and how to do it in SSA 2008.

  1. Raghu
    June 13, 2011 at 12:35 pm

    How are you?
    Is conditional visibility is possible in SSRS Parameters?
    I am using Dropdown button in the report, in the dropdown there are two fields, 1)CostCentre 2)CostCentreName. I did create one parameter for the CostCentreName (My purpose is, when i select CostCentreName from the dropdown, the parameter has to show the list of CostCentreNames), it is working fine.But, my manager asked me, when the user select “CostCentre”, it has to display one textbox to enter the CostCentre number.Can you please provide me suitable solution to display the textbox and then user has to enter CostCentre number.
    Many thanks in advance.
    Kind Regards

    Like

  2. Sherry
    June 15, 2011 at 6:49 pm

    Hi,

    Sorry about the late reply. I gave you an alternative in this post.

    https://bisherryli.wordpress.com/2011/06/15/ssrs-57-alternatives-to-hide-a-parameter-dynamically/

    Sherry

    Like

  1. No trackbacks yet.

Leave a Reply

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

%d bloggers like this: