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.

(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. 

(info) This page covers historic reporting only. Refer to the Web Reporting portal pages for the daily-business side of reporting. Also refer to the Role Based Access - RBAC pages for related user rights.

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. (info) 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. (info) 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:

  1. A running Azure subscription for creating Azure SQL databases
    (lightbulb) Please contact your Microsoft Representative to discuss Azure SQL Costs.
  2. Azure DB permission account with rights to
    1. Create New Azure SQL Database
    2. Create Azure Active Directory group
    3. Create guest accounts
    4. Manage Azure firewall
  3. Azure Reporting account with rights to read / write in your new Reporting DB
  4. SQL Server Management Studio installed

Create and Configure SQL Database

On your Azure Account management:

  1. Look for the "Create SQL Database" option
  2. Create a new Database with name "StratusReporting"
  3. On the "Compute and Storage" section, click on "Configure Database"
  4. Go to “Looking for basic, standard, premium” section. Select "Standard" and confirm.
  5. Go to “Additional Settings”, use CollationSQL_Latin1_General_CP1_CI_AS”.
  6. Head to the Tab “Review + create" and confirm the database creation.

    Azure SQL Database creation dialogue

Retrieve database name

  1. When the database is created you can see it in your SQL Databases list

    List of SQL Databases in Azure
  2. Open database and note down the Server Name.

    SQL Database Server Name
    1. (tick) You have to provide this name to Luware during initial on-boarding and setup.
    2. (lightbulb) 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.

  1. Head to your newly created database. Locate and click on “Show firewall settings

  2. 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

  1. Use the search or go to Services > “Azure Active Directory
  2. Go to “Groups” and click + New group
  3. Add new Group named “LuwareStratusReportingAdministrators

Grant DB ownership permissions

"db_owner" permissions need to be granted to the newly created LuwareStratusReportingAdministrators group.

  1. Open SQL Server Management Studio
  2. In the "Connect to Database Engine" dialog window specify the Server Name (→ See Step 2)

  3. In the "Connection Properties" specify Database Name, and click "Connect"

  4. 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;
    EXEC sp_addrolemember 'db_owner', 'LuwareStratusReportingAdministrators';
  5. 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; 
    EXEC sp_addrolemember 'LuwareGetDataset', 'LuwareStratusReportingUsers';

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

  1. Open Azure Active Directory “Users and click “+New Guest User
  2. Select Invite user.
    (tick) Luware has to provide you an representative email address. 

    Azure User invite via mail address
  3. Search for the new user and open the user properties
  4. Open "User Groups and click on "+ Add Memberships".
  5. Search for “LuwareStratusReportingAdministrators” and click “Select”.
  6. 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. 

SQL Setup

  1. Open Microsoft SQL Management Studio.

  2. Create a user (may be created without UPN)

  3. Grant the User read permissions on your SQL Database

RBAC Reporting permissions

Now you need to provide the report viewing rights within Stratus Agent

  1. Head to Stratus Agent Configuration -> Users -> Agent/Supervisor Roles with rights to Agent or Service Tabs respectively
  2. Create or access the same User used in in SQL Setup above.
  3. Reporting Data Warehouse access

    (warning) Note: Do not mix up the permissions with "Reporting Portal". Those are needed for Web Reporting (Frontend) access.

  4. The user is now able to access

Access data via local reporting template

(info) To access the database with your new Reporting DWH user, refer to the data evaluation template use chapter.