Introducing Power BI dataflows

1. Matthew Roche • Senior Program Manager • Microsoft • @SQLAllFather • https://ssbipolar.com Introducing Power BI dataflows

2. Please Thank & Support our Sponsors SQL Saturday is made possible with the generous support of these sponsors. You can support them by opting-in and visiting them in the sponsor area. • Silver Sponsor: Global Alliance Partner: Venue & Internet Sponsor: Operations Support: Endless Support from: OGMA Consulting Corp. Bronze Sponsor:

3. Introducing Power BI dataflows, Three Ways

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. 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

10. Integrating Power BI dataflowswith Azure Data Lake Storage gen2

11. 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

12. 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

13. 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

14. 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

15. Demo: Azure Integration, Two Ways

16. Wrapping Things Up

17. 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

18. 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

19. 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/

20. Thank you!