I was working on a dimension hierarchy lately and hit at this interesting setup which I thought of sharing with you guys. It was all about creating a proper conformed dimensional hierarchy and setting up granularity for facts at different grains. It seemed quite simple though, but I felt its THE hurdle one has to certainly pass through during cube design. So, here it goes
My Product dimension :I have this product dimension, with a natural hierarchy Product -> SubCategory -> Category.
My Sales Fact :
We record the products sold by each rep for a day in our sales fact table. So, its grain is Product*Date.
My TargetSales Fact :
Every Sales rep has some fixed targets set at the SubCategory level for a week which we enter into our Target Sales Fact table. So, its grain is SubCategory*Week which is at one level higher in product and period hierarchies.
All I am trying to get here is, to set up the product dimension as a conformed dimension to the Sales and TargetSales facts and analyze my numbers with this dimension.
A bit of a relational context, I have mapped the following relationships in my DSV.
SalesFact.Date = Period.Pk_Date
SalesFact.Product_Code= Product.Product_CodeTargetSales Fact :
TargetSales.Week_Name = Period.Week_Name
I processed the cube, but was unable to analyze my sales at SubCategory(Desc) as it just repeats the total target figures for all subcategories as below.
However, I could still analyze my data with SubCategoryCode as below
Later I realized that, the data is being loaded using SubCategoryCode and I have not created a relationship between SubCategoryCode and SubCategory. So, I did one as below
This new relationship has certainly brought in my figures back, as below
However, I am still not convinced that this is a proper design as there are multiple roll up paths in the hierarchy, which even BIDS keeps complaining of.
So, I figured out another simple, but basic point of dimensional dimension, SubCategoryCode as being the key of the SubCategory level.
So, Set up the Production dimensional hierarchy with just the description columns, with the Product as dimensional key as below
And, I set the KeyColumn for SubCategory as SubCategoryCode and Name Column for SubCategory as SubCategory in the properties dialog
And, Set up the granularity of the Target Figures is the cube’s Dimensional Usage now to SubCategory level as below
After these steps, now I can analyze my numbers at the Product Hierarchy as below
This approach even solves another issue of exposing the surrogate key to the users unnecessarily due to the design. So by keeping the surrogate keys as ‘Key’ and Description as ‘Name’ properties, we can give a clean dimension to the users for slice and dice.
Note : There might be many ways of resolving the same issue and I would appreciate any positive feedback/comments in this approach.