Analyze in Excel is an extremely important new Power BI feature for reasons that I’ll outline in more depth in an upcoming post. If you want to try this feature for yourself, there’s a good possibility that you’ll hit one of the errors that I ran into. This post will hopefully help other intrepid pioneers past the hurdles and get working with this fantastic new feature.
Initialization of the Data Source failed
In order to enable the Analyze in Excel feature, Excel must use the Microsoft AS OLE DB Provider from SQL Server 2016. This driver supports claims based authentication for SSAS, which is what Power BI uses. Observant readers will notice that I just used “claims based authentication” and “SSAS” in the same sentence, but I digress. What’s that you say? SQL Server 2016 has not yet reached RTM? That’s OK because you can use the driver from the Release Candidates, which can be obtained from the SQL Server 2016 Feature Pack directly. More commonly though, you’ll get it from Power BI user interface. There it can be acquired in one of two ways.
You can proactively download the updates from the Power BI “Download Center”, which is the little down arrow in the upper right of the toolbar:
Clicking the “Analyze in Excel updates” button will download the installer for the driver, which you can run right away.
Running the file takes you through the installation of the driver, which is simple and wizard driven.
Once installed, you may then navigate to a data source and launch “Analyze in Excel”.
At this point you will be prompted for two things. Firstly, you will be prompted to download and launch an ODC. This is the connection file that will be used to connect your Excel client to the SSAS service that your data source is housed in. If you’ve already installed the new driver, you can just go ahead and run it, and Excel will launch. If you haven’t you can take advantage of the second (simultaneous) prompt, which is to download the driver. This prompt will appear whether or not you have already installed it.
This approach is a bit different. This dialog has a big yellow box that just begs to be clicked, and it will install the 32-bit version of the driver. You can also take the high road and install the 64-bit version which of course you’ll need if your Excel is 64 bit. After all, anybody serious about doing data in Excel is using 64 bit Excel, right? In any event that’s the difference between the second approach and the first. The first approach does not give the option, it just goes ahead and uses the 32-bit version.
I of course originally opted for the first option, and whenever the ODC launched, and Excel opened, I received the error “Initialization of the Data Source failed”, which is hardly intuitive.
The issue of course is a mismatch between the bit level of the driver, and the bit level of Excel. The solution to this problem is quite simple. The 32-bit version needs to be removed, and the 64-bit version installed. You’ll find the offending package in listed in Programs and Features as “Microsoft AS OLE DB Provider for SQL Server 2016”, but it makes no mention of the bit level.
If you happen to have both installed (you can) the only way to tell the difference between the two is that the 64-bit version is that the 64-bit version is about twice as large. This distinction also holds for the installer files – they are named the same but the 32-bit version is approximately 29 MB, and the 64-bit version weighs in around 62 MB.
Excel cannot find OLAP cube Model
About a week ago I completed a small IoT project that takes data from several weather stations and pumps it into a number of sources, one of them being Power BI. Writing data directly to a Power BI data model through the API (which is what Azure Streaming Analytics does) introduces a number of idiosyncrasies into the mix, one of them being that the data can only be updated from the API. Apparently another is that you can’t use the Analyze in Excel feature with it. The error that you get when you try to do so is:
You also cannot use Analyze in Excel with any Direct Query data sources or on-premises SSAS through a gateway. The solution to this is apparently patience – it’s currently not supported. I can only presume that it is coming soon, but for now, you can stop beating your head against the wall, it won’t work.
The HTTP server returned the following error: Forbidden
This one sounds pretty ominous, doesn’t it? It also doesn’t make sense. You needed to be logged in in order to find the “Analyze in Excel” button in the first place, but this error is indicating that you don’t have access. This problem occurs when you have multiple AAD (Organizational) or Microsoft accounts, and you are maintaining a connection to one that does not have access to the data source. It’s particularly galling, and difficult to remedy if you have an AAD account and a Microsoft account that use exactly the same email address. I know, because I do. In order to fix this, you need to force the connection to logout, but the only way to do that currently is to modify the ODC file.
An ODC file is simply an XML file that can be edited with any text editor. Once open, you search for the connection string (<odc:ConnectionString> and add the desired user ID to it by adding “User ID=account” as in the example below:
Launching it after the edit will force the logout of the previous connection and you should be presented with a login screen for the correct one. Unfortunately, in the case like mine where the two accounts are named identically, this does not work. What you must do is to use a different account first. This will force the logout. Once that is done, you can add the correct account, or remove the User Id section altogether. Subsequent launched will force the login, where you can choose the correct login type, and the feature will work.
These few tips should help you get up and running with Analyze in Excel, and shortly I’ll be discussing the reasons that I think this feature is such a big deal.
5 Comments