Managing BI User and Data access

Grant Data Warehouse Permissions

To manage  the permissions in databases, the system allows the usage of multiple user-defined roles. Domain users and group accounts can be assigned as members of these roles to assign permissions for database.

The following Database Roles exist in the database.

• • LuwareDataLoader – this role has permissions to execute integration jobs.
  • LuwareReporting – this role has permissions to execute all stored procedures in Reporting schema. This role is required to:
    • Get data through Excel Power Pivot Reports (version 2.3 – 3.2)
    • Get data through Excel Power Query Reports (any version)
    • Get data through Power BI Reports (any version)
    • Get data through SSRS Reports (any version)
  • LuwareGetDataset (Release 3.3 and above) role has permissions to execute [Reporting].[usp_GetDataSet]. This role is the most secure way to grant access to the report user. This role is required to get data through Power BI and Excel Power Query Reports (version 3.3 and above)
  • LuwareSystem – this role has permissions to run Luware application (PS Service).

Before configuring the role membership, it is important to understand the difference between SQL Server Logins and Users. Logins have the access to the server and Users have the access to the database. All work is done in the context of database, but to get to do the work, one needs to have firstly access to the SQL server. It means that to grant windows account permissions of a role, both Login and User are required.

Example: granting domain security group permissions to refresh data

The goal of this example is the permissions granting for a domain security group "Marketing" to get data in Excel Power Query and Power BI Reports using the "LuwareGetDataSet" role.

This scenario - with small changes- is applicable for granting other database roles to Users and Groups.

1. Open SSMS and navigate to Object Explorer > Expand SQL Server > Security > Logins.
2. Execute the right click and select New Login… in the opened context menu.
3. On General page, ensure that Windows authentication is selected.
4. On General page, click Search button 
   1. Click Object Types and ensure that Groups are selected
   2. Click Locations and select Entire Directory
   3. Enter the object name, e.g.  "Marketing" and click Check Names
   4. Click OK on Select User, Service Account, or Group window
      
5. On the User Mapping page, find database, on which permissions are required.
   
   
   1. In the list of "Users mapped to this login" on database row, tick the according "Map" checkbox
   2. In the role membership select "LuwareGetDataSet" role in Database role Membership
6. Click OK

Accessing the Data

Via Template

→ To access the database with your new Reporting DWH user, refer to the data evaluation template use

Via Power BI Cloud: Publisher Workspace

  A Power BI "Pro" Account is needed for this procedure!

1. Open Power BI Cloud https://app.powerbi.com/ and sign in as publisher.

2. Creates a Workspace and (if it is needed) add viewer as member or admin of the Workspace:

   1. Open Workspaces -> click ‘Create a Workspace’
      

      

   2. Configure the workspace with parameters as follows:

      1. Private – Only approved members can see what’s inside

      2. ‘Members can only view Power BI content’

      3. Viewer is a ‘Member’

After that you need to configure row level security.

Configure Row Level Security

Row level security is distinguished by individual workspace owner and workspace member rights. Members can be elevated to with supervisor rights.

Configuration steps:

1. Open Workspaces -> Choose the needed workspace -> Datasets -> Security
   

   

   Power BI - Security Settings

2. Enter the email of the user and click ‘Add’.

    This user must have "supervisor" rights in LUCS Configurator -> Users -> Role based access

Power BI Desktop: Publish Report to Cloud Workspace

As power BI creator you may publish a report to the cloud, making it available to additional viewers.

Publish

1. As Creator click Power BI Desktop -> Home -> Publish
   

2. Sign in with the Publisher cloud account (it can be the same as creator or another one)

3. Select the workspace as destination and click Select button
   

   

   Check Report

   1. Open Power BI Cloud -> Your Workspace -> Reports tab
      

      

      Power BI - Inspect Report Tab

   2. → The report should be visible and up to date

Add members as viewers

With publisher rights in BI you can add some viewers via the following steps: 

1. Right Click your new Reporting Workspace

2. Selct "Edit Workspace"

3. Select "Add Members"

   

   Power BI - Add members

4. As publisher access the Row-Level Security:
   Open publisher’s Power BI Cloud -> Workspace -> Datasets -> Security
   

   

   Power BI - Update Security

5. Add viewers to the Row Level Security members

Grant permissions to Viewer

To create a new BI viewer account, you need to add it as viewer user with UPN:

1. Access LUCS Configurator -> Users -> Users and add a new "viewer" user.
    The UPN will be synchronized from Active Directory by CIC, if the user belongs to current Active Directory. When user belongs to another Active Directory, you need to enter UPN manually during the creation.
   

   
   

   

2. Provide the rights for the new "viewer" user / service:

   1. LUCS Configurator -> Users -> Role Based Access -> Supervisor -> Reporting -> Agent (to view the content of Agent tabs)
      and/or 

   2. LUCS Configurator -> Users -> Role Based Access -> Supervisor -> Reporting -> Service (to view the content of Service tabs)

   3. Within database → Execute data loader (Execute Stored Procedure)

      

      LUCS DB - Execute stored procedure to add reporting user data

Power BI Cloud: Access Reporting Data as Viewer

To inspect 

1. Open Power BI Cloud https://app.powerbi.com/ and sign in as viewer.

2. Open the workspace and the report

Power BI Data Refresh

After everything is configured according to the previous steps, the following steps 

Data refresh in Power BI Cloud

Gateway Configuration (automatic refresh)

Power BI Cloud will show new report data only when a new report is published in Power BI Desktop. However, it is possible to configure a gateway and report data will be refreshed in Power BI cloud from the DB directly. This is done via the following steps:

1. Download and install gateway to the machine that is always available ( no sleep mode ) by using the link https://powerbi.microsoft.com/en-us/gateway/

   → Complete the installation successfully

2. Log into the the installed gateway with publisher

3. Open Power BI Cloud -> Workspaces -> Workspace -> Datasets -> Actions -> … -> Settings
   

   

   Power BI - Report Dataset Settings

   
   

4. Open Datasets -> your published report -> Gateway connection -> Manage Gateways

5. Click Add data sources to use the gateway

   

   Power BI - Gateway Cluster Settings

6. Fill Data Source Settings

7. Choose Authentication Method = Windows, User Name and Password = credentials of "Creator".

   

   Power BI - Data Source Settings

8. Choose the gateway

   

   Power BI - Gateway Settings

Timezone Configuration (Power BI Desktop)

To configure the timezone, change "@Offset" in Power BI Desktop:

1. Home Tab  → Select "Edit Queries"
   

   In the left "Queries" section: 

2. Select -> ServiceTask -> Advanced Editor

   → The Advanced editor opens

   

3. Change the "@offset" value to your required value

   

Language Configuration

To configure the language change "@LanguageId" in Power BI Desktop:

1. Home Tab -> Select "Edit Queries" 
   
2. In the left "Queries" Section, right click on "Date" -> Advanced Editor
   → The "Advanced Editor" opens
3. Locate and change @LanguageId (‘ENG’, ‘DEU’, ‘ESP’, ‘ITA’, ‘FRA’).