Big Data (Admin)
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.
- 1 Request Feature
- 2 Stratus Setup
- 2.1 Prepare Reports and Fields
- 2.2 Configuration
- 2.2.1 Reports
- 2.2.2 Start Date
- 2.2.3 Run As Admin User
- 2.2.4 Database Connection
- 2.3 Analytics Reports
- 2.4 Sync History
- 3 Power BI Setup
- 4 Convert Existing Power BI (Big Data Alpha) to Big Data Beta SQL Connection
- 5 Power BI Training Resources
Request Feature
To request that the Stratus Big Data feature be turned on for your company, send an email to Matt Tate. Once the feature is turned on, a full data sync will commence. It is recommended that this step be completed before proceeding with Configuration steps which will take a couple of hours to complete. If reports have already been selected in Configuration it could take up to 2 days to complete. The progress can be viewed under the Sync History tab.
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:
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.
Report configuration tips:
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.
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.
Report Item Types - When creating reports, only the following Report Item Types are allowed:
Models - Parts
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.
Packages - Package
Assemblies - Assembly
Parts - Part
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.
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.
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. The included report field depends on the report’s item type (Package, Assembly, or Part). If one of these report fields is included in the report, it will result in duplicate data fields in Power BI. For example, a tracking status report field was included in a report and as a result, there is a Status and a Status_ where the underscore was added to the duplicate field give it a unique name. Both of these include the same data.
Cadid
CreatedDT - Created Date
Id
ModelId
Name - Therefore a property like Stratus.Assembly.Name does not need to be included in the report. However, a Report Field Header value can control what is displayed in Power BI, so a Header name like Spool Name will be available in Power BI. Similarly, a Field Display Name will also be used in Power BI.
Number
Office End Date
Package Category
ProjectId
Purchasing End Date
Required End Date
Shop End Date
Status
Tracking Log Entries
Type
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.
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:
STRATUS.Assembly.NumParts to NumberofParts
Package(s) to Package_s_
Spool Name to SpoolName
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:
Go to Admin > Company > Big Data.
Reports
Under Reports, select the report to be used for:
Models
Packages
Assemblies
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.
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.
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:
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.
Click Yes to generate a new SQL User name and Password.
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
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:
Click the New Analytics Report button.
Name - Enter the Name that will display in your analytics dropdowns in Stratus.
Report Type - Defines the report type. Right now, the only option is Power BI.
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.
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.
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.
Workspace Id - Locate Workspace Id in your company’s Azure settings.
Client Id - Locate Client Id in your company’s Azure settings.
Client Secret - Locate Client Secret in your company’s Azure settings.
Projects - Select this option to display the Analytics Report under the Projects > Analytics tab in Stratus.
Models - Select this option to display the Analytics Report under the Models > Analytics tab in Stratus.
Packages - Select this option to display the Analytics Report under the Packages > Analytics tab in Stratus.
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.
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.
In Power BI, click Get data and then click More.
In the Get Data dialog, click Azure, then Azure Synapse Analytics SQL and then Connect.
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.
For Data Connectivity Mode, select either:
DirectQuery (Recommended) - Check this option if you want to query the server database directly.
Import - Check this option if you want to download the entire database and “Import” it into the report.
When done, click Connect. The SQL Server database dialog will display.
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.
Click Connect.
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.
Note: Use <shift> + click to select the first then last to toggle ON and select all tables, then click Load button.
When done, each table will be available in Power BI as a separate data set.
The data tables will load into Power BI and are available for report building.
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:
Click Options and settings
Click Data source settings
Locate the s-bigdata source
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:
In Power BI, click the Model View button on the left side. The data tables will display.
Click and drag the data tables below to get them in proximity to each other.
Configure the relationship between the Projects and Models data tables.
To be continued.
Convert Existing Power BI (Big Data Alpha) to Big Data Beta SQL Connection
Content coming soon!
Power BI Training Resources
© Copyright 2022 GTP Services, LLC All rights reserved. | About | Contact