Star Schema & Keys

[#1]

Hi All,

I'm posting this because i can't really find any thread about star schema and the key.

So, we are all aware about concatenate tables n avoid sync keys - http://www.quickqlearqool.nl/?p=910

When we have many fact tables, coming from different sources, and they all share the same dimensions (eg, month, year, zone, country...etc) we want to use them in LIST BOX, the best way i guess is to create a star schema and concatenate them and fixing the synthetic keys.

When that's done, you will have a pretty star schema, you have all the IDs in "Reference_Table", then you have all those other tables for figures, sales, stock amount etc link to this Reference Table via a KEY, which is a combination of several fields.

Key is the topic for this discussion. What is the best way to create a key for star schema?

Say we have to build something to show sales, account receiveable (AR), and best selling product at World wide level, so u can imagine in your Ref table, you will throw in fields like YEAR, MONTH, ZONE, COUNTRY, SALES_ID, AR_ID, product_ID, etc...

To create key to link to ref table, i can either do this >

In Sales table:
(YEAR&'-'&MONTH&'-'&ZONE&'-'&COUNTRY&'-'&SALES_ID&'-'& ' '&'-'&' ')

In AR table:
(YEAR&'-'&MONTH&'-'&ZONE&'-'&COUNTRY&'-'&' '&'-'&AR_ID&'-'&' ')

In product table:
(YEAR&'-'&MONTH&'-'&ZONE&'-'&COUNTRY&'-'&' ' &'-'& ' '&'-'&prod_ID)

but, when you have a lot of dimension and it becomes long, i guess you would use >

In Sales table:
AUTONUMBER(YEAR&'-'&MONTH&'-'&ZONE&'-'&COUNTRY&'-'&SALES_ID&'-'& ' '&'-'&' ')

In AR table:
AUTONUMBER(YEAR&'-'&MONTH&'-'&ZONE&'-'&COUNTRY&'-'&' '&'-'&AR_ID&'-'&' ')

In product table:
AUTONUMBER(YEAR&'-'&MONTH&'-'&ZONE&'-'&COUNTRY&'-'&' ' &'-'& ' '&'-'&prod_ID)

I'm using the AUTONUMBER, because i'm using a lot of dimension in the key. and you must know that, if you are adding a new source says AP(acct payable), you will need to expand each of those key with empty value in AP_ID, because not all tables would have AP_ID.

I'm sure there's better way of building the key.

So, please share your comments regarding this topic as i might be useful for others. 🙂

Happy Qliking,
Nick

[#2]

Hi,

I think this link will be helpful.....

http://www.quickqlearqool.nl/?p=910

[johnw]

Most people would likely disagree, but the first thing I'd personally try is doing nothing. It is possible that the best way to create the key(s) for your star schema is to simply load the tables as is and let QlikView build the synthetic key(s) to connect them together:

http://community.qlik.com/forums/t/31028.aspx

However, a case where that might get you into trouble is the very one you mention - "you are adding a new source says AP(acct payable), you will need to expand each of those key with empty value in AP_ID, because not all tables would have AP_ID." With the synthetic key approach, you don't have to add any empty values. But if the AP_ID is in more than one but not all tables, QlikView will then create ANOTHER synthetic key for the fields that DO exist in more than one table. Do this enough times, and you'll have a lot of interconnected synthetic keys, and things could get ugly. But I don't know if your fields like AP_ID are in multiple but not all tables, so this may be a non-issue.