STRATUS API.

Best Practices

We actively monitor API usage for excessive errors and/or API abuse. Here are a few tips to becoming a "good citizen":

• Avoid high frequency polling. Polling too often can overwhelm our servers.
• Consider taking advantage of "include" and/or "where" filters to reduce payload sizes.
• Handle exceptions to reduce server load. Consider retry logic but set a retry limit after 3 failures. If exceptions continue, contact support, don't keep hammering the API.
  • We recommend using: GitHub - App-vNext/Polly: Polly is a .NET resilience and transient-fault-handling library that allows developers to express policies such as Retry, Circuit Breaker, Timeout, Bulkhead Isolation, and Fallback in a fluent and thread-safe manner. From version 6.0.1, Polly targets .NET Standard 1.1 and 2.0+.
• Some of our API's have throttle limits. If this scenario is encountered, the API will return a 429 (too many requests) response code along with an expiration in seconds. Your code should be able to handle this expiration accordingly and wait before retrying.
• When paging, handle page size changes dynamically. From time-to-time (not often), we change page size limits to conserve load on our servers. Your code should be able to flex dynamically to handle these scenarios.
  • To improve performance, and you don't need the 'total' property, consider using the 'disabletotal' query arg.
  • AVOID PARALLEL PROCESSING.

Getting Started

Generate an App Key

In STRATUS, navigate to Admin > Company > App Keys and generate an App Key:

Example 1: Using C# to get Cost Types

Example 2: Using C# to get Paged Assemblies

Example 3: Using C# to get Paged Activities (disabling total for better performance)

Example 4: Using Postman to get Cost Types

Download Postman here.

Example 5: Using Insomnia to get Cost Types

Download Insomnia here.

Power BI Connected to the STRATUS Open API

Summary

A Power BI report can be directly connected to the STRATUS Open API utilizing company queries available to all customers without requiring custom programming. In a few places, users will need to fill in the unique company id or app key, but this is feasible for all customers.  

In this example, complete instructions are provided to build the Power BI report Total Feet by Completed Date from scratch to show cut lists processed in the last year, broken down by material, size and station.  The report will get data directly from STRATUS via the Open API using predefined company queries available to all STRATUS customers. Alternatively to building the report from scratch, users can download the finished report file (.pbix) and then consult the step-by-step instructions to tweak the report. See the Use Existing Report Steps for more information.

Below is an example of the final report.

Prerequisites

• Create a STRATUS App Key via STRATUS > Admin > Company > App Keys
• Install Power BI Desktop to author the Power BI report file
• Configure a Power BI Workspace to publish reports to the cloud for sharing
• Purchase Power BI Pro licenses for Microsoft Office 365 user accounts in order to share Power BI report results via Microsoft Teams

Use Existing Report Steps

The following steps can be used to build the Power BI Report Total Feet by Completed Date using an existing file created by Jonathan at GTP.

1. Download the STRATUS Cut List Power BI report .pbix file.
2. Open the file. You will get some warnings about credentials.  Modify each of the three queries to fill in your unique App Key and Company ID and then it will run for you. 
   Here’s how:
   1. From the Data panel at the far right, click the … menu for Cut Lists and choose the ‘Edit query’ option.  This will open Power Query Editor.
   2. In Power Query Editor, click the first ‘Source’ entry in the APPLIED STEPS list on the right.
   3. Then, click the little down arrow to expand the text edit window, so you can see the entire query with placeholders for FILL-IN-COMPANY-ID and FILL-IN-APP-KEY.
   4. You need to replace these for each of the three queries.  You click click through each of them on the left side by selecting Cut Lists, Stations, and Tools.
   5. Once you’ve updated each, click the disk save button, close out of the editor and choose to Apply your changes to the report.
3. If you complete these steps and save the report, you will end up in the same place as running through the entire tutorial document below.
4. Please submit a STRATUS Service Desk request if you need to know your Company Id.

Steps to Create the Report from Scratch

Create Power BI Report and Get Data

1. Open Power BI Desktop
2. Click “Get Data”
   
3. Click “Connect”
4. Choose “Advanced” and fill in the URL parts as: https://api.gtpstratus.com/v1/company/run-query?queryId=1c578a52-9145-4b42-8fb3-fcdca971be14&companyId=FILL-IN-COMPANY-ID
   1. Set the command timeout to a reasonable value like 5 minutes.
   2. Enter app-key as a header parameter and fill in your app key id value from https://www.gtpstratus.com/companyadmin#tab_company-appkeys
   3. NOTE:  Partner App should be empty for App Keys used here. 
   4. NOTE: email me to request your company id (it is temporary, as we plan to remove the argument since it is redundant and provided by the app-key)
   5. NOTE: the queryId value 1c578a52-9145-4b42-8fb3-fcdca971be14 corresponds to the Admin Company Query named Cut Lists In Last Year
      You can locate the queryId value for each available query in this way…
      1. Open https://www.gtpstratus.com/companyadmin#tab_queries
      2. Press F12 to open the developer tools in the web browser
      3. Click on the Network tab and then reload the webpage
      4. Within the Network tab, search the Name column for get-queries and select it, then click the Response tab, and finally the pretty print { } button
         
         
   6. After clicking OK on the dialog and the web request succeeds, this query editor will display with the raw results.

