These pages inform you on:

  • how to set up LUCS users and manage access to Power BI Data Warehouse.
  • how you connect your reports to a database via either manual or automated means.

(lightbulb) Power BI itself is supported by Microsoft. We highly recommend reading the official Power BI User Documentation by Microsoft as UI elements and procedures described below may change. 

(lightbulb) The procedures for setting up the DB user and LUCS permissions are identical for both Power BI and Excel. After the permissions setup on this page head over to Data Evaluation Template Use page to see how to connect to the data source.

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.

(lightbulb) 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

Grant permissions to standalone user

It might be advisable to have a standalone user for BI access (e.g. to not loose password / access permissions). To create a new "standalone" 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.
    (info) The UPN will be synchronized from Active Directory by the CIC component, 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:: 
    Web Configurator > Users > Agent/ Supervisor Roles > Reporting:

    1. Agent (to view the content of Agent tabs) 

    2. Service (to view the content of Service tabs)

    3. Customer (to see the CustomerPath Report which can contain Agent and Service Tasks). 

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

    LUCS DB - Execute stored procedure to add reporting user data
  4. Your new user should now be able to access Reporting Data.

Accessing the Data via Template

After setting up data access and permissions you may want to head over to the Data Evaluation Template Use page and learn how to access and evaluate the data.

BI Users and Permissions

(tick) Precondition: The following users need to be set up in LUCS either with individual Role Based Access - RBAC permissions or managed as an AD group.

LUCS and Power BI Sync Users

User

Description

LUCS Config

LUCS SQL DB Config

RBAC / RowLevelSecurity (RLS)

Power BI.com Config

Pro Licence needed

LUCS Supervisor Licence needed

1

Managing the Workspace at PowerBI.com 

Not required as LUCS user

Not required as LUCS user

RLS is working according to LUCS RBAC configuration

No action required.

If this user shall also act as a reporting user → Refer to user 3..x Row Level Security Settings.

(tick) Yes

(tick) Yes

Mirrored user for LUCS / PowerBI.com

  • LUCS Power BI Data Access User
  • Power BI Data Source Credentials

(lightbulb) User "sees" and uploads all the Data to PowerBI.com according to his "RBAC" incl. the RLS for the Reporting Users

Must be added as a LUCS User and RBAC configured

(info) This user needs RBAC Reporting access to all data potentially filtered later for users 4...x.

  • Added to DB (user or per AD Group)
  • LUWAREReportingRole added



  • In LUCS
    • RBAC Configurator: all Users for Reporting must be configured with its "rights"
    • Access to all Organitzation units that need reporting
    • Viewing permissions limited either by Service, Agent or Customer Tasks


In PowerBI.com

  • Added with same identifier (value) as configured in LUCS SQL DB
  • To enable users on a report, they must be added to the "Access" as Users or AD Group

No

No

3...x

Added from "User 1" as a "Viewer" of individualized Reports.

added as a User and RBAC configured

added to DB (user or per AD Group)
and LUWAREReportingRole added

Same configuration as User 2

(warning) Cannot access more data than limited for User 2  (which defines the base data for all 3..x) 

  • User or AD Group must be added to the "Row Level Security" Settings

(info) See detail steps below

(tick) Yes

(tick) Yes

(question) When is the Power BI Gateway needed? 

You might need a data gateway if your data sources are behind a firewall, require a VPN, or are on virtual networks. → Refer to the Microsoft Documentation.

Power BI Reporting Setup Steps

(tick) Precondition: These steps need to be done as User 1 (Reporting Workspace Manager / PowerBI.com Admin)

Create Power BI Workspace 

  1. Log into your Power BI Pro Account (User 1)
  2. Go to "Workspaces"
  3. Click "Create a Workspace" and name it. (lightbulb)We will just call it "LUCS Reporting" in this example. You can of course pick any other name.
  4. Confirm the creation → New Workspace is shown

    New BI Workspace

Upload Reports via Power BI Desktop

  1. Open Power BI Client and the included Report Template
  2. In the Home Ribbon click on "Publish" → "Publish to Power BI"
    (lightbulb)In newer versions this Option might also appear in the "File" menu.
    (info) As your User 1 is already associated and logged into the BI account you can directly select your new "LUCS Reporting" Workspace
  3. Wait for the Report Upload to complete.

  4. The Report and the Dataset are now listed in your "LUCS Workspace" → You can continue with the next step.

    Uploaded BI Report

Grant Access to Reporting Users

  1. Open the "Access" menu
  2. Either Add individual members or User Groups according to your local AD
  3. Ensure that the permissions for all newly added users is set to "Viewer"


  4. Repeat these steps for all users that later need to access Reporting in a viewing role (e.g. Agents, Supervisors).
    (lightbulb) Refer to the Table above for the users. Note that 
    (lightbulb) User 2 can be exempt from this.
  5. Close the Access menu

Configure DataSet

(tick) Optional Requirement: A PowerBI Gateway needs to be configured at this point if you are working behind a firewall, require a VPN, or are on virtual networks. Refer to the Microsoft Documentation.

  1. Click on your DataSet context menu and select "Settings" → Datasets Tab Opens
  2. Unfold "Data Source Credentials" and select "Edit Credentials"
  3. Enter your LUCS Configured credentials for User 2 (Data Source Credentials) into the form.
  4. Unfold "Parameters" and enter the following information: 

    ParameterDescription
    Database NameYour Azure Database
    LanguageLocale for the Date / Month descriptors
    MonthLimitHow many months back data is retrieved
    ServerNameYour LUCS server instance name
    TimeZone

    Used to calculate Data offset for end of day calculations

    (lightbulb)  By default reporting data is stored with UTC0 offset

    VersionDB Version

    Parameters must be the same as in your configured BI Template via the "Home" Ribbon → "Transform Data" → "Parameters Section"


    (tick) Check with "Sign In" if the connection works, otherwise the next step will not work.

  5. Click "Apply" when done → The connection to the DB 
  6. Unfold "Scheduled Refresh" and set your times when the Report should update with new data.
    (lightbulb) You can configure up to 8 Refreshes per day.

Configure RowLevelSecurity

  1. Click on your DataSet context menu and select "Security" → Row-Level Security View opens
  2. Add users for Report viewing permissions for all users with a viewing role (e.g. Agents, Supervisors). 
    (lightbulb) User 2 can be exempt from this.

  3. Save and Close. → (tick) Your Report is now ready to share.

Share your Report

  1. In your "LUCS Reporting" Workspace
  2. Locate your "Report" 
  3. Click on the "Share" icon → "Share Report" view opens
    (lightbulb) All users with with viewing permissions should already be added from the earlier "Grant Access to Reporting Users"
    (lightbulb) Read the notes provided in the "Share" view closely as it instructs who can now see this report.

    This link is also used for the Reporting Dashboard of LUCS.

    Sharing a BI Report URL