Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I am a new in Qlik Sense. I meet an issue of data modeling in my work. Business Background: We have two order tables that are respective CY ORand PY OR. In business scope. We have district structure that Territory→Branch→Office. Each year, we adjusted the structure a little, like maybe office A belongs to Branch AA in last year, but this year, it maybe belongs to Branch BB. So, the district structure in CY and PY order table, it doesn't completely same. Q: When I design the modeling, Is it necessary to build a district structure table as a dimension table to connect with the fact tables. Besides, for PY district structure, we don't has a record. I have to distinct the structure in PY OR table to obtain. Kindly appreciate for your reply.
The officially recommended data-model is a star-scheme by merging (concatenating and/or mapping/joining) all facts within a single fact-table and n dimension-tables. And this fits quite well to your data because both order-tables contain the same - just another year - and could be simply concatenated. The district-information could be outsourced in a dimension-table - just linked per Office to plot the current structure against all data or per Office & '|' & Year to respect any changes in regard to the Year.
- Marcus
The officially recommended data-model is a star-scheme by merging (concatenating and/or mapping/joining) all facts within a single fact-table and n dimension-tables. And this fits quite well to your data because both order-tables contain the same - just another year - and could be simply concatenated. The district-information could be outsourced in a dimension-table - just linked per Office to plot the current structure against all data or per Office & '|' & Year to respect any changes in regard to the Year.
- Marcus
Hi, Marcus. I'm so glad to receive your reply. Your explanation is very useful for me. But I want to double check it. I draft it in an Excel. The right table is the district dimension table, is that right as you mentioned?
Usually, in our business, people would like to select each layer alone. So I divided them into 3 columns as a dimension table. I think it also works, is it right?
Yes, each layer alone is right. Means the district table contains in the end 5 fields: Territory, Branch, Office, FY + the key-field to the order-table of Office & FY which might be also a numeric by using an autonumber().
- Marcus
Exactly. Thank you so much. I will try it in practice.