none
How to reduce processing time in Cube in SSAS 2008

    Question

  • HI All,

    I have created cube in SSAS 2008 which is like to fetch data from oracle database ,for this i have created partition in one of the measure group which contains data of 20 month ,when i process the Cube in SSAS 2008 it will take 5-6 hr which is not usual .

    I have created partition with aggrregation .

     

    Kindly Suggest for the same.

     

    Regards

    Amit

    Thursday, August 19, 2010 11:27 AM

Answers

  • So if you don't have access to Oracle and don't know the rowcount, how do you know that the WAN speed is acceptable? Also, why do you merge the partitions, why not leave the data in the 20 partitions? To check the number of processors either log on to the server and check task manager or use perfmon to connect to the server and check the processor counters. I see you have also started another thread on this, should we be responding to that one instead?
    Thursday, August 19, 2010 6:18 PM
  • I'm afraid I cannot give you any more information than is already in this thread and the other two related threads that you have started. You really need to read the performance guide and check amongst other things:

    1. Your WAN and Oracle database is tuned and capable of returning the data quick enough.
    2. AS has multiple processors and is processing in parallel.
    3. The IO subsytem for AS is fast enough.
    4. You are not saturating the IO or CPU or memory of the AS server during processing.
    Please read the performance guide document mentioned above and tune accordingly. If it still doesn't process fast enough then let us know what you have tried and how performance has changed or not changed.
    Friday, August 20, 2010 1:55 PM

