Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Alternative views of data

3 posters

Go down

Alternative views of data Empty Alternative views of data

Post  jchamizo 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

Back to top Go down

Alternative views of data Empty Re: Alternative views of data

Post  BoxesAndLines Tue Nov 17, 2009 10:55 pm

Create a summary table.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Alternative views of data Empty Re: Alternative views of data

Post  jchamizo 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

Back to top Go down

Alternative views of data Empty Re: Alternative views of data

Post  Vincent Rainardi 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

http://www.datawarehouse.org.uk

Back to top Go down

Alternative views of data Empty Re: Alternative views of data

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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