SSAS Deployment Wizard – Issue with multiple connection strings

I've often found errors reported from SSAS indicating a duplicate key value in the dimension. When I've reviewed the source data, the duplicates aren't found. While I can't shed light on why this error is popping up in the dimension, I can offer a solution that bypasses the error and allows the cube to finish deploying and processing.

If you open the dimension object and look at the properties, you'll see a section for ErrorConfiguration. If you change this to "custom", you can tell SSAS what to do when it encounters errors in processing the object. If you want to log the error and allow the processing to continue, use "ReportAndContinue" as the value for properties like KeyDuplicate, KeyNotFound, NullKeyConvertedToUnknown, NullKeyNotAllowed - whatever is appropriate for your design. I set the KeyErrorLimit to 1 and set the KeyErrorLimitAction to StopLogging so your log files don't get overloaded with the same or similar errors.

As I said, this doesn't SOLVE the issue, but it does allow the cube to continue processing and sometimes, I've found, that's enough.

I will tell you the way we deploy in my place SSAS projects between different environments. We use a set of Powershell scripts that:

Part 1:

• get the last version from the build server and deploy the file to generate the last working version of XMLA;
• take the generated XMLA from the build server and copy it to the QA server;

Part 2:

• Now, on the QA server, we change manually the server name, the database name (set as parameters to the PS script), we verify that all required scripts are there;
• one PS script runs the resulted XMLA on the current QA server, so the SSAS database is created (not processed though);
• a XMLA script that commands the full process of the cube and its dimensions;

All of these steps are done as automatically as possible, with as little intervention from the developer/QA guy as possible. We do, though, insert some eyeballing in between, just to keep our minds safe. I won't say that it's the best, but it works and gets the job done. Before the last step I'm sure you can insert any XMLA script to remove existing roles and create new ones. And to change a PS script to execute some new SQL or XMLA script is not hard at all. I suppose that the roles and permissions don't change that much, so they can be scripted and reused.

PS: this is an environment with domain users and fixed servers (so we can use shares, domain accounts and all that fluff). If you have another environment it might be a more difficult to get it done.