I have an MDX question finding hard to solve. I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date. All records in the fact table are assigned the batch date with the batch date key.
There are many records for each day and each batch date is an entire copy of the source records. So, the grain of the fact table is one record for each batch date that exists in the source system. These facts rows have another date in them
for when the record was entered. This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered. If a record was entered many days
before, its batch date will be today but its entered date will be several days ago. Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present.
Fact Table :
FactSnaphshotKey (surrogate for easier administration)
BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures)
EnteredDateKey (link to entered date dimension – date dimension)
Facts Count – measure for fact table - default measure from Analysis Services cube
View Complete Post