Tag: Power BI

Rethinking Business Intelligence in SharePoint and SQL Server 2016

Published December 14, 2015

SharePoint 2016 and SQL Server 2016 will both be released in 2016, adding to the changing Business Intelligence landscape already being disrupted by Power BI. Many of them will be incremental, but some are significant architectural changes that require a rethink of how we will approach on premises and cloud based Business Intelligence.

All of the bits to deploy the SharePoint based BI components are now available. With the December 8, 2015 publication of the white paper “Deploying SQL Server 2016 PowerPivot and Power View in SharePoint 2016” white paper, it’s possible to kick the tires and to come to a few conclusions. I will be posting a number of “how to” posts in the coming weeks, but I felt that it was important to set the context for them first. The Business Intelligence ground has shifted significantly, and this greatly affects the way that we think of, design and use Business Intelligence tools with SharePoint. For the record, there is quite a bit of opinion in here, and I want to make it crystal clear that the opinion is mine, and not stated by Microsoft.

Excel and Excel Services

In August 2015, Microsoft announced that Excel Services would not be a part of SharePoint in 2016, which came as a big shock to the community. Excel has always been one of the main pillars of BI in SharePoint, the other two being SQL Server Reporting Services (SSRS) in SharePoint integrated mode, which we’ll get to below, and PerformancePoint. As I’ve argued before, PerformancePoint, while still included in SharePoint 2016, has been dormant for several versions, and likely doesn’t have much of a future. It’s been exposed to the elements, and has gotten quite rusty. I wouldn’t put much weight on that particular pillar. In this context, Microsoft’s decision to remove Excel Services, (the only BI component delivered by the Office team) seems like a big deal.

In reality, it’s not such a big deal in itself. Microsoft has, for the most part, shifted the functionality of Excel Services to Office Online Server (OOS). I explained in another post that despite its name, OOS is NOT a cloud service, but in reality is the new name for the Office Web Apps server – the server that allows for browser-based editing and viewing of Excel documents. The difference between Excel Services and Office Web Apps Server has always been confusing to users and a configuration headache for administrators using both. I believe that Microsoft’s consolidation of Excel Services and OOS makes a great deal of sense.

BI professionals need to understand the change to options and components with this new model, and they need to understand that the change Microsoft has made does not represent a net loss in functionality. In fact, I think you will see a net gain because users can make changes to workbooks with data connections and embedded data models directly from a browser.

PowerPivot for SharePoint

Microsoft will continue to deliver PowerPivot for SharePoint as a SharePoint Service application, and a special instance of Analysis Service. However, Microsoft has significantly changed the installation process. Previously to install PowerPivot for SharePoint you had a separate installation mode for SQL Server, which would install either SSAS in SharePoint mode alone or SSAS in SharePoint mode along with the PowerPivot for SharePoint bits if you wanted to install it on a SharePoint server. With SQL Server 2016, it’s simply one of the modes for the SSAS install (called PowerPivot mode), and the PP4SP bits are installed separately (always). You’ll also need to perform separate configuration steps to connect the OOS server to the PowerPivot mode instance.


Installing SSAS in PowerPivot mode in SQL Server 2016 CTP 3.1	Installing PowerPivot for SharePoint SQL Server 2014

Microsoft did not make very many changes to PowerPivot for SharePoint 2016. Users will note one big improvement—they can refresh Power Query-based connections. While this improvement is not currently in the preview s, Microsoft has promised this feature for the final release. Until now, PowerPivot for SharePoint could update workbooks with new data, but only if those workbooks contained standard Excel-based or PowerPivot-based connections. If you had used Power Query to import data, you were out of luck. This contrasts sharply with Microsoft’s cloud based Power BI service , which can only use Power Query to import and refresh many data sources. It was impossible to answer the question “which tool should I use for data import” without being aware of the destination platform. Now, it is simple. Use Power Query, and your workbooks will work on all platforms.

When I mention that there aren’t many changes, this includes the PowerPivot Gallery. The Gallery is a specialized SharePoint document library template that allows you to see thumbnails of your Excel and Power View reports, and gives easy access to refresh options and self-service reporting options. As with prior versions of the Gallery, it is delivered as a Silverlight application on a SharePoint view page. As I will discuss later, the Silverlight dependency could be construed as a problem, but it is not necessary to use the Gallery in order to interact with Power Pivot workbooks. You can switch to a more standard library view and still have access to workbook refresh options.

