Tag: Power BI

Analyze in Excel – Setup and initial issues

Published April 4, 2016

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

SSRS 2016 – Integrated or Native Mode – Which one should you use?

Published March 28, 2016

The answer to the SQL Server Reporting Services Integrated vs. Native mode question used to be very simple. Once upon a time, if you had a SharePoint environment, you would want to deploy SharePoint Integrated mode, and if you didn’t, you would pick Native. Integrated mode would leverage your pre-existing security model in SharePoint, it would allow reports to look like documents in SharePoint making them more user friendly, and you would be able to use the advanced features of the SSRS web parts in SharePoint. Non-SharePoint users were able to do what they needed around security and report storage with Native mode. Everyone was happy.

SQL Server 2012 changed that a little bit. Power View reports were first introduced in SQL Server 2012 as a part of SSRS. These reports leveraged the tabular (PowerPivot) data models available in SSAS 2012 and provided some very user friendly tools for self service analytical reporting. However, one of the catches introduced was that Power View reports were only available in SharePoint Integrated mode. Suddenly, the choice of mode became feature based. This suited those with SharePoint environments just fine, but those without SharePoint would now need to stand up a SharePoint farm just to gain access to Power View. This is a daunting prospect, especially for those unfamiliar with SharePoint. This requirement, coupled with the minimal investment into new features for core SSRS in SQL Server 2012 had the effect of making the Native mode users feel abandoned. After all, we know what typically happens when Microsoft stops investing in a product. The balance was heavily tilted in the favour of Integrated mode.

The new normal

This situation remained exactly the same in SQL Server 2014, but has changed dramatically with SQL Server 2016. SSRS in SQL Server 2016 contains significant advancements, chief among them are a new HTML5 rendering engine, a new report portal, mobile reports, and (soon) Power BI Desktop rendering. This is fantastic news, but it also changes the game significantly with respect to the Integrated/Native mode decision. With SSRS 2016, most of the new investments are in Native mode only – the balance has shifted. The table below shows an (incomplete) list of new features, and their supported modes.

Feature	Integrated Mode	Native Mode
HTML 5 based rendering engine	X	X
New chart types	X	X
PDF based printing (no ActiveX)	X	X
PowerPoint rendering and export	X	X
New UI for Report Builder	X	X
Customizable parameters pane		X
New web portal		X
Mobile reports		X
KPIs		X
Pint to Power BI		X
Render Power BI reports*		X

* Coming soon

You can see above that the balance has shifted very heavily in favour of Native mode. The folks using Native mode are very happy about this move – they are no longer having SharePoint forced on them in order to access new features. However, now it’s the SharePoint folks turn to feel abandoned, but they really don’t need to. SSRS Integrated mode is still getting a significant enhancement in 2016, it’s just not as significant as the improvement to Native Mode. Integrated mode is also still required for rendering Power View reports. Last fall’s Reporting Roadmap reconfirmed Microsoft’s commitment to SharePoint as a platform -“We will continue to support embedding of BI content into SharePoint”. SharePoint has a bright future as an report destination. The only question is how that will be brought about.

It may well be that the features had to go into Native mode first in order to meet the shipping schedules, and that they’ll be brought along eventually. I suspect however that this is not the case. I think that this is either the last, or penultimate version of SSRS to contain Integrated mode. If the same level of embedding into SharePoint could be provided by Native mode, and the user experience improved (as it has been in the new report portal) then there is very little real need for Integrated mode at all.

Building shared service applications in SharePoint is a non-trivial task, and those resources could likely be better spent on features for SSRS. A new embedding model could support both SharePoint on-premises (as it currently does) and SharePoint Online (as it currently doesn’t). The same mechanism could be used to embed Power BI reports. We’ve already seen glimpses of this hybrid interoperability in the SSRS and Excel pin visual to Power BI capability. I suspect that over time we’ll see SSRS Native mode and its reporting portal also assume the role currently played by PerformancePoint Services as well. For all of these reasons, I think that SSRS Native mode is the only future for SSRS.

But that’s the future. What about the present?

