Stratus projects capture a huge volume of data, like tracking logs for example, but Stratus reporting has limits and Stratus APIs can be complex to consume. Stratus wants to provide customers access to all their project data, including custom calculated fields and much more. Stratus Big Data is the Stratus approach to providing an easy to consume data set, aggregated across all projects, comprised of data required to build extremely valuable cross project dashboards, with a fast API and straight forward data model. And there is a side benefit too, the new Power BI dashboards created with big data can be embedded into Stratus, so Stratus users have interactive context specific visual reports right in Stratus.

Stratus Academy Course Video

To take the Big Data course, login to Stratus Academy and and locate the course ADM-521: Big Data.

Stratus Setup

Prepare Reports and Fields

Create new reports and fields dedicated to Big Data Analytics for Models, Packages, Assemblies, and Parts that specify metrics to be captured.

Prepare Fields and Reports Summary:

1. Big Data Package and Assembly reports are analogous to existing Package and Assembly dashboard reports, and can be configured to extract as many package and assembly data fields as needed.

2. Report configuration tips:

   1. Dedicated Big Data Reports - It is recommended that a dedicated report be created for each of area (Models, Packages, Assemblies, and Parts) and that the reports only be utilized by Stratus Big Data. A single report will specify metrics to be captured in each Big Data area (Models, Packages, Assemblies, and Parts). Any unexpected changes to the referenced reports or fields can cause issues with the Power BI data and republishing data could take up to 2 days to complete.

   2. Permissioned Reports - In addition to creating dedicated reports, it is also recommended that Big Data reports be permissioned to those who are involved in Big Data and creating Power BI reports.

   3. Report Item Types - When creating reports, only the following Report Item Types are allowed:

      1. Models - Parts

         1. Note: The Model report is actually a “Part” item type report and does not work like the others. Report metrics are gathered at the model level, so this report only extracts aggregated column data (the bottom or total row) holding a Sum or Average numeric result.

      2. Packages - Package

      3. Assemblies - Assembly

      4. Parts - Part

   4. Required Properties - Only one report can be specified for each area (Models, Packages, Assemblies, and Parts), so create each report with the required properties that need to be published and used in Power BI. Note: If a report field needs to be added after the database has been published, it can take up to 2 days to publish the database again to include a missed report field.

   5. Report Field Format and Precision - It is recommended to set Format to String, Integer, Decimal, Date, and Boolean. If, for example, FeetInch or InchFraction is selected, Big Data will convert it to a Decimal as Power BI does not recognize these as numbers. It is also recommended to configure Precision to specify how many decimal points are to be used.

   6. Report Field Built-in Properties - The following Report Field properties are automatically included in reports published to Power BI and do not need to be included as a Report Field of a Report. If they are included, they will be ignored. Below is a list of “built-in properties”.

      Open

      

       

   7. Report Field Header or Field Display Name - Under Reports > Report Fields, the Header overrides the Property Name. Similarly, under Fields the Display Name overrides the Name. Overriding these names can become confusing in Power BI if more than one field has the same override name. For this reason, it is recommended that Big Data report fields do not use either a Report Field Header or Field Display Name.

   8. Converted Property Names - SQL and/or Power BI requires that Report Field property names follow certain standards, so Stratus helps convert property names. For example, spaces and invalid characters are replaced with an underscore. Below are some examples of how property names will be converted:

      1. STRATUS.Assembly.NumParts to NumberofParts

      2. Package(s) to Package_s_

      3. Spool Name to SpoolName

   9. Model Viewer Checkbox - Check the Model Viewer checkbox while testing and validating the data in the report. Once the report is working as expected, the Model Viewer checkbox can be unchecked.

Configuration

To Configure Big Data:

1. Go to Admin > Company > Big Data.

   Open

   

Reports

1. Under Reports, select the report to be used for:

   1. Models

   2. Packages

   3. Assemblies

   4. Parts

Start Date

The Start Date is used to filter dated Tracking Log and Task entries. Only those entries newer than the Start Date will be published to the Big Data database in Power BI.

Run As Admin User

The user selected under Run As Admin User is required to publish Big Data. The selected user does not require a specific project role and the data is not filtered based on the selected user’s permissions.

Database Connection

The Database Connection section has two parts. The Server and Database values will automatically populate after the full data sync has completed. See the Sync History tab to see the data sync percent complete. The Regenerate SQL User and Password button generates the user name and password that enables login to the database in Power BI. These values will be used when connecting to Power BI. In addition, this section provides an option to rebuild the data after relevant Stratus report fields have changed.

1. Server and Database - The Server and Database information identifies the database in Stratus Big Data. These values are copy/pasted into the SQL Server database dialog during step 3 of the Power BI Setup process.

2. Regenerate SQL User and Password - The SQL Server database in step 1 requires a User name and Password. To generate this User name and Password:

   1. Click the Regenerate SQL User and Password button. The Regenerate SQL User and Password prompt will display. In addition to the prompt message, know that Stratus does NOT store the username and password. You must store these values for safe keeping within your own password management system. If Yes is clicked and a username and password already exists, then all existing connections will be broken and will require updating to the new username and password values.

   2. Click Yes to generate a new SQL User name and Password.

   3. The New SQL User and Password dialog will display. Warning: Be sure to copy the entire Password string as it is cutoff in this dialog. Store this User and Password in a safe place. All users running Power BI desktop will use this User name and Password to access the SQL Server database. Copy/paste this information during step 5 of the Power BI Setup process