The fact that Power Query refreshes PowerPivot-based and Excel-based connections may be reason alone to update existing PowerPivot for SharePoint installations to the 2016 versions. If you navigate to the Feature pack page for SQL Server 2016 CTP 3.1, you’ll see add-ins for both SharePoint 2016 and 2013, so this will be possible, for SharePoint 2013 at least (and no, you will not need your database server to be SQL 2016 as well). Correspondingly, the SSRS Integrated mode from SQL Server 2016 will work on all SharePoint from 2013 and up (but will need the new 2016 add-in).

Power View

Power View first debuted with SSRS in SQL Server 2012. Microsoft developed Power View to become the future self-service BI reporting tool that SSRS itself never really was. Initially, Power View has very specific requirements to use it, so specific that very few people did. You needed to be running SSRS in SharePoint integrated mode (it wasn’t available in native mode), and it needed to connect to a SQL Server 2012 SSAS tabular mode instance. Once that was set up, you would create a BISM connection file and then use it to launch Power View from a SharePoint Library. When Excel 2013 debuted, it contained a version of Power View that could work with embedded data models, which greatly increased its adoption. In all cases, Power View interaction required Silverlight in order to access it using a browser.

The Silverlight dependency was a clear problem, as it prevented mobile users from working with it, and Silverlight’s “retirement” meant that realistically, no new features would be added. Microsoft addressed this problem fairly quickly in Office 365 with the addition of an HTML5-based rendering engine that would be invoked if the browser machine did not have Silverlight. These HTML 5 enhancements never made it into the on-premises version of SharePoint. While Microsoft initially put a lot of energy into the HTML5 rendering engine, but Microsoft appeared to stop adding new features at about the same time that it achieved feature parity with Silverlight.

This stoppage, combined with recent moves, indicate to me that Power View has no future. I can think of three major developments that lead me to this conclusion, and you can find these developments in Power BI, Excel 2016, and the Office Online Server.

The original Power BI Service, originally introduced in 2012 and retired on Dec 31, 2015, was based on Office 365 and Excel, and leveraged Power View for self-service reporting. Microsoft introduced a new version of the Power BI Service in July 2015; it is a standalone service that connects to Excel among many other sources. The visuals in the new Power BI service are similar to, but not the same as Power View. Realistically, they are the logical evolution of what Power View started, and are based on the D3 JavaScript engine. I think Microsoft could have made it less confusing had they just called these visuals Power View V2, but it is what it is. The open architecture allows Power BI to quickly implement new visualizations, whether they come from Microsoft or from the community. You can see the many new visuals in the Power BI Visuals gallery, most of which have been submitted by the community. When you import models and Power View from a workbook into Power BI, any Power View reports are converted to the new version, and it’s a one-way street. In this environment, Power View is clearly considered a legacy technology.

Microsoft first included Power View in Excel as a ribbon item in 2013. During the test phase of Excel 2016, it disappeared from the ribbon. Power View still exists in the product, and you can add it back to the ribbon as I describe in this article. However, I wonder why Microsoft removed it. In my opinion, the reason is simple. Microsoft wants people to stop using embedded Power View in Excel, and to start using Power BI for self-service reporting. This just makes sense from where I’m standing. Excel has a rich set of native visuals that can connect to embedded data models, and most of the Power View visuals aren’t as mature as these are. Having Power View in Excel never made much sense, except possibly from a usability standpoint. If an analyst wants to live within Excel, they can use Excel Visuals and expose them as an Excel report in Power BI. For self-service reporting, we can connect to an Excel file and use Power BI visuals. There’s simply no longer any need for Power View as an Excel embedded tool.

Finally, let’s look at the new Office Online Server 2016. When OOS renders an Excel workbook that has an embedded Power View report, it will use Silverlight to do so. That’s right… OOS 2016 will require Silverlight for Power View rendering. We know that Microsoft has already updated the Power View rendering engine for HTML5 with some of the visuals for Office 365, so we know that it was possible to do so. The only reason that I can think of that Microsoft didn’t do this entirely through Power View is to discourage people from using it, or the effort was too great for a technology that was being replaced. In addition, now in order to user Power View with OOS, you’ll need to use Kerberos constrained delegation. Microsoft did not require this in the past because Excel Services was running on the same server as SharePoint itself and could pass the User Principal Name through to the backing SSAS server that used EffectiveUserName. Now Kerberos is required for this.

