Power BI Enterprise Gateway, Everything You Need to Know

On-premises Data Gateway (aka Power BI Enterprise Gateway) is release a while ago (2 Dec. 2015), but, with the latest release on 22 Dec. 2015 Power BI Enterprise Gateway now supports live connections to both SQL Server Analysis Services Multidimensional and Tabular models as well as SAP HANA. In this post I’ll explain lots of important aspects of the Power BI Enterprise Gateway including installation, configuration for different data sources including SSAS Multidimensional, Tabular and SQL Server Database and much more. If you need to have the lowest possible latency then you need DirectQuery/Explore Live feature on top of your on-premises data sources. The good news is that Power BI Enterprise Gateway now supports all following data sources:

SQL Server Database

SQL Server Analysis Services Multidimensional

SQL Server Analysis Services Tabular

SAP HANA In this article you’ll learn how to install and configure Power BI Enterprise Gate Way, how to manage different live data sources, how to create reports on top of live data sources and more.

Note 1: If you want to use DirectQuery to connect to your on-prem SQL Server Database OR Explore Live your SQL Server Analysis Services Tabular model then you might not need to install and use the Power BI Enterprise Gateway. In those cases you can install Power BI Personal Gateway to connect to an instance of SQL Server OR install Power BI Analysis Services Connector to connect to your on-prem instance of SQL Server Analysis Services Tabular model rather than installing the Power BI Enterprise Gateway. But, bear in mind that selecting the best gateway is really depending on your use cases, your data sources and the environment you’re working on.

Note 2: The Power BI Enterprise Gateway and Power BI Personal Gateway CAN be installed on the same machine.

Downloading and Installing Power BI Enterprise Gateway You can download the gateway from Power BI website when you logged in to your account and click on “Power BI Gateways” from the download menu: OR you can go straight to the gateway page then download the Power BI Gateway – Enterprise (Preview):

After you downloaded EnterpriseGatewayInstaller.exe file double click it to install the gateway.Note: To reduce network latency it’s better to install the gateway on a server that hosts the data sources you are connecting to. Click “Sign in to Power BI” and sign in with your Power BI account Enter a name for the gateway, then enter and confirm the recovery. Make sure you record the recovery key in a safe place as you’ll need it for restoring the gateway then click “Configure” The installation is completed now. You can either click “Close and Add data sources” or “Close” In the next section I explain how to manage Power BI Enterprise Gateway Data Sources. Managing Power BI Enterprise Gateway Data Sources So far so good. We have installed the Power BI Enterprise Gateway and we’re ready to manage our data sources. To do so follow the below steps: Login to Power BI Service . This account should be the account you used when you installed the Power BI Enterprise Gateway. Click on the Settings (gear icon) on the top right of the page and click “Manage gateways” You should see “My Enterprise Gateway” now Setup Gateway Administrators From the Gateways page click a desired gateway Click “Administrators” tab Enter the users’ email addresses. You can add multiple emails at the same time Click Add Managing Power BI Enterprise Gateway Data Sources Click “ADD DATA SOURCE” Type a name for the data source Select a desired data source type from the list Depending on the data source you different configurations will appear in this page. Managing a SQL Server data source Select “SQL Server” from the list Enter the Server Name and Database name Select authentication method Enter your Windows credentials (I select Windows authentication, but, depending on your case you might select Basic authentication here. In that case you need to enter a SQL user and password like SA) Expand “Advanced Settings” and a privacy level then click “Apply” * Learn more about privacy levels here. You should see something like the screenshot below if your connection is successful Managing an Analysis Services Multidimensional data source From the Gateways page click “ADD DATA SOURCE” again This time select “Analysis Services” from the data source type list Enter the server name and data base name Enter Username and Password then click “Add” You should see something like below screenshot if your connection to the SSAS Multidimensional instance is successful: Managing an Analysis Services Tabular data source If you have an instance of SSAS Tabular model and you’re willing to add it to the enterprise gateway you need to do exactly the same thing as you did to add a new SSAS Multidimensional data source as above, but, you just enter the SSAS Tabular Model instance name for the server name. Then you should see something like the screenshot below: Note: If the data source you are adding is hosted by the same server as you installed the Power BI Enterprise Gateway then using “.Instance_Name” rather than “CUMPUTER_NAMEINTANCE_NAME” for the server name is legitimate for SQL Server and both SSAS Multidimensional and Tabular models.



Managing Data Source Users

Managing data source users is the same for SQL Server Database, SQL Server Analysis Services Multidimensional (SSAS Multidimensional) and SQL Server Analysis Services Tabular (SSAS Tabular). The users you add will be able to publish reports that use the data sources. So it is trivial that the users should have read access to the databases (SQL database, SSAS cubes or SSAS tabular models).

Fro the Gateways page expand the gateway to see the underlying data sources

Click a desired data source

Click “Users” tab

Enter the users’ email addresses then click Add (you can add more than one users at the same time)

Browsing (Live Connection) SSAS Multidimensional from Power BI Desktop

Note: Connect live to SSAS is now released and it is not in preview anymore. So some parts of this section might be out-dated. You can find more about Connect Live to SSAS Multidimensional here.

Previously we weren’t able to connect directly to an instance of SSAS Multidimensional. But, using the new Power BI Enterprise Gateway we can browse the cubes directly from Power BI Desktop. In this sanction I show you how to do the job.

