MDX Script deploy - Sharing my experience

Topics: Resolved
May 15, 2007 at 9:13 PM
Just so other users would know:

- If you try to do deploy MDX while cube is checked-in (read-only), you will get error message:
Access Denied.(Exception from HRESULT: 0x80030005 (STGEACCESSDENIED))
I do not believe this is a bug, or anything can be done. I am getting very similar message when I try to run SSIS package from BIDS environment while package is checked in. This is just heads up.

- After using MDX Deploy, if you will try to deploy whole cube, you will get message:
The 'xxx" database on "server" has changed since the last time the project was deployed. If you proceed with deployment, the database will be overwritten. Would you like to continue?
Again, not a big deal, just so other know that this will happen.

- No errors are generated when I reference non existing dimension member or in some other cases. If I deploy cube in full, then I do get error message while deploying.
Example when it was noticed:
I used: Dim.Hierarchy.Level.&Name
Was supposed to be: Dim.Hierarchy.Level.Name
Also other types of errors are not reported. Not a big deal, error is reported latter when querying using front end.


Coordinator
May 15, 2007 at 10:04 PM
Thanks Vidas,

I think the first issuwe may be because we are trying to force a save to make sure any edits are flushed to disk before we push them up to the server. I think there might be a couple of approaches to fix this so I will investigate and see what I can do.

This second issue is this is basically BIDS saying that something else has altered the DB since it was last deployed which is correct. But the intent of this message is to warn you that the server and BIDS are out of synch which is not correct, I think there might be a way to fix this so I will add it to my list. :)

The third issue is interesting as we are not doing any special validation of our own, we are actually asking BIDS to validate the script, so I would not have expected any errors to get through which were caught by a full deploy. Neither BIDS nor BIDSHelper do a really deep check for the existance of members so there will always be some errors that will not be found until runtime. I will double check the validation, if you could post a sample calc against Adventure Works that reproduces this issue that would really help
May 16, 2007 at 12:11 AM
For the third issue:

To reproduce just add statement anywere in your code:

END SCOPE;

This statment has valid syntax, so MDX will deploy. But when you will query data, you will get error.
Coordinator
May 16, 2007 at 3:25 AM
Thanks Vidas, I have fixed the issue with trying to save to a read-only / checked in file. It is checked into source control and will be included in the next release.

The "END SCOPE;" issue is interesting as clicking on the "validate script" button in BIDS does not catch this either. It appears that these sorts of issues are only found when the script is deployed and the server does a full parse on it. I have a few ideas I will try to see if I can improve this experience.
May 16, 2007 at 1:10 PM
Thank you for such an awesome set of utilities. The "MDX deploy" piece alone should save me quite some time every day - if only I can get it to work, though. When selected, all it does is bring up a message box telling me "Object reference not set to an instance of an object". any idea what I'm doing wrong?
Coordinator
May 16, 2007 at 9:32 PM
srlanger - This feature was one of the simpler ones in terms of implementation, I'm not sure what is going on here. I think I have figured out how to fix the three issues that Vidas has raised. I might improve the error reporting for the next release too so we can figure out what is going on in your case.
May 17, 2007 at 4:25 AM
Darren - thanks! Looking forward to it.
May 25, 2007 at 2:53 AM
Edited May 25, 2007 at 2:53 AM
Darren,

I tested END SCOPE issue with the todays release, and everything now works - errors are generated.

This is very very useful utility. Saves me a lot of time! Thanks very much for making it.
Coordinator
May 28, 2007 at 1:48 AM
srlanger - We think we found your issue. We occasionally have trouble figuring out the default deployment server and there was not sufficient error trapping around this section. See how you go with the latest (v1.1) release.
May 29, 2007 at 6:23 AM
Edited May 29, 2007 at 6:23 AM
Thanks! This now behaves as advertised;-) A week from now I'm going to wonder how I ever worked without it.