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 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
    • 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

ViewQueries

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

LoadData

  • Select Server

SpecifiyServer

  •  Select Data

SelectData

  • Once the data is loaded, the tables look like this in Power BI Desktop

TableRelationshipsPowerBi

  • The same tables look like this in SQL Server Management Studio

TableRelationshipsSSMS

  • First report – SatellitePowerBiProject_Summary.pbix

PowerBiSummary

  • Second report – SatellitePowerBiProject_DetailViewBySatellite.pbix (with filter)

PowerBiDetail

PowerBiDetailFilters

NOTE:

  • 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.

Stay tuned!

References

  1. https://erichelin.wordpress.com/2016/04/18/cloud-adventures-part-3-staging-data-preparation-logic-app-with-web-api/
  2. https://powerbi.microsoft.com/en-us/documentation/powerbi-service-get-started/
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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