Hi,

I'm experiencing strange server behaviour (locks/hangs?) when doing UPDATE
CUBE that results in large number of leaf level rows to be written to SQL.

First example:
UPDATE CUBE [Przychody i Koszty Bud¿et] SET (
[Bud¿et].[Scenariusz].[Podstawowy],
[Czas].[Kalendarzowy].[Wszystkie lata].[2004].[Kwarta³ 1, 2004].[Styczeñ,
2004],
[Firma].[Struktura].[Ca³a firma],
[Kategoria Finansowa].[Wynik].[Koszt],
[Measures].[Zwiêkszenie],[Wymiar].[Charakter kosztów].[Wszystkie],
[Wymiar].[Dzia³alno¶æ firmy].[Wszystkie],
[Wymiar].[Model].[Wszystkie],
[Wymiar].[Pracownicy].[Wszystkie],
[Wymiar].[Rodzaj kosztu].[Wszystkie]).VALUE = 30000 USE_WEIGHTED_ALLOCATION
BY 1 / 14 / 3 / 3 / 15 / 42 / 70 / 97

After some 30-40 minutes throws out "Syntax Error near: <here the above
MDX>". In this case update tries to write 14 * 3 * 3 * 15 * 42 * 70 * 97 =
538,990,200 rows

Trying to lighten the load, after removing one dimension from distribution
(97 members) I used the following MDX:

UPDATE CUBE [Przychody i Koszty Bud¿et] SET (
[Bud¿et].[Scenariusz].[Podstawowy],
[Czas].[Kalendarzowy].[Wszystkie lata].[2004].[Kwarta³ 1, 2004].[Styczeñ,
2004],
[Firma].[Struktura].[Ca³a firma],
[Kategoria Finansowa].[Wynik].[Koszt],
[Measures].[Zwiêkszenie],[Wymiar].[Charakter kosztów].[Wszystkie],
[Wymiar].[Dzia³alno¶æ firmy].[Wszystkie],
[Wymiar].[Model].[Wszystkie],
[Wymiar].[Pracownicy].[<here a particular member>],
[Wymiar].[Rodzaj kosztu].[Wszystkie]).VALUE = 30000 USE_WEIGHTED_ALLOCATION
BY 1 / 14 / 3 / 3 / 15 / 42 / 70

that gives now two orders of magnitude less rows (about 5.4 mln).

Now I have two scenarios:

1) Open transaction -> Update Cube (from above) -> second update cube (again
from above, but with different member in [Wymiar].[Pracownicy] dimension ->
OLAP hangs (does not return control after execute)

2) Opening transaction -> Update Cube (from above) -> Close transaction,
Open new transaction -> second update cube (again from above, but with
different member in [Wymiar].[Pracownicy] dimension -> works fine

Looks like a transaction that spans many rows is somehow running out of
resources. Killing client process does not help, things come back to normal
after reprocessing the budget cube (that probably rolls back teh transaction
and frees some resources).

My question is - what are the limitations in the writeback scenario and what
resources should be tracked / increased to make things work fine?

TIA

Szymon Slupik, CTO
CDN S.A., Krakow, Poland

Hi Szymon,

Thanks for using MSDN Newsgroup!

Looking at the nature of this issue, it would require intensive
troubleshooting. I am trying to find some experts on OLAP to give you a
reply By this way, I believe, will make you closer and quicker to the
resolution.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

OK, I'm waiting for some good news from the experts !
Best,
Szymon

""Mingqing Cheng [MSFT]"" <v-mingqc (AT) online (DOT) microsoft.com> wrote

I have not worked with this problem directly from MDX . My experience is
with usinging a writeback supporting client(ProClarity). Most important is
that all your writeback entries will be allocated down to the leaf-level in
each dimension in the cube. The exception is if you are on the leaf level.
So if you have a cube with many dimensions and each dimension contains many
levels(and maybe many records at the leaf level), this is probably not the
best structure for supporting writeback. Remove dimensions and use fewer
levels in each dimension. Else you will run into performance problems on
your client.

Another approach we are using in a project is to use OLAP actions that
direct the user to a web based data entry page together with a snapshot of
the report/grid in a separate frame. We have a separate fact table for this
and copy the values in this table to the cube fact table with data
transformation services.

HTH
Thomas Ivarsson BI Consultant Malmö Sweden

"Bluetooth" <szymon_dot_slupik (AT) cdn (DOT) com.pl> wrote

Yes, Thomas, I am aware top-down writebacks may be very time / resource
consuming, but I lack the knowledge what exact resource limits we are
hitting. Is the thing memory related or tempdb size related or something
else....?

Thanx,
Szymon

"Thomas Ivarsson" <TI (AT) nospam (DOT) com> wrote

Heavy CPU-usage(100 percent) over a long period on the client that uses
writeback. Memory on the client does not peak the same way. I have not
analyzed network traffic or the effect on the server.

/Thomas


"Bluetooth" <szymon_dot_slupik (AT) cdn (DOT) com.pl> wrote

Yeah, but my problem is not that it takes long, but comes up with an
ambigous error message or locks the server entirely.

Szymon

"Thomas Ivarsson" <TI (AT) nospam (DOT) com> wrote

Hi Szymon,

Could you describe your solution,

How much inserts into writeback partition does your mdx query make?
Are there overlapping inserts (the same cell value should be updated)?
How big is fact table for the writeback partition.

From my expirience I can tell your, that in AS 2K I have accounted many
troubles with the writeback and I have went away from it.
For my bugeting and planning solution I have choose realtime ROLAP and have
developed own allocation algorithm (pure T-SQL) into the fact table.

Vladimir Chtepa.


"Bluetooth" <szymon_dot_slupik (AT) cdn (DOT) com.pl> schrieb im Newsbeitrag
news:#JiTiCCZEHA.1656 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

Hi Szymon,

Let me investigate the error and will get back to you if I have any more
questions.

Thanks

Hong Tan
Microsoft OLAP support

This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Hi Szymon

What level of services pack do you have on your AS? What is the OS? What is
the memory? CPU and # of processor?

In the Update Cube example, are the dimensions you reference to down to the
leaf level?

Based on your initial posts, you clamied that you have 2 scenario, the 1st
senario is to wrap 2 Update Cube in single transaction, you are seeing a
hang. Then the 2nd scenario is to do Update cube in 2 separate transaction
and it work fine. Is this right? In both the above scenario, the Update
Cube is the second Update Cube with 1 less dimension in the distribution,
right?

Thanks

Hong Tan
Microsoft OLAP support

This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!