Creating Fact table using different tables

[SunilChauhan]

Hi All,

I have created a fact table using below

Table 1

TID1,other fields

Table 2

TID2,other fields

Table 3
TID3,otherfields

Table 4

TID4,otherfields

there are seprate tables available for linkage

Linkage 1:

TID1,TID2

Linkage 2

TID1,TID3

Linkage 3:

TID1,TID3

Linkage4:

TID1,TID4

now i am creating a fact table using

Table1

left

linkage1

lef join

linkage2

left join

linkage 3

left join

Linkage 4

Table2

left join linkage1

left join linkage2

left join linkage 3

left join linkage4

same for Table 3 and Table 4

and then concatenating Table 1-4

this will create my fact table 35 lacs record

i want to optimize this fact table.

Could you please suggets if there is any way to optimize this

[its_anandrjs]

Hi,

Use the Mapping table load in the Fact table if only two fields from any table or may be three ( By creating two fields and then do mapping load ) by this you can optimized it and also create different level of the Qvds.

Regards

Anand

[SunilChauhan]

Anand if you remember mapping load can have only two field in mapping.

here other fields means we have other fields name like F1,F2,F3.

[Roop]

Concatenate the fields together with something like TID1 & '-' & TID2 as LinkageField and then do the ApplyMap. Works well.

[timanshu]

Hi,

Can you elaborate?

I didn't understood what doe linkage Tables contain and which  purpose  these solves?

[Roop]

You can use ApplyMap to link a single field to another field (such as a simple lookup). If you have a more complex lookup using 2 fields you can use a Mapping Load followed by an ApplyMap statement using the concatenated fields as above. This may be a long process (to write) if you have multiple joins, but will optimize your performance.

[SunilChauhan]

Well

Table 1,Table 2 ,Table 3 ,Table 4 has no link

so we are using linkage table which shows linkage between these four table.

Linkage tables are intermediate table for two tables from table 1-4.

hope this make sense

[SunilChauhan]

Thanks
do you thing we can create TID1&'-'&TID2 without using left join. as TID2 is not available in Table 1

or if i dont understant

do you mean after doing left join then create TID&'-'&TID2

[jonathandienst]

Hi

Something like this (assuming Table2, 3, and 4 contain too many fields to join in using ApplyMap):

Map2:

Mapping LOAD TID1, TID2

FROM Linkage1;

Map3:

Mapping LOAD TID1, TID3

FROM Linkage1;

Map4:

Mapping LOAD TID1, TID4

FROM Linkage1;

Table1:

LOAD TID1,

  ApplyMap('Map2', TID1) As TID2,

  ApplyMap('Map3', TID1) As TID3,

  ApplyMap('Map4', TID1) As TID4,

  ...

FROM Table1;

Left Join (Table1)

LOAD * FROM Table2;

Left Join (Table1)

LOAD * FROM Table3;

Left Join (Table1)

LOAD * FROM Table4;

HTH

Jonathan

[SunilChauhan]

Thanks its really good solution.

but from Table 2 the record which are not linked to Table1 (Record(TID2 which have no TID1 )will visible using this model

i need to display all record from Table1,Table2,Table3,Table 4
thanks again