These pages inform you on:
- how to set up and connect your own reporting database to Stratus Agent.
- how to grant Luware access to push the newest reporting data onto your database.
- how to set up Stratus Agent users and manage access to Power BI Data Warehouse.
- how you connect your report templates 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.
How we connect your Reporting Data
To connect to the Luware Cloud and Stratus Agent to your own Azure environment two connections are required:
- An external access to send newest reporting data via push replication. This step requires setup of your own Azure SQL database as described below.
- An internal access to evaluate your reporting data using Luware-provided templates. Also see Data Evaluation Template Use
Azure SQL Setup for Cloud Reporting
Azure SQL Reporting Prerequisites
In order to complete an Azure cloud SQL database configuration you need to fulfill the following prerequisites:
- A running Azure subscription for creating Azure SQL databases
Please contact your Microsoft Representative to discuss Azure SQL Costs.
- Azure DB permission account with rights to
- Create New Azure SQL Database
- Create Azure Active Directory group
- Create guest accounts
- Manage Azure firewall
- Azure Reporting account with rights to read / write in your new Reporting DB
- SQL Server Management Studio installed
Create and Configure SQL Database
On your Azure Account management:
- Look for the "Create SQL Database" option
- Create a new Database with name "StratusReporting"
- On the "Compute and Storage" section, click on "Configure Database"
- Go to “Looking for basic, standard, premium” section. Select "Standard" and confirm.
- Go to “Additional Settings”, use Collation “SQL_Latin1_General_CP1_CI_AS”.
Head to the Tab “Review + create" and confirm the database creation.Azure SQL Database creation dialogue
Retrieve database name
When the database is created you can see it in your SQL Databases listList of SQL Databases in Azure
Open database and note down the Server Name.SQL Database Server Name
- You have to provide this name to Luware during initial on-boarding and setup.
- Also you will need the name to connect to the database using SQL Server Management Studio / Power BI during later use
Configure Firewall Rules
In this section a firewall rule is added to permit connections from Luware's external IP address.
Head to your newly created database. Locate and click on “Show firewall settings”
Add the rule for Luware's external IP addresses, which your system engineer will give you.Luware external IP Address
Additionally, all computers (IP address) which will access the Azure DB need to be added to the firewall rules.
Create Azure AD Group
Next we require a LuwareStratusReportingAdministrators security group for permission management
- Use the search or go to Services > “Azure Active Directory”
- Go to “Groups” and click “+ New group”
Add new Group named “LuwareStratusReportingAdministrators”
Grant DB ownership permissions
"db_owner" permissions need to be granted to the newly created LuwareStratusReportingAdministrators group.
- Open SQL Server Management Studio
In the "Connect to Database Engine" dialog window specify the Server Name (→ See Step 2)
In the "Connection Properties" specify Database Name, and click "Connect"
Execute following T-SQL code on the new StratusReporting database to grant a “db_owner” role to the “LuwareStratusReportingAdministrators” security Group:
SQL Script to grant DB Admin ownership
CREATE USER [LuwareStratusReportingAdministrators] FROM EXTERNAL PROVIDER; GO EXEC sp_addrolemember 'db_owner', 'LuwareStratusReportingAdministrators'; GOSQL
Execute following T-SQL code on the new StratusReporting database to grant a “LuwareGetDataset” role to the “LuwareStratusReportingUsers” security Group:
SQL Script to grant DB Reporting Rights
CREATE USER [LuwareStratusReportingUsers] FROM EXTERNAL PROVIDER; GO EXEC sp_addrolemember 'LuwareGetDataset', 'LuwareStratusReportingUsers'; GOSQL
Add external user into to “LuwareStratusReportingAdministrators” group
Now you need to add the Luware account as “guest user” to your azure AD. This is necessary to push structure and data into new StratusReporting database
- Open Azure Active Directory “Users” and click “+New Guest User”
Select Invite user.
Luware has to provide you an representative email address.Azure User invite via mail address
- Search for the new user and open the user properties
- Open "User Groups and click on "+ Add Memberships".
- Search for “LuwareStratusReportingAdministrators” and click “Select”.
Ensure that the membership is now assigned to that user.LuwareStratusReportingAdministrators Group Membership
7. Congratulations - Reporting DB Setup should now be complete. Make sure to inform us to proceed with further functionality checks.
Internal Database Reporting Access
To access to the Luware DataWareHouse (DWH) group and use the provided evaluation templates with the DB you need to perform the following steps.
Open Microsoft SQL Management Studio.
Create a user (may be created without UPN)
- Grant the User read permissions on your SQL Database
RBAC Reporting permissions
Now you need to provide the report viewing rights within Stratus Agent
- Head to Stratus Agent Configuration -> Users -> Agent/Supervisor Roles with rights to Agent or Service Tabs respectively
- Create or access the same User used in in SQL Setup above.
- Reporting Data Warehouse access
Note: Do not mix up the permissions with "Reporting Portal". Those are needed for Web Reporting (Frontend) access.
- The user is now able to access
Access data via local reporting template
To access the database with your new Reporting DWH user, refer to the data evaluation template use chapter.