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

Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

3 posters

Go down

Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables Empty Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

Post  bandik Thu Jul 01, 2010 11:22 am

Its a new capital markets data warehouse in design stage.

I have

deal_deatils_fact - 1 row per deal with all measures at deal level - dealID (Degenerative DIemsnion - DD)
deal_tranche_fact - 1 row per deal per tranche with all measures at the tranche level (TrancheID - DD, the dealID from above - the parent key)
deal_product_fact - 1 row per deal per product with all measures at product level (productID)

There is a one to many relationship between deal_deatils_fact and deal_tranche_fact and deal_deatils_fact and deal_product_fact
many to many relationship between deal_tranche_fact and deal_product_fact . There are identifiers to connect between dealdetails, deal product and deal tranche.

The problem I am encounetring is - In a single report - measures from all the above fact tables are required as separate columns (excel report). with dealID - we can get the measures by joining between the three fact tables - but I read every where that it is not a good practice.

What would be the alternate solution for - joining between the fact tables to extract the data for the report ?

should I using multiple sql queries - 1 sql to extract the list of dealIDs for the search criteria entered by user and separate sqls to extract the tranche and product level measures passing list of dealIDs as filter criteria.

Is this the correct design approach ?

Thanks so much in advance.
Bandi







bandik

Posts : 4
Join date : 2010-06-24

Back to top Go down

Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables Empty Re: Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

Post  ngalemmo Thu Jul 01, 2010 11:26 am

The basic technique is to query each fact separately, summarizing on conforming dimensions, then combine the results using either joins of the summarized result sets or a summarized UNION ALL of the same.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables Empty Re: Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

Post  kap Tue Jul 06, 2010 2:38 am

If query performance is not a big issue then joing all tables is an easy and acceptable solution. One query is easy to maintain and number of possible errors is less with less code. If query performance is an issue of course depends on many things such as your users expectations, number of rows in the tables, hardware, indexes, overall load and more.

kap

Posts : 4
Join date : 2010-07-05

Back to top Go down

Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables Empty Re: Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

Post  ngalemmo Tue Jul 06, 2010 10:56 am

kap wrote:If query performance is not a big issue then joing all tables is an easy and acceptable solution. One query is easy to maintain and number of possible errors is less with less code. If query performance is an issue of course depends on many things such as your users expectations, number of rows in the tables, hardware, indexes, overall load and more.

Note that since relationships between fact tables are many-to-many, a direct join will not work. Different facts must be summarized to the same grain before they can be joined.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables Empty Re: Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

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