Is periodic snapshot the right choice?

[FresnoBob]

I'm a newbie to warehousing and in my instance SSAS. I have a database which has an agreement table in which the status of the agreements changes over time. This is stored in the agreement log. the status can be any combination over an extended period of time. One set of questions I will need to answer are how many agreements are of a given status and also to show trends in the status over time. I'm reading Kimball and the periodic snapshot seems to be the best fit but I'm at a loss how to design the fact table. Do I preaggregate the data into periods broken down by status? And then how do I manipulate it in SSAS and how do aggregations work as it's more like a bank balance. I sort of get some of the concepts but I'm still pretty confused. Cheers, Chris.

[jchernev]

I'm not an expert myself either but it looks like this approach may get you what you want - http://www.kimballgroup.com/html/12dt/DT%20145TimeStampingAccumulatingSnapshotFactTables.pdf

As stated in the pdf, you can deliver both the current state of each grain while maintaining history/trends.

[FresnoBob]

Yes I looked at that but I was under the impression that was for a defined set of states that follow a process. In my situation an agreement can be started cancelled uncancelled frozen cancelled again and so on. Am understanding the accumulation incorrectly? Cheers. I'll look at it again.

[jchernev]

You'd have a finite amount of states right? You are bound to have a few exceptions records that are going to be coming in and out of different states a lot more than your average record and that's okay.

I'd simply role-play all the date dimensions for the different states and keep this accumulating snapshot history.

Then again, there may be better approaches to this.

[BoxesAndLines]

It seems like you should have some more metrics. What you have at this point is a factless fact table with a status dimension. Make the status dimension a type 2 and you can answer the questions you are asking.

[FresnoBob]

Bear in mind I only started reading Kimball today! I'm really just exploring the concepts. The idea that I think would solve my problem would a factless kind of aggregated fact table. Basically there is an agreement which has status' which change over time but the agreements relate to people with ages and locations and so on which also will need analysis. I was thinking a kind of factless aggregation at the end of each month and then add this to a master factless table for all the months which I could pull into my cube. I think that would answer the questions I am thinking of at the moment mainly cumulative trends over time. But I suspect I am not going in the right direction at all....

[FresnoBob]

Hi I'm looking at slow changing dimensions as per your suggestion but I can't get SSAS to use them.

In SQL I have a fact table with a agreementid and current status
I have a dimension table with a surrogate id and a row for each change to the agreement with a start date etc.

How do I make SSAS convert this data structure into cube I browse and query?

Cheers, Chris.

[jchernev]

I'm not an SSAS expert but I'm pretty sure you can use SCDs with it. I believe it's a part of the the cube definition inside the SSAS project. You'd have to link your fact and dimension table based on a single key column (which is a weird limitation in SSAS).

[Sponsored content]