zaterdag 28 januari 2012

SSAS : Error with many-to-many dimension and level of granularity

Introduction
In this blogpost a brief explanation of an error i've encountered during a implementation of a 'many2many relationship' in a SSAS cube. Although i haven't found the rootcause of this problem i do want to share with you the solution. I did only found one relevant website of Melissa Coates with the same sort of problem. So hopefully this will help the MS BI community a bit futher.
 
Problem
In my former post i've worked out an example with Referenced Dimension relationship and a Many2Many relationship. As you have seen in this blogpost everything worked fine but when i implemented this solution at my client i got the following error(off course i replaced the names used at the client with the names used in the example) :

Errors in the metadata manager.
The 'Category' many-to-many dimension in the 'Orders' measure group requires that the granularity of the 'OrdersDD' dimension is lower than that of the 'OrderLines' measure group

Solution
The situation at the client is much more complex than the labsituation that i've descibed in the blogpost. I have about 15 measure groups and about 75 dimensions. So debugging the problem is hard to do. I had a couple options to debug this problem:
  1. Downgrade the complete solution to a certain level where the problem is more manageable. Break everything out of the solution that is not needed for understanding the problem. Off course use a copy of your development environment ;-).
  2. Trail and error. In this approach you tweak the solution a bit and see whether the error disappears.
  3. Reproduce error in labsituation. As already said, í've created a labsituation where everythings works fine. A manner could be to change some settings in the project and see whether the error appears.
I decided to take a gamble on option 2 and found a solution very quickly, very fortunately!!

The solution was to change the Fact dimensionship relationship of the OrdersDD dimension with the Orders Measure group to a regular dimension relationship.

Conclusion
A short post about a problem i've encountered during implementation of a many2many relationship in a SSAS Cube. I've presented a solution that helped solving the error. Currently testing whether everything works fine but everythings seems working very fine. 

Greetz,
Hennie

Geen opmerkingen:

Een reactie plaatsen