5 Replies Latest reply: Jan 14, 2015 5:48 AM by Jesús Santisteban Rivero RSS

    Data Model Issue

    Diwakar Nahata

      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

        • Re: Data Model Issue

          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

          • Re: Data Model Issue
            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

            ];

              • Re: Data Model Issue

                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

                ];

                  • Re: Data Model Issue

                    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.

                      • Re: Data Model Issue

                        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

                        ];