topic Re: 13 Fact Tables: Concat, join, link . . . or resign?! ;-) in QlikView

13 Fact Tables: Concat, join, link . . . or resign?! ;-)

bullish35 — Mon, 25 Apr 2016 08:00:25 GMT

Hello Community,

I spent many sleepless nights building a data model to accommodate 13 fact tables. During unit test it became clear that the associations needed were not working with the link table I drafted..So, back to the drawing board. Anyone have ideas about how to manage the data in the attached mocked up tables?

Regards,

Ellen

Added in-progress files.

Re: 13 Fact Tables: Concat, join, link . . . or resign?! ;-)

marcus_sommer — Mon, 25 Apr 2016 13:06:49 GMT

It would be require to dive very deeply into your tables to be able to give you concrete advices - therefore it's more generic with the main-goal to reduce the number of fact-tables and if possible to avoid any link-tables and rather to create table-associations.

For this I would concatenate such fact-tables like sales and planning - you could harmonize this by creating a table-source field like 'sales' and 'planning' and the values are within a field value - but even without them and leaving an asymmetrical table with NULL's will work fine. Maybe the coupon-table and the prescription-table could be (after a crosstable-transforming) be concatenated, too.

Other fact- and maybe also dimension-tables would I try to join or to map to these fact-table. I'm not sure if it's possible and also useful to try to get everything in one big fact-table. I mean with them that I have the impression that your datamodel might be in reality two or more datamodels and it could be sufficient to leave them as loosely coupled datamodels (maybe associated with only a date and one further key) to get your required views between them (and be cautious to match not the wrong dimensions within your charts).

- Marcus

Re: 13 Fact Tables: Concat, join, link . . . or resign?! ;-)

bullish35 — Wed, 27 Apr 2016 04:02:00 GMT

Thank you for your response. I am making progress! It looks like I am going to need a calendar bridge, too. Can I have a link table and a calendar bridge in the same model? Or, should I include the date field dimension in my link table?

Re: 13 Fact Tables: Concat, join, link . . . or resign?! ;-)

marcus_sommer — Wed, 27 Apr 2016 05:24:46 GMT

In general you could have a calendar-bridge and a link-table but it could be that's more suitable to use several calendars and/or to match them into a canonical-calendar:

Canonical Date

Why You sometimes should Load a Master Table several times‌

- Marcus