Compliance Enabled DW and Analysis Services - Design Tip#74?
2 posters
Page 1 of 1
Compliance Enabled DW and Analysis Services - Design Tip#74?
Hi,
I'm working on implementing Point in Time functionality in a data warehouse where, due to auditing purposes, we need to be able to select a date and view the data as it looked like on that date.
We have both type1 and type2 changes to our dimensions and type1 changes to our facts (measures) which the logic would have to take into account.
I came across Kimball Design Tip #74 which describes exactly what I’m trying to achieve.
http://www.kimballgroup.com/html/designtipsPDF/DesignTips2005/DTKU74Compliance-EnabledDataWarehouses.pdf
The problem is that it assumes that a date range can be selected, in whatever tool is querying this architecture, to link the fact to a dimension.
However most front-end BI tools can only deal with straight fact-dimension joins and my client is using Analysis Services Cubes which also has this limitation.
So, in summary, has anyone been able to successfully implement a compliance enabled data warehouse where the end user can select a specific date and view the data as it was at that point in time in Analysis Services?
I’m interested in any solution, but so far Kimball Design Tip #74 appears to be closest to what I’m after but falls on the date range selection.
Thanks in anticipation.
I'm working on implementing Point in Time functionality in a data warehouse where, due to auditing purposes, we need to be able to select a date and view the data as it looked like on that date.
We have both type1 and type2 changes to our dimensions and type1 changes to our facts (measures) which the logic would have to take into account.
I came across Kimball Design Tip #74 which describes exactly what I’m trying to achieve.
http://www.kimballgroup.com/html/designtipsPDF/DesignTips2005/DTKU74Compliance-EnabledDataWarehouses.pdf
The problem is that it assumes that a date range can be selected, in whatever tool is querying this architecture, to link the fact to a dimension.
However most front-end BI tools can only deal with straight fact-dimension joins and my client is using Analysis Services Cubes which also has this limitation.
So, in summary, has anyone been able to successfully implement a compliance enabled data warehouse where the end user can select a specific date and view the data as it was at that point in time in Analysis Services?
I’m interested in any solution, but so far Kimball Design Tip #74 appears to be closest to what I’m after but falls on the date range selection.
Thanks in anticipation.
johankarlss- Posts : 10
Join date : 2011-05-31
Location : New Zealand
Re: Compliance Enabled DW and Analysis Services - Design Tip#74?
Hi Johan,
I'm not sure this will meet your requirements, but you could try a Bridge table (in SSAS: a Many-to-Many dimension relationship) that includes your date keys. This might follow the "Cross-Time" or "Transition Matrix" patterns described in the "Many-to-Many Revolution" paper:
www.sqlbi.com
The bridge data might need many rows, but with just a few columns (keys only) it can be manageable to build. Often you can get away with a SQL view with a cross join to your Date dimension.
Good luck!
Mike
I'm not sure this will meet your requirements, but you could try a Bridge table (in SSAS: a Many-to-Many dimension relationship) that includes your date keys. This might follow the "Cross-Time" or "Transition Matrix" patterns described in the "Many-to-Many Revolution" paper:
www.sqlbi.com
The bridge data might need many rows, but with just a few columns (keys only) it can be manageable to build. Often you can get away with a SQL view with a cross join to your Date dimension.
Good luck!
Mike
Similar topics
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
» Modeling tool with support for hierarchies
» CRM DW, measuring product and services subscription
» Subscription Services - Timespan Fact Table
» Accumulating Time - Professional Services Firm
» Modeling tool with support for hierarchies
» CRM DW, measuring product and services subscription
» Subscription Services - Timespan Fact Table
» Accumulating Time - Professional Services Firm
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum