Error in large OLAP cube generation

[cbasah]

Hi,

I am new to OLAP and have been trying create a large OLAP cube from SAS ABM.

The fact table has about 150 million rows. 16 dimensions, each having on average 3-level hierarchies and not more than 4 levels.

The OLAP server runs on a machine with the following specs:

- 8 core

- 32 gb of memory

- 500 GB dedicated disk for tempdb

- underlying RDBMS is MSSQL

I face 2 problems in generating the cube with the following settings:

1) Aggregation with index

- the 500gb-tempdb gets maximised causing a cube generation to stop with tempdb full error

2) No aggregation index (NOINDEX option applied to PROC OLAP)

- the tempdb does not max out and grows up to approximately 200 gb

- however,the OLAP cube generation runs for more than 8 hours before ending with a "...Class selection..." error

- SQL is telling me that:

     - there are 100 parallel threads each doing the same select statement of the entire fact table

     - each statement is suspended with CXPACKET wait types

- Window Perfmon is telling me that:

     - tempdb disk queue length of about 0.8 to 1.2

     - disk write is on average is 8mb/s and disk writes on average is 32mb/s

     - Page life expetancy of about 220 (An MS SQL 2005 recommends > 300)

I believe all the above are happening due to the size of our fact table. Reducing dimensions is not an option due to business requirements. I was thinking of splitting the fact table into smaller tranches where each selection query can fit into memory and avoid going into disk i/o. Appreciate insights/ advice on the above problems.

Thanks.

[LinusH]

The first question that comes into mind is, do you really need all 150'' rows in the cube?

Maybe, some detail level could be accessible via drill-through?

Is the fact table aggregated to the lowest level of the dimensions? If not, try to summarize it before the PROC OLAP.

If yes, are you using COMPACT_NWAY? Try without it.

Since you hav more dimensions than cores, you might want to try setting ASYNCINDEXLIMIT.

For testing purposes, try load let's say a 10th of the fact table, and try differnet option settings to see how they influence your cube build.

Are you on 64-bit Windows? And during the job, are you getting all available RAM for you SAS process?

I'm not very familiar with Perfmon statistics. But if you find some of these alarming, maybe you should make a call/send message to SAS tech support.

/Linus

[cbasah]

I managed to create the cube using the NONUPDATEABLE flag which means my dimensions are non-updateable. The cube is created monthly though.