Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Huge fact table, is it a way to go?

Is it possible to build a large fact table, but to maintain all logical connections between tables that were put into the large fact table?

If the answer is true, how to maintain connections in QV?

If I have a huge amount of synthetic keys or circular references, is one large table a way to go?

Thanks,

Marko

4 Replies
mov
Not applicable

Huge fact table, is it a way to go?

Marko,
The proper data model design is a key when you build a more or less complex QlikView application.
Im general, it most cases it makes sense to have a star schema with one, usualy large, fact table, and many dimensions tables which are connected to the fact table with one key field. If you have synthetic keys or circular references, not mentioned "a huge amount", something is not right.

Not applicable

Huge fact table, is it a way to go?

I was trying to think of a hypothetical situation when I was making that post Smiley Happy I'm not really sure how are fact tables (in a traditional BI sence) handled in QV. In theory, you could put all measures and dimensions in one fact table without much changinge performance, but could you then use that table to generate reports like you would with a "normal" star schema (the one you mentioned above, fact table in the middle and dimension tables around)?

mov
Not applicable

Huge fact table, is it a way to go?

Marko,
The short answer will be "yes", the long one is "yes, but..."Smile
The major problem with one table is that all your many-to one and many-to-many relations will multiply many of your "real" fact rows, so the results of sums and counts will be wrong, unless you do something (maybe rather complex) about it.

Oleg_Troyansky
Not applicable

Huge fact table, is it a way to go?

Marco,

generally speaking, "fewer tables" work better in QlikView than many smaller tables, however I wouldn't go as far as loading all of your data into one huge table. De-normalization is good, as long as it makes sense.

For truly complex data models, we often force concatenation of various tables into one "fact table", to avoid synthetic keys and circular references. If that is what you call "one fact table", than - yes, I'd say this is the best way to approach a complex data model, as long as you know what you are doing and avoid data duplication and other possible side effects.

cheers,

Oleg