Data Model Question

[udaya_kumar]

Hi,

I have two fact tables and those two are surrounded by dimension tables.

Consider Fact1 has following fields

Field1

Field2

Field3

Volume

Fact2 has following fields

Field1

Field2

Field4

Salary

Here Fact1 and Fact2 are having two common fields Field1 and Field2,

but Fact1 has extra one field Field3 and Fact2 has extra one field Field4, these are joined to the dimension tables

Dimension1

Field3

Field4

Name

So the Fact1 joins to Dimension1 through Field3 and Fact2 joins to Dimension1 through Field4

Field1 and Field2 in Fact1 and Fact2 joins to other dimension tables

Now how can we concatenate Fact1 and Fact2 tables and make into single fact table?

Since the fact table joins to same dimension table with different field.

How can we concatenate and make a single fact table?

[avinashelite]

Hi Uday,

If your data is low go with concatenation or outer join between the two table.

this would be the simple way to create single fact table.

Consider Fact1 has following fields

table1:

Field1

Field2

Field3

Volume

outer join(table1)

Fact2 has following fields

Field1

Field2

Field4

Salary

[jagan]

Hi Uday,

You need to create a link table using below script

LinkTable:

LOAD

DISTINCT Field1 & '_' & Field2 AS Key,

     Field1,

     Field2

FROM Fact1;

Concatenate(LinkTable)

LOAD

DISTINCT Field1 & '_' & Field2 AS Key,

     Field1,

     Field2

FROM Fact2;

Fact1:

LOAD

   Field1 & '_' & Field2 AS Key,

   Field3,

   Volume

FROM Fact1;

Fact2:

LOAD

   Field1 & '_' & Field2 AS Key,

   Field4,

   Salary

FROM Fact2;

Hope this helps you.

Regards,

Jagan.

[sujeetsingh]

Uday,

i dont think concatenating will work.

Just try the linking first ...and ensure that the results you are getting in your dashboard are as per you need.

You can use concept of Composite keys or just join the all fact table based on one key.

[sujeetsingh]

Follow this

Best Practices for Data Modelling

[simenkg]

This is the way I think it should be done, if I have understood your problem correctly:

Fact:

load

Field1,

Field2,

Field3 as %DimensionKey

Volume,

'Volume' as FactType

from Table1;

Concatenate (Fact)

Load

Field1,

Field2,

Field4 as %DimensionKey,

Salary,

'Salary' as FactType

from Table2;

Dimension:

load Field3 as %DimensionKey,

Field3 as Dimension,

Name

from DimensionTable;

Concatenate (Dimension)

load Field4 as %DimensionKey,

Field4 as Dimension,

Name

from DimensionTable;