If you really want to use a link table, then you cannot use NULL values in it. Instead I think you need to define a symbol for "any" - e.g. the string '<ANY>' - and map that to all values in the dimension.
However, in this specific case I would not use a link table. I would instead just use the tables the way they are. It is true that CustomerID will create an unacceptable loop. But this is a problem that could be solved in a different way:
If Ticket.Customer and Invoice.Customer are always the same, then you do not need to load the Customer in the invoice table. And if they could be different, then you should load the Customers from both tables, but under different names
The ticket is always assigned to a customer, but may be billed to a different customer/supplier
The situation which I want to report on is similar to a service company doing warranty work for a car manufacturer.
For example, I have purchased my BMW and it is under warranty.
I take my car in for a routine service.
They open one ticket for the serivce which will be billed to me.
I also tell them about the window that doesn't automatically wind up when I lock the door as it should. They open another ticket which is for my car, so I am the customer for the ticket, but the invoice will go to BMW.
In the above example, this would probably require a new field called InvoicePayer - who pays for the ticket.
So, I agree that my example may have mislead people, but it was only to try to show a circular loop problem.
What I get as a result is:
The Red rectangle shows that the CustomerID is blank where I want it to be 'Z' as in the Blue rectangle below.
Also, I want to remove the row as shown by the green rectangle.
My data was a bit messed up, as I had a ticket which belonged to one customer and billed to another as in the above car example. This would in a real situation require different fields.
So, now that I have fixed the data, the result is still a problem:
Here is the data:
And the result I get is still the same, there are too many rows:
The linkage looks OK:
This is my linked table load script:
LOADTicketNumberasTicketKey, Description // CustomerID // cannot use this as there will be a loop - so use the linked table. FROM linktabletest.xlsx (ooxml, embeddedlabels, tableis Tickets);
LOADBillingIDasInvoiceKey, invoiceNumber // CustomerID FROM linktabletest.xlsx (ooxml, embeddedlabels, tableis Invoices);
LOAD // TicketNumber as TicketKey, TimeIDasTimeKey, // TicketNumber, Hours, BillingID FROM linktabletest.xlsx (ooxml, embeddedlabels, tableis Time);
// Linked Table
LinkTable: LOADDISTINCTTicketNumberasTicketKey, Null() asTimeKey, Null() asInvoiceKey, TicketNumber, CustomerID// cannot use this as there will be a loop - so use the linked table. FROM linktabletest.xlsx (ooxml, embeddedlabels, tableis Tickets);