Data modeling with four fact tables and two dimensions tables

[srinivas1921]

Hi all, We have four fact tables which are consistent of common key but remaining all others fields are different and all these four fact tables used two dimensions tables commonly.please suggest to create data model approach in this scenario?

[Anil_Babu_Samineni]

In fact, 4 data models is not a practical matter. You need to arrange from 4 to 2 fact tables using Joins first and then from 2 fact tables i would think more on Bridge table.

[luismadriz]

Hi,

I'd say join those 4 tables into 1 table.

Cheers,

Luis

[woshua5550]

I would say concatenate these four tables would be a good choice , script like this

Load * from A;

Concatenate

Load * from B;

Concatenate

Load * from C;

Concatenate

Load * from D;

you will get some Null values in uncommon fields, but it will be easy to deal with in frontend

[luismadriz]

Hi, I should've elaborated more:

Once the 4 fact tables are loaded you could do the following:

AllTables:

NoConcatenate

Load * Resident Table1;

Join Load * Resident Table2;

Join Load * Resident Table3;

Join Load * Resident Table4;

Drop Table Table1;

Drop Table Table2;

Drop Table Table3;

Drop Table Table4;

[neha_shirsath]

first way- You can Concatenate all 4 fact tables or by

second way- You need to create Link Table with composite key and list all common fields in link table.

And link dimension table to Link table.

[luismadriz]

Hi,

Just wondering to know how did you go,

Cheers

Luis

When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others