So why doesn’t Microsoft just state that Power View is at end of life? Microsoft didn’t say that, but that was the message I heard at the October 2015 PASS summit. I think that the reason is that so far, Power BI is a cloud service only. In order to replace Power View fully with Power BI, customers need to embrace cloud services to some level, and there are organizations that are still not ready to do this. Very soon, after Microsoft releases SQL Server 2016, SSRS will support direct rendering of PBIX reports. (The file format for Power BI Designer and the new visuals.) At that point, the new visuals, and the new self-service reporting tools will be available on premises, but for now Power View is the only tool that can function in an on-premises only environment. It doesn’t really have a future, but it’s still necessary. It’s certainly not the only technology to exist in this state – both InfoPath and PerformancePoint serve similar roles. Microsoft still supports them and PerformancePoint, while dormant, could come back any time should Microsoft choose to do so.

SQL Server Reporting Services

Microsoft has included SSRS as a core part of the Business Intelligence workload in SharePoint since SharePoint 2003. SQL Server 2005 SP1 introduced SSRS in SharePoint integrated mode, which allowed administrators to replace the web server and storage functions of the SSRS server with that of SharePoint’s, making it easier to administer. With SQL Server 2012, you had the option to deploy SSRS as a SharePoint service application, further simplifying administration and scaling. During this period, the native mode SSRS server was always still available for those that didn’t use SharePoint, but over time, it lagged behind its sibling from a features standpoint. Many people wondered aloud if native mode SSRS had a future at all, and if SharePoint would become a required component. They needn’t have worried.

At the same time, in the past few years we’ve seen a marked shift in the way that Microsoft has positioned SharePoint, from being at the center of everything to being more a set of services. The first hint of this was the new app (now add-in) model for SharePoint, and more recently with the wholesale shifting of services, of which the Excel Services change is a prime example. This shift, combined with a renewal of emphasis on SSRS for structured reporting is cause for re-evaluation.

At the PASS summit, Microsoft rolled out its reporting roadmap. It’s comprehensive, well thought out, and exciting. I’ve pointed out before that it doesn’t include the name “PerformancePoint”, but you know what else doesn’t feature prominently? SharePoint. Microsoft committed to SharePoint integration but they offered few details.

With SQL Server 2016, customers will still deploy SSRS through both native and SharePoint integrated modes. However, for the first time, the feature set will be significantly greater in native mode at least on initial release. With the roadmap, Microsoft defined four report types:

Paginated Reports – I call these operational, or structured reports. These are “classic” SSRS reports.
Interactive Reports – These reports are built with Power BI Desktop, and will run in SSRS and Power BI Web. I call these “Analytical reports” and this role would previously been performed by Power View.
Mobile Reports – These reports are aimed at mobile devices, and are what was previously known as Datazen.
Analytical Reports and Charts – Excel workbooks.

SSRS 2016 will be the delivery mechanism for 3 of these 4 report types, but only in native mode initially. Integrated mode will support these report types one way or another down the road, but we just don’t know when. Microsoft is investing in quite a few new areas in SSRS, and it’s worthwhile to break down exactly which of the new features will be available in the two different Reporting Services modes at release. For a definition of these features, please refer to Microsoft’s roadmap announcement.

Feature	Native Mode	SharePoint Integrated Mode
Paginated Reports	X	X
Interactive Reports	X*
Mobile Reports	X
New Reporting Portal	X
New visuals	X	X
HTML5 rendering	X	X
Pin and link SSRS visuals to Power BI dashboards	X

*Shortly after initial release

It’s pretty clear that the tables have turned. Power View reports are now the only thing that is uniquely offered in SharePoint integrated mode. If you have SharePoint and you decide to use native mode SSRS, no functionality is lost – you can still use the SSRS web part in SharePoint for report rendering and dashboards. Reports will be stored in the SSRS server, and you’ll need to set up security separately. On the plus side, you can leave these tasks to a Reporting admin, who will not need to know how the SharePoint security model works. The biggest issue that I can see is that while integrated mode allows you to work with a single authentication provider, the SSRS native mode server requires its own, making a direct connection with it necessary, at design time at least.

