I have an write enabled cube withe 2 parent child dimensions (unbalanced up
to 8 levels, up to 12.000 leaf members).

Writing in the cube is fast enought. Reading the data from cube is a pain.
Can take up to 20-30sec (i.e.Analysis manager, opening a knot in the
hierarchy). The data in the Write-Table is quite small (1000-2000 recs).


Do you have some tips for optimizing the read performance?

The cube is used in an planning application.


MS SQL 2k+ AS SP4, 2xeon 3.6G 4Gram, disk SAN

I saw a recommendation at one point saying that you should convert the write
back table to a fact / partition using dts.

think there' s an article out there somewhere....


"Radu Colceriu" <radu_colceriu (AT) hotmail (DOT) com> wrote

I had a similar problem with a customer dimension with 340,000 members.
The dimensions did not have much of a 'natural' heirarchy, so what we
did was to lay the parent child salesperson hierarchy over the top.

So we had a couple of other small parent-child dimensions too. What I
ended up doing was writing a stored procedure that produced a ragged
"levelled" version of a parent-child dimension. We were able to go from
query times of 20+ seconds down to a couple of seconds. All this over
hundreds of millions of fact records (split into about 10 partitions).
Of course this is a work around and will only work if the depth of your
dimensions does not fluctuate too much.

The issue with parent-child dimensions is that they are inherantly
tagged as "changing" dimensions and as such only store aggregations at
the "All" level and the leaf level. The figures for anywhere else along
the hierarchy have to be aggregated on the fly.

Regards
Darren Gosbell
<dgosbell_at_yahoo_dot_com>

In article <uW2EZPksFHA.2948 (AT) TK2MSFTNGP15 (DOT) phx.gbl>, xxx (AT) xxx (DOT) com says...

Actually each night the data from write table is moved to the fact table.

Anyway as soon as in the write table are more that 1 record the performance
drops. The time need it by the AS is by building the SQL statement that is
sent to the SQL server. The SQL statement is anyway very fast executed...



"Michael Vardinghus" <xxx (AT) xxx (DOT) com> schrieb im Newsbeitrag
news:uW2EZPksFHA.2948 (AT) TK2MSFTNGP15 (DOT) phx.gbl...