Speaking at SQL Saturday Prague 2019

Last weekend I had the great opportunity of speaking at my first SQL Saturday, in the beautiful city of Prague in the Czech Republic. Together with my colleague Jeroen Schalken (B | @JeroenSchalken), we travelled to Prague to deliver our session on implementing Power BI (Premium) at Van Lanschot Kempen.

I also was very active on twitter during the weekend on the hashtag #sqlsatprague, I embedded every first tweet of my daily threads in the blog.

Friday

All settled and ready to go to #sqlsatprague with Jeroen Schalken! We are going to present the Tips & Tricks from our #PowerBI Implementation at Van Lanschot Kempen tomorrow at 17.15! #pbifamily https://t.co/6g7aGMQbDg

— Nicky van Vroenhoven (@NickyvV) September 20, 2019

We started Friday morning with our travel to Eindhoven Airport. After a little delay in Eindhoven, we were just in time to drop our bags at the hotel and get ready to be picked up for the speaker's dinner. It was in restaurant Kuchyň, with a beautiful view over the city next to Prague Castle.

Having a great speaker's dinner at #sqlsatprague! With @JeroenSchalken @JiriNeoral @pinaldave @christinaleo @Neil_Hambly @SQLSwimmer @tomaz_tsql @MatijaLah @sqlpass_cz @jardaminha @SQL_Alex @DejanSarka @DamianWidera @TomasTucek @jaroreken @malekpav pic.twitter.com/7gLfFy8Rkr

— Nicky van Vroenhoven (@NickyvV) September 20, 2019

We shared some starters on the table and then could pick our own food right from the kitchen, which was very nice. We were very well taken care of by the SQL Saturday Prague team!

Saturday

I started the SQL Saturday with a short run around the convention center and through the park and after having breakfast in the hotel we headed to the conference.

Rise and shine! It's #sqlsatprague time! pic.twitter.com/TLqGgG4apQ

— Nicky van Vroenhoven (@NickyvV) September 21, 2019

During the day I attended these sessions:

• Profiling Your Data by Angela Henry (B | @SQLSwimmer)
  Angela showed how you can use Power BI or the SSIS Data Profiling task (also available in Biml) to profile your data. Oh, and you should do that before the start of your project btw.
• Power BI Row Level Security Deep Dive by Jiří Neoral (B | @JiriNeoral)
  Jiri showed RLS in Desktop, from defining your security in a (hidden) table to dynamic RLS.
• Power BI as a universal analytic tool by Michal Chmelár (B)
  Michal showed several Cognitive Services for analyzing and translating text to sentiment analysis and also how to integrate the PowerApps visual into a Power BI report.
• Designing your tabular/PowerBI model for performance and scalability by Ivan Donev (B | @ikdonev)
  Ivan showed us several best practices of creating an SSAS model after breaking down Hash, Dictionary and Run Length Encoding.

There was also time for relaxing in the speaker room and taking some fresh air outside. And then finally of course our own session at the end of the day:

Tips and Tricks of a Power BI (Premium) Implementation at a Financial Institute by Jeroen Schalken and me. Because we were the latest slot of the day quite a few people already left, but our audience was very interactive and had lots of questions, so thank you!

We also went to restaurant Na Pekařce for the SQL Saturday after party, where we were joined by the speakers, organisers and also around 40 attendees.

Sunday

We had this day to enjoy the beautiful city of Prague, together with a few other speakers and Jarda, who was our awesome guide for the day!

Sightseeing after #sqlsatprague with @pinaldave @jardaminha @christinaleo @SQLSwimmer @JeroenSchalken pic.twitter.com/6Le9LbgYJo

— Nicky van Vroenhoven (@NickyvV) September 22, 2019

Monday

Because our flight was at 9.30 AM we started this day very early with a quick breakfast and on to the metro and bus to the check-in and security at the airport. It all went very smoothly so I even had some time to work on this post. At 11 o'clock we were back again on Dutch soil. As a surprise my wife and 2 youngest kids were there to pick me up.

Dobrý den: Final preparations before going back home from #sqlsatprague. Massive thanks to all the organisers @sqlpass_cz , sponsors, attendees and other speakers! A special thanks to @jardaminha for taking such good care of us and taking us on the sightseeing tour yesterday! pic.twitter.com/cXI4awH0Ah

— Nicky van Vroenhoven (@NickyvV) September 23, 2019

Closing words

All-in all it was a wonderfull weekend and I met many new great friends from the SQL Community. I hope I can some day go back to (SQL Saturday) Prague!

I also made a short video of my adventures during the weekend, so enjoy!

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.

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.

Suggested reading: MDX Date Picker, MS Certification Paths, Dynamics 365

I found some posts, news and came across some issues you might be interested in.

---

I'm creating some reports for a client and am working with SSAS Tabular. I therefore have to deal with MDX based parameters and I wanted to have my date parameter to resemble a Date Picker as you normally would have when using SQL Server as a source:

• Calendar Date Picker for MDX based SSRS Reports on MSSQLTips.com

---

Microsoft has changed their MCSA and MCSE exams by aligning to industry-recognized areas of competence. There's no need anymore to recertify every (three) year, but you can re-earn you certification each year. Due to the fact that the technology changes faster than a few years ago, (SQL Server had changes in the way updates were rolled out earlier this year, Azure changes almost every week) they decided to switch to a more aligned model for certification.

Certification paths

The Data Management & Analytics Path is most appropriate for me and in detail looks like this:

What this means is you will have to earn one of the MCSA's listed here and then take one elective exam to earn the MCSE. You can then re-earn the MCSE each year by taking a new elective exam and in this way adding knowledge every year.
The Microsoft Learning team is already developing a solution where some exams will be eligible for a retake every (calendar) year with rapidly changing technologies, for example Azure.
You can find more information here:

• Microsoft Streamlines Technical Certifications by Larry Kaye on Born To Learn
• Top 12 Questions About the New Streamlined Certification Paths by Larry Kaye on Born To Learn
• Microsoft Certification Changes by James Serra on his blog

---

Since I made the switch to Pulse I started working with Dynamics AX, Microsoft's ERP system. This Tuesday Microsoft had a First Look event at the AXUG Summit 2016 on (the new) Dynamics 365 for Operations, which is the new name for Dynamics AX. Dynamics NAV is also renamed and is now called Dynamics 365 for Financials.

I came across a very good blog by Fredrik Sætre (b | @AxFredrik) which summarizes the First Look event very well. He speaks about the Common Data Model that will be replacing the Entity Story introduced earlier in AX 2012 R2 and will be the core of Dynamics 365, for AX, NAV and CRM.

Power View for Multidimensional Models - Preview

Yesterday the public CTP on Power View for Multidimensional has been released by Microsoft!

In SQL Server 2012 RTM/SP1, Power View can consume data from tabular models, however multidimensional models (a.k.a. “cubes”) were not supported. The Power View for Multidimensional Models CTP addresses this key limitation.  Now Power View users can connect to both tabular BI Semantic Model AND multidimensional BI Semantic Model (cubes).  This is achieved through native support for Data Analysis Expressions (DAX) in Analysis Services Multidimensional models, ensuring optimal performance and functionality.

The SQL Server BI team blogged about it here. You can also directly download the Microsoft SQL Server 2012 With Power View For Multidimensional Models CTP.

If you have any issues or suggestions you can provide feedback via MS Connect.

Microsoft TechEd Europe 2012

I'm proud to announce I am one of the already 2.5k+ attendees at the TechEd Europe in Amsterdam next month!

I allready put up my agenda at europe.msteched.com, to attend at least some of the sessions of the following speakers:

- Kasper de Jonge

- Marco Russo

- Alberto Ferrari

- Jen Stirrup

See you all at the TechEd!

SSAS BISM Tabular Workshop

On april 16 and 17 I attended the BISM Tabular workshop from Marco Russo and Alberto Ferrari (from SQLBI.com with my colleague Bas Kroes. We were guests at Microsoft BV @ Schiphol, the facilities were excellent, although the temperature in the room was a bit too high these days.

BISM Tabular Workshop

It were 2 long days, also because on monday evening we attended the PASS Community event, co-hosted by ADA ICT, where Marco and Alberto talked about: Tabular or Multidimensional?

PASS Community event

I've been working with PowerPivot for Excel for almost a year now, but haven't done very much on SSAS, and nothing with Tabular (in regards to customers) yet, so I learned a lot in the workshop.

Specially the filter and row context in DAX was very good explained by them, it's still a hard topic, especially with 1-to-many and many-to-many relationships.
I can recommend the workshop to everyone who wants to know more on Tabular and SSAS 2012!