Point of time information from accumulating snapshot.

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.

Hi,

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

thanks

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

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;

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!

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.

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.