Huge fact table, is it a way to go?

[#1]

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

[Anonymous]

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.

[#3]

I was trying to think of a hypothetical situation when I was making that post 🙂 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)?

[Anonymous]

Marko,
The short answer will be "yes", the long one is "yes, but..."
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]

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