Microsoft Power BI integration

Microsoft Power BI Desktop is a free data analysis and visualization tool that provides huge flexibility in processing data from various sources. You can use Power BI to perform analysis on data returned by 1E Platform instructions. This page discusses how to take the results from an instruction and bring them into Power BI.

Downloading Microsoft Power BI Desktop

You can download the Power BI Desktop application from Microsoft's site. When you start Power BI, you will be prompted to get data for your report.

Creating data for use with a report

We will invoke an instruction which returns memory details for each device, as shown below. Because this is an aggregating instruction, we specify the -Drilldown parameter to retrieve detailed per-device memory information. Our target scope is defined as our lab domain which in this scenario returns data for three devices.

Copy 
Invoke-1EInstruction 1E-Explorer-TachyonCore-GetCurrentInstalledMemoryDetails -TargetScope urth -Drilldown -Csv out.csv

This will create a CSV file as output, the contents of which look like this.

Visualizing data in Microsoft Power BI

  1. Open Power BI and create a new data source using a CSV file that contains your data.

  2. When you have browsed to the file and pressed Load, you will be taken back to the visualization screen. Click Transform Data on the ribbon.

    The data from the CSV file will appear in the Query Editor window.

    If the column headers were not automatically chosen as the column names, you can force this by clicking Use First Row as Headers in the ribbon.

  3. Click Apply and Close to return to the visualization screen.

  4. On the right-hand side of the visualization screen, you will see a Fields pane containing the data source. In the screenshot below, the data source is called out. Click each field in the data source to display the fields as columns in the table on the left-hand side of the screen.

  5. In the Visualizations pane, click the chart type called Clustered column chart. Your data will now be visualized.

Refreshing the data

To refresh the data from the file source, click Refresh in the ribbon. If you had re-rerun the instruction using the PowerShell invoke-tachyoninstruction cmdlet, then the refreshed data would now be displayed.

Modifying the data source

To modify the data source, click Transform Data to re-open the Query Editor window. Then in the Query Settings pane on the right-hand side of the screen, click Source in the Applied Steps section. This will allow you to modify the data source file.

Creating a report with multiple data sources

You can create more data sources associated with the results of other instructions, for example. You can do this by clicking New Source from the ribbon in the Query Editor window and defining the additional sources.

Normally, you would set up the 1E Platform instructions to send data to uniquely named CSV files, corresponding to the data sources that you have set up. Then you can visualize each of these sources.

Power BI is an enormously sophisticated product, and we have just scratched the surface here. One interesting possibility is to use Power BI to join multiple data sources together. This allows you to explore data patterns such as correlations across multiple data sources and platform instructions.

Another possibility is to set up a Windows scheduled task that runs cmdlets to retrieve platform instructions at regular intervals, thus automatically providing fresh data to your reports.