When I first learned of these developments, I suspected that I would be recommending Native mode for anyone moving forward. However, as I discuss in an earlier article, the SSRS web parts for Native mode are deprecated, and missing key pieces of functionality, parameters being first among them. They are really little more than iframes, and they certainly can’t replace the Integrated mode web parts. If you’re going to use reporting in SharePoint in any meaningful way, or you are looking to upgrade an existing SharePoint farm with SSRS integration to 2016, you’re going to need Integrated mode. That means no mobile reports, report manager, or Power BI integration.

So why choose?

There is nothing stopping you (apart from possibly licensing) from running both modes. Using Integrated mode, you can take advantage of the new rendering engine, etc, and a separate Native mode server can be used for Report Manager, mobile reports, and Power BI integration. Over time, more reports can be brought over to Native mode and the embedding story improves. Once they are all brought over in “the future”, the Integrated mode service can be simply removed. This provides for a smooth, gradual migration. In fact, you can set up an SSRS 2016 Native mode server along side an existing SharePoint 2013 farm with SSRS 2014 or earlier Integrated mode to get started. Your SharePoint reports won’t have any of the new features, but your Native mode certainly will.

We are clearly in a transitional stage when it comes to on-premises reporting technologies from Microsoft. There are significant, bold steps forward, but there is also a legacy of technology to support. The current lineup of technologies allows for both approaches for organizations to embrace at their own pace.

14 Comments

Integrating SharePoint 2016 with SSRS Native Mode

Published March 11, 2016

Why on earth would I want to write an article on this topic? Surely, if I am using SharePoint and SQL Server Reporting Services, I should be running in in SharePoint Integrated mode, right? That’s certainly the message that I have been delivering for quite some time. However, the game has changed significantly with SSRS 2016. Last October, Microsoft outlined their reporting roadmap, and that roadmap included a significant investment in SSRS. The roadmap was very clear as to Microsoft’s intentions.

“Reporting Services is our on-premises solution for BI report delivery”

Reporting Services is the solution, not SharePoint with Reporting Services, or PerformancePoint with Reporting Services, just Reporting Services. For several years now, the path to any new features in SSRS led through SharePoint Integrated mode. Features like Power View reports were only made available in Integrated mode as an example. While that was great for those invested in SharePoint, it presented an adoption issue for those that were not. This issue has prompted Microsoft to remove the SharePoint dependency, while still providing solid integration. In short, the goal for SSRS is to run with SharePoint, not on it.

In my opinion, this is all to the good. By making SharePoint integration pluggable, the product team can focus on one codebase instead of two, and spent more energy on features. This does however have some negative impact on administrators that will need to again manage two security models, but in the ideal world, it should be transparent to end users.

The immediate impact of this refocusing is that Native mode now receives new feature priority. If we look at the current state of SSRS 2016 (RC0 at the time of this writing), only a few of the major new features will be available in SharePoint integrated mode.

SSRS New Features

Native

SharePoint Integrated

HTML 5 Based Rendering Engine

Customizable Parameters Pane

New UI for Report Builder

New Web Portal

Mobile Reports

New Chart Types

PDF replaces ActiveX for printing

PowerPoint rendering and export

KPIs

Pin to Power BI Dashboard

Render Power BI Desktop files

HTML 5 Based Rendering Engine

New UI for Report Builder

New Chart Types

PDF replaces ActiveX for printing

PowerPoint rendering and export

This new disparity is likely to leave some in the SharePoint world feeling left behind. The reality is that although this may seem like the case in the short term, Microsoft stated that “We will continue to support embedding of BI content into SharePoint”. For the record, that statement is open ended enough to include both SSRS and Power BI. The improvements to integrated mode in SharePoint 2016 are a testament to this support. It would have been just as easy to leave Integration mode in its previous state (like PerformancePoint). My view is that ultimately Native mode reports will work with SharePoint in much the same manner that current Integrated mode ones do. In fact, it’s possible to do some of this today – to embed Native mode SSRS Reports into SharePoint. That’s what the remainder of this article describes.

The ability to embed Native mode SSRS reports has actually been available since SharePoint 2003. It fell by the wayside after Integrated mode was introduced in SQL Server 2008 R2, but it has continued to be there. What is needed is a Native mode SSRS Server, and the Native mode SharePoint web parts.

