Is it ok to have 2 dimensions that are the same but one is less deep?

Question

I have a fact table, with account number and some numbers associated..

I have my DimAccount which has a very long hierarchy of level1,sub-level2… up to sub-level20.

When reporting in PowerBI this makes it very hard to navigate…

My requirement is to have a sort of different/new DimAccount which is less deep (it will be similar to DimAccount but with a different grouping)

So, I want to create a different mapping. Where should this be done?

In the backend?

• Having some sort of DimAccount2, where it has less hierarchies or
• Creating new table? Perhaps creating a mapping table, where I just map sublevels to a less deep hierarchy?

Or should this be corrected in the cube/powerbi ? creating measures in DAX where one does the mapping manually there?

I am not sure where/how to do it... My Goal is to have a DimHighLevelAccount, but it is not that I just can remove sub-levels, the mapping will be also different, perhaps I group some categories from level5,6 and 7 together...

Answer 1

Power BI always has its own data model (called a "dataset" in Power BI docs), derived in this case from the data model in your data warehouse. And the Power BI data model has some modeling capabilities that your DW does not have.

So the Power BI data model should load/expose only the tables and columns from your data warehouse that are useful for the use case (you may have a handful of different Power BI datasets for the same DW tables). And then add additional modeling, like adding Measures, hiding columns, and declaring Hierarcies.

So in this case, have a single Account dimension table, but when you bring it in to Power BI, leave out hierarchy levels that you don't want, and add the remaining ones to a Hierarchy and hide the individual levels from the report view, so the report developer sees a single hierarchal property.