Modeling between fact and lookup tables

[#1]

Hi all,

I'm currently working on a fact table and a numbers of lookup tables. I'm not sure if i should choose concatenate or join method.

Can anyone recommend me the best approach to model these tables?

Thanks in advance!

[#2]

Hi ,

It depends on few things like

1) How many lookup tables you have ?

2) Are these lookup tables are connected to only 1 or multiple tables ?

Best option to use is use of "lookup" function in the FACT tables .

Syntax :

lookup("Field_taken_frm_LookupTable","lookup_Filed_frm_LookupTable",lookup_Filed_frm_FactTable,"LookupTable_Name")

Regards,

Bhushan N

Best approach is to se

[suniljain]

you have fact and multiple dimention table. best approach is use join instead of concatenate.

[#4]

I have total more than 10 lookup tables. Some of the lookup table are not directly linked to the fact. Instead, it's link to another few lookup tables before to fact table.

Example:

OUTLET _MSTR -> OUTLET_ADDR -> CITY_MSTR -> STATE_CODE

I'll need to go through all this lookup table before i can get the DESC of city from OUTLET_MSTR.

[suniljain]

OUTLET _MSTR -> OUTLET_ADDR ->Left Join -> CITY_MSTR ->Left Join-> STATE_CODE

You have to use left join to reduce hierarchy. otherewise performance become too slow.

[#6]

Hi there. From my experience, it definitely pays off to join dimension tables if they have several fields in common, for example the Outlet dimension table with the Outlet address table. It is definitely better in performance esp with a large load of data eg. more than 1 million transactions. Aim also for a Star schema consisting of your fact table connected directly to your dimension tables and avoid having dimension tables not directly related to your fact table. I usually use Concatenate only with tables where it is almost natural for them to exist in one single table. For example, sales of outlet one and sales of outlet two.