BI Schemes
Greetings,
When we work with ERP/CRM databases or some other transactional system, we meet database that usually normalized and designed for fast writing in it.
It is one of the main purposes of that systems, it is being able to write many data/transactions very fast.
On the other hand, in BI we are building schema than can be read from it very fast, it is in a highly de-normalized state, which is why the dimensional modeling is very common.
The classic schema of dimensional modeling is Star Schema. Star Schema contains fact table and dimension tables. Fact tables:
Contains measurements or metrics for a specific subject, for example, if the fact is orders, it will contain quantity, prices and discount. Dimension tables: Usually small tables that contains attributes to describe the fact table.You can see it as questions to: What? Where? When? Who?There can be many dimensions, but most common is Time Dimension, Product Dimension, Customer Dimension and many more. Besides the star schema, there is also a snowflake schema.
Snowflake schema, just like star schema only more normalized on dimensions.
Snowflake and Star Schema (actually I call them just a star schema even when it a snowflake) comes often in data warehouses, but what happens when your model contains multiple facts?
In Qlik products:
There is 2 techniques:
Concatenate fact tables
Key Table
I always work with the key table, even when there is no need, because in the future, connect another fact table to key table is much easier.
In Power BI: You can also use one of the previous techniques, but also you can use cross filter directions on relationships.
Anyway, in future posts i will show the best way to deal with multiple fact tables in qlik and power bi. In addition, I will share how I build date dimension and much more.
Feel free to contact me on any of those channels:
YouTube
