Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

2 posters

Go down

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

Post  johankarlss 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

Back to top Go down

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

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

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

http://www.mangasolutions.com

Back to top Go down

Back to top

- Similar topics

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