Microsoft Power BI is an interactive data visualization software with a primary focus on business intelligence. Combining this tool with Fusion Operations API service (documentation available here) allows you to get your data, build analytics and dashboards in Power BI.
In this article, we'll go over the steps needed.
Retrieving the data from Fusion Operations
1. Open a new Power BI, click (1) "Get Data", and (2) "Blank Query".
2. On the top menu, select (1) "Manage Parameters" and then create a new parameter.
Create the following parameters:
2.1. apiKey, with type set to "Text" and mandatory. The value for this parameter can be obtained from your Autodesk Fusion Operations profile (1).
2.2. apiSecret, with type set to "Text" and mandatory. The value for this parameter can also be obtained from your Autodesk Fusion Operations profile (2).
2.3. lastNDays, with type set to "Decimal Number" and mandatory. This parameter will determine how many days of data we will be retrieved from Fusion Operations. Beware that the larger this number, the longer the dashboard will take to refresh.
2.4. retryAfter, with type set to "Decimal Number" and mandatory. This parameter will be used as the buffer (in seconds) between consecutive requests to be made to Fusion Operations's API. Set it as 0.25.
All four parameters will be visible on the left side of the screen.
3. Select "New Source" and then "Blank Query".
4. On the left menu, double click the query (1) and rename it to "getProductionRecords", and click (2) "Advanced Editor".
5. Copy the text below and paste it to the Advanced Editor, to create a function in Power BI.
(page as number) =>
let
Source = Json.Document(Web.Contents("https://fusionoperations.autodesk.com/api/authorization",
[Headers=
[#"Content-Type"="application/json",
Authorization="Basic " & Binary.ToText(Text.ToBinary(apiKey & ":" & apiSecret), 0),
#"Accept"="*/*"],
Content = Json.FromValue([scopes = {"productions_write"},
random=Number.Random()])
])),
access_token= Record.ToTable(Source){2}[Value],
endpointUrl = "https://fusionoperations.autodesk.com/api/productions",
startTime =
Number.ToText(Date.Year(Date.AddDays(DateTime.FixedLocalNow(),-lastNDays))) & "-" &
Number.ToText(Date.Month(Date.AddDays(DateTime.FixedLocalNow(),-lastNDays))) & "-" &
Number.ToText(Date.Day(Date.AddDays(DateTime.FixedLocalNow(),-lastNDays))),
Origin = Json.Document(Web.Contents(endpointUrl,
[Query=[access_token = access_token,
#"start-time-after" = startTime,
#"worker-name" = "true",
page = Number.ToText(page)]]))
in
Origin
6. Repeat steps 3 and 4, but rename the new function "allProductionRecords". In case you are trying to get data via API besides production records, change the query name and the "endpointUrl" accordingly.
7. Repeat step 5, but copy and paste the content below instead.
let
Query = List.Generate(()=>
[Result = if List.IsEmpty(getProductionRecords(1)) then null else
getProductionRecords(1), page=1],
each [Result]<>null,
each [MidRes = Function.InvokeAfter(()=>getProductionRecords([page]+1),
#duration(0,0,0,retryAfter)),
Result = if List.IsEmpty(MidRes) then null else
MidRes,page=[page]+1],
each [Result])
in
Query
8. You should be good to go! A list with the data should now be returned by Fusion Operations.
Processing the data
1. To process the data (which is in JSON format), click (1) "To Table".
2. Select the options below, and press ok.
3. Click on (1) the diverging arrows icon to expand the lists and (2) "Expand to New Rows".
4. Click again on (1) the diverging arrows icon to expand the records, select (2) the information to be processed on Power BI, remove (3) the option "Use original column as prefix" and click (4) OK.
5. Click (1) on the diverging arrows for the columns that are relevant to be expanded, which will depend on the analysis to be performed.
In this case, we will expand the columns (i) operation, (ii) worker, (iii) quantity, (iv) work-time, (v) start-time, and (vi) end-time. To get a better understanding on what each column means, the option "Use original column as prefix" should now be active (check point 4).
6. For each relevant column, click (1) on the left side of the header, and set the data to the correct type.
In this example, operation.code and operation.product should be set to text, worker.number should be set to whole number, quantity should be set to decimal number, work-time should be set to text (we will have to process it), and start-time and end-time should be set to date/time/timezone.
7. As mentioned, the duration column should be processed, since Power BI can't parse "00H00m16s" to a duration field. Instead, we will convert the values in this column to minutes.
7.1. Click (1) Add Column, and (2) Custom Column.
7.2. In this custom column, name it (1) work-time-minutes and paste in box (2) the formula below. Click (3) OK to submit the formula and create the column.
Number.From(Text.Start([#"work-time"],Text.Length([#"work-time"])-7))*60 +
Number.From(Text.Middle([#"work-time"],Text.Length([#"work-time"])-6,2)) +
Number.From(Text.Middle([#"work-time"],Text.Length([#"work-time"])-3,2))/60
7.3. Save column "work-time-minutes" with the type "decimal number".
8. We will also add two new columns from fields start-time and end-time. These fields will be named start-day and end-day. These two columns will be useful to the final dashboard.
8.1. Select (1) the column, click (2) Date, and pick (3) Date Only.
8.2. Rename (1) the column "start-date".
8.3. Repeat the process for the end-time column. Name the final column "end-date".
9. Select (1) the Home tab, and click (2) Close & Apply.
Building a simple dashboard
1. On the following screen, select (1) a dashboard type. We'll select a clustered bar chart, but any other one can be selected. Then, expand (2) the productionRecordsList.
2. Drag (1) the data fields to the dashboard fields.
3. Tap (1) to customize the dashboard. These options will depend on the type of dashboard selected.
4. Add more data, visualizations and create your custom production dashboards!
Beware that creating the dashboard above required a substantially higher number of steps compared to the ones described in this document (as you can see on the right side of the image, under the Data menu).
Troubleshooting errors
When getting the data from Fusion Operations to Power BI, some errors may be returned. You can find a list below, and how to solve them:
1. Formula.Firewall: Query 'allProductionRecords' is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
To fix this error, follow the steps below:
1.1 Click "Data source settings"
1.2 Select (1) all Fusion Operations-related entries and click (2) "Edit Permissions"
1.3. Select the privacy level to "Public". Don't forget to save changes.
2. An error occurred in the ‘’ query. DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
Details:
DataSourceKind=Web
DataSourcePath=https://fusionoperations.autodesk.com/api/authorization
To fix this error, follow the steps below:
2.1 Click "Data source settings"
2.2 Select "https://fusionoperations.autodesk.com/api/authorization" and click "Edit Permissions"
2.3 Under "Credentials" select "Edit..."
2.4 On the left menu select (1) "Anonymous". Don't forget to (2) save changes.
If you still have some doubts, feel free to reach out in our live-chat feature.
Need help? Request a Fusion Operations expert to contact you here.