Compliance Enabled DW and Analysis Services - Design Tip#74?

View previous topic View next topic Go down

Compliance Enabled DW and Analysis Services - Design Tip#74?

Post  johankarlss on Sun Aug 05, 2012 7:05 pm

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.

johankarlss

Posts : 10
Join date : 2011-05-31
Location : New Zealand

View user profile

Back to top Go down

Re: Compliance Enabled DW and Analysis Services - Design Tip#74?

Post  Mike Honey on Mon Aug 06, 2012 8:10 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum