Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Guys
I am a bit new to the modeling in qlick, I have already done some research about creating a star schema from a relational DB.
I have done these steps so far:
1- Connected to DB
2-Selected some tables and columns
3-Made sure each table has a primary key
4-Removed all relationships between tables by renaming foreign keys
5-Created a link table by concatenating primary keys of Dim Table (Which I am not sure whether I did the right thing or not)
6-Joined the sales Header and Detail tables to create a Fact table.
Now, the problem is some values in Header table have different granularity than Detail's, for example Cost. How can I divide the amount proportionally between lines of detail table.
ShipDim:
Load
ShipOrderID,
ShipOrderDetailID,
ShipMethod,
ShipDate
Resident ShipDetail;
left join load
ShipOrderID,
RegDate,
Status,
Tax,
Cost
Resident ShipHeader;
Would you please let me know your opinion on the whole design and let me know how I can make it right?
Thank you so much
Hi Ar,
A quick trick is to add the number of records (ShipOrderDetailID) that a ShipOrderID has. You can do:
LEFT JOIN (ShipDim)
LOAD ShipOrderID,
Count(ShipOrderDetailID) AS [# ShipOrderDetails],
RESIDENT ShipDetail
GROUP BY ShipOrderID;
In the expressions you can divide Cost / [# ShipOrderDetails].
Anyway, maybe you should not join into one if this could mean future problems...
Regards,
H
Dear Munoz,
Thanks for your response.
Any comment of the rest of modeling procedure?
Am I doing it right?
Hi,
As you said, you are new in Qlik but you are creating link tables, asking in the forum, guessing how to improve things... I think the answer is yes! 😉
Regards,
H
Dear Munoz
I guess there's a problem in my model.
I created the link table only by concatenation, added the primary keys of all dimension tables to link table but the columns have no relationship. I guess I should have joined the tables instead, Right?
Besides, I am getting an error while executing the expression you shared. would you please accept my follow request so I can send you the logs there?
Thank you so much
Of course! Send me the QVW if you want.
Regards,
H
Dear Munoz,
Thank you
Is it possible for you to give me your email address?
May be look here. Link table is nothing but all similar fields created the there own table while using Link table. PFA