Monday, November 21, 2016

SSAS Deployment Error: The Following System Error Occurred

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.

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.

Monday, November 7, 2016

My Takeaways For SQLSaturday 551: #SQLSatHolland

Finishing this post was on my list for quite some time now, but I gave my post last week about the Technical Preview for Power BI in SSRS priority because of the "hotness" of the topic. So without further ado, here's my SQLSatHolland write-up.

It's that time of year again: it was SQLSaturday in the Netherlands, a.k.a. SQLSatHolland!
It's always nice to meet up with SQLFamily, (Twitter) friends/old colleagues and new friends.

This year I also volunteered for the first time by being a timekeeper for the speakers and making sure the session evaluation forms were distributed before the sessions started and collecting them at the end of the session. It was a good and fun experience and I will try to keep giving back more to the community where I can.

I wrote down some notes of the following sessions:

If a machine can learn, why can't YOU learn Azure Machine Learning? by André Melancia (t)
This was an introduction into AML by André where he started out with some theory and explained what ML is not and the difference with AI. We learned how to create a free Azure subscription, set up a ML workspace and start using ML Studio, which is very much alike the Integration Services graphics of connecting processes through intermediate steps using arrows. He then walked us through the demo of creating our first model and experiment in ML.

Using PowerShell for SSIS by Joost van Rossum (b|t)
Joost used the first few minutes to explain what PowerShell (PS) is and goes right into the demo's after that. He even ignores the best practice of not typing in a demo, but all goes well fortunately :-) He showed us some neat Star Wars "song" of PS-beeps and also explains what you can actually use PS for: a.o. deploying IsPacs and environments, download packages from the catalog, search within multiple packages/projects and setting authorization.

Azure SQL Data Warehouse by James Rowland-Jones (t)
James starts with explaining what SQL DW is and is not. Analytics, aggregates and large data volumes are the key words when you want to use SQL DW. When NOT to use it: RBAR-processing, OLTP, incompatible formats like JSON and XML. You pay for the data you store and the compute you provision. You can choose between Hash and Round Robin (default) distribution, where you have to take into account that the distribution key is read only.

Continuous Integration and the Data Warehouse by John Tunicliff (b|t)
You can watch (a shorter version of) this session recorded at an earlier conference on YouTube. John talks about the problems why CI is still not a basic part of database development, the main point being the data in the database. TeamCity Build Server, PowerShell and psake is mentioned also for implementing CI, all with different use cases. Another key point to CI is a test framework, e.g. NUnit BI which is an open source framework to test BI solutions. A best practice when using SSDT for database design is not to upgrade projects, but to start over with a clean import and transfer over the pre- and post-deploy scripts.

Q&A BI with Joost van Rossum, Jan Pieter Posthuma (b|t) en Remko de Boer
These Q&A sessions were a try-out to see if we liked them and they could be a succes. Our BI session was mostly questions to the speakers but sometimes turned out to be a discussion between several attendees and speakers. It was really an open hour, and I really enjoyed it. Questions ranged from performance of Azure, Power BI, the new MS Certification paths (which I wrote about here) and the Master Data Services session from Remko, which was a very good session I heared from several people.

Introducing the SQL Server 2016 Query Store by Enrico van de Laar (b|t)
I didn't actually attend this session, but rumors say it was an exciting one so I have to share it with you. Let's start with some pictures, they say more…

It all started when he was about to begin with his session:

He even did some Windows updates that morning "to be sure". Well, that clearly didn't help…

What I heard from a lot of people (and also from Enrico himself), is that the session still was great and he turned it into a Q&A. He even turned to the whiteboard to get the picture across! Well done for the professional approach and for thinking in solutions when this happened.

If you want to know more about one of the sessions listed above or are just interested in any of the other sessions of SQLSatHolland, the session materials can be found at the SQL Saturday website, although at the time of writing not all materials have been added to the site.
I hope to also see you next year at SQLSatHolland!