Is periodic snapshot the right choice?

View previous topic View next topic Go down

Is periodic snapshot the right choice?

Post  FresnoBob on Mon Jun 04, 2012 8:51 am

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.

FresnoBob

Posts : 4
Join date : 2012-06-04

View user profile

Back to top Go down

Re: Is periodic snapshot the right choice?

Post  jchernev on Mon Jun 04, 2012 12:11 pm

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.

jchernev

Posts : 14
Join date : 2011-12-08

View user profile

Back to top Go down

Re: Is periodic snapshot the right choice?

Post  FresnoBob on Mon Jun 04, 2012 12:19 pm

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.

FresnoBob

Posts : 4
Join date : 2012-06-04

View user profile

Back to top Go down

Re: Is periodic snapshot the right choice?

Post  jchernev on Mon Jun 04, 2012 12:23 pm

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.

jchernev

Posts : 14
Join date : 2011-12-08

View user profile

Back to top Go down

Re: Is periodic snapshot the right choice?

Post  BoxesAndLines on Mon Jun 04, 2012 2:56 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Is periodic snapshot the right choice?

Post  FresnoBob on Mon Jun 04, 2012 6:34 pm

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

Posts : 4
Join date : 2012-06-04

View user profile

Back to top Go down

Re: Is periodic snapshot the right choice?

Post  FresnoBob on Tue Jun 05, 2012 8:01 am

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.

FresnoBob

Posts : 4
Join date : 2012-06-04

View user profile

Back to top Go down

Re: Is periodic snapshot the right choice?

Post  jchernev on Tue Jun 05, 2012 6:14 pm

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).

jchernev

Posts : 14
Join date : 2011-12-08

View user profile

Back to top Go down

Re: Is periodic snapshot the right choice?

Post  Sponsored content


Sponsored content


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