Transform Query results into Data

1. Rename the Query in the right side Query Settings under Properties Name:
   
2. Notice the APPLIED STEPS in the same panel, these are a series of sequential steps that transform the query results into a usable data table, so we need to add steps to cleanup the results
   1. Click Split Column by Delimiter, so we can break the content into multiple rows at each carriage return
      
      
   2. So it now looks like this:
      
   3. Then we need to get rid of extra blank characters at the beginning and end of each line, so right click on the content column heading and choose the Transform Trim option as shown here:
      
   4. Resulting in this now:
      
   5. Now, if you scroll to the very bottom of the list, you’ll see a blank row which needs to be removed, so click the toolbar button “Remove Rows” which flies out and choose the “Remove Bottom Rows” option, enter 1 and click OK
      
   6. Now we’re ready to split each row into columns, using the Split Column by Delimeter again, but with different options specified:
      
   7. Resulting in something like this:
      
   8. Which is starting to look better.
   9. Now we need to make the first row be the column headings, by clicking the “Use First Row as Headers” button:
      
   10. Let’s review each column and make any changes to data types per column.  For example, I don’t care about the actual time, only the date for each entry, so I’m going to change the format of the CreatedDT column by right clicking the heading and choosing Change Type to Date.  If it prompts you, choose “Add new step” for the conversion.
   11. Next, I scrolled over and noticed the last few columns have data that looks all messed up and has some null values, like this:
       
   12. And I realize looking back at the Name column, we have some comma separators in there, so the general rule of splitting the column at all instances of commas didn’t work right… so I’m going to take a step back and rework that step… which I can do by clicking the second “Split Column by Delimiter” in the APPLIED STEPS sequence:
       
   13. After clicking it, only the steps to that point are applied to the output table results and we can right click the Split Column step and choose “Edit Settings”, where instead of using the “Each occurrence of the delimiter” option, let’s change to the “Right-most delimiter” and see what that does, click OK
   14. I see it pulled off only one column at the right, but the values look good now, so let’s do the same again, click Split Column by Delimiter and lets add another of the same, splitting off one column from the right at a time
   15. You will see this dialog, which is fine, click Insert
       
   16. Choose Comma at Right-most delimiter again and click OK
       
   17. Repeat this, splitting one right-most column at a time until you get all the columns created
   18. You may need to split left-most column if there’s a column that includes commas in the values
   19. Once you have each column separated and the data looks good, you can click to the next step in the APPLIED STEPS and if you get one that no longer works, just delete it, like this, clicking the little X icon on the step and then confirming Delete it
       
   20. Finally, after clicking through the sequence of the APPLIED STEPS, if you can click down to the last one and all the data looks good, you are done!
   21. (Note: I found I also needed to convert the last CompletedDT column to Date-Time and then another step to Date only)
   22. You have transformed the data into something now usable in Power BI.
   23. Click the little Save button in the upper left of the Power Query Editor
       
   24. And then choose “Apply” in the dialog.
       
   25. Then you can click the X in the upper right corner and you may be prompted again, choose Yes to apply.
       
   26. You should land back in the Power BI Desktop report editor and see your “Cut Lists” on the Data panel.
       

Add Stations and Tools

1. Repeat the previous step, but this time choosing the Stations query id.
2. Repeat the previous step with the Tools query id.
3. Resulting in 3 Queries now and 3 sets of Data:
   

Link data tables together by Id

1. Click the Model view button
   
2. Now we need to relate the cut list’s StationId to the Station’s Id and the Station’s ToolId to the Tool’s Id, to link the data together
3. Before completing this, your expanded model view will look like this:
   
4. So let’s link the properties together now…
5. Click “StationId” in the Cut Lists model, then click and drag it over and drop onto the Station’s Id property.
6. Then do the same for the tool relationship by selecting Station’s ToolId property and then clicking and dragging it over and dropping it on the Tool’s Id property.
7. Once these relationships are created, the model view will look like this:
   
8. Showing there is a 1 to many relationship… meaning 1 station may have many cut lists.
9. Building these relationships allows the report to understand the links between the data returned from STRATUS API calls.

Add By Material Data Table

1. Now we can click back to the Report view.
   
2. Notice we have “Page 1” as our only tab at the bottom.  We can create a report with multiple pages, but for now, let’s just rename this page to “Cut Lists” by right clicking on the the Page 1 tab and choose rename, then type in the new name and press enter.
3. It’s time to add our first visual! Let’s click to add a Matrix to the report view
   
