Data Modeling, creating a Fact from Header and Detail tables

[arixooo123]

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

[hector_munoz]

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

[arixooo123]

Dear Munoz,

Thanks for your response.

Any comment of the rest of modeling procedure?

Am I doing it right?

[hector_munoz]

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

[arixooo123]

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

[hector_munoz]

Of course! Send me the QVW if you want.

Regards,
H

[arixooo123]

Dear Munoz,

Thank you

Is it possible for you to give me your email address?

[Anil_Babu_Samineni]

May be look here. Link table is nothing but all similar fields created the there own table while using Link table. PFA