Cloud Adventures Part 4 – Creating Consumable Data from Staging

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 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
    • ClientUpdate.sql
    • StatusUpdate.sql
  • I took these two tables and broke them into a more normalized view
    • WarehouseLoads.sql
  • Reviewed the data for any issues
    • ViewQueries.sql


After the database portion was done, I created two Power BI Desktop reports and deployed them to  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 -
  • 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.

Stay tuned!



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s