Power BI dataflows 201 - Beyond the basics

1. Power BI dataflows 201 - Beyond the basics Matthew Roche Senior Program Manager Microsoft @SQLAllFather https://ssbipolar.com

2. Introducing Power BI dataflows, three ways

3. Power BI dataflows are part of the evolution of BI Self-Service BI Data Warehouse Reports and dashboards OLTP systems Data Lake OLAP / Analytics Models Data Preparation / ETL

4. Power BI dataflows are part of the evolution of BI Self-Service BI Data Warehouse Reports and dashboards OLTP systems Data Lake OLAP / Analytics Models Data Preparation / ETL

5. Power BI dataflows are another object/artifact type Reports & dashboards Datasets Dataflows Azure Data Lake Storage Gen2 CDM folder CDM folder CDM folder Business analysts Low/no code

6. Power BI dataflows are like Excel Sources 20 Ingest from Dynamics Sales 22 entities Clean and enrich sales data 10 entities 8 CRM – Production Dynamics 365 10 Final Business View 11 entities 4 1 1 Product Telemetry in Azure 5 entities Add Telemetry Customer Attributes 6 entities IoT Signal Azure Data Lake Storage 4 1 Product Usage Dataset Sales/Telemetry Reference Data External Dataflow

7. Demo: Power BI dataflows end to end

8. Self-Service BI Data Warehouse Reports and dashboards OLTP systems Data Lake OLAP / Analytics Models Data Preparation / ETL

9. Integrating Power BI dataflowswith Azure Data Lake Storage gen2

10. Using an organizational ADLSg2 resource with Power BI Reports & dashboards Azure SQL DW Azure Data Factory Azure Databricks Azure ML Datasets Dataflows Azure Data Lake Storage Gen2 CDM folder CDM folder CDM folder Data scientists Data engineers Low to high code Business analysts Low/no code

11. BYOSA E2E – 3 steps In Power BI Done Assign workspace to ADLS account Create and refresh dataflow Enable people to use it Create and configure Storage account Attach storage account to Power BI Create v2 workspace GlobalAdmin Done Workspace Admins In Azure Attach to CDM folder from “Azure Data services" Find dataflow CDM folder storage location Get authorized to storage location Done Developers and Data scientists

12. Bring your own storage account Use your organizational ADLSg2 resource and attach it to Power BI A Global Administrator account and an Azure subscription is required Once a dataflow storage location is configured, it cannot be changed By default, only owners of a dataflow stored in Azure Data Lake Storage Gen2 can access its data To authorize additional users to the dataflows stored in Azure, you must add them to the dataflow’s CDM folder Creating dataflows with linked entities is only possible when they are stored in the same storage account

13. Deliver ready-made insights to Power BI users from Azure Dynamics 365 Self service customizations in Power BI Azure SQL DW Azure Data Factory Azure Databricks Azure ML Dynamics 365 data Dataflow Azure Data Lake Storage Gen2 CDM folder

14. Demo: Azure Integration, two ways

15. What is the Common Data Model?

16. Common Data Model: a SQL Server analogy in 3 parts

17. The Common Data Model (1 of 2) The Common Data Model is a metadata system that simplifies data management and application development by unifying data into a known form and applying structural and semantic consistency across multiple apps and deployments.

18. The Common Data Model (2 of 2) In addition to the metadata system, the Common Data Model includes a set of standardized, extensible data schemas that Microsoft and its partners have published. This collection of predefined schemas includes entities, attributes, semantic metadata, and relationships. The schemas represent commonly used concepts and activities, such as Account and Campaign, to simplify the creation, aggregation, and analysis of data.

19. What are Common Data Model folders?

20. Common Data Model folders (CDM folders) A CDM folder is a folder in a data lake that conforms to specific, well-defined, and standardized metadata structures and self-describing data. These folders facilitate metadata discovery and interoperability between data producers and data consumers.

21. CDM folders include two types of content model.json A metadata file in a folder in an Azure Data Lake Storage Gen2 instance that follows the Common Data Model metadata format. Data files CSV data files in a Common Data Model folder have a well-defined structure and format and are referenced in the model.json file.

22. Common Data Model folders (CDM folders)

23. Common Data Model folders (CDM folders)

24. Why am I asking these questions?

25. CDM folders are the “magic glue” between Azure and Power BI IT professionals, Data scientists Business analysts No code, low code Low to high code Train & predict Visualize and report Advance data prep Model & serve Orchestrate & move Azure Machine Learning Azure Databricks Power BI Azure SQL Data Warehouse Azure Data Factory Ingest Ingest Power BI dataflows Azure Data Lake Storage CDM folders

26. Positioning and use cases

27. Power BI dataflows sort-of-FAQ • A new capability for self-service data preparation in Power BI • Delivered in a familiar Power Query experience • Built on the foundation of Azure Data Lake Storage gen2 • Utilize the CDM folder format for data storage • A tool for business users to drive data reuse without requiring IT involvement • Enable Excel-like data lineage and orchestration • NOT a replacement for datasets • NOT a replacement for a data warehouse • NOT a replacement for Azure Data Factory or SSIS • NOT a Premium-only feature • NOT an additional cost or fee • NOT spelled with a space or any capital letters

28. Positioning Power BI dataflows Dataflows are for Power Query users Easy to build reusable data entities Easier to compose and orchestrate Multi-stage, multi-user data prep workflows Dataflows fill a self-service gap in the end-to-end story Without dataflows, users will Be blocked on IT involvement Use Excel and manual processes Require 3rd party data preparation tools like Alteryx, Datameer, Trifacta, etc. Dataflows bridge Power BI and Azure CDM folders and BYOSA enable simple collaboration between business and IT CDM and CDM folders are strategic technologies beyond Power BI

29. Canonical Production Customer Scenario Sources Datasets Dataflows Metric Base Tables Ingest PostgreSQL Link Metric Final calculated cleansed data Ingest Spark (ODBC) Workspace Link Metric – specific business line Filtered to the specific line of business Metric – specific business line Metric – specific business line Filtered to the specific line of business Metric – specific business line Workspace Workspace Workspace Workspace See also: https://ssbipolar.com/2018/12/05/a-spectrum-of-data-enrichment-and-reuse/

30. Session resources Dataflows on public Microsoft sites: Dataflows documentation: https://docs.microsoft.com/en-us/power-bi/service-dataflows-overview Dataflows roadmap / release notes: https://docs.microsoft.com/en-us/business-applications-release-notes/April19/business-intelligence/power-bi-service/power-bi-dataflows/self-service-data-prep-with-dataflows Dataflows on Power BI Ideas: https://ideas.powerbi.com/forums/265200-power-bi-ideas?category_id=341638 Dataflows on Power BI community forum: link Common Data Model on public Microsoft sites: : Common Data Model documentation: https://docs.microsoft.com/en-us/common-data-model/ CDM Folder model metadata: https://docs.microsoft.com/en-us/common-data-model/model-json Common Data Model on GitHub: https://github.com/Microsoft/CDM End to end CDM partner sample: https://aka.ms/cdmadsblog Matthew’s blog: Dataflows landing page: https://ssbipolar.com/2018/10/23/dataflows-in-power-bi/ Dataflows FAQ: https://ssbipolar.com/2018/11/27/power-bi-dataflows-faq/

31. Thank you!