These downsides aside, this shift to a focus on native mode fits with what is happening with Excel on the Office side. The two become peers that work together, as opposed to being dependent on one another. The increased functionality makes native mode compelling, even if you are running a SharePoint farm. If you are creating a new BI environment and you want to take advantage of the new SSRS features, and/or you are looking to the future for your BI investments, my recommendation is now to provision a native mode SSRS server whether or not you have SharePoint in most cases. If you already have an investment in integrated mode Reporting Services, don’t panic. Patience will be a virtue here. You will gain all of the new visuals and appearances immediately, and the other pieces will come in over time. Microsoft has not yet clearly stated the roadmap for integrated mode beyond the release of 2016.

Summary

So, to summarize, all of this represents a shift away from SharePoint as a dependency and to it as an interface option. Instead of these tools working “on” SharePoint, moving forward they will work “with” it. Overall:

Power View can now be considered a legacy product.

Power View was the future of the past. The future is now Power BI. If it helps, think of the visual elements in Power BI as being Power View V2, which in effect they are. If you’re thinking about using Power View to build a report, please consider Power BI Desktop. If it’s not possible, the good news is that if you use Excel to build it, you will be able to easily import it into Power BI Desktop – it has a migration path forward, and on premises PBIX support is also on the way.
SQL Server Reporting Services should be deployed in Native mode

Whether or not you have a SharePoint farm, native mode SSRS is the way to go for a new deployment, even if you’re not yet ready for SQL Server 2016 SSRS. The reason for this is that migrating reports between the two modes is not simple.
Excel Reporting is alive and well

Excel is still well supported and Microsoft is investing in it. It is the tool for analysts and model builders and is easily portable to Power BI and to SSAS. The removal of Excel services in SharePoint is simply and architectural shift, not a functional one, and Excel reports are very well supported in Power BI.
Power BI and Power BI Desktop are the preferred tools for self-service reporting

Self-service reporting is clearly the domain of Power BI. The legacy options are still available for current on-premises customers, but if you want to future-proof your investments, look to Power BI.

6 Comments

How to Enable Cortana and Quick Insights with Power BI

Published December 2, 2015

This post will be quick. On December 1, 2015, the Power BI team announced that the Quick Insights feature and Cortana integration were now included on Power BI. They are not, however, turned on by default. Since the announcement didn’t have much “how to” in it, I thought that I’d post this. Both are very simple to do. Both features are tied to the data source, so if you’re working with a shared data set, you’ll need to get the owner of the data set to do this.

Cortana Integration

To start with, Cortana integration requires that you be running Windows 10, with the November 2015 update (1511). Of course, you also need to have Cortana enabled. If you do, open up Power BI in a browser, and navigate to your data source. Click on the ellipsis (…) and select “Schedule Refresh”. No, we won’t be scheduling a refresh, but this is a bit of a shortcut. You can also select the settings gear in the upper right, select settings, select the Datasets tab, and then select your dataset.

Once open, you’ll see a new section titled “Cortana” open it up, select the “Enable Cortana to access this dataset” checkbox, apply the changes, and you’re done.

If Cortana has already been connected to Office 365, you may not see any results right away – I know that I didn’t on two different machines. Thanks to David Eldersveld of Blue Granite, I found the solution in a big hurry. All that is necessary to get Cortana to wake up and start using Q&A in Power BI is to disconnect from your Office 365 account and then reconnect. If you’re having trouble, try that.

You should now be able to ask questions of your data directly from Cortana. Very, very cool.

Quick Insights

Using Quick Insights is even easier. From the Power BI service, locate your dataset, click on the dataset, and select “Quick Insights”.

Once selected, the service will display a dialog “Searching for Insights” while it scans the data and builds interesting visualizations. Once done, you can select “View Insights” from the dialog, or, you can select it from the data source ellipsis later on. This is important to know as that dialog box disappears pretty quickly. Clicking “View Insight” will show you a page with all of the visualizations that the service thought were important or interesting. Here is an example using our tyGraph product, which analyzes Yammer data.

It’s pretty slick really. I’ll be playing with this a fair bit in the coming days.

2 Comments

How to Set an Hourly Refresh Schedule with Power BI

Published September 3, 2015

