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.
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
Accessing the Data via Template
To access the database with your new Reporting DWH user, refer to the data evaluation template use
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.
Power BI Cloud: Access Reporting Data as Viewer
Open Power BI Cloud https://app.powerbi.com/ and sign in as viewer.
Open the workspace and the report
Power BI Data Refresh
After everything is configured according to the previous steps, follow these steps depending on which Power BI solution you use:
For Power BI Cloud
Data refresh steps for Power BI Desktop
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:
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
Log into the the installed gateway with publisher
Open Power BI Cloud -> Workspaces -> Workspace -> Datasets -> Actions -> … -> SettingsPower BI - Report Dataset Settings
Open Datasets -> your published report -> Gateway connection -> Manage Gateways
Click Add data sources to use the gatewayPower BI - Gateway Cluster Settings
Fill Data Source Settings
Choose Authentication Method = Windows, User Name and Password = credentials of "Creator".Power BI - Data Source Settings
Choose the gatewayPower BI - Gateway Settings
Timezone Configuration (Power BI Desktop)
To configure the timezone, change "@Offset" in Power BI Desktop:
Home Tab → Select "Edit Queries"
In the left "Queries" section:
Select > ServiceTask > Advanced Editor
→ The Advanced editor opens
Change the "@offset" value to your required value
To configure the language in Power BI Desktop:
- Go to the the Home Tab > "Transform Data"
- Select "Edit Parameters"
- Change Language to your desired option.
Language Parameter not present? Check that you have the latest BI Template or contact your Luware BI Support.