Feedback on data model with multiple fact tables

[QlikMo]

Hello,

I was hoping to get some feedback on my data model which includes four fact tables - orders, invoices, jobs and dispatches. I decided to use a link table with a type field as well as separate calendars and a common calendar.

I would like to know if there is a better alternative to this, especially when we move to Qliksense and users will be doing their own analysis. For now it's no problem for me as I'm the only developer creating dashboards but I want to start getting into the mindset of creating the best data model for end user simplicity.

One of my worries in the current format is that the user might think to do sum(OrderCounter) to get a count of orders using dimension Date,  which would bring in wrong results. Instead it should be sum({<DateType={'Order'}>} OrderCounter) to get the correct results. 

Should the fact tables be merged into one? Order, job and dispatch have a 1-1 relationship but there could be multiple invoices for an order.

I would appreciate any comments

Thanks

[Mark_Little · Accepted solution]

Hi @QlikMo 

I think you find with a model like this there is always going to be complication when you have end users trying to work it, this will come down to education. 

For me I would have combined the Fact tables in to a single table with a Transaction Type, Then i would have created a canonical calendar. 

[QlikMo]

Hi @Mark_Little  

Thank you very much for your reply. It is good to know that the users will need some education. There were some presumptions here that the model could be created in a way where the end users would need minimal understanding. Of course that may well be the case with a single fact table. 

I will have a go at combining them like you mentioned with a canonical calendar.

[Mark_Little]

It is best to try and always keep it as simple as possible, but some model just have complexities.