Very Computer

Large Dimensions kill OLAP performance.

There is one OLAP-Database on AS2K (Standard Edition) with Cubes: SalesBase,
SalesOrder, SalesReturn. Everyone Cube is based on own fact table. They have
for now from 100000 up to 2 millions Records.

The cubes are combined in a virtual cube.

Hardware: 2x1GHz Xeon, 2Gb RAM, Hardware RAID-0 auf 3 SCSI Chitah.

ADOMD connection options : Execution Location = 3;

The dimensions (overall 18) have von approximate 10 up to 1000000 members.

This is the namely Problem. The MDX-Query, that slicer is a member of a
large dimension such as product or order, runs considerably slower and takes
mach more RAM on the client side.

WITH

SET [RowSet0] AS '{[Product].[All Product].children}'

SET [RowSet1] AS 'FILTER([RowSet2],(  (([Year].[All
Years].[2001].[H1].[Q1],[Measures].[Position Count])<>0)))'

SET [RowSet3] AS 'HEAD([RowSet2],100)'

SELECT

{[Year].[All Years].[2001].[H1].[Q1] } ON COLUMNS,

CROSSJOIN({[RowSet3]},{[measures].[Sales],[measures].[Profit],[measures].[Pr
ofit %]})

ON ROWS

FROM Sales

WHERE ([Order].[All Order].[20010131_002188])

If a customer dimension member such as [Customer].[All Customer].[(0520510)
Krupp Stahl AG] applies as slicer, performance raises considerably.

The queries, where the huge dimension is drilled down on a Axe, have similar
behaviour.

WITH

SET [RowSet0] AS '{[Order].[All Order].children}'

SET [RowSet1] AS 'FILTER([RowSet0],(  (([Year].[All
Years].[2001].[H1].[Q1],[Measures].[Position Count])<>0)))'

SET [RowSet3] AS 'HEAD([RowSet2],100)'

SELECT

{[Year].[All Years].[2001].[H1].[Q1] } ON COLUMNS,

CROSSJOIN({[RowSet3]},{[measures].[Sales],[measures].[Profit],[measures].[Pr
ofit %]})

ON ROWS

FROM Sales

WHERE ([Customer].[All Customer].[(0520510) Krupp Stahl AG])

The maximum available aggregation count is taken at DB storage design. If
user defined set is not used in MDX-Queries, it get not performance benefit.