Forum Discussion
Choose my data model! multiple fact tables
Ok, I have built a couple of different data models with the same fact tables, but want to see what is the best method.
Context -- I have multiple fact tables, where 5 of them have an account number, date, and some value with a category that splits the value (so one table has account number, date, application used and the corresponding value of volume under that application)
I started with a many to many relationship scheme, with the survey data being the central table that all other Volume/revenue, calls etc.. falls into. This was fine, but i couldnt look at a help desk categories corresponding VnR, as they all had to go through the "central" survey table. one this i did in the many to many was concecate the account number and date together to tie values by the date and specific
I am trying this method now, where there is a dim table for account numbers, and another date key that all are linking it too. Is this the right method, or should i use the many to many example.
Star schema doesnt work for me, as the dim tables will have duplicate account numbers due to the multiple categories/apps that each account falls in
highlighted tables only have dates, and no account numbers. should i do this? or is there a better way
7 Replies
Hello Sut_Datanaut ,
definately not the many to many, always try to make the relationships one to many from the dim to the fact.
yeah the many to many was causing issues.
Lets say I have a table that has account number, date, and service desk cases by origin (phone, email, chat). another table has volume and revenue by account number, date and application (one account number can have multiple applications that all have specific totals per app)
How would I make a dim table out of these? I cant make it a single account number per row, without either pivoting the origin column (so phone would be a column, chat etc..) or choosing the top 1 for each category for each account.
Ideally, I would be able to see VnR by phone origin through the account numbers.
I would like to
you need to get the fields that you want to filter the fact tables with that are common in the fact tables and make dim tables out of them.
in the dim table they shouldn't be duplicates of these fields, so you remove the duplicates and then link the to the facts.
