1) Since Cell.Value is read-only, what is the solution to writeback values
to an AdomdClient.Cellset?

2) Assuming we have to use UPDATE CUBE to make leaf level update to
workaround #1, can we string multiple UPDATE CUBE statements into a single
AdomdCommand?

I'm trying to find the most efficient path to update a large grid of values
(2 years of columns and 100 rows of data is 2400 cells) without making more
than 1 or 2 server round trips.

I'm working with ADO.NET 2.0 and Analysis Services 2005.

Hi Zoltan,

I suppose that the roundtrips to the server will be not a bottleneck in your
scenario.
The real bottleneck will be inserts in facttable of the write-back rolap
partition.
Don't forget to wrap all the commands in one transaction.

Vladimir Chtepa


"Zoltan Grose" <zgrose (AT) mac (DOT) com> schrieb im Newsbeitrag
news:e4tFJbfHGHA.648 (AT) TK2MSFTNGP14 (DOT) phx.gbl...

Several thousand HTTP calls (since AdomdClient needs to do XMLA over HTTP
for reasonable impersonation) per CellSet is more than likely going to be
the bottleneck. As I understand from some knowledge base docs, Sql Server is
already smarter about batching the relational side of the situation.But the
OLAP API seems to be genuinely lacking in either features or documentation
(or both).

I have a support case open but they don't seem to have any idea yet either.

-z

"Vladimir Chtepa" <vc.nospam (AT) diacom-systemhaus (DOT) nospam.de> wrote

Unfortunately ADOMD.NET doesn't support the cellset writeback feature that
is in the OLE DB for OLAP provider. If you use the OLE DB for OLAP provider,
it implements a batched cell update mechanism.

For ADOMD.NET the only current options are:
- use the UPDATE CUBE statements
- do your own writeback to the relational database

Theoretically you could use the batched updated mechanism in ADOMD.NET
yourself with custom XMLA statements, but it is a totally non-trivial
solution and I wouldn't seriously recommend it...

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Zoltan Grose" <zgrose (AT) mac (DOT) com> wrote

Can you confirm that using AdomdCommand with multiple UPDATE CUBE statements
involves a round trip per command or is there a single round trip when then
transaction is committed?

Something tells me I won't be able to use this without doing some odd
chained delegation hack but I'll investigate.

-zoltan


"Akshai Mirchandani [MS]" <akshaim (AT) online (DOT) microsoft.com> wrote

Yes, UPDATE CUBE will require multiple round trips -- they shouldn't be very
expensive in general though (compared to a single round trip) unless you are
on a slow network.

I would expect that the cost of actually updating a cell (or rather many
cells in a transaction) would be quite a bit larger than the additional
round trip cost...

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Zoltan Grose" <zgrose (AT) mac (DOT) com> wrote

My experience with AS2K was that updating the writeback table was fast
enough (all in relative terms, of course) but the HTTP POST-> MSAS
process -> HTTP response -> move to next cell cycle had enoungh overhead
that creating a direct-to-relational SQL update gave us reasonable upload
performance with the cost of exponentional configuration/deployment issues.

I was hoping AS2005 would make some progress since my company lives in a
less analytic and more planning space of OLAP (small amounts of actual data
but large amounts of shared what-if and collaboration data). Apparently
Hyperion still rules this domain.

Looks like UPDATE CUBE will have to be the path. I'll forward customer's
performance concerns to our MS TSC. =)

Thanks for the help.

-z


"Akshai Mirchandani [MS]" <akshaim (AT) online (DOT) microsoft.com> wrote

Firstly, ADOMD.NET doesn't *have* to use HTTP -- unless you explicitly
specify "http://" in your data source, it will use TCP. If that is an option
for you then you should find a significant improvement in performance if you
use TCP over HTTP.

Secondly, the update of the writeback table only happens at commit time. The
intermediate updates are expensive because of the cost of inserting and
searching an in-memory cache of updated cells. In fact, in AS 2000 you may
not even have seen much network traffic for the individual UPDATE CUBE
statements because a lot of the work was done on the client. On the other
hand, a single UPDATE CUBE statement may have required the client to fetch
lots and lots of data from the server and therefore caused lots of network
traffice

So essentially I would suggest taking a look on 2005 to see how good or bad
things really are...

In the meantime, your concerns about this are noted -- and I'm sure this
will improve over time.

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Zoltan Grose" <zgrose (AT) mac (DOT) com> wrote

A small followup.

The authentication requirements/restrictions (depending on your point of
view) for the TCP option makes it an undesirable option in most web 3+ tier
applications. It seems more appropiate for thick client Windows applications
where the end user is making a direct connection with SSAS for data.

Yep. Moving forward. Get it working, get it working right, get it working
faster. That's my motto. I guess we need to add "get it working securely" at
the end there now. =)

"Akshai Mirchandani [MS]" <akshaim (AT) online (DOT) microsoft.com> wrote