Get your data flowing with Data Flows and

Get your data flowing with Data Flows! and. . . umm. . . dataflows. Nicholas Schafer Learner of Business Intelligence https: //www. linkedin. com/in/nicholasschafer/

A famous quote “Microsoft recently launched Data Flows in Azure Data Factory (ADF) to move the service beyond the pure orchestration space into the world of extract, transform and load (ETL). At roughly the same time, Microsoft also introduced dataflows into the Power BI service world, allowing users to leverage common query models across an enterprise. So, what do we do with these new offerings? What are they, really? Is Microsoft just trying to confuse us? This session untangles and explores ADF Data Flows and Power BI dataflows to help you to get a better understanding of how you may be able to apply them in your world. ” -Nicholas Schafer

A confession(s) • Earlier this week, I was not very prepared because, well…reasons. Now it’s a bit better. • I stole content from a presentation by Miguel Llopis and Matthew Roche from Ignite on Power BI dataflows (Ignite 2018 presentation titled Microsoft Power BI: Unify all your data and deliver powerful insights with selfservice data prep capabilities for big data - BRK 2061) • I stole content from a presentation by Mark Kromer from SQL Saturday in Redmond on Azure Data Factor Data Flows (https: //www. slideshare. net/kromerm/azure-data-factory-data-flow) • I am learning about Data Flows and dataflows – I am not an expert. : )

I have an agenda • Overview | Data Flows and dataflows • Azure Data Factory | Data Flows • Power BI | dataflows • Conclusion | Data Flows and dataflows

Overview | Data Flows and dataflows

Modern Business Intelligence (BI) Challenge Fragmented, incomplete data Requires a team of specialists Pulling together data from traditional and cloud data sources and figuring out how to enrich it is extremely difficult. Creating E 2 E BI solutions requires multiple BI tools. This requires specific knowledge of each of the tools and complex integration to build and maintain an E 2 E BI solution. Business data has no structural or semantic consistency Complex system integration Different applications, departments, and analysts define data in different ways, which makes data exploration, and reuse of data and apps extremely challenging. Source: Ignite 2018 presentation titled Microsoft Power BI: Unify all your data and deliver powerful insights with self-service data prep capabilities for big data - BRK 2061 Traditional BI solutions span multiple applications and services. Sharing data across systems requires each system to understand the location, structure and meaning of the data.

Title

Azure Data Factory | Data Flows

What is Azure Data Factory (ADF)?

How do Data Flows fit into ADF? Data Loading Azure Data Factory r Load files into data lake on a schedule Ingest storage Azure Storage/ Data Lake Store Serving storage Data Flow Data Transformation Extract and transform relational data Azure Data Factory Applications Load processed data into tables optimized for analytics Azure SQL DW Clean and join disparate data Azure Databricks Dashboards Scheduled & orchestrated by ADF Business/custom apps (structured) Source: https: //www. slideshare. net/kromerm/azure-data-factory-data-flow

A simpler explanation

So, what’s so cool about it? It’s a visual t. Source: https: //www. slideshare. net/kromerm/azure-data-factory-data-flow

Code free transformations at scale Does not require understanding of Spark, Big Data Execution Engines, Clusters, Scala, Python …so you can focus on building business logic and data transformation What can you do code free? • • • Data cleansing Aggregation Data conversions Data prep Data exploration Source: https: //www. slideshare. net/kromerm/azure-data-factory-data-flow

Build your logical data flows adding data transformations in a guided experience Source: https: //www. slideshare. net/kromerm/azure-data-factory-data-flow

Debug mode provides row-level context and visible results in inspector pane Source: https: //www. slideshare. net/kromerm/azure-data-factory-data-flow

Debug Data Flows with Data Preview and Data Sampling with Inspect Pane Source: https: //www. slideshare. net/kromerm/azure-data-factory-data-flow

Deep Monitoring Introspection of Data Transformations Source: https: //www. slideshare. net/kromerm/azure-data-factory-data-flow

Microsoft Azure Data Factory Continues to Extend Data Flow Library with a Rich Set of Transformations and Expression Functions Source: https: //www. slideshare. net/kromerm/azure-data-factory-data-flow

Interactive Expression Builder – Build data transform expressions, not Spark code Source: https: //www. slideshare. net/kromerm/azure-data-factory-data-flow

Let’s have a look

Power BI | dataflows

What is Power BI?

What is Power BI? Power BI Desktop Get Data Build Reports Power BI Data Sources Data Set Adding Visuals Power BI Service Collaborate Power BI Workspaces Power BI Service Share Power BI Apps Data Set Reports Dashboards Worksheets

What is Power BI? Source data Power BI Desktop Get Data Build Reports Power BI Data Sources Adding Visuals Power BI Service Collaborate Power BI Workspaces Transformed to tables Data Set Power BI Service Share Power BI Apps Assembled into data model Data Set Reports Dashboards Worksheets

What is a Power BI dataflow? Self service customizations in Power BI Dataflow Dynamics 365 for Finance & Operations Dynamics 365 data Azure Data Factory Azure Databricks Azure Data Lake Storage Gen 2 CDM folder Azure SQL DW Azure ML

A simpler explanation

What is Power Query With Power Query, you can: 1. Connect to data to create a new Query 2. Create multiple queries from multiple data sources to support your data model 3. Preview your data and write formulas in the formula bar to transform it 4. Modify the query properties and apply transformation steps https: //docs. microsoft. com/en-us/power-bi/desktop-query-overview

What is the Common Data Model? The Common Data Model is a standardized, modular, extensible collection of data schemas published by Microsoft that are designed to make it easier for you to build, use, and analyze data. Power BI Dataflows allows you to ingest data into the Common Data Model form from a variety of sources such as Dynamics 365, Salesforce, Azure SQL Database, Excel, or Share. Point. Once you've connected and prepared your data, you can choose to map it to a Common Data Model standard entity or load it as a custom entity in Common Data Model form in Azure Data Lake Storage Gen 2 helps speed your transition from proof of concept to production by combining the power of a file system that's compatible with Hadoop, an integrated hierarchical namespace, and the massive scale and economy of Azure Blob Storage. The Common Data Model brings semantic consistency to data within the lake so that applications and services can interoperate more easily when data is stored in Common Data Model form. Common Data Service, which supports Dynamics and Power. Apps, stores data in conformance with the Common Data Model definition. In fact, many of the original business entities in the Common Data Model came from Dynamics offerings, such as Dynamics 365 for Sales and Dynamics 365 for Marketing. Industries such as healthcare working closely with Microsoft to extend the Common Data Model to their specific business concepts, such as Patient and Care Plan. This extends the benefit of the Common Data Model standard entities to these verticals so that industry solutions interoperate more easily.

What is the Common Data Model

Create and use dataflows There are three primary steps to using a dataflow: 1. Author the dataflow, using Microsoft tools that are designed to make doing so straightforward 2. Schedule the refresh frequency of the data you want to bring into your dataflow 3. Build the dataset using your dataflow, using Power BI Desktop

Data Sources

Connecting to dataflows in Power BI Desktop

Let’s have a look

Conclusion | Data Flows and dataflows

Conclusion

Resources Azure Data Factory Data Flows • Microsoft Docs Mapping Data Flows Concepts (https: //docs. microsoft. com/en-us/azure/data-factory/concepts -data-flow-overview) • Microsoft Docs Create data flow Quickstart (https: //docs. microsoft. com/en-us/azure/data-factory/data-flowcreate) • Azure Data Factor Data Flows - Mark Kromer from SQL Saturday in Redmond on (https: //www. slideshare. net/kromerm/azure-data-factory-data-flow) Power BI dataflows • Microsoft Docs Power BI Self-Service dataflows (https: //docs. microsoft. com/en-us/power-bi/service-dataflows -overview) • Microsoft Power BI: Unify all your data and deliver powerful insights with self-service data prep capabilities for big data - BRK 2061 Ignite 2018 presentation by Miguel Llopis and Matthew Roche Sample Data • Kaggle Lego Database (https: //www. kaggle. com/rtatman/lego-database)