I’ve spent almost half a year figuring out, how to best retrieve SAP BW Data and display useful insights in Power BI. So therefore I’ve reviewed the 3 most common connection types. Power BI offers standard SAP BW API’s, but it makes a huge difference for which one you go. You can use my findings in order to make a faster and better decision.
At first some info:
If I speak of Corporate BI, I mean a special BI units that focusses on Power BI. The first chapter is a general quick introduction to Power BI App Workspaces. If you are already familiar with it, or need to get some more details I recommend heading over to the Microsoft pages.
1. Managing Access to App Workspaces and Apps
In Power BI Service there is the possibility to create Workspaces and Apps. Workspaces are used as a container for reports / datasets / dashboards based on the same department / use case.
To collaborate with other colleagues Corporate BI department creates Workspaces to publish the reports to. Apps are used to deliver the reports via premium capacity to a broader audience of non Pro users. At the most basic level, whether a user can see a given piece of information in an App Workspace is determined by whether he or she has access to an App Workspace or App that displays that data.
What is an App Workspace ?
As described in section Workspace Concept, App Workspaces should be treated as places where groups of users can collaborate and free users can access reports in premium capacity. The same group or distribution list can be reused to control access to multiple Apps, which saves a lot of time setting up access rights for Apps and minimizes the chances of error. Access to the App can be controlled centrally by Corporate BI, who is responsible for managing security, which makes administration within Power BI much easier.
However these permissions will help to secure data, but nevertheless Power BI Pro users will always be able to share reports with various users via their private workspace. Especially if they’ve used import mode, data will be imported to Power BI as a Power BI dataset and published and distributed via Power BI Service. My best advice regarding corporate BI is: don’t use sharing via personal workspace and instruct all Power BI users to make use of the workspace concept and the use of a Row-Level-Security feature.
2. Different Authorization types (Row Level Security):
As you may already have heard of, in Power BI there exist different kind of connection types. Depending on the specific type, you’ll need different kind of authorization settings.
2.1 Import Mode
It is generally recommended to use the Import Storage Mode to retrieve data inside Power BI. The reasons for this evaluation are discussed later on, as well as their advantages / disadvantages. So far the Import Mode seems to be the suitable fit as it offers huge Features and has less downsides.
Import Mode has the following main advantages:
+ Fast frontend performance due to xVelocity engine
+ Full DAX compatibility
+ Ability to add external data files (e.g. Excel)
+ Time based hierarchies are possible
Unfortunately if you choose Import Mode, you can’t use the built-in security concept from the underlying data source (e.g. SAP BW). To make use of a powerful row level security feature in Power BI, we have to re-model the existing security concept again in Power BI. This seems like a big drawback at first view, but if we compare it with the other methods, this seems like a good fit.
What is necessary to re-model the security concept and how does it work?:
2.1.1 Authorization concept Import Mode:
To re-model the current SAP BW security concept, we have to view every use case independently. Therefore we use the already implemented security concept existing in SAP BW, or in a different Source System and analyze all fields called “Berechtigungsrelevant”, with their corresponding characteristic. This part has to be done by SAP BW department, because only their technical user is capable to view existing roles for this Cube. SAP BW department exports the current security concept state in an Excel File. SAP BW Team provides corporate BI department with the latest version, if there are any changes to it.
After this was analyzed we have to re-build the SAP Model inside of Power BI. There exist two common ways to implement security in Power BI.
- a) Manual Implementation for every “Berechtigungsrelevantes” attribute:
This has to be done inside Power BI Desktop under section “manage roles” by a corporate BI employee. Image 1 shows this how to manage roles. At the moment it is not possible to create roles inside the Power BI service. Until now, these steps have to be done manually and there has to be a separate role with the corresponding filter context for each SAP BW role. After that we publish the report to the Power BI Service.
Once published we can navigate to the security chapter for this dataset. The chapter is called row-level security and contains now our created role “BCS_C00107” without any users or groups inside. Image 2 shows how to add Members to an existing group.
We’ll add the existing SAP BW security concept inside here. This might either be user level or group level and depends on the granularity provided by SAP BW.
These steps are performed at the beginning of a new project, once the department reveals their needs. Your corporate BI department should provide a Power BI Desktop template. This will establish a common report design and an already implemented correct security concept. Free templates are available on google.
Nevertheless this mode needs a huge manual effort, because we have to create and maintain the security concept twice at the one hand and have to do those steps for every report on the other hand. That’s why I recommend you to use at least (Azure) Analysis Services. For this method, corporate BI only has to implement the security concept once in the Analysis Services Cube and not for every single report. This method requires (Azure) Analysis Services and some more time to implement the cube at the beginning.
- b) Manual Implementation via Master-File (Dynamic RLS):
In addition to locking down access to tables with hard-coded filters, we can also leverage security that is built into the data. To get a better understanding, let’s say that we are sourcing this data from an application that handles security in its database by granting user’s access to specific states. In this case, we can bring in this security information and use these relationships to filter data by who is viewing the report. Below, we can see these Security Tables in Image 3 brought into Power BI; the User Table (greyed out because we are hiding it in the final report) filters the State User Table to a combination of the user that is logged in and the state’s data they are permitted to see.
This table further filters the State Table, which then filters the Geography Table, which filters at the end the Sales Table. Following this method, we need 1 Master-Security File imported for every report that stores the user information (AD-User) and their appropriate “Berechtigungsrelevantes” Field. (e.g. Country, Buchungskreis etc)
Afterwards we only need to setup one single dynamic Role containing the filter context for the specific user. Image 4 shows the way we implement dynamic RLS.
Therefore Power BI offers a function called “USERPRINCIPALNAME()” which provides us with the actual windows user E-mail address. This E-mail address will be used to filter dynamically. At this time, anyone with this role applied will have the User Table filtered by their email address and subsequently, will have the rest of the tables down the line. To test this, we will have to define who we are logging in as to see the report they will see.
As a last step we should create only 1 single role inside Power BI service containing all the Users that should have access to this report. This works the same way as described in the 1 alternative.
Using this method, there is a lower error rate compared with the first method, because the correct security context has to be applied only in 1 Master file.
2.2 Analysis Services Model
This alternative is used to work as a layer between SAP BW and Power BI. It contains all data we need for our reports for each use case. By using Analysis Services, companies can deploy data models in the cloud from different data sources. The built-in InMemory Database provides a fast data access. In combination with Azure Active Directory we can make use of a user based row level security. The RLS works like depicted in chapter 2.1.1 b) Manual Implementation via Master-File (Dynamic RLS). The difference is, that the security data model is only implemented once per Cube and not per every single Power BI Dataset.
To put it in a nutshell you can find the following advantages / disadvantages:
Advantages:
+ Security concept stored in backend (Analysis Services)
+ No row limit
+ Performance optimized
+ Object Level Security
+ Time based hierarchies
The main advantage is the pre-calculation and that we only have to implement the security concept once per use case. This will save a lot of time in comparison to the import mode.
Disadvantages:
- Have to extract data from SAP BW and afterwards process cube. It takes longer till data is available in front end, due to preprocessing of Analysis Cube
- No composite model (yet). It is on Microsoft’s Backlog, but not yet implemented.
2.3 Direct Query Mode
In this section we’ll discuss the authorization features that come with Direct Query Mode in conjunction with SAP BW and Power BI.
Direct Query offers great advantages, when it comes to security topics, because we can use the already existing security concept from the backend system, if we have enabled Single-Sign-On (SSO) in SAP BW with Windows User. Of course it’s as well possible to use the Power BI security model built on a DirectQuery connection.
Besides the authorization concept issue DirectQuery Mode in conjunction with SAP BW and Power BI brings some more serious drawbacks.
- The Data Volume is limited to only 1 Mio. Rows
- The Query performance depends on the backend System (SAP) and is always slower than import mode (because no xVelocity engine)
- Only limited DAX compatibility
- Produces lot of network traffic, as almost every Power BI click produces a new database query
- Time based hierarchies are only evaluated at the current date
Furthermore in the next chapter you’ll learn more about the known Limitations according to Power BI and either SAP BW and SAP HANA in DirectQuery Mode. This may vary for future versions of Power BI and SAP BW and needs to be re-evaluated at the specific time.
2.3.1 Limitations for SAP BW (DirectQuery Mode)
- No support for calculated columns:The ability to create calculated columns is disabled. This also means that Grouping and Clustering, which create calculated columns, are not available.
- Additional limitations for measures:There are additional limitations imposed on the DAX expressions that can be used in measures, to reflect the level of support offered by SAP BW.
- No support for defining relationships:The relationships are inherent in the external SAP source, and additional relationships cannot be defined in the model.
- No Data View:The Data View normally displays the detail level data in the tables. Given the nature of OLAP sources like SAP BW, this view is not available over SAP BW.
- Column and measure details are fixed:The list of columns and measures seen in the field list are fixed by the underlying source, and cannot be modified. For example, it is not possible to delete a column, nor change its datatype (it can, however, be renamed).
- Additional limitations in DAX:There are additional limitations on the DAX that can be used in measure definitions, to reflect limitations in the source. For example, it is not possible to use an aggregate function over a table.
Some more SAP BW features are not fully supported, or will behave differently when using Power BI. For more Info check this Post:
https://docs.microsoft.com/en-us/power-bi/desktop-directquery-sap-bw
Review
In order to implement an authorization concept, it is necessary to choose between the different Power BI Storage modes, to handle the associated technical circumstances. Therefore the above blog post summarized the pro’s and con’s regarding these storage types.
Many companies decide to start with the Import Mode because this method could be implemented the fastest and provides all major features for enterprise companies. Due to higher Implementation time and costs the Analysis Services Solution is the more stable way for the medium term.
The usage of DirectQuery Model can be also an option for mid-term. If you already have have a valid SSO Connection to SAP BW, there might be some actual use cases for this scenario. Especially the Direct Query connection to SAP HANA might be interesting in the future.
