There is One new feature however, that I found very interesting is the “Network Ports Test” under the “Diagnostics” tab. This is an important feature I believe that gives you the ability to deal with your network administrators or your security officers much easier. You can now easily run a test directly from the gateway UI and pass the test results to your network admins if any of test steps fails.
As you can see in the screenshot you can click on “Start new test” and then open the completed test results to send them back to your networking team is needed.
you are a Business Intelligence consultant working in Power Platform, Azure
Logic Apps and Azure Analysis Services landscape, you probably felt that
On-premises Data Gateway is one of the essential parts of your engagements with
the your customers. Installing On-premises Data Gateway can go smoothly if you
already have a well thought implementation plan otherwise, it can quickly turn
to a beast if you don’t have one. In this post I do my best to provide you some
guidelines that can help you with your On-premises Data Gateway implementation
planning. Consider the following points before, during and after the
Culture of Engagement
Environments (with all peopleinvolved)
Identity Access Management
Installation, Configuration and Testing
Here is a diagram of important point that you should consider:
need to understand the use of On-premises Data Gateway for your customer. If
they need the gateway for their Power Platform, Azure Logic Apps, Azure
Analysis Services or all of them. This is important as you either need to have
access to your customer’s Power BI Service or Azure Portal or both, or you need
to assist your customer to configure On-premises Data Gateway in Azure or in
Power BI Service. The next points are:
Accessing customer’s Azure Portal and/or Power BI Service: The customer to decide whether to create a new account with sufficient rights for you or give you the credentials of an existing account. It is important to make sure you can access all environments and you have necessary rights to install/configure the gateway
You assist/consult a person at customer side with the implementation: you need to make sure you communicate with that person and see if he/she understands the requirements before the implementation date. Send them a calendar invitation beforehand to make sure he/she is present at that date. Always ask for a backup person just in case of an emergency happening to the primary person.
Update 2019 April: If you’re interested in exporting the data model from Power BI Service to SQL Server check this out.
Note: The method explained in the above post involves Power BI Premium or Embedded capacities with XMLA endpoints connectivity at the time of writing.
A while ago I wrote a blog post explaining how to Export Power BI Data to SQL Server with R. In that post I explained how to get the job done in Power BI Desktop using R scripts. In this post I explain how to export Power BI Service data to SQL server. YES! You can export data from Power BI service to a SQL Server database sitting in your on-premises environment. Keep reading to see how.
How It Works?
This is going to be a short post as I already covered the first part of the process in my other post on Export Power BI Data to SQL Server with R. So in this post I show you how to use the Power BI Desktop file you already created using the method explained in that blog post to export your Power BI Service data to an on-premises instance of SQL Server. All you need to do is to
Publish the existing Power BI Desktop solution to Power BI Service
Install On-premises Data Gateway in PERSONAL MODE
Note: R is NOT supported by the current version (Version Number: 14.16.6614.5) of the On-premises Data Gateway in Enterprise Mode.
After you successfully published the model to Power BI Service you’ll notice that you cannot refresh the model if you don’t install the On-premises Data Gateway in Personal Mode.
From April 2017 onward we can use On-premises Data Gateway for Azure Analysis Services. This means you can connect your Tabular Models hosted in Azure Analysis Services to your on-premises data sources through On-premises Data Gateway. In this post you learn how to setup and configure the On-premises Data Gateway as well as how to configure Azure Analysis Services to connect to the on-premises database(s) via the gateway.
You have an Azure subscription
You already have an up and running instance of Azure Analysis Services (AAS)
Your instance of Azure Analysis Services, your instance of Power BI Service and your On-premises Data Gateway are all in the same “Region” (Check Prerequisites section below for more details)
This post covers the most common scenario which doesn’t involve Kerberos, Proxy Server, OKTA etc…
Reading: If you are not that familiar with On-premises Data Gateway then I encourage you to read this and for more in-depth read this article. You may also watch this video from Adam Saxton discussing On-premises Data Gateway.
As stated in the “Requirements” section, it is important to check if your instances of Azure Analysis Services, Power BI Service and On-premises Data Gateway located in the same “Region”.
The reasons behind that are:
Your On-premises Data Gateway MUST be installed in the same Region as your Power BI Service sits in. Otherwise your Power BI cannot see the gateway, therefore, you’re unable to schedule automatic data refresh in Power BI Service
To get the best performance and more reliability, you have to create On-premises Data Gateway Resource in Azure side in the same region as your Azure Analysis Services lives in
But, in some cases people created their Power BI account a long time ago and perhaps they didn’t set the right region for their Power BI Service to sit in. Now, they need to create an instance of Azure Analysis Services, but, due to their organisation privacy and security, they don’t want to (or perhaps not allowed to) create the instance of Azure Analysis Services in the region that their Power BI tenant sits in. So they would prefer to create the Azure Analysis Services in another region. In that case, it is recommended to install a new instance of On-premises Data Gateway in a separate server and change the “Gateway Region” during the installation.
To check the “Region” of your instances follow the steps below:
Power BI Service Region:
Login to your Power BI Service
Click the question mark on top right side of the page
In one of my previous posts I explained how to use Power BI on top of your SSAS Multidimensional using Data Import Scenario. You can also find definitive explanation about “Managing Analysis Services Multidimensional Model” here. In this post I show you how to connect live your SSAS Multidimensional model with Power BI. “Connect Live SSAS Multidimensional” means making a Direct Connection from Power BI Desktop to your SSAS Multidimensional instance.
As it is a direct connection you’ll be able to see/use the following SSAS Multidimensional objects:
Hierarchies including Parent Child
In this post you’ll learn:
How to connect live from Power BI Desktop to SSAS Multidimensional
Creating reports using SSAS objects like hierarchies
Publishing your reports from Power BI Desktop to Power BI Service
To be able to successfully create and publish your reports using Power BI Desktop on top of SSAS Multidimensional you will require:
The latest version of Power BI Desktop (Current version is 2.31.4280.361 64-bit (January 2016))
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
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: