Analytics and Big Data (beta)
As this feature is in beta, we will be turning a few select customers on and implementing them slowly. Please reach out to your sales rep if this sounds like something that you want to test out.
Why Big Data Analytics?
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. Over time, we have witnessed challenges using existing APIs and reporting limitations with performance and generating reports across projects. Some customers have built nightly processes to clone Stratus data into their own databases, in order to drive Power BI and Tableau dashboards. These are complex IT projects which ideally should not be required.
Big Data Analytics 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.
We are excited to see how customers leverage this new and powerful extension to Stratus!
Big Data Analytics Configuration Overview
Big Data
Go to admin company big data and select the reports, start date, and run as a user for the data extraction.
Configuration
This is where you will define the relationships between your data and the aggregated metrics that will drive analytics in Stratus.
Reports
You can specify reports with field values to extract for models, packages, assemblies, (and parts - coming soon). Model parts report must have aggregate enabled on columns to extract meaningful data. Only numeric column data types will be extracted for Models. Package and Assembly reports will be analogous to your Package and Assembly dashboard reports, and can be configured to extract as many package and assembly data fields as needed. In order to build a relationship between assemblies and packages, you should consider including STRATUS.Assembly.PackageId (or one of the package category specific values) in your Assemblies report. (Similarly, when Parts reports are available, you should include STRATUS.Part.AssemblyId it the Parts report.)
The OData feed API will dynamically include your custom fields as properties for Models, Packages, Assemblies, (and Parts soon).
Start Date
This determines the earliest date from when to start gathering tasks and tracking log data.
Run As Admin User
This is required simply for user access to the system but does not require specific project roles.
Analytics Reports
This is where you can configure Power BI documents to be embedded into your Stratus company
Name - Simply the name that you want to appear in your analytics dropdowns in Stratus
Report Type - Defines the report type. Right now, the only option is Power BI
Report Id - A unique ID that associates to a specific report
Tenet Id (CTID) - A unique ID that associates with a specific Microsoft tenant.
Projects - Choose this option if you want your Analytics Report to show on the Projects page of Stratus.
Models - Choose this option if you want your Analytics Report to show on the Models page of Stratus.
Packages - Choose this option if you want your Analytics Report to show on the Packages page of Stratus.
Show Filters - Choose this option if you want your Analytics Report filters to show which will give users access to the same filters you might see in Power BI.
(Note: we are looking into adding the service principal authentication option for embedded Power BI.)
Sync History
This page shows a history of data syncs from Stratus to your Stratus Analytics database. We are currently running delta syncs every hour and you can see things like completion status and success rates.
Big Data Analytics Setup Steps (From Template)
1. Download the PBIX Template File
Download the following PBIX file:
2. Modify the App Key
Open the PBIX file in Power BI.
Select Transform data from the Home tab of Power BI Desktop and click Edit parameters.
Add an active Stratus app key found in Company Admin > App Keys to the StratusAppKey parameter.
Click OK and save the PBIX file
3. Modify the Power BI to Meet Your Needs
Start creating meaningful dashboards for you and your organization. You can start by getting a more holistic picture of things like packages per due date by whatever metric you can think of. Get creative and have fun!
4. Embed your Power BI into Stratus
After saving the Power BI and publishing it, we can embed these analytics and metrics directly into Stratus!
Publish by navigating to the Home tab of the Power BI and clicking the Publish button, being sure to publish it to the correct workspace in your organization.
Now open your Power Bi in the web browser to grab the Report and Tenant IDs.
Click File, then Embed report, and finally Website or portal.
Copy the entire embed link and paste it into something like Notepad. In this link, you can find the unique Report and Tenant IDs
Configuring Credentials in your Microsoft Tenant to auto-refresh the data (optional)
Go to your Power BI workspace (should be something like your company name)
Find the Semantic Model of your Power BI in the dashboard and click the schedule refresh button (looks like a calendar with a refresh symbol)
Under the Data source credentials, you will need to click on each of the Edit credentials buttons
On the dialog choose the Organizational level and the Skip test connection checkbox clicking the Sign in button (these are the settings that we use but your organization may want a specific level of authorization chosen.
Go back to Stratus and navigate to the Big Data tab of the Company Admin and go to the Analytics Reports sub-tab
Create a New Analytics Report and add your Report and Tenant IDs
Give your report a unique name as you want it seen in Stratus
Check the following boxes as needed:
Projects - Will show up as a new tab on the Projects hub in Stratus
Models - Will show up as a new tab on the Models hub in Stratus
Packages - Will show up as a new tab on the Packages hub in Stratus
Show Filters - When checked, displays and enables the report and dashboard filters from your Power BI. When unchecked, the defaults will be applied as they were last configured in Power BI.
You may not require the use of all the embedded queries in the PBIX template file so you can remove any that are unused.
You may need to manage the relationships between the data tables based on the IDs and set the cardinality and directionality of those connections appropriately. This will be the most challenging part and is why we encourage you to only connect to the feeds you need. While it may be enticing to bring them all in, that will add complexity as you try to manage circular references in the model.
Big Data Analytics Setup Steps (From Scratch)
1. Prepare your Fields and Reports
Create or edit the reports for models, packages, and assemblies that specify the metrics to be captured. Be sure that the reports have the numeric format, the sum or average option, and the display in dashboards turned off. Although displaying the package or assembly report temporarily in dashboards may be useful for data validation.
Package and Assembly reports are setup the same as typical dashboard reports and support columns of all data types.
The Model report is actually a “Part” item type report and does not work like the others. Since we are gathering metrics at the model level, this report only cares specifically about aggregated columns holding a Sum or Average numeric result. It is basically capturing the summary or total row which appears at the bottom of the model report. It may take some creativity to specify fields with unique filters for expressions to gather the unique metrics you wish to quantify and associate with each model. Doing so will enable higher level BI reporting across all projects.
2. Configure your Big Data
Go to admin company big data and select the reports, start date, and run as a user for the data extraction. Be sure to click the Save button to save your changes.
NOTE: The Rebuild Data button is provided as a way to force a full refresh of all big data. Stratus does not attempt to recognize all the potential modifications an admin user can make that would impact the report generated results. The rebuild data option is provided so following completion of changes to reports, referenced field expressions, and related, the next hourly sync process will perform a full rebuild of the big data database.
3. Choose Your Data (OData API)
Go to the open API page to see the available endpoints for the data: https://api.gtpstratus.com/
Select the STRATUS - Big Data OData API option from the dropdown at the top of the page
Note the available endpoints that can be used when setting up your OData connections.
4. Configure your Power BI
First, we need to create a new dynamic parameter in Power BI. You can see how to do that by visiting g
the following link: Dynamic M query parameters in Power BI Desktop - Power BISelect Home > Transform data > Transform data to open the Power Query Editor.
In the Power Query Editor, select New Parameters under Manage Parameters in the ribbon.
Create the following parameters”
Stratus API URL
Name = “Stratus_API_URL” (this is an example and you can name it however you see fit)
Type = Text
Current Value = “https://api.gtpstratus.com ”
Stratus App Key
Name = “Stratus_App_Key” (this is an example and you can name it however you see fit)
Type = Text
Current Value = An active Stratus app key found in Company Admin > App Keys
There is no required Partner App selection needed.
Now we need to write the Queries to get our data from the OData feed.
To get to Power Query Editor, select Transform data from the Home tab of Power BI Desktop.
In the Query Editor, click the New Source dropdown from the Home tab and choose the Blank Query option.
Name your query accordingly in the properties on the right-hand side of the editor. The name will depend on which feeds you choose to use. For this one, we will start with the Assemblies feed and so we will name it “Assemblies”.
Now it is time to write the Query for assemblies in the formula bar. Note that I am using the URL and App Key parameters created above and yours will depend on your naming conventions for them.
= OData.Feed(Stratus_API_URL&"/v1/bigdata/assemblies", [#"app-key"=Stratus_App_Key,#"api-version"="1"], [Implementation="2.0"])
Above is the common structure for all of the Odata feeds and will only change by the last piece of the URL… namely “assemblies” as seen above. For instance, you can change that to read “fields” or “packages” and it will pull that respective data.
Now hit Enter or click out of the formula and hit the Edit Credentials button.
Choose Anonymous.
Click the dropdown under “Select which level to apply these settings to”.
Choose the root level (https://api.gtpstratus.com/ ).
Click the Connect button.
Repeat steps 2.a-2.d for each of the feeds that you want to connect to your Power BI. Remember, you can find the most recent list of these on our API website located at https://api.gtpstratus.com/.
You can also duplicate a query by right-clicking on it and choosing Duplicate. Then all you have to do is edit the formula and rename it.
Create relationships between the data tables based on the IDs and set the cardinality and directionality appropriately. This will be the most challenging part and is why we encourage you to only connect to the feeds you need. While it may be enticing to bring them all in, that will add complexity as you try to manage circular references in the model.
Create new columns or filters as needed to get the specific metrics from the field values.
Create visuals and reports in Power BI using the data and publish them to the GTP Team workspace.
Have fun!
5. Embed your Power BI into Stratus
After saving the Power BI and publishing it, we can embed these analytics and metrics directly into Stratus!
Publish by navigating to the Home tab of the Power BI and clicking the Publish button, being sure to publish it to the correct workspace in your organization.
Now open your Power Bi in the web browser to grab the Report and Tenant IDs.
Click File, then Embed report, and finally Website or portal.
Copy the entire embed link and paste it into something like Notepad. In this link, you can find the unique Report and Tenant IDs
Configuring Credentials in your Microsoft Tenant to auto-refresh the data (optional)
Go to your Power BI workspace (should be something like your company name)
Find the Semantic Model of your Power BI in the dashboard and click the schedule refresh button (looks like a calendar with a refresh symbol)
Under the Data source credentials, you will need to click on each of the Edit credentials buttons
On the dialog choose the Organizational level and the Skip test connection checkbox clicking the Sign in button (these are the settings that we use but your organization may want a specific level of authorization chosen.
Go back to Stratus and navigate to the Big Data tab of the Company Admin and go to the Analytics Reports sub-tab
Create a New Analytics Report and add your Report and Tenant IDs
Give your report a unique name as you want it seen in Stratus
Check the following boxes as needed:
Projects - Will show up as a new tab on the Projects hub in Stratus.
Models - Will show up as a new tab on the Models hub in Stratus.
Packages - Will show up as a new tab on the Packages hub in Stratus.
Show Filters - When checked, displays and enables the report and dashboard filters from your Power BI. When unchecked, the defaults will be applied as they were last configured in Power BI.
© Copyright 2022 GTP Services, LLC All rights reserved. | About | Contact