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.
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.
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.
This scenario - with small changes- is applicable for granting other database roles to Users and Groups.
- Open SSMS and navigate to Object Explorer > Expand SQL Server > Security > Logins.
- Execute the right click and select New Login… in the opened context menu.
- On General page, ensure that Windows authentication is selected.
- On General page, click Search button
- Click Object Types and ensure that Groups are selected
- Click Locations and select Entire Directory
- Enter the object name, e.g. "Marketing" and click Check Names
- Click OK on Select User, Service Account, or Group window
- On the User Mapping page, find database, on which permissions are required.
- In the list of "Users mapped to this login" on database row, tick the according "Map" checkbox
- In the role membership select "LuwareGetDataSet" role in Database role Membership
- 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:
Access LUCS Configurator -> Users -> Users and add a new "viewer" user.
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.
Provide the rights for the new "viewer" user / service::
Web Configurator > Users > Agent/ Supervisor Roles > Reporting:
Agent (to view the content of Agent tabs)
Service (to view the content of Service tabs)
Customer (to see the CustomerPath Report which can contain Agent and Service Tasks).
Within database → Execute data loader (Execute Stored Procedure)LUCS DB - Execute stored procedure to add reporting user data
- Your new user should now be able to access Reporting Data.
Accessing the Data via Template
BI Users and Permissions
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 SQL DB Config
RBAC / RowLevelSecurity (RLS)
|Power BI.com Config|
Pro Licence needed
LUCS Supervisor Licence needed
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.
Mirrored user for LUCS / PowerBI.com
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
This user needs RBAC Reporting access to all data potentially filtered later for users 4...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)
Same configuration as User 2
Cannot access more data than limited for User 2 (which defines the base data for all 3..x)
See detail steps below
Power BI Reporting Setup Steps
Precondition: These steps need to be done as User 1 (Reporting Workspace Manager / PowerBI.com Admin)
Create Power BI Workspace
- Log into your Power BI Pro Account (User 1)
- Go to "Workspaces"
- Click "Create a Workspace" and name it. We will just call it "LUCS Reporting" in this example. You can of course pick any other name.
Confirm the creation → New Workspace is shownNew BI Workspace
Upload Reports via Power BI Desktop
- Open Power BI Client and the included Report Template
- In the Home Ribbon click on "Publish" → "Publish to Power BI"
In newer versions this Option might also appear in the "File" menu.
As your User 1 is already associated and logged into the BI account you can directly select your new "LUCS Reporting" Workspace
Wait for the Report Upload to complete.
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
- Open the "Access" menu
- Either Add individual members or User Groups according to your local AD
Ensure that the permissions for all newly added users is set to "Viewer"
- Repeat these steps for all users that later need to access Reporting in a viewing role (e.g. Agents, Supervisors).
Refer to the Table above for the users. Note that
User 2 can be exempt from this.
- Close the Access menu
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.
- Click on your DataSet context menu and select "Settings" → Datasets Tab Opens
- Unfold "Data Source Credentials" and select "Edit Credentials"
- Enter your LUCS Configured credentials for User 2 (Data Source Credentials) into the form.
Unfold "Parameters" and enter the following information:
Parameter Description Database Name Your Azure Database Language Locale for the Date / Month descriptors MonthLimit How many months back data is retrieved ServerName Your LUCS server instance name TimeZone
Used to calculate Data offset for end of day calculations
By default reporting data is stored with UTC0 offset
Version DB VersionParameters must be the same as in your configured BI Template via the "Home" Ribbon → "Transform Data" → "Parameters Section"
Check with "Sign In" if the connection works, otherwise the next step will not work.
- Click "Apply" when done → The connection to the DB
- Unfold "Scheduled Refresh" and set your times when the Report should update with new data.
You can configure up to 8 Refreshes per day.
- Click on your DataSet context menu and select "Security" → Row-Level Security View opens
Add users for Report viewing permissions for all users with a viewing role (e.g. Agents, Supervisors).
User 2 can be exempt from this.
Save and Close. → Your Report is now ready to share.
Share your Report
- In your "LUCS Reporting" Workspace
- Locate your "Report"
Click on the "Share" icon → "Share Report" view opens
All users with with viewing permissions should already be added from the earlier "Grant Access to Reporting Users"
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