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.
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
unqualify Linkfield1, Linkfield2, Linkfield3;
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.
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
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.
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.
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:
CodiceCommessaTime as CodiceComessa,
CodicePersonaTime as CodicePersona
RESIDENT Time Report:;
Left Join (LINK)
CommessaCodice as CodiceComessa,
BuCodice as CodiceBu
RESIDENT Commesse ;
Left Join (LINK)
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