Microsoft Power BI for UltraTimeSeries Reporting

What is Power BI

Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights.

Connection to UltraTimeSeries

The steps for the connection to UltraTimeSeries through requests to our API:

1. Home - “Get data”

2. “Blank Query”

3. “Advanced Editor”

4. URL (Address to UltraTimeSeries API)

• Source: Type of connection and definition of connection Headers if needed

• Convert to Table: Convert the result of the query to the table

• Changed Type: Define the type of columns

Get Actors
let
Url = "https://virtual-factory.cloud.ultratimeseries.com/api/actor",
Source = Json.Document(Web.Contents(Url,[
Headers = [#"Authorization" = Token,
#"Content-Type"="application/json"]
]
)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "created"}, {"key", "created"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"key", type text}, {"created", type datetime}})
in
#"Changed Type"
------------------------------------------------------------------------------
Machine1 Production

let
Url = "https://virtual-factory.cloud.ultratimeseries.com/api/messages?subject=Machine1&action=Production",
Source = Json.Document(Web.Contents(Url,[
Headers = [#"Authorization" = Token,
#"Content-Type"="application/json"]
]
)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"date", "value"}, {"date", "value"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"value", Int64.Type}, {"date", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Subject", each "Machine1"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Subject", "date", "value"}),
#"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Action", each "Production"),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Subject", "Action", "value", "date"}),
#"Inserted Parsed Date" = Table.AddColumn(#"Reordered Columns1", "Parse", each Date.From(DateTimeZone.From([date])), type date),
#"Inserted Time" = Table.AddColumn(#"Inserted Parsed Date", "Time", each DateTime.Time([date]), type time)

in
#"Inserted Time"

5. It is possible to use Power Query Editor to transform or add columns

6. Result of the created query

Create an auxiliary table with dates

We need to create an auxiliary table with dates to connect with each table and use only one timeline to choose a timeframe.

Create a table

1. Separator “Data”

2. “New table”

3. In Textbox insert this code:

Date =
ADDCOLUMNS (
CALENDAR (DATE(2022,4,1), DATE(2022,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] )
)

• In this case, the table will be populated with dates between April 1, 2022, to December 12, 2022.

Connection between tables

Dashboards

Create graph

• Chose the type of graph (on the three dots is possible to add more visuals)

• Chose Tables created in previous steps

• Chose fields to use in the graph. In this case, we use three tables: Date (with dates to choose timeline), Machine1_Production (get parts produced by day), and Machine1_Scrap (number of scrap parts by day)

• Graph Settings

• Final Result

• Other Graphs