if I have understood correctly your problem, you have created multiple dates attributes (with different logical meaning) and they are mapped on different aliases of the calendar table.
Until users use different a single fact table in their reports there is no problem, but when they use metrics/facts from sales and invoices you have multiplied results because "Order Date" and "Invoice Date" are different attributes.
Your SQL looks something like:
...
FROM order_fact a11
INNER JOIN invoice_fact a12
INNER JOIN lu_calendar a13
ON a11.order_date = a13.date_id
INNER JOIN lu_calendar a14
ON a12.invoice_date = a14.date_id
...
As usual there are possible solution, not all of them very straight forward.
Option 1 - Single date attribute
You mention this possibility in your question, instead of using "Order Date" and "Invoice Date", just use a single "Date" attribute and teach users to use it. You can call it "Reporting Date" or "Operation Date" if this makes the life easier for them.
The SQL you should get is something like:
...
FROM order_fact a11
INNER JOIN invoice_fact a12
ON a11.order_date = a12.invoice_date
INNER JOIN lu_calendar a13 -- Only one join
ON a11.order_date = a13.date_id -- because the date is the same
...
Option 2 - We need to keep the two date attributes!
Map "Order Date" and "Invoice Date" on the same alias of your calendar table. This is usually can cause problems in MicroStrategy, because two attributes will be joined together on the same look-up table [see later on this], but in your case this is exactly what you are looking for.
With this solution you should get an SQL like this:
...
FROM order_fact a11
INNER JOIN invoice_fact a12 -- Hey! this is again a cross join!
INNER JOIN lu_calendar a13
ON a11.order_date = a13.date_id -- Relax man, we got you covered.
AND a12.invoice_date = a13.date_id -- Yes, we do it!
...
This is nice, but it works only if you have description forms coming from the calendar table (this is not always the case with dates because the ID is usually also the actual value that you show on your reports). In case you don't have a join with the calendar lookup, you SQL will end up again with duplicated result:
...
FROM order_fact a11 -- Notice no join column between the two facts
INNER JOIN invoice_fact a12 -- and no other conditions will help to join them
...
For this reason if you want to keep the two attributes separate, beside mapping them on the same lookup, you should also:
- Create an hidden attribute (let's call it "Date_on_fact") map it on the fact table and the calendar table and make it child of both "Order Date" and "Invoice Date".
- Un-map the "Order Date" and "Invoice Date" from the fact tables.
The idea here is to force MicroStrategy to use always the SQL code always the calendar lookup table:
...
FROM order_fact a11
INNER JOIN invoice_fact a12 -- This is like the previous one
INNER JOIN lu_calendar a13 -- But I'm back to help you
ON a11.order_date = a13.date_id
AND a12.invoice_date = a13.date_id
...
The attribute "Date_on_fact" can actually be hidden and users don't need to put it in their reports, but MicroStrategy will use it to go from the parent attributes to the fact table.
Hope this can help you to get out from the mud.
