These prerequisites are not installed: 64-bit version of Microsoft Excel 2010

I had all the prerequisites installed (.NET framework 4.6.2 and the Visual Studio 2010 Tools for Office Runtime) and the correct bitness. A quick search led me to this KB article:

FIX: Can’t install Master Data Services Add-in for Excel on a computer that has Microsoft Office 2013 installed

The article says you can get rid of the error message by using the 2012SP1 installer. Which I was using. Dead end. I also found this post at Katie&Emil: MDS Excel 2013 and 2010 Add-in Installation. Emil had the same issue and succeeded in installing the add-in by running the installer as admin through the command line. However, I still got the same error. That add-in must really hate Office 2016.

So I installed the SQL Server 2016 add-in which did install successfully, but apparently isn’t able to connect to SQL Server 2012. No backwards compatibility. I turned to Twitter and someone suggested me to install the 2014 add-in. Luckily, this one does install and fortunately has backwards compatibility with SQL Server 2012.

To wrap everything up:

• SQL Server 2012 with Office 2010 – MDS add-in for SQL Server 2012
• SQL Server 2012 with Office 2013 – MDS add-in for SQL Server 2012 SP1
• SQL Server 2012 with Office 2016 – MDS add-in for SQL Server 2014
• SQL Server 2014 – MDS add-in for SQL Server 2014
• SQL Server 2016 – MDS add-in for SQL Server 2016 (no backwards compatibility)
• SQL Server 2017 – MDS add-in for SQL Server 2017

“Invalid connection information to establish the server connection”

I was using SSMS 17.3 to connect to the instance, so I wasn’t doing anything wonky with connection strings. I tried the following, without any success:

• repair the SSAS instance (when does a repair ever work?)
• uninstall and re-install the SSAS instance
• uninstalling SSMS 17.3 and installing a slightly older version (17.0)

Nothing worked. I got a similar error when I tried to create a new project in Visual Studio using an integrated workspace. However, I could create an SSAS project using a local workspace server. I could create a model and deploy it to the server. And I could connect with Power BI Desktop to the SSAS instance. The plot thickens. Turns out I’m not the only one either who ever encountered this error:

Can’t connect to SSAS tabular nor MD on clean SQL Server 2017 Dev edition (on clean Win10 VM, SSMS V17.3)
Anybody else?#sqlhelp #SQL2017 pic.twitter.com/oT2GPfqhBv

— Nico Jacobs (@SqlWaldorf) October 11, 2017

I took a quick look at Event Viewer, and there were a bunch of errors in there as well for the OLAP service. Of course with pretty error messages:

Googling for this kind of messages led to one forum thread where someone had locale issues. Note the 8192 number, which isn’t even featured in the list of locale identifiers. Anyway, I fired up SQL Server Profiler and ran a trace while I was trying to log into the SSAS server. An error popped up:

Device attached to the system not functioning

This really didn’t make any sense. However, in one of the Discover Begin/End events, the same number appeared again: 8192 (this time explicitly marked as locale identifier). Hmmm, I had problems with weird locales before. I dug into my system, and yes, the English (Belgium) locale was lingering around. I removed it from my system and lo and behold, I could log into SSAS with SSMS again. Morale of the story: if you get weird errors, make sure you have a normal locale on your machine because apparently the SQL Server client tools go bonkers.

(note: there are some references to the en-be locale: here, here and here)

Looking at the specific log, the following error was shown:

Error 0x80070666: Cannot install a product when a newer version is installed.

Indeed, there was a newer version installed: the 2017 package. I had already installed the latest version of SSMS (17.3) and SSDT for Visual Studio 2017 and one of those had probably already installed the 2017 redistributable. The official documentation states that installation of the 2015 (or later) will fail if a newer version is already installed. However, it also says it’s easy to discover if a newer version has already been installed (using the registry). So the SQL Server 2017 set-up should have been able to work around this issue. But it didn’t. So how do we solve this?

1. Uninstall the 2017 redistributable.
2. Install the 2015 redistributable.
3. Re-install the 2017 redistributable.
4. Install SQL Server 2017.

Installation still gave me a pop-up something was wrong; the set-up probably still found that there was a newer version installed (either the 2017 one, or a more recent version of the 2015 one), but now it continued with the set-up. You can probably avoid this by installing SQL Server 2017 first (which will install the 2015 redistributable) and then re-install the 2017 redistributable.

