Point of time information from accumulating snapshot.

View previous topic View next topic Go down

Point of time information from accumulating snapshot.

Post  arnayj on Thu May 24, 2012 12:22 am

tblCase(
CaseID
,CaseOpenedDate
,CaseClosedDate
,... [other dimension keys like country etc]
)

We need to find the count of open cases at end of each month i.e. for 2012 jan the open number of cases can be found by below query
select Count(*) CaseCount from tblCase where CaseClosedDate > '2012-01-31' and CaseOpenedDate < '2012-01-31'
But I need to get them for each month and each country.
Any help to model this type of scenario.

Or if there is any tacnique to convert this accumulating snapshot to transactional will be of great help.

arnayj

Posts : 5
Join date : 2011-03-01

View user profile

Back to top Go down

Re:Point of time information from accumulating snapshot.

Post  hkandpal on Thu May 24, 2012 8:07 am

Hi,

did you try Count(*), period, country GROUP BY period, country ?
How many rows are there in the table ?

thanks

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re:Point of time information from accumulating snapshot

Post  arnayj on Thu May 24, 2012 8:20 am

This is a role playing dimension, which role of period you are talking about open date or closed date? but below example will not work.
I already have posted the sql logic to calculate that point of time snapshot, I need that value for all the time.
Moreover there are 1.5 million rec in the case table for now.


hkandpal wrote:Hi,

did you try Count(*), period, country GROUP BY period, country ?
How many rows are there in the table ?

thanks

arnayj

Posts : 5
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Point of time information from accumulating snapshot.

Post  ngalemmo on Thu May 24, 2012 11:54 am

It would be easy if your date dimension has a last day of month attribute. You could do something like:

select d.year, d.month, c.country, Count(*) CaseCount from tblCase c, date d where CaseClosedDate > d.date and CaseOpenedDate < d.date and d.lastdayofmonth = true group by d.year, d.month, c.country;
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

You may want to preserve history in the accumulating snapshot fact

Post  tim_huck on Thu May 24, 2012 12:17 pm

See Kimball Design Tip #145 "Time Stamping Accumulating Snapshot Fact Tables", also try searching for "SCD type 2 fact table", a poor choice of terminology that some practitioners are using to describe the same thing. Assuming that cases have an evolving status over time as best represented by accumulating snapshot, where you want to know the detailed status at some past time, preserving history within the snapshot fact is probably better suited for your situation than making it a transaction table.

To answer your question, the technique to turn such a table into a transaction fact is to review all the data changes that currently cause a row to be inserted or updated, and assign them event names so they can be modeled as transactions. You end up with events such as "New case", "customer interaction", "resolve case", "Close case", "Re-open case", etc.. This can be useful in some situations, and I've used it multiple times with mixed success (always got the answers needed, but in some cases with great complexity in ETL development and/or report logic). I recommend trying the history-preserving accumulating snapshot as in the design tip, if that describes your data needs at all.

Once you have a historic fact table as described, you can get the open case count for previous periods by filtering on the last update date to count using the version of the data in effect at the end of that period.

Have fun!

tim_huck

Posts : 8
Join date : 2009-04-09
Location : Evanston, Illinois

View user profile

Back to top Go down

Re: Point of time information from accumulating snapshot.

Post  ngalemmo on Thu May 24, 2012 12:27 pm

While there are techniques to convert snapshot data to transactional data, I do not believe a transaction fact helps you, it may even make matters worse.

To identify of a case was open during the month requires that you have a begin and end time period. A transactional arrangement would have a row when it starts and a row when it closes. You would then need to combine the two rows to get the time period you already have in the snapshot. The structure you already have is appropriate for the query you are trying to do.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Point of time information from accumulating snapshot.

Post  arnayj on Fri May 25, 2012 9:14 am

Thanks for the valuable feedbacks. I went thorough the design tip #145 - timestamping accumulative snapshot and found that ngalemmo is correct and that will not help current situation. As we have big SCD-1 dimensions and the query was taking very long time. I thought may be some design can help.
Actually we are using SSAS cube for data destribution and MDX is getting very crazy for the same... will be exploring the same.

Thanks again for all of you guys.

arnayj

Posts : 5
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Point of time information from accumulating snapshot.

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