All replies

  • For Improving Processing performance lots of things matter like your server configuration i.e RAM,Operating system, if you are using Windows Server 2003 OS then you can preallocate memory for your analysis services which improves processing performance. 

    Other things like attribute relationships also plays vital role.

    You will get more details in the Performance guide for SSAS 2008 which you can download from the link;

    http://www.microsoft.com/downloads/details.aspx?FamilyID=3be0488d-e7aa-4078-a050-ae39912d2e43&displaylang=en

    Also another useful link;

    http://sqlcat.com/technicalnotes/archive/2008/07/16/running-microsoft-sql-server-2008-analysis-services-on-windows-server-2008-vs-windows-server-2003-and-memory-preallocation-lessons-learned.aspx

     


    Ani
    Thursday, August 19, 2010 12:03 PM
  • Hi Aniruddha,

     

    This will not help please elaborate more.

     

    Regards

    Amit

    Thursday, August 19, 2010 1:05 PM
  • So how much data are you talking about? 20 months worth of data taking 5-6 hours to process seems like it is quite a few rows, assuming there is no network latency involved here.

    As such, you probably need to create more partitions in Analysis Services (maybe by month) such that you can then process the data in parallel. Note that the general rule of thumb is no more than 20 million rows in a partition. Using a single partition means that AS can only process the data in serial and then needs to build aggregations on a huge volume of data. Splitting this across multiple partitions means that AS can process a number of these in parallel and create aggregations and indexes on lots of smaller sets of data which will be quicker.

    However, be careful how partition your data as ideally you need to keep the partitions in line with how your users will query them if you can. (eg. If users will tend to query by month then monthly is a good way to partition the data as a single query will hit only 1 partition and should be quick as well.)

    Thursday, August 19, 2010 1:15 PM
  • Regarding Preallocate you will get more detailed information in performance guide under Tuning Server resources ->Using Preallocate.

    More detailed information regarding other processing performance optimization techniques, go through the following SSAS 2008 performance guide topics;

    Understanding and measuring processing,Enhancing Dimension processing performance.

     


    Ani
    Thursday, August 19, 2010 2:51 PM
  • Hi Philip,

    i have partitioned the data on the basis of Sysdate of 30 days and created 20 Partition using parallel one and aggregate them indiviually and finally merge all the partition parallely but still processing time is not reduced

     

    One thing more -- i have some issue in attribute ,,,when i process the cube i found error "Attribute not found " so i have used the Stop looging option in setting.......

     

    Please tell me would this effect the performance .......of cube processing

     

     

    Regards

    Amit

    Thursday, August 19, 2010 3:52 PM
  • Hi all,

    Please tell me merging of partition reduced the Processing time of cube. as partiotned,aggregated and merged the partition but still processing time not reduced

     

    Regards
    Amit

    Thursday, August 19, 2010 3:56 PM
  • Okay, first off you really need to fix the error, from a data integrity issue if nothing else. Certainly I have found that errors in AS are resource intensive from a querying perspective and would imagine that they are to a degree from a processing perspective too based on bubbling up the exceptions etc.

    Secondly, I don't really understand what you mean about the partitioning you have done. So...

    1. How many rows do you have in total?
    2. How many processors in the Analysis Services server?
    3. Is the network between the two databases fast enough or are you going across a WAN?
    4. Do you have 20 Analysis Services partitions and have they been configured to only select data from the table that is for the given partition? (i.e. Only day 1 should go into partition 1 and day 2 into partition 2 etc.)
    5. When you process the partitions, are you doing so in parallel? I am assuming that the Analysis Services server has multiple processors.
    6. You shouldn't need to merge partitions so what do you mean about merging?
    7. What do you mean by "aggregate them individually"?
    Thursday, August 19, 2010 4:04 PM
  • HI Philip,

    Ans 1 :  I exactly not able to count the total rows as i dont have access to relational database.

    Ans 2: Please tell me where to check number of processor in analysis services server.

    Ans 3: YAP ,network is connected across a WAN but speed is good enough .

    Ans 4: YAP i have created 20 partition and and in every partition same query is used accept sysdate filter is changed.

    Ans 5: Ya i have done this in Parallel mode.

    Ans 6: Actually i had merged the partition together.

    Ans 7: I have created partition say of July.10 and simulteneously i have aggregate the same....and i have done this to every partition i have made.

     

    Regards

    Amit

     

    Thursday, August 19, 2010 4:13 PM
  • So if you don't have access to Oracle and don't know the rowcount, how do you know that the WAN speed is acceptable? Also, why do you merge the partitions, why not leave the data in the 20 partitions? To check the number of processors either log on to the server and check task manager or use perfmon to connect to the server and check the processor counters. I see you have also started another thread on this, should we be responding to that one instead?
    Thursday, August 19, 2010 6:18 PM
  • I am not really sure I understand your question, so if you could restate it along with a longer description of the problem that would be useful.

    I have found that having partitions not only increases query performance, but also cube processing performance as well as giving you some options for incremental processing over time.  The time that it takes for your cube to be processed is going to greatly depend on the speed and number of processing cores available on your SSAS server.  If you merge the partitions and notice that CPU utilization doesn't hit 100% while you are processing your cube, then you should split the partitions out again and see if that allows for quicker processing.

    This is all assuming you have the enterprise edition of SSAS which allows you to use parallel processing.

    Thursday, August 19, 2010 6:31 PM
  •  

    Amit,

    I would really study the option of loading the Oracle data (that is needed on SSAS) into a staging SQL database (preferably the same SSAS machine)

    Build a simple ETL tool using SSIS to load data from Oracle to SQL (overnight task)

    Given that the SQL server is a fat one, and taking into consideration all the above notes (attributes relationships, etc...) I can confirm that the performance will be much better.

     

      


    My Profile

    Jordan Group

    Raed Taha

    Thursday, August 19, 2010 8:35 PM
  • HI Philip,

    Please  tell me step wise way to increase the performance of cube processing.What should i check in task manager for analysis services processor

     

    Regards

    amit

    Friday, August 20, 2010 9:32 AM
  • Hi Justin,

    Please tell me how to check number of processing cores available on your SSAS server.

     

    Regards

    Amit

     

    Friday, August 20, 2010 9:46 AM
  • I'm afraid I cannot give you any more information than is already in this thread and the other two related threads that you have started. You really need to read the performance guide and check amongst other things:

    1. Your WAN and Oracle database is tuned and capable of returning the data quick enough.
    2. AS has multiple processors and is processing in parallel.
    3. The IO subsytem for AS is fast enough.
    4. You are not saturating the IO or CPU or memory of the AS server during processing.
    Please read the performance guide document mentioned above and tune accordingly. If it still doesn't process fast enough then let us know what you have tried and how performance has changed or not changed.
    Friday, August 20, 2010 1:55 PM
  • Hi

    dont forget use the native oracle provider, when you  use the native oledb driver this offers a great performance

    kind regards

    Friday, December 06, 2013 10:47 PM