Power BI Enterprise Gateway is release awhile 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:
First of all I’d like to explain what a Role-playing dimension actually means. Then I’ll express the way you can implement it in a SSAS tabular model.
When you link a dimension to a fact table several times for logically distinctive roles you’re using a role-playing dimension.
The key points are:
1.You are linking a fact table to a dimension multiple times. The relationships are defined by linking multiple foreign keys in the fact table to a single key in the dimension table.
2.Each linkage represents a single role or concept
The most popular role-playing dimensions are DimDate and DimTime.
NOTE: The sample is from Microsoft “AdventureWorksDW” for SQL Server 2012 and might be different from your own data warehouse design.
For instance, in a sales system that you have something like FactInternetSales fact table which has several links, or relationships, to a DimDate or DimAddress for distinct concepts like “Order Date”, “Ship Date” and “Due Date”.
As you see, all of the above columns obviously represent different meanings of date. In the data warehouse design you’ll see something like this:
Although this is absolutely OK in the relational database layer, but, this sort of relationship is NOT permitted in the tabular model, so what should we do?
As you might know SSAS tabular models do not support composite keys so you always must have just one column to make a unique row through the whole table. This is such a pain especially when you are new to the tabular models and don’t have that much detail information about it. So when you import some tables with existing relationships based on composite keys, the Table Import Wizard will ignore those relationships.
So what should we do to solve the problem?
The solution is to combine the values of the composite keys.
Here is how you can do the job?
·Creating a view on top of the source tables:
1.If you’re using SQL Server 2012 and above you can use the “concat” function to combine the values. The function combines several expressions regardless of their data types. So you can use it like thisselectCONCAT(1, 1.22100001,‘First’)SQL2012 and the result would be something like this
2.If you’re using earlier versions of SQL Server then you need to mind the data types. So for the above sample the SQL code would be selectcast(1 aschar(1))+cast(1.22100001 aschar(10))+‘First’SQL2008 . As we expect the result is the same.
·Adding a new computed column to all tables involved in SQL Server before importing the tables to the tabular model
·Adding a new calculated column to all tables involved after importing the tables to the tabular model
As a quick note, you’ll need to remove the existing relationships imported from SQL Server and create the new relationship based on the combined keys.
You’ve changed something in your Tabular model and you’re facing an ugly “Error: Object reference not set to an instance of an object.” message, don’t rush. Just go to the table you’ve made some changes and click on “Partitions” button.
You are working on a BI project that involves SSAS Tabular model. As you cannot process the model manually you need to develop a solution to process the model automatically. There are two scenarios here:
You have an up and running SQL Server Integration Services (SSIS) instance: In this case you can build a SSIS package to process the Tabular Model.
You do not have an SSIS instance ready on the server: So you need to create a SQL Server Agent Job.
In this post I’m expressing easy ways to solve the problem based on the above scenarios.
Using SQL Server Integration Services to Process SSAS Tabular Model
Follow the steps below:
Create a new SSIS project using (SQL Server Data Tools) SSDT
Right click on the “Connection Managers” area and select “New Analysis Services Connection”