Time of changes
Rising interest rates, new regulatory requirements, problems in supply chains – more than ever, companies are facing the challenge of being able to quickly assess the impact of new situations and initiate appropriate measures if necessary. Against this backdrop, the IT infrastructure is being rethought in many places and requirements are being redefined. The focus is on greater flexibility, faster access times, less coordination effort, and dependencies. In addition to conceptual considerations (see also the blog entry on the topic of “Data Mesh“), new data modeling and reporting tools such as Microsoft’s Data Factory, Analysis Services, and Power BI are also being evaluated.
When selecting new tools, the question quickly arises as to whether and to what extent existing data models and authorization logic can be integrated. As a rule, a lot of money, time, and nerves have already been invested in this. A complete rebuild should therefore be avoided.
Today’s blog entry is dedicated to the topic of “Authorizations in Power BI – in interaction with SAP BW”. First, the most important elements of the Power BI authorization concept are outlined. Then we will show you a way to dynamically transfer existing BW analysis authorizations to Power BI – to avoid time-consuming and costly redefinitions.
The Power BI authorization concept
Artifact
Areas of work
Content description
Containers for combining logically related elements, from data sources (datasets) to reports, dashboards, and apps. Bundle the other artifacts in a common area. Roughly comparable to an info area in SAP BW.
Relation to other artifacts
- May contain N other artifacts
Authorization setting
At this level, Azure Active Directory users (or user groups) can be assigned a workspace role. These are already predefined and include: Administrators, Members, Contributors and Readers. The role assignment has a direct impact on the creation, release and modification authorization of datasets, reports, dashboards and apps.
Datasets
Container for one or more data sources. The data can be available both physically and virtually – depending on the type of access (import or live connection). Provide the basis for reports.
- Belongs to 1:N workspaces
- Can be used in 0:N reports, dashboards, and apps
The authorizations for datasets can be assigned in two ways: via the assignment of a workspace role (at the workspace level) or at the level of the respective dataset. The authorizations relate to changing, releasing, and creating content based on the dataset. In the case of role assignment, the specific authorizations are implicitly derived from the role (role-specific gradations). If a user is assigned directly, however, the scope of authorization can be defined individually.
Reports
Tabular and/or graphical representation of the contents of a dataset. Can be created and modified via Power BI Desktop as well as via Power BI Service (Web). Enables self-service functionality for end users.
- Belongs to 1 workspace
- Based on 1 dataset
- Can be used in 0:N dashboards and apps of the same workspace
Similar to datasets, the authorizations for calling up and creating reports can be assigned in two ways. Via the assignment of a workspace role or the direct assignment of a user to a report. The authorizations can include, on the one hand, the release of the report (granting read authorization for other users) and, on the other hand, the authorization to create content (build option). This authorizes other users to build their own reports based on the underlying dataset.
Dashboards
Summary of content from different reports and datasets. The home page can contain any number of tiles and widgets that can be used for detailed navigation.
- Belongs to 1 workspace
- Obtains the data from 0:N reports and datasets
- Can be used in 0:N apps of the same workspace
In contrast to the other artifacts (datasets, reports, and dashboards), apps are equipped with an additional authorization layer. As with the other artifacts, permissions can initially be inherited via the workspace role or assigned directly. However, direct assignment offers additional functions. Individual user groups can be defined at this point. Here, access to the underlying reports & dashboards can be set for specific user groups. Due to this feature, it is recommended to always distribute dashboards and reports via apps.
Apps
Summary of content from different reports and datasets. The home page can contain any number of tiles and widgets that can be used for detailed navigation.
- Are created based on 1 workspace
- Include data from 0:N reports, datasets, and dashboards of the same workspace
In contrast to the other artifacts (datasets, reports, and dashboards), apps are equipped with an additional authorization layer. As with the other artifacts, permissions can initially be inherited via the workspace role or assigned directly. However, direct assignment offers additional functions. Individual user groups can be defined at this point. Here, access to the underlying reports & dashboards can be set for specific user groups. Due to this feature, it is recommended to always distribute dashboards and reports via apps.
Now that an overview of functional authorizations has been provided, we will turn our attention to data authorizations in the following section. We will also look at the dynamic integration of SAP analysis authorizations in Power BI.
Mapping of data authorizations via Row-Level-Security (RLS)
In Power BI, data authorizations are mapped using the so-called “Row-Level-Security” approach. It enables the definition of individual data roles in which the authorized data volume can be restricted via DAX statements. The restrictions can relate to all characteristic fields of the underlying data sources. Calculated tables that represent a view of another table can also be used as the basis for authorization – this is particularly interesting for the so-called dynamic RLS (more on this later).
The following illustration shows a simple example of a role definition for restricting the amount of data:
In this case, the reporting model only consists of one data source (headcount). A Dax filter is used to restrict the characteristics “Business unit” and “Country” to the characteristics to be authorized.
While the roles are defined within “Power BI Desktop”, the user or user group assignment is carried out in “Power BI Service”. The published dataset has the option “Security at row level” for this purpose. The corresponding users can be assigned here:
Without a corresponding assignment, an error message is displayed to users with read authorization.
Attention: Security at the line level does not apply if the users are assigned one of the above-mentioned workspace roles (administrator, member, or contributor). These roles should therefore be assigned with great care and only to power users.
Static vs. dynamic Row Level Security
In the example shown at the beginning, RLS was mapped using the static method. This should be avoided in practice for the following reasons:
- High implementation and maintenance effort: existing analysis authorizations (in SAP BW) would have to be recreated manually. Depending on the number of authorization-relevant objects and characteristic values to be authorized, maintenance via DAX formulas quickly becomes confusing and difficult to manage.
- The roles are defined for each Power BI report. They can neither be maintained globally (i.e. across reports) nor transported.
Due to these limitations, it is advisable to map authorizations using the dynamic RLS approach.
Dynamic RLS is characterized by the fact that the authorized values are kept in a separate table for each user. The DAX function “USERPRINCIPALNAME” is used to determine the user when a report is called up and the data is filtered according to the authorization table. This offers the great advantage that the maintenance effort within the data roles is reduced to a few statements:
The authorization table is linked to the data table (here “Headcount”) via “Relationships”. Filtering the authorization table thus automatically filters the data accordingly. Depending on the complexity of the data model, however, it may be necessary to create additional authorization tables. As a rule, 3 additional tables are required for each authorization-relevant characteristic – but these can be created relatively quickly within Power BI Desktop or Analysis Services. A concrete example is described in the following section.
Exemplary implementation of the integration of SAP BW analysis authorizations in Power BI via dynamic RLS
The starting point of the example is a simplified BW data model. This is outlined in the following figure (left). It consists of a composite provider that contains headcount data (heads, FTE, employees, organizational units, etc.). The characteristics “country” and “business unit” are marked as authorization-relevant. The authorizations are managed via function and data roles, whereby the data role contains a reference to the analysis authorizations. The permitted values of the two characteristics are stored here.
To implement the dynamic RLS approach in Power BI, a table is required at this point that contains the authorized values for each user and authorization-relevant characteristics. This list can be generated using a corresponding ABAP program.
Next, the Power BI model can be created in Power BI Desktop on this basis. Depending on the connection variant (import/direct query), either the metadata or the complete table content is loaded into the model (for more details, see the following blog entry). In this context, however, it should be noted that table relationships and roles cannot be created in Power BI for every connection variant. Here is an overview of this:
Variant
Dynamic RLS supported
SAP BW -> Power BI Desktop (Direct Query)
SAP BW -> Power BI Desktop (Import)
SAP BW -> Microsoft SQL Database -> Power BI Desktop (Import)
SAP BW -> Microsoft SQL Database -> Power BI Desktop (Live)
SAP BW -> Microsoft SQL Database -> MS Analysis Services (Import)
SAP BW -> Microsoft SQL Database -> MS Analysis Services (Live)
Once a suitable connection variant has been selected and the BW tables have been loaded into the model, the next step is to create a “security” table for each authorization-relevant feature. The data is later filtered using these when the reports are called up. The following figure shows an example of this.
Once the tables have been created, they must be set in relation to each other as shown above. The direction of the arrow indicates the filter direction that is used for row-level security. As soon as the relationships have been created, the Power BI role can be created. Only then does dynamic security become active at the row level. The procedure for creating roles has already been described in the two previous sections. In this example, the DAX function “USERPRINCIPALNAME” must be entered in the “Security-User” tables.
This completes the steps required in Power BI Desktop. Optionally, the report definition can be made at this point (alternatively in Power BI Service). To activate the authorizations or the dynamic RLS procedure, the role assignment to the individual users/user groups is now required. This is done in the Power BI Service. Below the published dataset, there is the “Security” option. The corresponding user assignments must be made here.
Summary and Outlook
In this blog entry, the Power BI authorization concept was presented. It was shown that a distinction can be made between functional and data-related authorizations. With regard to functional authorizations, the underlying artifacts (workspaces, datasets, reports, dashboards, apps) were explained as well as their relationship structure. An understanding of the relationship structure is a prerequisite for understanding artifact-inherent inheritance logic. In this context, the predefined workspace roles (administrator, member, contributor, and reader) were also discussed, the assignment of which has a major impact on data authorizations – which is why great care must be taken at this point.
With regard to data authorizations, the “Row-Level-Security” (RLS) concept of Power BI was presented. The differences between static and dynamic RLS were highlighted and the advantages of the latter method were described. Finally, an application example was used to show that it is possible to automatically transfer SAP BW analysis authorizations to Power BI, thus avoiding the manual reconstruction of authorization logic.
Whether the presented concept for mapping SAP BW authorizations is suitable for a company now only depends on the question of whether the definition of authorizations within Power BI Desktop meets the requirements with regard to reusability. As mentioned, there is the restriction that authorizations cannot be transported or used across different systems. The procedure described would therefore have to be repeated from model to model if the business requirements cannot be mapped in one model and several reports have to be created.
However, the problem can be solved by defining the model and authorizations not within Power BI Desktop, but in Azure Analysis Services. Analysis Services data models can be consumed in Power BI as an independent data source. Once an authorization model has been built, it can therefore be reused by any number of Power BI reports. If you would like to find out more about this topic, please let us know. We will be happy to advise you.