One of the advantages of Power BI “V2” over “V1” is the ability to refresh from data sources more frequently than once per day. Daily was the maximum in the first version of the service, and continues to be the maximum for the free version. If you have a Pro license however, refreshes can be performed more frequently. Unfortunately, it isn’t immediately obvious as to how this is done.

If we navigate to one of our datasets in Power BI, and select “Schedule Refresh” we’re presented with the user interface for setting this up (assuming that it’s a refreshable source). First we turn on scheduled refresh, and then next, we select our frequency.

Wait – there’s no “hourly” option in the dropdown. Didn’t I just say that it can be more frequent than daily? It can. You just need to be more explicit about it. The way to enable refreshes more granularly than daily is by explicitly adding the times for the refresh using the “Add another time” link.

Therefore, in order to have true hourly refresh, you will need to add 24 refresh times for the data source. That’s not all that appealing, is it? Unfortunately, 8 is all you’re allowed. I can only assume that this was by design in order to dissuade people from overtaxing the system when it isn’t required. You do get refreshes more frequently than daily, but hourly isn’t an option either.

Therefore, the minimum amount of data latency that can be achieved by using refresh with Power BI is, as of this writing 24/8 = 3 hours. Of course, this does not apply to direct connected sources.

2 Comments

Working with Excel Files in Power BI V2

Published August 11, 2015

In the beginning, Excel was at the center of Power BI. In the “V1” version of Power BI, the entire product was all about enabling analytics in the cloud, and Excel/Excel services was the delivery vehicle for those analytics. The authoring tools were all Excel add-ins, and the service revolved around updating data models in Excel, and allowing you to work with data models greater than 10 MB. Power BI “V2” changed that focus completely, completely removing that dependency on Office 365 and Excel. However, all of the Excel goodness that was within the “V1” product is still there, it just may have moved around a bit, and it works a little differently now.

In the early days of Power BI V1, I wrote up a post about the limitations of the product, specifically the file size limitations. Reviewing this article recently, I was actually struck by how little has changed. The maximum data model size remains 250 MB. In fact, 250 MB is the maximum size of any data model in Power BI, whether or not it originated in Excel. What has changed, rather drastically is the means of enabling this 250 MB limit for an Excel file, which we’ll get to below.

Let’s have a look at some of the major differences between Power BI “V2” and Power BI “V1” as it pertains to Excel.

More than just pretty face

In V1, Excel was almost always used as a means of presenting data. It was possible to use Excel as a data source, but the presentation of that data would inevitably be through another Excel file.

In V2, Excel is far more commonly used as a data source. The Power BI Designer, or the web interface can connect to Excel files, and then import the data into data models stored in the service. Native Power BI visualizations are then used for data presentation. These Excel files can be local, in OneDrive, or in OneDrive for Business. If the files are in OneDrive, or OneDrive for Business, the data models can be automatically refreshed when the source workbooks change. However, we can continue to work with Excel as a presentation mechanism.

If the Excel file is stored in OneDrive for Business (as opposed to OneDrive), you can connect the Power BI interface to the workbook in place. Once connected, the workbook can be viewed and interacted with through Excel Services right within the Power BI interface. To do this, from the dashboard, select “Get Data”, choose Files, select the OneDrive for Business option, select the desired workbook, and finally, select the “Connect” button”. You will then be presented with two options.

Importing data from Excel vs connecting to a workbook in place

Note that these two options will ONLY appear if the repository for the workbook is OneDrive for Business. Selecting “Import” will import the data contained in the file into a service based data model. This is the operation that will occur for all other repositories, and it uses Excel as a data source. Selecting “Connect” however connects to the workbook in place, and it adds the workbook as a report to the Power BI user interface.

Excel Workbook in the Power BI section

The workbook appears in the Reports section with a small Excel icon beside it. To view the workbook in Excel Services, click on the ellipsis to the right of the name, and select View. The workbook should load in a new window in full fidelity, and allow interaction.

No artifacts are created in the Datasets or Dashboards sections, the workbook is a self-contained unit. This is important because personal sharing can only be done through Dashboards. Therefore, Excel Services based reports can only be shared through Office 365 Groups.

Office 365 Groups

