Optimizing the link between tables

[Amit_B]

Hi all,

The model has 3 table - Orders (facts), InvoiceDetails and Dates.
Main fields in each table:
* Orders - OrderID, OrderDate, PrimaryInvoice, SecondaryInvoice.
* InvoiceDetails - InvoiceID, InvoiceDate, DeliveryDate.
* Dates - Date.
(An invoice can be primary in one order and secondary in another order.)

I use the Qualify() function to join the InvoiceDetails table to the two invoices in the Orders table.
I also use Qualify() for the date fields.

The use of 3 date tables as shown below creates a loop problem (6 tables in total).

So I used 5 date tables as shown below, each date field has a dedicated date table. A total of 8 tables in a model that consists of 3 tables.

Is it possible to optimize the connection so that there are not too many tables? Maybe create a main date table to which all the date fields will be connected?
I tried to use Concatenate() instead of Qualify() but I didn't come to a solution.

Hope I was clear.
Thanks!

[vincent_ardiet_]

Depends of how you want to exploit this datamodel after. But one solution could be to keep a single table InvoiceDetails containing Main and Secondary ones.
However, when you load the table Order you do something like this:

Order:
Load OrderId, OrderDate, ..., MainInvoice as Invoice, 'Main' as InvoiceType From Order.Qvd (qvd);
Concatenate(Order)
Load OrderId, OrderDate, ..., SecondaryInvoice as Invoice, 'Secondary' as InvoiceType From Order.Qvd (qvd);