Getting Crazy with Cycle Join

[#1]

Dear,

I've a classic problem that is getting me crazy. (even if it seems easy to solve...)

Scenario :

1) I've Invoice Header table and Invoice Position table

2) I've Sales Order Header table and Sales Order Position table

3) I've some customer/item/Calendar master table

What we need it's just to have per day/area the invoice and Order

I've problem 'cause to relate the master table to the data (fact) table (Invoice and Order) I always have problem with the cycle join loop as in the below image. (I just included some of the table to give u an example)

Which is the best practice in this situation ?

1) Creating a single dimension table with all the customer/item/Calendar data ?! Not so easy

2) Creating one single Fact table ? I don't think so...

Please let me know your suggestion

Thanks

Sergio

[#2]

I would suggest to concatenate the Header tables (Invoice and Orders) and adding a field DocumentType (1 for invoive and 2 for Order).

Then concatenate the detailed tables together.

The link between Header and Details would be done by their numbers (Check that there is no redundant numbers between Orders and invoices)

Then the calendar can be linked to the Header table !

[#3]

Thanks spastor,

Unfortunately I've same problem on position level too, 'cause I've some other fields that need main master (as item master, so group sales data by item group and so on)

It means that I need to concatenate all these data too .

Do u think I've only this solution or u have in mind something else ?

Sergio

[#4]

Well you can also use qualify to separate orders and invoices.

In this case your loop will disappear, but you will need to double table.

Rgds,

Sébastien

[#5]

Yes,

But I won't be able to have linked data to my dimension tables. Right ?

So I think that the concatenate should be the best solution

Sergio

[fernandotoledo]

Olá Sergio!

I think the link table is the best solution, even if it is too complex in the script...

best regards,

Fernando

[#7]

Do u mean a single link table ?