Periodic snashot and SSAS

Hello,

I've to choose a periodic snapshot design for one of my fact table to track changes and evolutions of my transactions on a daily basis.
We want to be able to say , this specific day we were at this status with this amount.
Technical environemnent is SQL Server with SSAS, all the accesses will be done through SSAS.

To answer this need, I've to choose between those 2 solutions:

Model one
----ID---------STATUS----------Amount---------FROM---------------TO------------------------IS_CURRENT
----1-------------A---------------201-------------1900-01-01---------2010-12-31-------------0
----1-------------B---------------203-------------1011-01-01---------2054-12-31--------------1

This answer the need, we will be able to track the detail informations for a given date in sql BUT Will be complicated in SSAS, It means that every time you need the value at a specific date, you will need to write a mdx calculation that makes a between.

Model two

----ID---------STATUS----------Amount---------Date------------------------
----1-------------A---------------201-------------2000-01-01
----1-------------B---------------203-------------2000-01-02
----1-------------A---------------201-------------2000-01-03

This answer also the need, it means that you have one line by day (cause we need it by day).

But this one is very consuming in space used , it means that all the transactions will be multiplied by the number of days of data.
--> btw in SSAS no calculation is needed...

What's your opinion on this? Do you have another idea to model this need?

Thanks in advance for you help.

Hi Adriano,

I would try to make Model One work if at all possible - your servers will probably drown in the I/O required to populate and query Model Two.

Have you considered the "Last Child" Aggregation Function:
http://msdn.microsoft.com/en-us/library/ms175356.aspx

This can probably avoid complex MDX for most scenarios. There are a few gotchas to consider, e.g.:
http://prologika.com/CS/blogs/blog/archive/2008/02/06/last-non-empty-affairs.aspx
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=16
* Note if your client tool is Excel it sorts dimensions alphabetically, ignoring the specified sequence

Good luck!
Mike

Hello Mike,

Thanks a lot for your answer.

I don't undertsand you point cause with last child, I will only get the last value but the objective of my fact (periodic snapshot) is to find back measures as there where in the past at a specific date.

For example 1:

If I do it in Sql, I will need a between to find back the value at a specific date (reportingfilterdate Between fromdate and ToDate)--> this is my value at a specific date.

--> BUt I'm here in MDX can be done but will be complex

For Example 2:

The cube will be correct anyway but heavy cause it means that I have one measure each day of the year.

I don't understand how I can solve the first example with last child?

Thanks in advance for your help.

Now I understand your scenario a bit better, perhaps the Last Non Empty aggregation method would suit better. For your example 1, with a Last Non Empty measure, you are able to query for a date range that ends at your "report filter date" and it will efficiently return the result for that date, or for the last prior date in the date range if the selected date returns a null.

OK thank you for your feedback and help !
I think that in my case , as I'm working with MDX Cubes, I will have to adapt the architecture for them and store in my sql tables 1 line for each day of my history without any Start and end date but just with the value and the day when this value was applicable.

I have to store my model like this one.

Model 2
----ID---------STATUS----------Amount---------Date------------------------
----1-------------A---------------201-------------2000-01-01
----1-------------B---------------201-------------2000-01-02
----1-------------A---------------201-------------2000-01-03
---1