One of the biggest changes that Power BI “V1” users will notice (and need to deal with) is the fact that in order to work with Power BI in the same manner, Excel workbooks MUST be stored in a OneDrive for Business repository. With “V1”, a workbook could be stored within any SharePoint Online repository, and enabled for use with Power BI. Therefore, all “V1” users will need to move these workbooks into OneDrive repositories before the “V1” service is deprecate (Dec 31, 2015).

Every Office 365 user gets a OneDrive repository by default, and these repositories work just fine for personal use, but most current users that are using SharePoint Online will have workbooks in shared libraries in a collaboration environment. Office 365 Groups also each have their own OneDrive for Business repository, and all group members have access to that repository. Power BI V2 fully supports Groups, so this is the logical place to store all of the “V1” workbooks for collaboration purposes.

Connecting to a workbook in a Groups OneDrive is identical to the process above, but first, you need to navigate to the Group’s context in the Power BI UI. This is done by clicking on the My Workspace button (and not entirely obvious).

Groups Selector

Simply select the group and you will be working in that Group’s context.

Increased File Size

One of the biggest benefits of the original Power BI “V1” was the ability to work with workbooks that contained data models larger than 10 MB. The way that this was done was by “enabling” the workbook for Power BI as I outlined in this article. The enablement mechanism no longer exists, but the benefits are still there. The process of connecting a workbook outlined above intrinsically enables the workbook for Power BI and increases the maximum model size from 10 MB to 250 MB. In addition, the workbook can also be opened and interacted with directly from OneDrive. It is also possible to share that workbook with others in your organization, but in order to open those large workbooks in a browser, those users will need a Power BI licence.

Workbook Refresh

The ability to refresh workbooks stored in the cloud from data stored on-premises was, at the time of its introduction, the most important feature of Power BI. Each workbook would be enabled for refresh through an administrative interface, and if the data source matched a registered data source, the service would call an on-premises Data Management Gateway, which would facilitate the refresh of the workbook. This worked, but was somewhat difficult to get set up. Power BI “V2” has simplified the process tremendously. This does however mean that the process has changed.

Most refreshes are performed on a Dataset, but as mentioned above, an Excel report is self-contained, so the refresh options are available from the report itself. Simply click on the ellipsis to the right of the report and select “Schedule Refresh”.

Report refresh options

The first time this is done, you will need to enter the Data Source credentials. Once entered these will be used for subsequent refreshes. This will also need to be completed before the report can be refreshed on demand. You can also schedule the refresh time here.

If the data source is a supported cloud source, no further steps will be necessary. If it is on-premises, then it is necessary to install the Power BI Personal Gateway. This does not require administrative permissions to run (although it’s best if you do), nor does it require any special permission on the service side. The Personal Gateway is meant to be just that – personal and easy to use.

Hopefully this covers most of the major differences of working with Excel workbooks in V2 of the service. It looks like a big change, but most things are still possible, and some things significantly enhanced.

1 Comment

Connecting to Analysis Services in Power BI

Published July 24, 2015

At the moment (July 2015), SQL Server Analysis Services (SSAS) data has the widest range of connection options in Power BI. This is a good thing, but the flip side of this is that great power tends to lead to complexity. In this post, I hope to clarify some of the complexity, and clearly spell out the different data connection options for it.

To begin with, SSAS running in Tabular mode is currently the only on-premises data source that supports direct query data. When operating in this fashion, at no point is data persisted or cached in the Power BI Service. Reports exist in the service, but every interaction with the reports goes back to the SSAS on-premises server for execution, and the results are streamed live back through the service to the requesting client. Operating in this mode has several advantages. Since you are maintaining your own SSAS server, there are no total capacity limits on your data models, compared to the 250 Mb limit on most models in the Power BI service. All data is stored on-premises, so it does not count against your total overall storage limits (10 GB for Pro users). Data is served up in real time, so there is no need to wait for a refresh process. Finally, since data is persisted on-premises, there are also no data sensitivity or sovereignty issues. You can use the cloud based Power BI services, and still maintain a policy of having no sensitive data stored in the cloud, or out of country.

There is of course a cost to all of this flexibility. Live connections require a Power BI Pro license (currently $9.99 US/user/month), and anyone consuming these reports must have such a license. You must of course maintain an SSAS server, which has its own added cost, and you must also install and maintain an SSAS Connector somewhere within your environment. Power BI uses Azure Active Directory for authentication, while SSAS uses only Windows authentication. Therefore, your directory needs to be federated with AAD (same as Office 365 directory federation) in order to use it, although this excellent post by Greg Galloway describes a workaround for test scenarios.

