***Note this post applies to the SSAS Connector which has been deprecated as of mid-2016. Refer instead to the Power BI Enterprise Gateway.***
Welcome to part 4 of a 4-part series on using the SSAS Connector with the new Power BI Preview. This information is as of early June 2015 and will definitely change as the Power BI Preview evolves.
Part I: Overview of Using Analysis Services Data in the new Power BI Preview
Part II: What You Need to Set Up the SSAS Connector in the new Power BI Preview
Part III: Monitoring the SSAS Connector in the Power BI Preview
Part IV: Connecting to Analysis Services From Power BI Website, Power BI Designer, and Excel (you are here)
Comparison of Connectivity Options
Power BI Website: A live query connection to the SSAS Tabular Model which sends DAX statements to the SSAS server for processing. Though it's considered a live connection, to improve performance the data is cached in the Power BI service; this cache is currently refreshed every 10 minutes. Therefore, I tend to think of this as a "near real time" connection. Ideally the 10-minute interval becomes a configurable option at some point because many SSAS models are refreshed less frequently, such as once per day.
Power BI Designer: Update 7/25/2015 A live connection is now available in the Power BI Designer and works as described in the previous paragraph. This requires installation of the newest version of Power BI Designer.
A live connection, like discussed in the previous paragraph, is not yet available in the Power BI Designer. Currently this operates as a data connection where you bring data in via Query to store in the Model. In all likelihood storing in the Model will become a choice rather than a mandate as the product evolves incrementally.
Excel: Depending on how you connect, you can set up a live query connection or bring the data into the Excel Data Model (Power Pivot). Setting up a direct connection is how we have queried SSAS servers for many years now - it keeps the data centralized in one place. The newer alternative is to replicate the data in the Excel Data Model which can be useful if you need to marry up the existing SSAS data with other data not available in the SSAS database and/or need to define new calculations and such.
Connecting to SSAS from the Power BI Website
The interface to connect to an SSAS server from inside the website is very straightforward, but it does require the SSAS Connector to be set up and running already for it to be discoverable. See the previous parts of this SSAS series for more details on setting it up.
Start with the Get Data menu:
Select the SQL Server Analysis Services option then Connect.
At this point what you will see are all SSAS servers that have been registered in the organization, regardless if you have permission to it. If you try to choose a server that you don't have rights to via a Role, it'll fail to connect at that point.
In my case I only have one SSAS server registered:
Once you have clicked on the server itself, it'll display the name of your SSAS Tabular model. Then you can Connect:
After it's connected, you'll have a new item listed in your Datasets area. It'll have an asterisk next to it to highlight that it's new. Note that if you go through the same process multiple times you'll get duplicate datasets listed (I'm guessing that is a temporary issue). It's a bit hard to see, but my last two Datasets are now duplicates of the same connection:
At this point if I click on the Dataset name, it opens a new report connected to my model. The field list is on the right that I can start working with to build out the report:
At this point since Excel Services and Reporting Services report types aren't available yet, this report is a Power View report (my suspicion is the Power View will remain the default report type but that's a guess of course).
Note the context of the 'current dashboard' is being displayed in the breadcrumb at the top of my screen (that's the Salesforce Sales Manager dashboard in my screen shot above). That is where a report item will go if you pin it while working inside of your report. If you want to pin a report item to a different dashboard (and in my case I would), you'll want to go back to the main menu, choose the dashboard, then return to your report. Then the context will be changed. Don't forget you can pin items to a dashboard from a variety of reports. And, a report item can be pinned to multiple dashboards.
Connecting to SSAS from Power BI Designer
Since the direct connection isn't available yet, the interface for searching for registered SSAS servers isn't currently the same in Power BI Designer as it is in the website.
A prerequisite for connecting to SSAS from the Power BI Designer is installation of the ADOMD.NET data provider. This gets installed on your local machine (where Power BI Designer is installed). Even if you're running SQL Server 2014, it seems that it still wants the SQL Server 2012 provider at this point in time (I installed the 2014 provider first to align with my version of SQL Server but it didn't work). The ADOMD.NET provider can be downloaded from the SQL Server Feature Pack: http://www.microsoft.com/en-gb/download/confirmation.aspx?id=29065. Expand the Install Instructions section, scroll down to the Microsoft SQL Server 2012 ADOMD.NET section about 1/4 of the way down the page. Choose x86 or the x64 file depending on if you're on a 32 or 64-bit machine.
To connect to SSAS, you start with the Get Data menu just like in the web. Under Database, find SQL Server Analysis Services Database then Connect.
Here's where things change from doing the connection via the website. Rather than show registered servers, it prompts you for the server name (including instance name if applicable):
The first time you make this connection, it'll prompt you for credentials.
The next time you make this same SSAS connection, it won't need to ask you for credentials because it'll have your preferences stored. To review your saved data source credentials, go to File > Options and settings > Data source settings. Here you can edit or delete saved data source settings as needed.
After you've authenticated you'll be prompted for which tables of data to pull into the embedded data model. When you make multiple selections, the selected source tables will be flatted out into one single destination table. So in many cases you may want to import them each separately to preserve the categorization (though you'd need to have the keys for each table exposed in order to do that & in SSAS usually keys are hidden). Be sure to click Edit next so you can fine-tune the Query steps before the data gets loaded to the data model.
There's a contextual menu called Cube Tools that comes up when you're on the 'Added Items' step. At this point you can proceed with your normal Query steps to refine the output as desired.
Connecting to SSAS from Excel
Here's where I was planning to write up a summary of obtaining SSAS data 3 ways: (1) Data menu ODC connection, (2) Power Query, (3) Power Pivot. However, while this post was sitting in draft status, John White wrote up a blog post covering this exact topic, so I'll just refer you to John's blog for details: Using Excel with External Data - What's the Right Tool?
Keep in mind that in Excel you have the choice as to whether to retain a direct connection or if you wish to store the data in the Excel Data Model. This is a very important decision point. As of right now, only options (2) and (3) are supported in the Power BI Preview, though I expect that'll change. In Excel 2016 these different options will be simplified which will be very nice.
And that's a wrap on this series about the SSAS Connector in the Power BI Preview. Thanks for reading!
Part I: Overview of Using Analysis Services Data in the new Power BI Preview
Part II: What You Need to Set Up the SSAS Connector in the new Power BI Preview
Part III: Monitoring the SSAS Connector in the Power BI Preview
Part IV: Connecting to Analysis Services From Power BI Designer and Excel (you are here)