Data Model Issue

[diwakarnahata]

Hi,

I have a data model, which has two facts Fact1 and Fact2 which are concatenated, common Dimension tables of Prod and Geo and a relate table which stores the affiliations between the two fact tables. Attached is the dummy implementation of the data model.

Now, there is a small issue that the Geo hierarchy is not connected to both the Fact tables directly. It has G1Key which is present in Fact table F1. But the fact table Fact2 has another key as G2Key, and there is a Mapping table which has G1Key and G2Key mapping.

Now i tried joining the Fact2 table which this mapping table on G2Key to get the G1Key which can act as a common link between the concatenated fact table and the Geo hierarchy, but then there is a many to many relationship between G1Key and G2Key, and joining that table with the fact table Fact2 will inflate the rows in the fact.

So, i thought of joining the Geo Dim table with the Mapping table ,but that also doesn't give desired results since the join now happens on a synthetic Key of G1Key,G2Key.

Please suggest how to fix this issue.

Regards,

Diwakar

[#2]

I'd suggest that, rather than joining Fact2 table with the mapping table, join Fact1 table (since G1Key as 1 and only 1 G2Key value so it won't create duplicate values in the join).

Check if it makes sense in your original data model. At least it works fine in your example.

Regards,

Jesús

[Gysbert_Wassenaar]

I don't understand why you can't concatenate the fact tables like this:

MapGeo:

MAPPING LOAD * INLINE [

G2Key,G1Key

11,11

12,11

13,12

14,12

];

Fact:

LOAD PKey, applymap('MapGeo',G2Key) as GKey, F2Key as FKey, Vol2 , 'Fact2' as Source  INLINE [

PKey,G2Key,F2Key,Vol2

101,11,20001,1000

102,11,20002,2000

103,12,20003,3000

104,12,20004,4000

];

Concatenate(Fact)

LOAD PKey,G1Key as GKey ,F1Key as FKey, Vol1 , 'Fact1' as Source  INLINE [

PKey,G1Key,F1Key,Vol1

101,11,10001,1000

102,11,10002,2000

103,12,10003,3000

104,12,10004,4000

];

[#4]

The MAPPING table expects the value to be found in the first column

Try with this instead:

MapGeo:

MAPPING LOAD * INLINE [

G2Key,G1Key

11,11

11,12

12,13

12,14

];

[#5]

Obviously, you need to have a unique value in the column 1, which is not what you got in my previous post.

So instead of using the applymap function in the first fact table, use it in the second one.

[#6]

Check if this is what you want to achieve:

Prod:

LOAD * INLINE [

PKey,PDesc

101,'Prod-A'

102,'Prod-B'

103,'Prod-C'

104,'Prod-D'

];

Geo:

LOAD * INLINE [

Source,GKey,GDesc

Fact1,11,'Geo-A'

Fact1,12,'Geo-B'

Fact1,13,'Geo-C'

Fact1,14,'Geo-D'

Fact2,11,'Geo-A'

Fact2,11,'Geo-B'

Fact2,12,'Geo-C'

Fact2,12,'Geo-D'

];

Fact:

LOAD PKey, G2Key as GKey, F2Key as FKey, Vol2 , 'Fact2' as Source  INLINE [

PKey,G2Key,F2Key,Vol2

101,11,20001,1000

102,11,20002,2000

103,12,20003,3000

104,12,20004,4000

];

Concatenate(Fact)

LOAD PKey, G1Key as GKey, F1Key as FKey, Vol1 , 'Fact1' as Source  INLINE [

PKey,G1Key,F1Key,Vol1

101,11,10001,1000

102,12,10002,2000

103,13,10003,3000

104,14,10004,4000

];