Installing and Configuring SSRS Native Mode

Native mode SSRS is installed from the SQL Server media. It should be installed on a NON SharePoint server. Run the SQL Server installer, and eventually you will be taken to the feature selection screen.

Native mode SSRS installs as a SQL Server instance, and it is the only option necessary to install. The SharePoint add-in is only used for Integrated mode.

Once installed, it is necessary to run the Reporting Services configuration tool. The first step in configuration is to set up the web service URL.

Once the desired options are set, click Apply and the SSRS web service will be set up. Next, click on the Database node to configure the SSRS database. If the SQL Server database is installed on the same machine, you can use it, but you can use any SQL Server at your disposal. The only restriction is that the database engine must be at least the same edition level as SSRS (ie Standard vs Enterprise).

To create a new SSRS database, click the “Change Database” button and provide the database parameters.

Two databases will actually be created, one of them a Temp database. I recommend using the word “Native” or some other identifier in the name, particularly when both Native and SharePoint Integrated mode servers may be used. Complete the database creation process, and move to the Report Manager URL node.

Click Apply to create the SSRS Report Manager. The initial URL will always be based on the machine name, but once complete, you can click on the Advanced tab to add additional URLs. This is how you can add a Fully Qualified Domain Name (FQDN) to your SSRS server, which is strongly recommended if you will be integrating SSRS with Power BI. Power BI users will need to connect directly to the SSRS server to view SSRS reports, and this requires an FQDN.

There are other steps to be performed at this point, including Power BI integration and exporting the Encryption keys, but this is all that is necessary for basic configuration. You should now be able to navigate to your Report Manager URL and create reports. The next step is therefore to integrate them with Sharepoint.

Integrating with SharePoint

Native mode ships with a pair of web parts that allow SSRS web parts to be embedded into a SharePoint page. The web parts are embedded in an installable .cab file that can be found in the folder “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Reporting Services\SharePoint” where C: is the installation drive, and “130” is the major installation version for SQL Server (130, or 13.0 corresponds to SQL Server 2016). The name of the file is RSWebParts.cab. Copy this file to a SharePoint server in the farm, and from there, it can be installed from either PowerShell, or the gool ol’ STSADM command. With PowerShell, the command is:

Install-SPWebPartPack -LiteralPath “D:\Software\RSWebParts.cab” -GlobalInstall

Where “D:\Software” is the folder that the file was copied to. The corresponding STSADM command is:

STSADM.EXE -o addwppack -filename “D:\Software\RSWebParts.cab” -globalinstall

Unfortunately, I and many others have found that the version of the .CAB file distributed with SQL Server 2012 and above are incompatible with SharePoint 2013 and 2016 – the web parts fail to deploy. The good news (and the bad) is that the web parts are unchanged from SQL Server 2008 R2, and that version of the .CAB file will work with modern SharePoint. Of course, not everyone has a SQL Server 2008 R2 server lying around, so if you happen to need the file, I include it here:

RSWebParts.cab from SQL Server 2008 R2

Using the Web Parts

Once deployed, the two web parts, Report Viewer and Report Browser will appear under the miscellaneous section when a web part is inserted into a page. Report browser allows the browsing of reports on a server, and Report Viewer renders them. By connecting the two, it is possible to provide a highly interactive navigation of the report server right in a SharePoint server. However, editing the Report Viewer web part reveals that it is lacking some very fundamental capabilities.

Native Mode Web Part	Integrated Mode Web Part

The Native mode web part is missing all of the view control features that are available to the Integrated mode part, which means that when it comes to Native mode reports, you get what you get. However, more concerning is the fact that it is also missing parameters. There is no way to configure parameters for, or pass parameter values to Native mode reports embedded on a page.

Add to this limitation the fact that these web parts are approximately 10 years old – they were designed for SharePoint 2007. They are able to render the new chart types, but not through the new HTML renderer. These limitations make it very difficult to recommend their use, except in a few very specific scenarios.

Recommendations

