I'm using SQL Management Studio to execute some UPDATE CUBE MDX statements
and I can see them being received through Sql Profiler but nothing is being
written to the writeback table. I can retrieve the values with SELECT
statements but nothing is updating. There are no errors that I can see in
traces for either Analysis Services or Sql Server. Any tips on where I can
check next?

I've confirmed that using the undocumented series of UpdateCell XMLA
commands updates the values properly and Sql Profiler seems to be showing
the same NTUserName so cube security doesn't seem to be the problem.

Does the management studio need an additional setting to commit the
transaction? I thought I read that auto-commit is the default.


On 12/27/06 12:41 PM, in article C1B81A8C.43D8%zgrose (AT) mac (DOT) com, "Zoltan
Grose" <zgrose (AT) mac (DOT) com> wrote:

OK, found the right sequence.

First you run a BEGIN TRANSACTION query.
Then you run the UPDATE CUBE query.
Then you run a COMMIT TRANSACTION query.

I tried experimenting a little bit and you can combine the three statements
into a single query by using the (undocumented?) GO keyword.

So the basic setup for an UPDATE CUBE query is:

BEGIN TRANSACTION
GO

UPDATE CUBE [cube_name] SET ([mbr1], [mbr2], ..., [mbrN]) = numeric_value
GO

UPDATE CUBE [cube_name] SET ([mbr1], [mbr2], ..., [mbrN]) = numeric_value
GO

COMMIT TRANSACTION
GO


For anyone finding this through Google/whatever, here is the equivalent
ADOMD.NET code (writeback is the sorely underdocumented):

using (AdomdConnection conn = new AdomdConnection()) {

string connStr = "Provider=MSOLAP;Data Source=local;Initial
Catalog=catalog_name";

conn.ConnectionString = connStr;
// you'll want error handling for this too
conn.Open();

using (AdomdTransaction trans = conn.BeginTransaction()) {
try {

string[] statements = new string[] { "UPDATE CUBE [cube_name]
SET ([mbr1], [mbr2], ..., [mbrN]) = numeric_value", "UPDATE CUBE [cube_name]
SET ([mbr1], [mbr2], ..., [mbrN]) = numeric_value" };

using (AdomdCommand cmd = new AdomdCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;

foreach (string mdx in statements) {
cmd.CommandText = mdx;
// no point checking result as it is always 1 regardless
int result = cmd.ExecuteNonQuery();
}

}

trans.Commit();
} catch (Exception ex) {
// or perhaps commit what you've sent? Not sure ExecuteNonQuery
will actually throw any meaningful exceptions at this point.
trans.Rollback();
}
}
}




On 12/27/06 1:29 PM, in article C1B825BE.43DD%zgrose (AT) mac (DOT) com, "Zoltan Grose"
<zgrose (AT) mac (DOT) com> wrote: