Continuing our series of tutorials on SaaS application patterns with SQL Database, we are delighted to announce an additional cross tenant analytics tutorial. This new tutorial shows how to extract and load tenant data into Azure SQL Data Warehouse (SQL DW) using Azure Data Factory (ADF) and then analyze it in Power BI.
In this tutorial, ADF is used to orchestrate data movement from tenant databases into a SQL Data Warehouse. Parameterized ADF V2 (preview) pipelines are defined to iterate across tenant databases, loading data from multiple databases in parallel. To accelerate loading, ADF stages extracted data in blob files and then uses PolyBase to load into SQL DW. Staging the data and enabling PolyBase are simple check-box operations in ADF.
The tutorial uses an Extract, Load and Transform (ELT) pattern – once data is loaded into staging tables in SQL DW, ADF invokes a stored procedure to upsert the data into star-schema tables, ready for query. Power BI is then used to visualize the data and extract insights that in the tutorial scenario can help the ISV improve their ticket selling application and business.