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

View previous topic View next topic Go down

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

Post  bandik on 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

View user profile

Back to top Go down

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

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

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

Post  kap on 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

View user profile

Back to top Go down

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

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

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

View previous topic View next topic Back to top

- Similar topics

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