data model

[master_student]

Hello everyone,

My data model generates loops, here's attached.

Could you please help me to remove loops?

Thanks in advance

[swuehl]

That's hard to do without knowing your model in detail.

In general, consider renaming fields that are used for different roles of the same entity:

Circular References

[master_student]

Hello,

Thanks for your response.

I will try to explain to you my model.

so I have a budget (excel file) that contins the departments budget LBopex: the name of the dep as the business users named them

I also use a database that contains the LB : name of dep in the database

so you can remark that LB is not exactly LBopex

To fix that I used a map table that contains the LBopex and its equivalent LB

the objective is to have a table that contains LBopex/LB/mesaures from the buget excel file/measures from the database.

please excuse my bad english.

Thanks

[ziadm]

Hi

The Data Model has Synthetic  keys DateID ,CDR and OPEX Category.  These Fields are duplicated in the Tables Budget, Map and BC.  I would suggest  rename the DateID field in the BC Table to different name and rename the field OPEX in the Table  map.  This does not mean you that your data model is would be a working model.  Try to creat a unique kets using Autonumber function to link two tables.

[master_student]

Hi,

I can't rename Date ID field, I am using it as a link to the Calendar dimension. any suggest to cretae a link table?

Thanks

Wiem

[swuehl]

Your map table, was that used for the mapping only? Maybe you have forgot to drop the table at the end?

If this is the case, this may solve the circular loop already.

[master_student]

if I drop the map tab, how can I get the corresponding LB/LBopex

[swuehl]

That's hard to answer without knowing more about your model.

Can you post your current script?

[master_student]

here is my script :

//excel budget file

budget:

LOAD [%Date ID],

OpEx_Category,

Direction,

CDR_Name,

Type

,trim(lower(LBopex))

,CDR

,Mois

Resident PO_BUDGET;

//maptable

map:

LOAD OpEx_Category,

     trim (lower([LB / Budget] ))as LBopex,

     trim(lower([LB / ERP])) as LB

FROM

xxxx

//calendar dimension

calendar:

[%Date ID],

Year,

month,

day

calendar date

//table comming from the database

BC:

LOAD trim(lower( LB)),

CDR,

[Date_PO]

AutoNumber( MakeDate( year(date([Date_PO],'DD/MM/YYYY')), Month(date([Date_PO],'DD/MM/YYYY')), day(date([Date_PO],'DD/MM/YYYY')) ) , '%Date ID') as  [%Date ID]

Resident SUIVI_DA_BC_tmp;

I need to join the LB FROM database with LBopex FROM EXCEL file to get the measures in the database that's why I am using a map table but it generates loops

Thanks in advance

[swuehl]

Try to avoid the map table in the data model, instead, use a MAPPING approach to replace the Excel LBOpex values with the corresponding value from the database, e.g. like shown here:

Data Cleansing‌

If LBOpex and LB are both representing your departments for budget and BC, you should end up with naming the fields in both tables the same.

This might create a synthetic key, but no circular loop anymore (if you've dismissed the map table).

You can then remove the synthetic key replacing it with a combined key in a second step.