SQL Cube Processing Window

Question

I've got Dim Tables, Fact Tables, ETL and a cube. I'm now looking to make sure my cube only holds the previous 2 months worth of data. Should this be done by forcing my fact table to hold only 2 months of data and doing a "full process", or is there a way to trim outdated data from my cube?

Answer 1

Your data is already dimensionalized through ETL and you have a cube built on top of it?

And you want to retain the data in the Fact table, but not necessarily need it in the cube for more than the last 2 months?

If you don't even want to retain the data, I would simply purge the fact table by date. Because you're probably going to want that space reclaimed anyway.

But there are also settings in the cube build - or build your cube off dynamic views that only expose the last two months - then the cube (re-)build can be done before you've even purged the underlying fact tables.

You can also look into partitioning by date:

http://www.mssqltips.com/tip.asp?tip=1549

http://www.sqlmag.com/Articles/ArticleID/100645/100645.html?Ad=1