Linking tables (circular reference)

[#1]

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.

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":

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.

[Peter_Cammaert · Accepted solution]

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

[#3]

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

[#4]

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.

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!