1

I have 10 dimensions and 10 measuregroups-- each measuregroup is having one partition each.

All the cube when processed it takes 5-6 hrs daily. so i thought to process the cube using SSIS analysis Services processing tasks where 1st i process dimensions "Process Full" and then i Process all the measure groups "Process Full".

MeasureGroup-1 analysis Services Processing task alone which has only one measuregroup takes 2 hours and rest of the 9 measuresgroups are in measuregroup-2 analysis Services Processing task take 2 hours.. So totally it will take around 3 hours to complete processing rather than 5-6 hours.

So do u think this approach is correct!! Please Suggest..if any constraints/Dependancies are there which Provides fault data after processing like this.

user2889510
  • 11
  • 2
  • It's Correct, like you say first you need to process Dimensions and then MeasureGroups, doesn't matter which measuregroups would be first – Justin Oct 22 '13 at 12:49
  • Thnx Justin. One More question :-) what if i process update all the dimensions at a time fisrt. Does it give any incomplete data. !! – user2889510 Nov 29 '13 at 08:41
  • yes you can process all dimensions in first proccesing element and then after that put other processing element with all the measures groups. So yours SSIS package should have 2 elements in first all dimensions in second all measure groups... – Justin Nov 29 '13 at 08:54
  • U mean i can process the all dimensions in one element as process update and all other mesure groups in other element as Process full !! – user2889510 Nov 29 '13 at 09:00
  • If for you works 'process update' for dimensions, yes. I just wanna say what first should be processed dimensions after that you can put MeasureGroups how you want: maybe all in one element, maybe divide measure groups in two elements... – Justin Nov 29 '13 at 09:05

1 Answers1

1

You should measure the amount of time needed. In many cases, doing all in one go can be faster than splitting the processing, as some data can be kept in memory from dimension processing, and the writing to disk need not have finished while measure group processing runs. On the other hand, your approach may be faster.

In general, the only correct answer is: run a test, as there are many factors influencing this.

To optimize processing performance, you would have to find the bottlenecks: Maybe more RAM would help (do you see that most of your RAM is used during processing? Then, testing a server with more RAM may be worth a try); maybe more CPUs allowing more parallelizing would help; maybe optimizing the relational source so that the statements that Analysis Services issues run faster would help; probably reducing aggregations would help to improve processing performance, but might compromise query performance; moving the AS storage to a different disk from the relational data (if they so far share a disk) should improve the performance; faster disks may also be an option worth testing.

Like many performance optimization tasks, without knowing the details, and running some tests, it is difficult to say which changes would improve or worsen the situation.

FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • THnx frank. I dint see any remarkable differnce after test run. any help on how to improve the performance – user2889510 Nov 29 '13 at 08:44
  • @user2889510 I added some general ideas about performance improvement to my answer. – FrankPl Nov 29 '13 at 09:16
  • Thnx Frank. I have initially ran in 4 GB RAM server and later on moved to 128GB RAM to test if it runs a bit faster.. It has run faster n=but .. Not much Improved.. Considering the fact that without seeing/testing the statement issued on Analysis services.. I would like to ask one more question here.. can i divide a BaseFact partition into two partitions where i can rewrite the existing statement into two statements for eact paartition divided using where conditions of statement leaving some date unprocessed depending on modified date of data before and after . – user2889510 Nov 29 '13 at 09:23
  • @user2889510 Actually, that would be worth a separate question in Stackoverflow. But of course, you can do that: in the partition configuration, you can specify the SQL for each partition. – FrankPl Nov 29 '13 at 09:27