In the previous blog entry (see reference #1), I attempted to use a logic app to load the warehouse with satellite telemetry. If I had been willing to pay for a huge increase in service, I could have fired my logic application once hour (as I read the fine print). Conceptually, this could have worked. But since I don’t want to pay for that, I abandoned the logic application route.
The next step was to add a console application to run the same code the logic application called, but without the batch. As expected, it was painfully slow. So, I then created some T-SQL scripts to import the staging data and this worked out much better than I had thought it would. There is only one catch. My MSDN Azure credits have expired for this month, so I ran these on a local database. I believe I would have had similar results on my Azure SQL database, but I can’t definitively prove it and I want to move on to new stuff 🙂
After this was done, I ported the normalized tables into Power Bi Desktop and published two reports to Power BI. It ‘seems’ like powerbi.com is Microsoft’s preferred location for this type of display/analysis and I wanted to show one possible way the satellite data would be consumable.
The process is as follows:
- I took the JSON staging table and broke it into two separate columned tables
- I took these two tables and broke them into a more normalized view
- Reviewed the data for any issues
After the database portion was done, I created two Power BI Desktop reports and deployed them to powerbi.com. That process is as follows:
- Select Data Load Screen
- Select Server
- Select Data
- Once the data is loaded, the tables look like this in Power BI Desktop
- The same tables look like this in SQL Server Management Studio
- First report – SatellitePowerBiProject_Summary.pbix
- Second report – SatellitePowerBiProject_DetailViewBySatellite.pbix (with filter)
- The SQL is located here -https://github.com/ehelin/SatelliteLogicApi/tree/master/Sql
- It doesn’t look like you can do multiple reports in Power BI desktop, so that is why I created two reports. If it is possible and I didn’t see it, one Power BI Desktop file with multiple reports would be more ideal.