Just three weeks ago, we had announced the release of the preview version of enterprise gateway. And today we are ready with an updated version of the gateway with support for two major features – connectivity to Analysis Services models, for both tabular and multidimensional models, and SAP HANA.
Live connectivity to SQL Server Analysis Services models (tabular and multidimensional)
With the updated gateway you can now connect to Analysis Services and issue live queries to both tabular as well as multidimensional models.
For multidimensional models, we use the DAX over MD capability to connect to on-premises multidimensional models and this works with Enterprise and Business Intelligence editions of SQL Server Analysis Services.
To get started:
1. Install ‘Power BI Gateway – Enterprise (Preview)’: Download the gateway from here and install it on a computer with connectivity to the data source. Learn more here. If you had installed the previous version of the gateway, you do not have to uninstall it first. This install is an in-place upgrade.2. Once the gateway is installed, you can manage it from ‘Manage gateways’ (on the top right) in Power BI portal.
3. Select the gateway you just installed and add your SQL Server Analysis Services model as a data source. You can define who can access this data source using this gateway in the ‘Users’ tab.
4. Using Power BI Desktop (install from here), you can create a report that connected to your Analysis Services model in “live mode” by choosing the Explore live option in Power BI Desktop.
5. Publish your Power BI Desktop report connected to the Analysis Services tabular or multidimensional model and the gateway will be automatically selected for you as long as you are in the Users tab for the data source and have access to the Analysis Services database.
You are good to go! You can create reports and pin tiles to dashboard just like with any other dataset. The gateway provides secure access to data sources. When you share your dashboards with other users they will only see data that they have access to. When you share the dashboard with a user, he/she does not have to be added to the Users tab in the data source section.
Whenever a user views a dashboard or report connected to an on-premises Analysis Services model, the username of the user is passed over to the Analysis Services using the “effective user name” property. If the model has role or row level security defined, the viewer only sees the data he/she has access to.
You can learn more about how to add security to your Analysis Services model here.
What happens to Analysis Services Connector?
This release of the enterprise gateway supports live connectivity to both Tabular and Multidimensional models. This means that you can now start using this gateway to connect to Analysis Services models. In addition to support for multidimensional models, the enterprise gateway offers additional benefits such as improved performance, better reliability and easy manageability of all gateways/data sources in one place. In our tests with Analysis Services Connector and enterprise gateway, we observed 2x improvements in performance when accessing and querying Analysis Services models.
If you currently use the Analysis Services Connector you can upgrade to the enterprise gateway following these steps:
- Install the latest enterprise gateway and add the same SQL Server Analysis Services data source you had configured with the Analysis Services Connector
- Next, under the User tab add the users you want to migrate from the Connector to the gateway
- As soon as you do this, existing reports published by the users you added to the User tab under data source and their new reports will start using the enterprise gateway instead of the Analysis Services Connector
- Once you added all users to this data source under Manage Gateways, you can uninstall the Analysis Services Connector
Here are the links to documentation and Analysis Services Connector upgrade guide.
DirectQuery support for SAP HANA
In addition to SQL Server, you can now add SAP HANA as a DirectQuery data source.
Follow the steps below to add SAP HANA as a data source. Select SAP from the drop down.
Rest of the steps are the same as adding a SQL Server or Analysis Services data source. Once your SAP HANA data source is set up, you can create and publish Power BI Desktop reports and view/edit these reports with up-to-date data in Power BI.
For SAP HANA, you must ensure that you have also installed the SAP HANA ODBC driver on the same computer where the gateway is running. You can download the driver from the SAP download page.
You can read more about SAP HANA support in enterprise gateway here.
Live connectivity to Analysis Services and Direct Query support for SAP HANA are two key features in this preview release of the gateway. However, we won’t stop with that! We will continue to add new features and capabilities to the enterprise gateway in the coming months such as support for additional data sources, scheduled refresh and ability to add security groups in the Users tab. In the meantime, give this version a try and share your feedback/comments with us. Please feel free to send us your questions or feedback directly to hybridbi@microsoft.com and join the conversation at the Power BI Community.
*Happy Holidays*