Here is a description of the issue.
Requirement:
1) Be able to show the values in a fact table for a given point in time.
2) Be able to show the underlying records for the aggregate data at a given point in time.
Design:
The cube has been built so every fact record has a surrogate key for Effective Start Date and Effective End Date to the Time dimension. As new records are added to the fact table, the Effective Start Date is set to the current date and the Effective End Date is set to NULL. If an update occurs from the source system for a record, the Effective End Date for the old record is set to the current date and a new record is created with the updated information (setting the Effective Start Date to the current date and the Effective End Date to NULL).
Implementation:
In order to select the aggregate data for a given point in time, the MDX has been implemented so the WHERE clause applies a date range using these two dimensions.
WHERE ({[Estimated Start Date].[Date].&[2004-01-01 00:00:00] : [Estimated Start Date].[Date].&[2007-06-010 00:00:00] },
{[Estimated End Date].[Date].&[2007-06-10 00:00:00] : [Estimated End Date].[Date].&[2020-01-01 00:00:00]})
Requirement 1 is achievable by using the above implementation. This correctly returns the value at a given point in time.
Problems:
1) Requirement 1 can be met, but the implementation is not 100% desirable. From the end user perspective, in the OLAP browser they would need to select all start date and end date values of a range to achieve the WHERE clause above. Ideally, they would be able to use an “Effective Date” dimension and select a single date corresponding to the point-in-time they desire.
2) To meet requirement 2, we are attempting to use the DRILLTHROUGH functionality in SSAS. This fails when a date range is applied because the date range may return aggregate data in more than one cell of the cube. The DRILLTHROUGH is only supported when the data is in a single cell.
Options:
1) Rather than use a start and end date, we could create new records nightly for the facts and timestamp them. This would allow us to get all the data for a single data and DRILLTHROUGH a single cell. However, this would drastically increase the amount of data in the fact table.
2) We could work around the DRILLTHROUGH problem by selecting all cells that have data, drilling through each individual cell and then union the results together. This is ok if there are only a couple cells, but we could have up to 1095 (3 years – 1 for each day) cells. That would be 1095 separate calls to the database for a DRILLTHROUGH query.
3) We could manually build a SQL query that directly hits the source OLTP database or a view to query the data for the DRILLTHROUGH. This means we need to maintain a cross reference of which dimensions correspond to which columns in the OLTP database so we can dynamically build the query.
Questions:
1) Is there another OLAP cube design option that would allow us to “version” fact table records and meet the requirements above?
2) With the current design, is there another way of implementing the DRILLTHROUGH so we can access the underlying records across multiple cells?
Requirement:
1) Be able to show the values in a fact table for a given point in time.
2) Be able to show the underlying records for the aggregate data at a given point in time.
Design:
The cube has been built so every fact record has a surrogate key for Effective Start Date and Effective End Date to the Time dimension. As new records are added to the fact table, the Effective Start Date is set to the current date and the Effective End Date is set to NULL. If an update occurs from the source system for a record, the Effective End Date for the old record is set to the current date and a new record is created with the updated information (setting the Effective Start Date to the current date and the Effective End Date to NULL).
Implementation:
In order to select the aggregate data for a given point in time, the MDX has been implemented so the WHERE clause applies a date range using these two dimensions.
WHERE ({[Estimated Start Date].[Date].&[2004-01-01 00:00:00] : [Estimated Start Date].[Date].&[2007-06-010 00:00:00] },
{[Estimated End Date].[Date].&[2007-06-10 00:00:00] : [Estimated End Date].[Date].&[2020-01-01 00:00:00]})
Requirement 1 is achievable by using the above implementation. This correctly returns the value at a given point in time.
Problems:
1) Requirement 1 can be met, but the implementation is not 100% desirable. From the end user perspective, in the OLAP browser they would need to select all start date and end date values of a range to achieve the WHERE clause above. Ideally, they would be able to use an “Effective Date” dimension and select a single date corresponding to the point-in-time they desire.
2) To meet requirement 2, we are attempting to use the DRILLTHROUGH functionality in SSAS. This fails when a date range is applied because the date range may return aggregate data in more than one cell of the cube. The DRILLTHROUGH is only supported when the data is in a single cell.
Options:
1) Rather than use a start and end date, we could create new records nightly for the facts and timestamp them. This would allow us to get all the data for a single data and DRILLTHROUGH a single cell. However, this would drastically increase the amount of data in the fact table.
2) We could work around the DRILLTHROUGH problem by selecting all cells that have data, drilling through each individual cell and then union the results together. This is ok if there are only a couple cells, but we could have up to 1095 (3 years – 1 for each day) cells. That would be 1095 separate calls to the database for a DRILLTHROUGH query.
3) We could manually build a SQL query that directly hits the source OLTP database or a view to query the data for the DRILLTHROUGH. This means we need to maintain a cross reference of which dimensions correspond to which columns in the OLTP database so we can dynamically build the query.
Questions:
1) Is there another OLAP cube design option that would allow us to “version” fact table records and meet the requirements above?
2) With the current design, is there another way of implementing the DRILLTHROUGH so we can access the underlying records across multiple cells?