Alternative views of data

View previous topic View next topic Go down

Alternative views of data

Post  jchamizo on Tue Nov 17, 2009 11:22 am

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

jchamizo

Posts : 2
Join date : 2009-11-17

View user profile

Back to top Go down

Re: Alternative views of data

Post  BoxesAndLines on Tue Nov 17, 2009 10:55 pm

Create a summary table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Alternative views of data

Post  jchamizo on Wed Nov 18, 2009 12:56 pm

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

Thanks,
jchamizo

jchamizo

Posts : 2
Join date : 2009-11-17

View user profile

Back to top Go down

Re: Alternative views of data

Post  Vincent Rainardi on Thu Nov 19, 2009 7:53 pm

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.

Vincent Rainardi

Posts : 6
Join date : 2009-02-03
Location : London

View user profile http://www.datawarehouse.org.uk

Back to top Go down

Re: Alternative views of data

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum