This is a Header Dimension Load Patterns with

This is a Header Dimension Load Patterns with Azure Data Factory Data Flows THIS IS A SUBTITLE Bob Rubocki – Practice Manager, BI Architect July 30, 2019

Agenda • ADF Data Flows background • Type 1, Type 2 review • This Demo is a Header THIS IS A SUBTITLE

Bob Rubocki • Practice Manager & BI Architect, Pragmatic Works This is a Header • brubocki@pragmaticworks. com THIS IS A SUBTITLE • linkedin. com/in/robertrubocki • @Bob. Rubocki • bobrubocki. wordpress. com

ADF Data Flows • AKA “Mapping Data Flows” • Visual data transformation tool • Create Data Flow, execute via Pipeline This is a Header • Preview feature THIS IS A SUBTITLE • ADF Data Flows <> Power BI dataflows

ADF Data Flows Transformations This is a Header THIS IS A SUBTITLE

Databricks Apache Spark Databricks This is a Header • Open Source • High performance analytics, THIS IS A streaming, SUBTITLE machine learning • Runs on a server cluster • Fully managed Spark clusters • Workspace tools, notebooks • Scheduling tool Azure Databricks • Azure Infrastructure • Azure service integration • Available in AWS

Dimension Load Patterns Type 1 • Current dimension values only, no history tracked • Insert new items • Update existing items if changed Type 2 This is a Header • Historical values maintained in separate records. Each item may have more than one record. THIS ISnew A SUBTITLE • Insert items • Changed Items • Insert new record with current values of existing items • Update previous record with expiration date

Dimension Examples This is a Header Type 1 THIS IS A SUBTITLE Type 2

Demo Architecture and Scenario • Source data has been extracted, loaded to ADLS Gen 2 • Star schema DW in Azure SQL DB • Two demos = two dimension tables, same structure • IDENTITY not used – need to generate surrogate key This is a Header THIS IS A SUBTITLE

DEMO This is a Header THIS IS A SUBTITLE

Questions? This is a Header THIS IS A SUBTITLE