Star-Scheme always best method?

chriys1337 — Sun, 13 Jan 2019 16:55:48 GMT

Hi,

I am thinking about the statement, that a star scheme is always best Datamodel approach in QlikView.

In my concrete scenario I have a fact table, with a connected dimension table Company, where all Companies are listed. In a third table, all Company Countries are listed. Therefore this third table is connected to my second dimension Company.

Would this not make more sense, when I am going with this snowflake scheme there would be for each Country just one entry, and not for each Company one Country entry.

Please advise.

Re: Star-Scheme always best method?

rwunderlich — Mon, 14 Jan 2019 18:12:53 GMT

I would not agree with the statement that a Star Schema is always best for Qlik. It depends on how you want the model to behave and what problem you are trying to solve.

In a typical database system, storing the Country field in a separate table would be most efficient for storage as it would eliminate redundant entries. Qlik stores data differently, so this redundancy issue is lessened or non-existent in some cases.

A star schema generally provides the best balance of storage and CPU usage at runtime. However, I would argue that in many cases, it's not worth spending time getting to a star.

My first rule is code for clarity and simplicity. Make the model easy to understand and use, and minimize the time you spend on creating and maintaining the script.

Considering your example. The separate Customer and Company tables are likely the way the data exists in your source system. Keeping it the same is easy to write the script and understand the model. What if I want a count of countries that purchased each product? Count(Country); easy. If I join Company to Customer I have to change to Count(DISTINCT Country); more complex.

We may be able to say that reducing the number of "hops" in a data model by going from snowflake to star would improve chart calc time by about 10%. If a chart now takes 50 milliseconds to calc and we reduce it to 45, is that important? Was it worth the effort? If I have 50 daily users with 30 clicks each, it it probably not significant.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

topic Re: Star-Scheme always best method? in QlikView

Star-Scheme always best method?

Re: Star-Scheme always best method?