12 Replies Latest reply: Nov 14, 2013 11:47 AM by Clever Anjos RSS

    Data Modelling

    Agnivesh Kumar

      Hi,

       

      I have 10 dimension tables ,

      and i already make fact table using these dimension tables ,

      but these table are not cross referenced to each other through fact table . how can i do this ,

      Please help .

        • Re: Data Modelling
          Friedrich Hofmann

          Hi,

           

          can you make your question a it more precise, please? There are a lot of people here able and perfectly willing to help, but no one possesses a crystal ball to my knowledge...

          As a first shot:

          - Are (and how are) the tables linked? What are the keys?

          Can you maybe post your qvw?

           

           

          Best regards,

           

          DataNibbler

            • Re: Data Modelling
              Agnivesh Kumar

              Suppose i have 3 Tables

               

              T1                                                           T2                                                                T3

              ID                                                            T2_ID                                                           T3_ID

              Name                                                      ID                                                                 T2_ID

              Address                                                   T2_Name                                                      T3_Cost

               

              thses are 3 tables T1,T2,and T3 and we can see these tables are linked each other

               

              after this i have loaded thses tables in qlikview like this :

               

              load  ID as %ID

                      Name

                      Address

              from T1;

               

              load  T2_ID as %T2_ID

                      T2_Name

              from T2;

               

              load  T3_ID as %T3_ID

                      T3_Cost

              from T3;

               

              now i make fact table

              Fact_Table:

              %ID

              %T2_ID

              %T3_ID

              #T3_Cost

               

              now these are connected with dimension table but they are not cross referenced (Means association is not making ).

                • Re: Data Modelling
                  Srikanth P

                  Just join the tables with KEY only

                   

                  FACT:

                  LOAD DISTINCT ID AS %ID Resident T1;

                  join LOAD DISTINCT ID AS %ID, T2_ID AS %T2_ID Resident T2;

                  Join LOAD DISTINCT T2_ID AS %T2_ID , T3_ID AS %T3_ID, T3_Cost AS #T3_Cost Resident T3;

                    • Re: Data Modelling
                      Agnivesh Kumar

                      One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.

                       

                      this msg appears when i reload script

                       

                       

                       

                      ///$tab T1

                      [T1]:

                      LOAD ID as %ID,

                           Name,

                           Address

                      FROM

                      C:\Users\agnivesh\Desktop\Question\Data\T1.xlsx

                      (ooxml, embedded labels, table is Sheet1);

                       

                       

                      ///$tab T2

                      [T2]:

                      LOAD T2_ID as %T2_ID,

                           ID,

                           T2_Name

                      FROM

                      C:\Users\agnivesh\Desktop\Question\Data\T2.xlsx

                      (ooxml, embedded labels, table is Sheet1);

                       

                       

                      ///$tab T3

                      [T3]:

                      LOAD T3_ID as %T3_ID,

                           T2_ID,

                           T3_Cost,

                           ID

                      FROM

                      C:\Users\agnivesh\Desktop\Question\Data\T3.xlsx

                      (ooxml, embedded labels, table is Sheet1);

                       

                       

                      ///$tab Fact

                      [Fact]:

                      LOAD DISTINCT %ID AS %ID

                      Resident T1;

                       

                       

                      join

                       

                       

                      LOAD DISTINCT ID AS %ID,

                         %T2_ID AS %T2_ID

                      Resident T2;

                       

                       

                      Join

                       

                       

                      LOAD DISTINCT T2_ID AS %T2_ID ,

                         %T3_ID AS %T3_ID,

                         T3_Cost AS #T3_Cost

                         //ID as %ID

                      Resident T3;

                • Re: Data Modelling
                  David Foster

                  This sounds like an almost perfect star schema (The rule of thumb proposed by Ralph Kimball is to keep to 7 dimensions or less) the dimension tables should only link to the fact table at the centre of the star.

                  • Re: Data Modelling

                    Hi,

                    We need the schema of tables to help you we need also the number of fact tables if exist more than one .

                    • Re: Data Modelling
                      Jonathan Dienst

                      Hi

                       

                      QV does the associations using the file names. When you load the fact table, you need to load the keys to the dimension tables using the same field name (in the fact table and in the dimension table). Like this:

                       

                      DimCustomer:

                      LOAD

                           CustomerKey,

                           CustomerName,

                           ....

                       

                      Fact:

                      LOAD ....

                           CustomerCode As CustomerKey,

                           ...

                       

                      HTH

                      Jonathan

                      • Re: Data Modelling
                        Clever Anjos

                        Into Qlikview, if two tables share a field with same name, QV will create a key between the tables.

                        Just make sure your fact table has a field (CodProduct, for example) with same same from dimension table and QV will do the rest to you

                          • Re: Data Modelling
                            Agnivesh Kumar

                            Yes thats i am asking , If same fields are present then QV makes internally synthetic table which contains all common fields and generate a synthetic key ,

                            my question is that if number of common fields are more then , is these is any way to remove this synthetic table (generated by QV).

                          • Re: Data Modelling
                            Clever Anjos

                            You can concatenate your fields, generating a unique key

                            autonumberhash128(Dim1,Dim2,...Dim10) as my_key