I have developed a cube that tracks the timeliness of certain steps in a business process. Steps are on time if the number of days between 2 dates is <= values that are stored in a table. The fact table for the cube is assigned a Timeliness dimension ID accordingly.
In addition, the users want to be able to play with the results by being able to dynamically enter a cutoff day value. To accommodate this, I have a column in the fact table, called "ElapsedDays" that is the difference between the two dates. My plan was to use this column with a parameter to re-calculate the timeliness dimension. The T-SQL logic would be as follows:
WHEN ElapsedDays <= @parameter THEN 1 -- On time
WHEn ElapsedDays > @parameter THEN 2 -- Late
ELSE 3 -- Unknown
END AS TimelinessDimID
In essence, I want to substitute my new dimension values for the old ones.
Can this be done in MDX?