Integrating BMC Discovery with Microsoft Power BI – a video walkthrough showing how to overcome the challenges.
Have you ever thought of showing BMC Discovery data in your BI?
The organisation we were working with implemented Microsoft Power BI and built several dashboards to provide the management team with up-to-date status reports about their various ITSM and ITOM projects. The dashboards provide management with an easy method to view the progress and overall success of their projects.
To populate these reports with discovery data required the following steps:
- Create a query to extract the data from BMC Discovery
- Add a query to a cronjob to provide regular extracts into a CSV file
- Copy the CSV file from the BMC Discovery Appliance to Microsoft SharePoint
- Integrate Microsoft Power BI with Microsoft SharePoint to import the CSV files
- Consume the discovery data within reports
To reduce the number of steps and multiple points of failure we developed a direct integration between Microsoft Power BI and BMC Discovery.
The challenge we encountered was less about the actual connection but around how to handle the format of JSON data.
The JSON data received from the BMC Discovery API does not provide “attribute : value” which would have been easily parsed within Power BI in a single step. Instead, the JSON data provides the headings at the top and then the values underneath, which when pulled into Power BI creates two sets of results – one for the headings and another for the results.
After some head-scratching, we finally arrived at the solution whereby we process each set of results and merge/append the results.
As they say, a picture is worth a thousand words, so we have put together the following video to walk you through the integration and how to handle the resulting queries/results.