September 24, 2008 at 10:26 am
We have Finance Cube with partition rowcount of 12Million for year 2007.
While Partition 2007 is processed
1) First it reads 1Million records from the source then groups them,there after
2) It then reads another 1Million records from the source then groups them and similarly this process continues million by million untill 12Million records it reads and groups then build indexes on them.
This takes about 3Hours 54Minutes to completely process the 2007 partition only.
Also partition query when run against the source database takes 14 minutes evethough the columns and respective tables are properly indexed[Fact table in Finance cube had 75Million rows].
Is there any option available in AS2005 where by AS2005 can read all the 12Million records in one go succeeded by grouping and follwed by indexing.
Idea behind asking this question is to reduce the total processing time of the 2007 partition.
Please let me know.
thanks,
venu
September 25, 2008 at 9:34 am
Check the throughput on your database. In general, is you're not approaching 100% CPU utilization on your MSAS box, you probabaly can do better. Plus, better to separate your MSAS box from your SQL box. Otherwise, you'll probably get caught in various syncronous senarios, like I/O bottle necks.
In general, partition your table based on yhour update strategy and cluster it based on your MSAS partition strategy. (The clustering will improve the 14 min query.) Plus, limit your database connections to one per CPU, to avoid over driving your database server.
For MSAS, I suggest you partition your cube based on typical query usage. While that may not optimize cube updates, it does optimize the querys (which is the point of MSAS in the first place!). We limit the processing threads on the MSAS server to 1 per CPU, again, to avooid overdriving the server (it's sometimes helpful to finish a task before starting another one!).
For some relative stats:
We process a cube containing 1.2 billion data rows (500,000 products, 1,200 stores, 108 periods). We do a full process every week (weekly delivery) that takes 2 hrs. The MSAS servier is a 4 way, dual core 64 bit machine. The database server is a 4 way, single core 32 bit machine (it's old).
Good luck.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply