In preparation of my upcoming webinar on the new features of Master Data Services 2016, I installed MDS on my system. Otherwise it would be quite hard to give demos, of course. The installation itself went quite smooth, no remarks there. I also enabled Internet Information Services (IIS) on my system, as well as a number of required prerequisites. Of course I forgot a few, but to be honest, the MDS Configuration Manager has become quite good at telling you which ones are missing.

Side note: Matt Smith has a PowerShell script in Github that will install all of the prerequisites for you!

@Ko_Ver https://t.co/tCrXeZlgAN

— Matt Smith (@matt40k) October 23, 2016

Next I had to install and configure the MDS database, which was quite painless, as usual. But then comes the tricky part: adding the MDS website. First I created a new website using the MDS Configuration Manager, but that did not work. At all. For some reason, IIS searched for the MDS website at …\inetpub\wwwroot\MDS, instead of at the MDS installation folder in Program Files. So I deleted the website and assigned MDS to the default website. That also didn’t work, but this time I got a different error:

The error seemed quite clear: Cannot read configuration file due to insufficient permissions. Just to be sure, I added the user MDSAppPool – created in the MDS Configuration Manager for the MDS Application Pool – to the Administrators group on the machine. A brute-force solution, but since it’s on my own machine for demo purposes, I didn’t really care. Of course it didn’t work. Then I assigned full control permissions for the MDSAppPool user on the folder C:\Program Files\Microsoft SQL Server\130\Master Data Services. Didn’t work. Used the browser in Administrator modus. Also didn’t work. Checked IIS settings and discovered that Windows Authentication was not enabled. So I enable it, but the error persists. This is the point where it all starts to get frustrating. Adding MDSAppPool to the IIS_IUSRS group doesn’t work. Giving that group full control on the MDS directory either.

As a last attempt, I went directly to the web.config file and assigned MDSAppPool full control directly on that file, for which I had to pass numerous UAC verifications. But lo and behold, the MDS website worked!

Conclusion: if you encounter a permission issue on the web.config file, directly assign permissions to it for the MDS application pool user. Adding the user to the admin group does not work, for whatever reason.

UPDATE: IT SEEMS THE BUG HAS NOW BEEN FIXED. THREE HOORAYS FOR THE POWER BI TEAM!

Recently I stumbled across a quite annoying error in the Power BI service. I uploaded two working reports from Power BI Desktop to the cloud service. Each report connects to its own cube and both cubes are in the same SSAS MD database. On the server, I have a gateway installed that connects this SSAS MD database to the online service.

Now, when I browse one of the reports, everything works fine. Once I open the other report, the visuals of this second report stop working:

This visual itself displays the error “This visual has unrecognized fields”. A bit odd, since everything worked just fine in Power BI Desktop. When clicking on the See details link, the following pop-up appears:

Apparently Power BI has trouble to identify some fields (in this case it’s the name of the measure group). What’s curious though, is that some fields of the second report still work. Those fields are attributes of the conformed dimensions. In other words, fields that are shared between the two cubes still work.

I monitored the queries to the SSAS MD database using Profiler: only the working queries came through. Queries from the non-working visuals didn’t even reach the cube. So this means they are stopped before the query is sent to the SSAS MD instance. I did a heavy investigation into the on-premise gateway. I reviewed all of the logs, but nothing spectacular came out. I reviewed all of the HTTPS traffic on the gateway server using Fiddler. With this tool I could retrieve the JSON that was sent between the gateway and the Power BI service. The JSON of the non-working visuals contained the following error message:

Could not resolve model references in the Semantic Query

The JSON also contained a help URL, but of course it doesn’t work. So for some reason Power BI (either the service or the gateway) can’t find the fields that are being referenced by the visual. However, the queries on the conformed dimensions are sent to the correct cube, so it’s not the case that Power BI is sending everything to one single cube and it ignores the other. Even more, after the weekend I tried again and suddenly the first cube stopped working but the second one worked without any issue. This led me to believe there was some weird caching issue going on.

I posted my findings on the Power BI forum and the Power BI team was quickly to confirm it’s a caching bug when working with multiple cubes in the same SSAS MD database. Apparently they didn’t test with the AdventureWorks cubes? They are currently working on a fix and I hope it’s released soon. I’ll update this blog once I know more.

I recently came across the following error message when I tried to look at the batches in the Integration Management section of MDS:

515: A database error has occurred. Please contact your system administrator

Too bad I am the system administrator on that machine…

Anyway, after some searching it came out the MDS stored procedure udpEntityStagingAllBatchesByModelGet throws a bit of a temper tantrum if one of the batch tags used in the staging process of a leaf member is NULL. You can check this in the table mdm.tblStgBatch.

Simple delete the offending batches and you’re good to go!

DELETE FROM mdm.tblStgBatch
WHERE BatchTag IS NULL;

I was creating an SSIS catalog at a new project, but I was greeted with the following error message when I hit OK:

The path to the catalog backup file could not be determined. Integration Services might not be installed on this server, or the user may not have the appropriate access permissions.

My initial reaction was something like this:

Why? Because I was sure SSIS was installed and I am an admin on the machine. A typical case of “the error has nothing to do with the issue, whatsoever”. Luckily I came across this Twitter conversation while googling for the error:

@SQLSoldier No, couldn’t connect. Which rang a bell – I was using SSMS 2012 :). SSMS 2014 did the trick. Thanks. #sqlhelp

— Blaž Dakskobler (@blazdax) April 7, 2014

And lo and behold, I was indeed using the wrong version of SQL Server Management Studio. Whoops. I quickly switched to SSMS 2014 and indeed, the issue went away.