These pages inform you on how to set up LUCS users and manage access to Power BI Data Warehouse. In the second step we explain 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.
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.
- 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
→ 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!
Open Power BI Cloud https://app.powerbi.com/ and sign in as publisher.
Creates a Workspace and (if it is needed) add viewer as member or admin of the Workspace:
Open Workspaces -> click ‘Create a Workspace’
Configure the workspace with parameters as follows:
Private – Only approved members can see what’s inside
‘Members can only view Power BI content’
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.
Open Workspaces -> Choose the needed workspace -> Datasets -> SecurityPower BI - Security Settings
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.
As Creator click Power BI Desktop -> Home -> Publish
Sign in with the Publisher cloud account (it can be the same as creator or another one)
Select the workspace as destination and click Select button
Open Power BI Cloud -> Your Workspace -> Reports tabPower BI - Inspect Report Tab
→ 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:
Right Click your new Reporting Workspace
Selct "Edit Workspace"
Select "Add Members"Power BI - Add members
As publisher access the Row-Level Security:
Open publisher’s Power BI Cloud -> Workspace -> Datasets -> SecurityPower BI - Update Security
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:
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.
Provide the rights for the new "viewer" user / service:
LUCS Configurator -> Users -> Role Based Access -> Supervisor -> Reporting -> Agent (to view the content of Agent tabs)
LUCS Configurator -> Users -> Role Based Access -> Supervisor -> Reporting -> Service (to view the content of Service tabs)
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
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, the following steps
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 change "@LanguageId" in Power BI Desktop:
- Home Tab -> Select "Edit Queries"
- In the left "Queries" Section, right click on "Date" -> Advanced Editor
→ The "Advanced Editor" opens
- Locate and change @LanguageId (‘ENG’, ‘DEU’, ‘ESP’, ‘ITA’, ‘FRA’).