Open Power BI Desktop. The Power BI Desktop should be installed on a machine which is in the same network as you installed the Power BI Enterprise Gateway.

Click “Options” from File-> “Options and Settings”

Click “Preview Features” then tick “Explore live with SQL Server Analysis Services multidimensional models” then click OK

You’ll get the below message, click OK, then close the Power BI Desktop

Open Power BI Desktop again

Select “Analysis Services” from Get Data

Enter the “Server” and “Database” names as you previously used in the Power BI Enterprise Gateway configuration

Make sure “Explore live” is selected

Click OK

Select a cube then click OK

Voila… We have all dimensions and measure groups on the “Fields” pane

Expand “Internet Sales”

Tick “Internet Sales Amount” to create a new column chart

Expand “Sales Territory” dimension and tick “Sales Territory” hierarchy. (YES, hierarchies are now supported in Power BI Desktop)

As you can see the drill down action is automatically enabled on the column chart which means you can easily drill down to the lower levels of the chart. To do so simply click the “Drill Down” button ( ) to enable the drill down action. Now you can drill down to the lower levels of the chart by clicking on each column:

Click “Publish” button from the ribbon to publish the report to the Power BI Service

If you get the sign in message then make sure you enter to the same Power BI account as you setup the Power BI Enterprise Gateway

You can click the hyperlink to navigate to the report on the Power BI Service

If you click on the ellipsis button on the right side of the dataset you already published you’ll see that the “This dataset connects to a SQL Server Analysis Services Tabular database & is always up to date. You don’t have to schedule a refresh on this dataset.” message.

Browsing (Live Connection) SSAS Tabular from Power BI Desktop

Live connection to an instance of SQL Server Analysis Services Tabular is not a new feature. Previously we were able to connect to a live dataset using “Power BI Analysis Services Connector”. The Analysis Services Connector is NOT compatible with the “Power BI Personal Gateway” so needed to install it on a separate machine. While with the new “Power BI Enterprise Gateway” we have the ability to live connect to all supported data sources including SSAS Multidimensional, SSAS Tabular and SQL Server database (and of course SAP HANA which is not a part of this article).

Open a new instance of Power BI Desktop (you cannot add different live connections in just one Power BI Desktop file as the data is NOT loading into the model. So you need to open a brand new instance.)

Click Get Data

Select “Analysis Services”

Enter the SSAS Tabular instance name

Enter the database name (this is optional)

Make sure the “Explore live” is selected then click OK

Select a desired perspective and click OK

Expand “Reseller Sales”

Tick Sales Amount to create a column chart

Expand “Geography” and tick Country Region Name

Now click the “Publish” button to publish the report to the Power BI Service

Click the hyperlink to open the report in your browser

DirectQuery to On-prem SQL Server Database from Power BI Desktop

We are now able to DirectQuery to a SQL Server Database from Power BI Desktop on top of Power BI Enterprise Gateway which means we don’t need to setup schedule refresh on the Power BI Service (Power BI website) anymore. With DirectQuery feature our Power BI reports and dashboards will be always up-to-date as the dataset is using a live connection to our SQL Server database.

Open Power BI Desktop

Click “SQL Server” from Get Data

Enter server name

Enter database name (database name is optional) and click OK

Click “Connect” (If you’re using windows authentication then you’re good to go, but, if you want to use SQL Server authentication then you need to click Database and enter your SQL credentials)

Select DimDate and FactInternetSales from the list then click “Load”

Click DirectQuey then OK

Expand FactInternetSales and tick SalesAmount

Expand DimDate and tick CalendarYear

Click “Publish” button from the ribbon to publish the report into the Power BI Service

Click on the hyperlink to navigate to the report from the Power BI Service

From the navigation pane from datasets click on the dataset ellipsis button. This is what we expect to see: “This dataset connects to a source with DirectQuery, which is always up-to-date. You don’t have to schedule a refresh on this dataset.” Putting all Live Reports Together in a Dashboard Now I want to create a dashboard and put all the charts together in the dashboard. Note: The dashboards are available only in Power BI Service (website). From the navigation pane click the plus sign ( ) from the “Dashboards” section Type a name for the dashboard Now click on each of the reports we created in previous sections and pin the visualisation to the new dashboard To do so you just need to click on the pin icon ( ) on the top right of each visualisation then select the new dashboard then click “Pin” After pinning all the visualisations to the dashboard click the dashboard to open it Enabling Q&A for a Dashboard Now we’ve created a dashboard that contains some visualisations on top live connections to the data sources. This means the lowest possible latency and the visualisations always indicate up-to-date information. But, what about Q&A? Can we enable Q&A for the dashboard? The answer is a NO for now. However, to see how to generally enable Q&A follow the below steps: Open the dashboard menu by clicking on the ellipsis button of the dashboard then click “Settings” Tick “Show Q&A search box on this dashboard” box As you can in the above screenshot at the moment Q&A is NOT available for live connected datasets So far you have learnt: How to install Power BI Enterprise Gateway How to manage several data sources supported by Power BI Enterprise Gateway Creating simple reports on top of the live data sources Putting all visualisations together in a single dashboard In the next article I’ll show you Power BI Enterprise Gateway security and authentication settings including how you can manage the users and administrators and more.



Like this: Like Loading...