SSAS 2005 cube data load optimisation

Hot Topic

AdventureWorks db for 101 Samples
How to add a col in the fixed position?
Properties affected by Expressions
Syntax error converting the varchar value 'a' to a column of data type int
How to build SQL Commands in a Remote Database Component
Security consideration for Pull Subscription
Recover System Stored Procedure
problem with custom conflict resolution
Replication distribution cleanup error
Capturing and reporting execution errors

SQL Server

Importing Access database into SQLExpress
how to load text file into a table?
DM Windows Forms Controls in November Feature Pack for SS2005
Urgent: Unable to create cube in Analysis manager from text file
Query Designer in SQL 2005 is total %#%!@
Show multiple order records in single row
Transform update sql statements into a pipeline
Automated backups in SQL Express?
SQL Server 2005 Trigger fires per statement or per Record
Deserialization failed: version 2.0 is not a compatible version

SSAS 2005 cube data load optimisation

Hi all:

I am currently loading about 12 million rows of data for a day into an SSAS 2005 cube which has 12 dimensions. The data loads in at around 3 million rows per minute so the current day loads in at around 4 mins. However a few years worth of history data can take days to load. Would any of you have any specific experiences around the following that can minimise the load time further :

1) Location of RDMBS (currently on same box)

2) Use of indexes (clustered, unclustered, a comination or none)

3) Any other quicker method

Please note the environment is as follows:

1) 64-bit dual hyperthreaded 3.7 GHz zeon processors with 12 GB of RAM

2) Schema is optimised - memberkeys are unique for all dimensions

3) Relational data is pre-aggregated - so they are at a one-to-one with the leaves of the dimensions

Thanks in advance to all contributors.

- Suranjan




Answer this question

SSAS 2005 cube data load optimisation

  • ChoKamir

    Few short answers.
    1) Location of RDMBS (currently on same box)
    It is better to put Analysis Server on a separate box.


    2) Use of indexes (clustered, unclustered, a combination or none)
    Take a look at the type of queries Analysis Server sends during Partitions processing and dimensions processing and optimize your indexes to that.


    3) Any other quicker method
    Fastest is to process partitions in parallel. Important here is to figure out how many partitions you can process in parallel. It greatly depends on size of your partitions, available memory and I/O.

    Edward.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.



  • Andrei Ismail

    Hi Edward:

    Thanks for your reply.

    Regarding your points:

    1) Yes I am aware of that - all my normal relational stuff are on a separate box. However, this particular measure group takes more than twice as long when the data is elsewhere - so I am basically having to stage for the process and then archive it off.

    2) Will double check this one.

    3) Is there any documentation/rules of thumb on this one - or just a matter of 'discovering' the right balance by trial and error

    Thanks.

    Suranjan



  • Tomer Noy

    3) Is there any documentation/rules of thumb on this one - or just a matter of 'discovering' the right balance by trial and error

    It is more of the trial and error process.

    Here is case study you should find helpful http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

    Edward.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.



  • SSAS 2005 cube data load optimisation
Answer this question