rolling partitions in AS 2005

[csmba]

Hi. I am a analysis services/cubes newbie, but on the track to getting what I need to know (-:

I wanted to get some best practices for the following issues (maybe I will start with one and then add as I go...)

My system records data, the classical thing a transactional DB is doing. the fact table gets new entry about every 10 seconds. that data is analyzed and other entries are written to other fact tables.

I then build a OLAP view in analysis services. lets say for now it is a simple MOLAP, updated once every 24H. Now, I have a web page showing the data, like a histogram of the facts in the table. But that histogram is usually just of the last 7 days (for example, if it was products bought, then it would have been a histogram of how many products bought from each product, in the last 7 days).

no, I do it over the partitions set up automatically by AS-2005. (I have one partition for each table). But I thought it would be MUCH more efficient if I had one partition for the last month, and another for the rest. the web page would then only run on the smaller partition and give the results much faster.

problem: I don't know how to make these partitions. I know how to make *a* partition, but how do I tell it that "it is of the last 30 days" data? and then another partition takes the rest?

thanks

 

[blom0344]

If you need a rolling set of , say , 30 days then why not split your factdata by using a view (union)
that splits the data into 2 sets?

like:

SELECT 'lAST 30 DAYS' AS TYPE,........ FROM .....
WHERE TDATE >= (DATEADD(day, -30, GETDATE()))
UNION
SELECT 'OLDER THAN 30 DAYS' AS TYPE,........ FROM .....
WHERE TDATE < (DATEADD(day, -30, GETDATE()))

and then partition over the new 'TYPE' field..



Ties Blom

 

[unclejimbob]

In the same vein as the above I guess you could also apply a filter condition (using the Advanced option in the last screen on the partition wizard) to the two partitions to give the required split.

 

[csmba]

thanks guys. I will try that.