The weird thing is, when I checked the installed programs in the control panel, the 2015 redistributable isn’t listed anymore.

Anyhow, it works. There’s also a Connect item for this bug (which is already around since RC2), so if you would upvote it, that would be nice.

I recently had to install SQL Server 2016 again on my Windows 10 machine. This also meant that I had to install and configure MDS as well. Last time it didn’t went so smoothly: Master Data Services error – Cannot read configuration file due to insufficient permissions. However, this time I had already applied Service Pack 1, so I had another error when I tried to launch the MDS website.

The error this time: “The ‘tempDirectory’ attribute must be set to a valid absolute path”. If you can’t see the error, it’s possible you have to enable them in the web.config file of MDS. Typically you can find this configuration file in the folder “C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication”. The customErrors attribute should be changed to the following:

<customErrors mode="RemoteOnly" />

Anyway, apparently a temp folder is missing. Cody Konior already warned for this bug. The error message at the website is kind enough to provide you with the exact location of the error in the web.config file. Funny that the comment above explains that the Configuration Manager is responsible for this temp folder. Someone has not done its job properly.

The resolution is simple: create a temp folder with the name specified in the web.config file at the specified location. Now you are greeted with the following error:

Of course the MDS Application Pool account doesn’t have write access to the newly created temp folder. This is also easy to fix:

Note that I assigned permissions to the MDS_ServiceAccounts group. I manually added the MDS Application Pool account to this group, because MDS also forgets to do this. Now you can finally browse to the MDS website!

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’m toying around with MDS 2016 CTP 2.3 in preparation of my SQLServerDays session about what is new for BI in SQL Server 2016. I uploaded the sample models into MDS and I opened up Master Data Manager in Internet Explorer to try out some of the new features. The Manager has received a make-over in the SQL 2016 preview, but two of the tiles were greyed out:

Quite annoying to say the least. Also, the version drop-down box doesn’t show any values. After a bit of Googling, a stumbled upon this MSDN thread, where it is explained it’s a bug which will be solved in the next release. If you’re an early adopter like me (and you want to actually do demo’s), you want this fixed now of course. Luckily there are two work arounds:

1. Use an Application under the MDS website instead of a top level website. (this should correct some of the URLs)
2. Edit the index.js file and correct the issue directly.

Since the first one is practically Chinese for me, I opted for option 2. In the index.js file (found in  c:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication\Scripts\) you need to locate /api/Models/ and change it to api/Models/.

Problem solved:

SQLKover update: I’m reblogging this a bit earlier (it’s a quite recent blog post) because I still see people with this issue. Also, it seems that sometimes the fix doesn’t work.

You may or may not have noticed it after installing SQL Server 2012/2014: the designer interface in Visual Studio 2010/2012 has had a make-over. Nothing too drastic, but at least the undo button works. However, sometimes when you drag or move tasks/components on the canvas, they start to shake or jump around. A lot. And very annoyingly as well. (Quite impossible to take a screenshot of that, so sorry). This behaviour emerged after applying CU6. There are Connect items logged about this issue:

• When dragging SSIS objects on canvas the objects shake/jiggle/jitter
• SSIS 2012 Control Flow moving objects is unusable

Luckily Microsoft patched this bug in CU7. You can download the hotfix here. If your environment is up-to-date with the latest patches, most likely you won’t experience this bug.

I patched the Visual Studio 2010 environment at the client and the issue went away. But at home I use Visual Studio 2012 and the issue was still there, even after applying the fix. Fortunately, Twitter was there with the answer:

@Ko_Ver Ok, because the CU that fixes that only updates the DLL’s for VS2010, you need to manually overwrite the assemblies in VS2012 folder

— Jan Van humbeek (@janvanhumbeek) October 2, 2014

If you go back to the first Connect item, these steps are described in the work arounds and they work like a charm.

The issue has also been reported for SSIS 2014, and while the item has been closed as fixed, there’s no explanation on how it is fixed. Maybe in an upcoming CU for SQL Server 2014? It’s possible the same work around works for SSIS 2014, but I haven’t tried it (I don’t have the bug in Visual Studio 2013) so if you do try it, it’s at your own risk.