Before I
made the switch
to Pulse I never had that many experience with Analysis Services cubes (neither Multidimensional nor Tabular). After making the switch from integration
(with Microsoft BizTalk) I did start out with Power Pivot at ADA, on which I
also wrote a few blog posts, about CALCULATE,
which is The
Queen of all DAX functions according to Marco and Alberto. But for the other projects we did at ADA it
just wasn't a direction we took, maybe also due to the lack of experience with
SSAS.
The last
months I got involved more in Multidimensional as well as Tabular and therefore
also ran into other issues
lately, the latest I will describe here.
Problem
During
deployment of an SSAS multidimensional cube in Visual Studio I received a very
descriptive error message saying (in Dutch): "The following system error
occurred:". And that was it. No description of any kind. The output window
of Visual Studio did mention some long error number, but that turned out to be
not very useful (read: useless).
Also,
when a key user wanted to change the rights of a cube he also received the same
error. He was trying to give a specific person (UserA) rights to the Purchase
role. Although UserA could be added perfectly fine to the Administrator role
for example, adding UserA to this role would fail with this error.
Problem solving
As you
can see in the screenshot of my Role Properties window below there is a SID
showing instead of a user name, but in the Properties window of the key user
the user name was correctly displayed. After seeing this SID I immediately
thought of a user that was no longer valid/active in AD, but let's see what
steps I took to come this far in the first place.
The first thing I tried was ignoring the error in VS and continue the deployment. That worked but I was unsure what gave me the error and if the deployment itself was properly done. I also noted that every cube gave this error in deployment, so it wasn't a cube-specific thing.
Then my
colleague Ron pointed me to the possibility of deploying the .asdatabase-file,
which is located in the bin directory of the solution.
This
.asdatabase-file is like the ispac-file for SSIS, it's the output of the
project when built in SSDT. You can either start the Analysis Services
Deployment Wizard or double click the .asdatabase-file. The
Microsoft.AnalysisServices.Deployment.exe that is used to start the wizard can
be found in the following location, where 120 stands for my SQL version (2014).
After
going through the first steps of the wizard brings me at the Options for
Partitions and Roles:
The important part here is the bottom selection: "Retain roles and members". This means existing roles and role members in the destination database are retained and no new roles are deployed. After having selected this option the deployment succeeded without any errors.
TL;DR: The Solution
Delete the SID from the Role Properties. Also make sure to delete it, or check that it doesn't exist, from the roles Membership tab in Visual Studio.
After the cleanup I was able to add UserA to the Role again and also the deployment of the project in Visual Studio was flawless.