DRILL DOWN issues on dates hierarchies -
Today we have a few date fields representing different actions of the business.Those dates are in different tables (in the fact and on different dims like: first and last action date attributes).
Building reports using APPEND/UNION statements that joins types of actions by the Year date part, when both dates has date hierarchies.
It seems that drilling down on a specific year, automatically inserts a condition to all of the queries (append/union/intersect/ minus/ master) that conditions the date of the action from the master query on the rest of the queries.
This makes the functionality of the drill down wrong when a user want to view different actions (usually that the case on our reports).
The problem is even bigger on intersect or minus, the date of the drill down automatically entered as part of the intersect or minus query, which in many cases completely destroys the whole purpose of using them.
It is important to note that this does not happen when the intersect or minus are from a different view.
Using the date dim is irrelevant, because there are a lot of important dates that are not on one fact table and you cannot connect a few tables to the same dimension on the semantic layer.
Is there a solution to this issues?
Thanks in advance!