1

I have the following scenario that I am thinking of implementing via Azure Stream Analytics.

My input consists of:

  1. Events streaming in from an Azure Event Hub.
  2. Reference data that relates to the events. Some of this data is "slowly changing" from day to day.

I need to join the events and the reference data, process them and output tables that will make up a "data warehouse" (with Power BI in mind as the consumer).

The output would be made up of:

  1. A facts table where the most important events are stored.
  2. A few dimension tables that hold the values that make up the facts.

Is Azure Stream Analytics suitable for this kind of work? It seems to me that ASA is well suited to persisting the events from the event hub stream and into a facts table. However, the additional work of keeping the dimensions tables up to date - i.e. adding new values periodically - is not a good fit.

Am I correct in this analysis? Should I switch over to Azure Data Factory for my project?

Dan Ciborowski - MSFT
  • 6,807
  • 10
  • 53
  • 88
urig
  • 16,016
  • 26
  • 115
  • 184
  • 1
    Stream Analytics is for *analysis* of real-time data, *not* ETL. – Panagiotis Kanavos Feb 10 '16 at 15:52
  • Thanks @PanagiotisKanavos :). Care to elaborate what the critical difference between analysis and transformation is in my scenario? – urig Feb 10 '16 at 16:40
  • 1
    Just as with any DQ, star schema, reporting db, analysis is what tells you how many Xs you have. ETL is what loads the data to the schema. In this particular case, analysis will tell you that you how many events you had in a window, or detect anomalous activities in that window. While you *coiuld* use a Analysis SQL for transformation, it's very cumbersome (steps are CTEs) and limited (max 6) and expensive. ETL requires much more. – Panagiotis Kanavos Feb 10 '16 at 16:55
  • 1
    Urig, can you share a hypothetical but more concrete scenario that reflects your needs? You are right that ASA is well suited for persisting events from a stream after applying few transformations or aggregations. You might be able to use a combination of ASA and ADF, or just ASA alone in some cases for keeping the dimension tables upto date. – Vignesh Chandramohan Feb 11 '16 at 22:52
  • Tx @PanagiotisKanavos. If you care to upgrade your comment to an answer, I'd be glad to accept it. – urig Mar 07 '16 at 13:30

1 Answers1

0

Is Azure Stream Analytics suitable for this kind of work? not really.Stream Analytics is made for Real-time stream processing in the cloud.

As you pointed out, the additional work of keeping the dimensions tables up to date - i.e. adding new values periodically - is not a suitable job for ASA (Azure Stream Analytics)

For this part/functionality you can use Azure Data Factory (ADF), it is a cloud-based data integration service that orchestrates and automates the movement and transformation of data. which is what you require here.

Also, Azure HDInsight Hadoop-based enables ETL scenarios, you can check this example of "ETL Workflow for the racecar telemetry data" over here https://msdn.microsoft.com/en-us/library/dn749886.aspx

Rami Sarieddine
  • 5,396
  • 35
  • 41