8 Replies Latest reply: Apr 8, 2011 9:43 AM by Jon Foote RSS

    Synthetic keys and circular references

      Hi,

      I have an app with some dimension tables and some fact tables. They are stored and then load from QVD files. When I store them into QVDs, I haven't any problems, but when I load them from the QVDs I have an issue about a circular reference existence. For a LOAD statement I have commented some fields and I have identified the field that causes the issue. When I read the internal table view I can see more synthetic keys and the data structure it is hard to read. I have seen the dashed lines that link the tables related the circulare reference, but it is very difficult to understand the reason of the circular reference. I want to remain the link by name between dimension and fact tables, but not between fact tables themselves.

      F.e., as dimension table I have Products: ProductCode, ProductDescription

      as fact tables Invoices (InvoiceID, InvoiceDate, ProductCode, InvoicedAmount) and Orders (OrderID, OrderDate, ProductCode, OrderedAmount).

      In this simple case it could be desirable to link by ProductCode, but I want to link Products and Invoices, Products and Orders, but not Invoices and Orders. Often the synthetic keys are groups of fields not directly derived from a single table: fe if I add OrderID into Invoices table I could have a synthetic key composed of ProductCode and OrderID, but it could be desirable to have two distinct synthetic keys, one for ProductCode to link the Products and the other one for OrderID to link the Orders.

      I think that simplifying the synt keys it is possible to solve or avoid the circular references and, for now, I have a similar problem.

      Any helps to me, please? Thanks

        • Synthetic keys and circular references
          CheenuJanakiram

          A circular reference in a data model means that there is more than one way QV can resolve a query across 2 tables.

          My best advice to debug your QVD loads, is touse exit script; behind every table load, and load the tables one-by-one, the 1st two, then the 1st three, etc. So that you can identify from where your circular reference emanates. Then use the table viewer to find the one rogue field that causes these 2 paths between tables. If you alias this fieldname to something else, it should resolve your issue.

          Otherwise:

          A good way of ensuring that QV only links via fieldnames that you specify is to use the Qualify statement. This prefixes the table name in front of the fieldnames, such as TableName.FieldName and this breaks the link between tables.

          Typically, your QV script should look like

          Qualify *;

          unqualify Linkfield1, Linkfield2, Linkfield3;

          Load...

          Load...

          Load...

          Unqualify *;

          In this case example, the first qualify means qualify all the fieldnames coming therafter. The unqualify * just under that one is the fields you do not want to qualify, so that QV makes a link across tables via these shared fieldnames, otherwise, you might end up with Product.ProductID and Customer.ProductID and no link between these 2 because they have been prefixed with the table they originiate from.

          The qualify statement is the only function in QV script you need to close. Hence the last unqualify *; which means stop qualifying any other load statements behind.

            • Synthetic keys and circular references

              Hi,

              I have tried to load by commenting pieces of code. In this way I have first time load two tables, then three, and so on.

              I have detected when the circular reference occurs but not the reason, also because the relationships between SQL Server tables are right and always I have made them.

              I have these tables:

              a) Companies (CompanyID + CompanyDescription),

              b) BusinessUnits (BusinessUnitID + BusinessUnitDescription + CompanyID),

              c) Roles (RoleID, RoleDescription),

              d) Personnel (PersonID, PersonName, BusinessUnitID, RoleID, ResponsibleID),

              e) Projects (ProjectID, ProjectDescription, BusinessUnitID, StartDate, EndDate, ProjectAmount, ...)

              f) TimeReport (TRPeriod, ProjectID, PersonID, WorkedHours)

              No problem when I load until Projects table, but when I load also TimeReport I have the circular reference. In the original tables view I can see three dashed lines that indicate Personnel table: the first one from Roles to Personnel about RoleID field; the second one from BusinessUnits and Projects to Personnel about BusinessUnitID field; the last one from TimeReport to Personnel about PersonID field.

              Any helps to me to proceed with this application? Thanks

            • Synthetic keys and circular references

              Can you post an image of your table structure, try to expand each table so that all fields can be seen.

              Jon

                • Synthetic keys and circular references

                  Hi. I attach the img with the circular reference. I have written my tables in simplified manner. My QV document is in Italian language, so Commesse means Projects, Societa means Company, Personale means Personnel and so on. Moreover, Codice means ID, Descrizione means Description.

                  However, I've solved the issue by adding BusinessUnitCode to the TimeReport table, but this isn't necessary into SQL Server and for respecting the relationships.

                  It is very important for me understanding how solving the circular references, also because they can be created into a relational db as a normal case.

                  Thanks

                  http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/4762.Original-Tables-view.JPGhttp://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/1122.Original-Tables-view.JPG

                    • Synthetic keys and circular references

                      I attach as a file.

                        • Synthetic keys and circular references

                          Your circular reference seems to be just between the Time and Time Report Tables, the fields Anno and Mese. Try to link by only one of those fields in each table and rename the other, or concatenate the key in both tables.

                          Anno&'-'&Mese as AnnoMeseKey

                          Then rename those fields in one of the tables to avoid the join on each individual field again.

                          That way it will stop the circular reference and you will get a join where both Anno and Messe are the same in both Tables.

                           

                            • Synthetic keys and circular references

                              Mmmhh, but there aren't any dashed lines between Time and TimeReport tables. Morevover, why renaming the concatenation between Anno (Year) and Mese (Month)? I want that when I select an Year for Time changes the rows to show for TimeReport.

                              Thanks

                                • Synthetic keys and circular references

                                  Sorry I don't know any Italian. I have seen the main circular reference as shown below:

                                  You need to break one of those connections by renaming or commenting out one of those fields:

                                  CodiceCommesse, CodiceBU, CodicePersona

                                  If you cant remove or rename anything, then you may need to build a Link table that uses concatenated keys to join the tables without that circular reference.

                                  In Commesse do the follwing

                                  CodiceCommessa&'-'&CodiceBU as CodiceCommessa_BU,

                                  CodiceComessa as CommessaCodice,

                                  CodiceBU as BuCodice

                                  In Personale :

                                  CodiceBU&'-'&CodicePersona as CodiceBUPersona,

                                  CodicePersona as PersonaCodice,

                                  CodiceBU as CodiceBUPersonale

                                  In Time Report:

                                  CodiceCommessa&'-'&CodicePersona as CodiceComessaPersona,

                                  CodiceCommessa as CodiceCommessaTime,

                                  CodicePersona as CodicePersonaTime

                                   

                                  The creatae and new table as below:

                                  LINK:

                                  LOAD

                                  CodiceComessaPersona,

                                  CodiceCommessaTime as CodiceComessa,

                                  CodicePersonaTime as CodicePersona

                                  RESIDENT Time Report:;

                                   

                                  Left Join (LINK)

                                  LOAD

                                  CodiceCommessa_BU,

                                  CommessaCodice as CodiceComessa,

                                  BuCodice as CodiceBu

                                  RESIDENT Commesse ;

                                   

                                  Left Join (LINK)

                                  LOAD

                                  CodiceBUPersona,

                                  PersonaCodice as CodicePersona,

                                  CodiceBUPersonale as CodiceBu

                                  RESIDENT Commesse ;

                                   

                                  You need to start with the tables that have a full set of data then link in the others, I'm not sure how that will work in your data.

                                   

                                  I hope that helps

                                   

                                  Jon