4. After clicking, the matrix will appear on the left and we can move and resize it as we like
   
5. Now, we are ready to wire it up to some data!
6. With the matrix visual selected, expand the Data sets in the far right Data bar and let’s select the Cut Lists : Material property and drag it onto the Visualizations Rows specification, where it says “Add data fields here”.
7. Then do the same with Cut Lists : Size property… so you’ll have Material then Size for the Rows, like this:
   
8. On the left in the matrix, you’ll now see something like this, where you can click to expand a material and see sizes:
   
9. Next we want to add columns for number of items cut and total length, so we will drag and drop those two properties into the “Values” section in the Visualizations panel, like this:
   
10. But this doesn’t look right… it thinks the items and total length values are text, not numeric values… so we need to go change the data type for each of these, so we can get a sum of their values.
11. So click over to the “Data view”
    
12. Then select the Cut Lists : Items column heading and then from the ribbon menu in “Column tools” at the top, change “Data type” from Text to “Whole number” and accept the message box that comes up.
13. While in here, let’s change the “Completed” column data type to “True/false” and the TotalLengthInInches column data type to “Decimal number”
14. In the case of the TotalLengthInInches column, we can also change the Format to Decimal number with 2 decimal places, instead of Auto
    
15. So now switch back to the report view, remove the existing Items and TotalLength properties from the “Values” section and then add them back again. Should look like this now:
    
16. Notice they say “Sum of Items” and “Sum of TotalLength”, which indicates they are recognized as numeric values.
17. Also notice in the data matrix view, they appear to be formatted and provide a Total at the bottom of the data table
    
18. I decided I also want to know how many cut lists of each material and size were processed, so to get this is kind of a trick… drag the Cut Lists : Id property into the first position in the Values section and then right click on the new entry and choose “Count (Distinct)”
    
19. Now I want to change the column headings for each of these value columns. This is easily accomplished by right clicking on each of the items in the Values section and then choosing to “Rename for this visual” and then after renaming each, we have:
    
20. I really would prefer Total Feet… so I need to add a new calculated column now. Click the … menu to the right of the Cut Lists entry in the Data panel and then click “New column”
    
21. This will start a formula expression above the report, change it to match this…
    
22. And then drag and drop the new TotalLengthInFeet property into the Values section and rename it to just “Total Feet” to look something like this now:
    
23. And then you can remove the Total Inches column by pressing the little X next to it
    

Add By Station Data Table

1. Now I want to see the number of cut lists each station has processed, so let’s add a plain Table to the report. Start by clicking in white space to deselect the existing Matrix.  Then click the Table visual to add one to the report.
   
2. Now, the first column to add is the Stations : Name, so with the Table highlighted, locate and click the Stations Data set and click the Name property to add it as a new column.
3. The result will look something like this:
   
4. Next, we want a count of Cut Lists for each Station, so we need to use the same trick from earlier and choose the Cut Lists : Id property and then use the Count (Distinct) option to get this:
   
5. Now we can rename it from Count of Id to Cut Lists

Add Graph!

1. I’d really like to see a plot of total feet cut by date. So let’s deselect everything in the report by clicking in white space again.  Then click to add a “Line Chart” visual.
   
2. Assign the Cut Lists : CompletedDT to the X-axis, but we need to change it from a Date Hierarchy to just a regular date, like this:
   
3. Then drag the Cut Lists : TotalLengthInFeet property into the Y-axis, making sure it is configured as Sum, but rename it to just “Total Feet”
4. Also rename CompletedDT to “Completed Date”
5. My data is from a test database and is not great, but I think I got the graph setup correctly
   
6. One of the really cool interactive features of Power BI is how it natively understands selections as filters. For example, when I click on Carbon Steel in the Materials list, it immediately filters the Stations and completed date graph down to just Carbon Steel content, like so:
   
7. Save your report as a *.pbix file.

Publish (optional)

1. If you have configured a Power BI workspace online, then you can publish your report (pbix file) by clicking the File menu and then Publish, and then Publish to Power BI
   

Configure Data Refresh (optional)

1. Once published, you can go to the Workspace at app.powerbi.com and choose Data hub
   
2. Then click on the Dataset that corresponds to your new Cut Lists report
3. Once selected, choose the Refresh drop down and “Schedule refresh”
   
4. From there, you can scroll down to “Scheduled refresh” and update the refresh frequency.

Add to Teams (optional)

1. In Microsoft Teams, from within any Team, at the top of the page click the + button to add a new tab to the view. From the “Add a tab” dialog, search for and select “Power BI”
   
2. From the Power BI dialog, browse your Workspaces to choose the report and then modify the “Tab name” below and click Save.
3. The Power BI report results will be updated and displayed for any Team user who has access to the Workspace and also a Power BI Pro license to view reports.