So what is a Report driven SharePoint administrator to do? All of the cool new features are showing up in Native mode, but except in certain circumstances, there no really good way to embed those reports in Sharepoint pages. It seems a difficult question, but the reality is that these choices are not necessarily mutually exclusive. SSRS Integrated mode is getting many of the modernization improvements and continues to be a totally viable platform moving forward. If you want or need to take advantage of the new SSRS features like mobile reports, parameter pane customization, or Power BI integration, you can stand up a separate SSRS Native mode server, and even integrate it with SharePoint using the older web parts.

Taking this dual approach means that you’ll be well positioned to gradually move assets from Integrated mode to Native mode as the embedding story and capabilities improve.

17 Comments

Power BI Enterprise Gateway and SSAS – What Account Should You Use?

Published February 23, 2016

With the release of the Power BI Gateway, it is now possible to publish specific data sources for the entire organization. The gateway works in both live connect (data stored on premises) and refresh (cached data stored in the Power BI service) scenarios. A variety of data sources are supported by the gateway including SQL Server Anlalysis Services (SSAS).

SSAS was previously supported by the SSAS connector which has now been deprecated in favour of the Enterprise Gateway. This certainly helps with understanding and complexity, but it does raise one particular concern around data security which you should be aware of.

When a data source is published, credentials are provided that the gateway will use to connect to the data source. All users that use the published data source will connect to it with those credentials. This proxy account will be used for all users and should therefore be a least privilege account – it should have no more access to data than needed. However, there is one data source that notably does not work this way – SSAS.

SSAS employs the EffectiveUserName feature to provide fine grained permissions to data in the model. With EffectiveUserName, the proxy account is used only to establish the initial connection to the SSAS server, and all queries are executed with the permissions of the consuming user, allowing the data to be security trimmed.

However, in order to use EffectiveUserName, the proxy account needs to have the highest permission level within Analysis Services – Administrator. This is the exact opposite of the account criteria for all other data sources. What is important is to understand these criteria in order to not unwittingly open up data to the wrong audience. If an admin level account were used as proxy for a SQL Server data source, potetially sensitive data could be exposed to the wrong users.

In a nutshell, the thing to remember is simply to always use a least privilege account for all data sources except for SSAS, which needs an admin level account.

3 Comments

Power BI and Shark Attacks – Oh My!

Published February 15, 2016

It’s not often that you get to combine interests, but when the Power BI team launched the Publish to Web feature for Power BI reports, I saw just such an opportunity. Quite some time ago, I had played around with the Global Shark Attack File for some Power View demos. One of the problems at the time was that there was no way to keep it automatically refreshed, and there was no real way to publish publicly.

Publish to web removed the second problem, so I doubled down on the first, figuring out a way to automatically download the Excel workbook on a nightly basis from the GSAF site. With that in place, armed with the Personal Data Management Gateway for data refreshes, I set out to do a little data modelling, and report building. The data in the file isn’t in the best shape, so it took a bit of work with both Power Query and DAX to beat it into t shape I wanted it (particularly extracting the species of shark).

However, as seems to be the case with Power BI, most things are possible, and I managed to put together something fairly interesting. I was inspired by a recent article in California Diver magazine, which had been shared around on Facebook. The article claimed that there were zero attacks on SCUBA divers in 2015, which is of course good news. Being an avid diver, that loves the sharks, I’m constantly amazed at the fear these animals generate. Cows kill a lot more people each year than sharks, but I digress.

I was quickly able to see that the figure for 2015 was in fact 1. The attack happened in Brazil on Dec 21, and it was non-fatal. Presumably, the article writer had a Chrstmas deadline. In any event, don’t trust me, check it out for yourself. The report is too wide to embed in this blog post, but I have created a page to host it here:

Global Shark Attacks

It is published with the Power BI Publish to Web feature. Provided that my workstation is up and running, along with connectivity, it will be updated every morning at 6:30 AM Eastern time. If you have any ideas for improvement, please let me know! I am interested to see how well this feature works.

This also seems as good a time as any for one of my favourite diving pictures. The shot below was taken of yours truly by my son while diving with a group of about a dozen Bull Sharks.

5 Comments