Of course, direct query is not the only way to work with SSAS data. SSAS data can also be loaded into a data model, and refreshed on a periodic basis, like most other data sources. In this mode, the data model is loaded into the Power BI service, and is refreshed periodically through the Power BI Personal Gateway. This approach is common to all on-premises based data sources.

On the tooling front, there are currently three different tools that can be used to connect to SSAS data, with only one of them supporting both the direct query and imported modes. Let’s walk through each one.

Excel

Excel can connect to SSAS three different ways. You can use the traditional Data – From Analysis Services on the Data tab, through Power Query and through Power Pivot. The first method connects directly to the SSAS server and is unfortunately not supported at all by Power BI, even with the SSAS Connector installed.

Direct Connection to SSAS in Excel

Power Query will import data from SSAS into a workbook, a data model, or both. Power BI works with the data model, so if Power Query is used the data should be loaded into the model, and not the workbook. There is one special case where data can be loaded into the workbook, and that is if Power BI connects to the workbook and uses Excel Services instead of importing it. When a workbook, is imported, only the data model and Power Views are brought in.

Power Query SSAS Import in Excel

Finally, PowerPivot in Excel can be used directly to import data from SSAS. PowerPivot only loads data into the model, so there is no confusion here.

PowerPivot SSAS Import in Excel

Once the model is created, and visualizations created, you can either import it or connect to it in the Power BI service. Since Excel does not support the direct query mode at all, only import, the data will need to be refreshed periodically. Refresh can be performed whether the Excel file has been imported, or if it is connected through Excel Services. Reports created in Excel can work with SSAS whether it is running in Tabular Mode, or Multidimensional (OLAP) mode.

Power BI Desktop

Power BI Desktop is a client application that allows you to import, or connect to live data, create and edit data models, and produce reports. You can use it to connect to Analysis Services in both live query mode, and in imported mode. Once launched, simply select “Get Data” and then select SQL Server Analysis Services Database and click “Connect”.

The next dialog is important to determine the behaviour of your Power BI Report. Here you enter the name of the SQL Server Analysis Services Server that you need to connect to, and then the way that you can connect. You MUST be able to connect to this from your client – communication does not flow through the SSAS Gateway at this point.

SSAS Connection Options in Power BI Desktop

The first option will connect to the SSAS using Live Query Mode. IN order to use this option from Power BI, you will need to be using the SSAS Gateway somewhere on your premises. The second option will import the data into a model in the same manner that Excel does. This mode will NOT require the SSAS Gateway, but it WILL require the Personal Gateway in order to keep the data in the Power BI model refreshed.

In addition, the Live Query approach will ONLY work with SSAS Servers running in Tabular mode. If your SSAS server is running in Multidimensional mode, the second option (import) is your only choice.

Once the report is created, it can be published to the service using the publish button, or the created .pbix file can be imported via the same mechanism as Excel.

Power BI User Interface

If you have an SSAS Connector registered somewhere within your organization, you can create a connection to it directly from the Power BI user interface. The Power BI interface can only connect to Analysis Services in Live Query mode, imported models must be created using either Excel or Power BI Desktop.

In order to browse an on-premises SSAS server, first Select “Get Data” in the Power BI user interface, then select “Get” in the Databases section. Then select the “SQL Server Analysis Services” option, and click “Connect”. When you do, you’ll be presented with a screen containing all SSAS servers that have been registered with your organization.

SSAS Servers Registered with Power BI

Click on the one that you wish to connect to, and you’ll be presented with a list of data models to connect to.

SSAS Model Selection

Once selected, the model will appear in the list of Datasets in Power BI.

Clicking on it will allow you to browse the model, and to start building reports. Since the Dataset uses Live Query, there is no need to schedule any sort of a refresh, your reports will always be as fresh as the data on SSAS.

Summary

We can summarize the various SSAS connection options for the various Power BI design tools in the following table.

Design Tool	Live Query	Imported
Excel	No	Power Query Load to ModelPower Pivot
Power BI Desktop	Yes (Tabular mode)	Yes (all modes)
Power BI UI	Yes (Tabular mode)	No

I expect some of this information to change over time, but at the initial launch of Power BI V2, this is where Analysis Services fits in.