Alternative views of data

Hi!!!

I have some data that have a particular granularity that I store in our Data Mart, but when I have to do most of the reports using the Data Mart I have to use a different granularity. For the input granularity I am using a Hierarchical Dimension, but in the output another Hierarchical Dimension must be used. How can I model this situation?

Thanks and best regards,
jchamizo

Create a summary table.

But how can I model the fact table with these different hierarchies, one in the loaded data and another for reporting purposes.

Thanks,
jchamizo

Model the fact table based on the lower granularity (the input grain), then build the levels of the output dim into input dim, by relating the attributes. For example:
the fact table has only 1 dim key and 1 measure: fact1(dimkey1, measure1)
the input dim has 2 attribute, attr1 on level1 and attr2 on level2: dim1(dimkey1, attr1, attr2)
the output dim has 2 attribute, attr3 and attr4: dim2(dimkey2, attr3, attr4)
We relate (map) attr3 and 4 to dimkey1, and build dim1 as: dim1 (dimkey1, attr1, attr2, attr3, attr4)
Then it's ready for reporting now: you can analyse measure1 by attr3 and 4.

Scenario 2: the input dim has no relation what so ever with the output dim. If this is the case, then build the fact table on the combined (lower) grain, i.e. the grain of the fact table is the combination of both input and output dim. We have to know the breakdown of the measure in terms of the output dim. For example,
the (input) fact table has only 1 dim key and 1 measure: fact1(dimkey1, measure1)
say the rows are:
dimkey1 measure1
1 50
2 60

now we build the combined grain fact table that I mentioned:
dimkey1 dimkey2 measure1
1 1 25
1 2 25
2 1 20
2 2 20
2 3 20
we have to know that (for example) each member of dim2 attribute is taking equal proportion, hence 50 is divided equally by 2 and 60 is divided equally by 3.
Now we can report/analyse measure1 on dim2 attributes.