My environment: SQL Server 2005 for database and SQL Server 2005 Analysis Services.
I have a fact table of 78M rows. I want to only process the first 10K rows in my development phase.
Other than removing the rows from my Fact table, is there another way of achieving this?
In the Data Source View, right click the Table Name, choose "Replace Table / Named Query" and in the query, use TOP N
EDIT
Once you do this, if you add Named Calculations to it, and then you replace the Named Query back with a Table, you lose your Named Calculations. Something to be careful about!
I agree with Raj on changing to Named Query. You could also build the top N into a database view and then add or remove TOP N from the view without having to change the deployed analysis services cube. Or you could point the view at a separate table that contains the 10K rows.
Another alternative would be to keep the DSV as it is, but change the partition on the Measure Group to use a TOP N or alternatively a trimmed down table. Then just change the partition back when you want to process all rows.
The benefits of using a separate table to duplicate the 10K rows is that you're going to guarantee you get the same results each time (TOP N doesn't) and so testing the results may be easier.
there is one more thing can be done if, Use Aggregations tab and Calculate the aggregations. Keep the size limit to 1 -2 MB. So it will calculate very less members in the partition.