3 Replies Latest reply: Dec 2, 2013 7:29 AM by Jose Manuel Sánchez RSS

    Linking tables (circular reference)

      Hi,

       

      i'm trying to connect some tables and i'm getting the "circular reference". I'm not really sure how to connect them.

      This is what I get right now.

       

      Circular.png

      I'm trying to connect the "t_Provincia", "t_Super" and "t_Distribuidor" and the main problem I have here is with the "idProvincia" and "Provincia".

      If I connect "t_Distribuidor" with "t_Provincia":

       

      cir2.png

      So, basically:

      "Provincia" field: shoul be in t_Distribuidor and t_Provincia

      "idProvincia" field: should be in t_Provincia and t_Super

      "Distribuidor" field: should be in t_Distribuidor and t_Super.

       

      I was thinking about creating a new table "t_ProvinciaDistribuidor" for the "Provincia" of the t_Distribuidor table, but the main problem is that I have to crete later a chart (stacked) where I have to show per "Provincia", the total of the t_Super (in a specific status) and the Num_SuperPerDistribuidor, so in some way, I have to select a specific "Provincia" for both table in one filter.

       

      I hope I've explained it ok.

       

       

      Thanks in advance.

        • Re: Linking tables (circular reference)
          Peter Cammaert

          Create a snowflake model where Provincia is a central dimension attached to a link table with suitable keys. The idea is that if you can't keep dimensions on the outer layer, put them in the center.

           

          Moreover, Povincia, Municipio and Comunidad on average can be organised into a hierarchy where only one of those is connected to the facts. That way you can create a real SnowFlake model.

           

          Best,

           

          Peter

          • Re: Linking tables (circular reference)

            Hi,

                Try to give Alias name for those fields  which you don't want to link with other tables(if there is any same field name as different table field name)  then it won't show circular reference.

            And Read this document  also for better understanding how to create good schema.

             

             

            Regards,

            Ashutosh

            • Re: Linking tables (circular reference)

              Thank you for your answers.

              Anyway, first of all, I apply a Mapping Load, because the "Provincia"  value in the t_Provincia and the t_Super were different.

              Then, I have to make a LEFT JOIN in the "t_Distribuidor" to populate that table with the correct IdProvincia, in order to have the real id, and not the value Provincia, so I can have the same field in t_Super and t_Distribuidor.

              I've have now something like this.

              final.png

               

              I guess now it is correct, isnt't it?

               

              I post how I create my LinkTable.

               

              LinkTable:

              LOAD Distinct

                  %Key1,

                  Distribuidor_idProvincia AS idProvincia,

                  Distribuidor_Distribuidor AS Distribuidor

              Resident t_Distribuidor;

               

              Concatenate(LinkTable)

              LOAD Distinct

                  %Key1,

                  Super_idProvincia as idProvincia,

                  Super_Distribuidor AS Distribuidor

              Resident t_Super;

               

              DROP Fields Distribuidor_idProvincia, Super_idProvincia, Super_Distribuidor, Distribuidor_Distribuidor

               

              Thank you!