3. Rebuild Data (As Needed) - Clicking the Rebuild Data button is used to force a full refresh of all big data. This manual operation is done anytime changes are made to the configured Report Fields and referenced field expressions as Stratus does not attempt to recognize all the potential modifications an admin can make that would impact the report generated results. Know that a full big data rebuild may take up to two days to complete as it processes all active projects in your company database.

Analytics Reports

The Analytics Reports section is where Power BI reports are registered and embedded within Stratus.

To register a New Analytics Report:

1. Click the New Analytics Report button.

2. Name - Enter the Name that will display in your analytics dropdowns in Stratus.

3. Report Type - Defines the report type. Right now, the only option is Power BI.

4. Report Id - Copy/paste the unique ID that associates a specific report. This reportId is found in Power BI under File > Embed Report > Website or portal.

5. Tenant Id (CTID) - Copy/paste the unique ID that associates a specific Microsoft tenant. This tenantId is found in Power BI under File > Embed Report > Website or portal.

6. Service Principal Authentication (Optional) - This is a unique Microsoft Authentication and can be used like a universal username and password for accessing all Big Data reports embedded in Stratus. Without configuring the Service Principal Authentication, when a user attempts to access a report, like under Projects > Analytics, they will be prompted for their credentials.

   1. Workspace Id - Locate Workspace Id in your company’s Azure settings.

   2. Client Id - Locate Client Id in your company’s Azure settings.

   3. Client Secret - Locate Client Secret in your company’s Azure settings.

7. Projects - Select this option to display the Analytics Report under the Projects > Analytics tab in Stratus.

8. Models - Select this option to display the Analytics Report under the Models > Analytics tab in Stratus.

9. Packages - Select this option to display the Analytics Report under the Packages > Analytics tab in Stratus.

10. Show Filters - Choose this option if you want your Analytics Report filters to display. This will give users access to the same filters you might see in Power BI.
    
    

11. Note: Since Power BI is leveraged to aggregate data, non-Stratus data sources (Ex. ERP) can also be used and embedded within the context of Stratus projects.

Sync History

The Sync History tab shows the history of data syncs from Stratus to the Stratus Analytics database. The Sync History section keeps the oldest publish record along with the most recent 9 updates, so 10 total. This allows you to see when the last full Rebuild took place and monitor how long each hourly sync is taking to complete. During a full rebuild, you can monitor the percent complete to know when all data has been updated in the big data SQL database. The initial data extraction may take up to two days to complete, as it processes all active projects in the company database. After the initial data sync, the data automatically synchronizes changed data every hour.

Power BI Setup

Connect to SQL Server Data Tables

Each user who will use the Power BI Desktop application needs to connect to the SQL Server database generated in Stratus Big Data using the same SQL Server database Username and Password. See the Database Connection section for more information.

1. In Power BI, click Get data and then click More.

    

2. In the Get Data dialog, click Azure, then Azure SQL database and then Connect.

3. In the SQL Server database dialog, copy/paste the Server and Database information from the Stratus provided Database Connection under Admin > Company > Big Data > Configuration. See step 1 of the Database Connection section for more information.

   1. For Data Connectivity Mode, select either:

      1. Import (Recommended) - Check this option if you want to download the entire database and “Import” it into the report.

      2. DirectQuery - Check this option if you want to query the server database directly. This method has limitations which includes the inability to Filter via Date Hierarchy (Ex. Year, Month, Day).

4. When done, click Connect. The SQL Server database dialog will display.

5. With Database selected, copy/paste the safely saved SQL Server database User name and Password. See step 2c of the Database Connection section for more information.

6. Click Connect.

7. After a successful database connection, the Navigator dialog will display the list of available SQL data tables available to bring into a Power BI report.

   1. Note: Use <shift> + click to select the first then last to toggle ON and select all tables, then click Load button.

   2. When done, each table will be available in Power BI as a separate data set.

       

8. The data tables will load into Power BI and are available for report building.

9. Note: The SQL Server database username and password are stored in Power BI and are used for multiple reports. If credentials need to be purged, in Power BI:

   1. Click Options and settings

   2. Click Data source settings

   3. Locate the s-bigdata source

   4. Click either Edit Permissions to change the permissions, or click Clear Permissions to purge the permissions.

Data Table Relationship Mapping

After data tables have been published to Power BI, relationships between the data tables must be established prior to creating a report. This section demonstrates common relationship connections between the project, model, and package that you will configure in your data model. Note that this section does not go into detail behind the setup. See the Power BI Training Resources for more information.

To configure relationships between data tables:

1. In Power BI, click the Model View button on the left side. The data tables will display.

2. Click and drag the data tables below to get them in proximity to each other.

3. Configure the relationship between the Projects and Models data tables.

   1. Within the Projects table, locate the Id property.

   2. In the Models table, locate the Project Id property.

   3. Click the Id property and drag it until it highlights the Project Id property.

   4. The New relationship dialog displays informing that when the property name is Id, like in the Projects table, that it is a unique value in that table. Contrarily, if the property name includes another name like Project and Id, then this is not a unique value. So, in this case, there is one project that can have many models.

   5. Click Save when done.

   6. A visual representation of the tables displays the one to many relationship.

4. Continue the process of connecting tables.

5. Below is a table of relationships that should work for all companies.

Common Data Table Relationships

Convert Existing Power BI (Big Data Alpha) to Big Data Beta SQL Connection

Below is a video of Jonathan Umscheid walking through the steps to convert existing Power BI data table relationships and reports to the newly supported Big Data SQL Connection.

Power BI Training Resources

• Microsoft Power BI Learning Path

• Microsoft Power BI get started documentation

• Microsoft Dynamic M query parameters in Power BI Desktop