Power BI Advanced Data Modeling Virtual Workshop

Classified as Microsoft Confidential Please message Sami with any questions, concerns or if you need assistance during this workshop. Housekeeping PLEASE SEND QUESTIONS TO SAMI. PLEASE MUTE YOUR LINE! WE WILL BE APPLYING MUTE. THIS SESSION WILL BE RECORDED. WE WILL SHARE SLIDES WITH YOU.

Classified as Microsoft Confidential CCG Analytics We bring great People together to do extraordinary Things DATA ANALYTICS STRATEGY Working with CCG is like working with extended team members. Consultants become an integral part of the work bringing expertise for cutting edge design and development. - CIO, HCPS

Classified as Microsoft Confidential Doug McClurg Solution Architect Data-driven and client-focused, drawing experience from several industries including manufacturing, oil & gas, retail and hospitality. Engineering leadership and systems design have been key areas of focus in recent engagements, helping companies sort through the chaos and build analytics at the speed of their market. www.linkedin.com/in/dpmclurg

Classified as Microsoft Confidential COURSE OBJECTIVES By the end of this course, you will be able to use DAX to create calculations in a Power BI Desktop data model. Specifically you will be able to: • Understand basic and advanced concepts of Data Modeling • Understand the consequences of data model design decisions • Understand concepts of calculated columns and measures • Gain familiarity with standard DAX patterns & CALCULATE • Understand evaluation contexts and their impact on calculations • Gain ability to parse data modeling formulas © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential COURSE AGENDA Introductions and Overview Module 1 Platform & Process Module 2 Modeling Best Practices Module 3 Modeling Scenarios Break Module 4 DAX Module 5 DAX Evaluation Contexts Module 6 DAX Best Practices Wrap-up & Questions © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential You’re in Control

Classified as Microsoft Confidential Modeling is about Choices and Productivity 1.Data Quality 2.PerformanceUI UX

Classified as Microsoft Confidential Analysis Services (Tabular) A Little History Analysis Services (Multidimensional) =VLOOKUP

Classified as Microsoft Confidential Analysis Services Server-Based Database System Dataflows Power Query Online Data Model The Power System Power BI Desktop Desktop Analytics Application Power BI Service Web-Based Analytics Services Power Platform Web-Based Data Services Power Query Desktop Model Dataflows & Flows Power Query Online Entities Data Sources Visuals Dataflows Datasets Power Query Online Visuals

Classified as Microsoft Confidential Data Architecture Power Platform Dataflows

Classified as Microsoft Confidential Modeling Principles Page Views Customer CustomerId AgeGroup Gender PostalCode Arrow points in the direction of the filter context Measures Rollups

Classified as Microsoft Confidential Modeling Principles

Classified as Microsoft Confidential What is unique about Power BI Desktop in Import Mode? Import Mode • Columnar database • In-memory database • Maximum features © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Columnar Database First Name Last Name Sales John Smith $10 Jane Doe $25 Hardy B $35 First Name Last Name Sales John Smith $10 Jane Doe $25 Hardy B $35 each row separately each column separately • Columnar databases are well suited for analytics © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential How Power BI Compresses Data – Dictionary Encoding Compressing Data – Dictionary Encoding Sale Id Color Sales Amount 390a30e0-dc37 Red $10 390a30e1-dc37 Green $25 390a30e2-dc37 Red $35 390a30e3-dc37 Red $15 390a30e4-dc37 Red $25 390a30e5-dc37 Green $30 390a30e6-dc37 Blue $10 390a30e7-dc37 Blue $12 390a30e8-dc37 Blue $15 390a57f0-dc37 Blue $18 390a57f1-dc37 Green $25 Red 1 Green 2 Blue 3 • Dictionary encoding is powerful when there are few unique values © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential How Power BI Compresses Data – Run Length Encoding Compressing Data – Run Length Encoding Sale Id Color Sales Amount 390a30e0-dc37 Red $10 390a30e1-dc37 Green $25 390a30e2-dc37 Red $35 390a30e3-dc37 Red $15 390a30e4-dc37 Red $25 390a30e5-dc37 Green $30 390a30e6-dc37 Blue $10 390a30e7-dc37 Blue $12 390a30e8-dc37 Blue $15 390a57f0-dc37 Blue $18 390a57f1-dc37 Green $25 • Run length encoding very powerful when data is sorted 1 2 1 1 1 2 3 3 3 3 2 Red 1 Green 2 Blue 3 © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Practical Example of Compression Compression Sales Fact 145.0 MB Dimensions 7.0 MB Int’l Sales 128.0 MB Total Data 280.0 MB Query Metadata 14 KB Almost 21X Compression!! Data Model 13.0 MB © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Key takeaways to design a good Power BI Desktop data model Designing good data models • RAM is precious !!!!! remove it • Sort columns try splitting Date & Time © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Data Types • Any – You should never see this in a data model. Bad things can happen!! Set your Data Types in the Query Editor Set your Data Formats ($ %, etc.) in the Data Model © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Synonyms © 2019 Microsoft. All rights reserved. The choice of tables and columns is important for Q&A. For example, say you have a table named CustomerSummary that contains a list of your customers. You would need to ask questions like “List the customer summaries in Chicago” rather than “List the customers in Chicago”.

Classified as Microsoft Confidential Data Modeling • An inefficient model can completely slow down a report, even with very small data volumes GOALS • Make the model as small as possible • Schema supports the analysis • Relationships are built purposefully and thoughtfully © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Move calculations to the source Scenario • Many DAX calculated columns with high cardinality Why is it undesired? • Calculated columns don’t compress as well as physical columns Proposed Solution • Perform calc in Power Query, ideally push down • Customize source query for non foldable transforms © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Remove unused tables and columns Scenario • Model contains tables/columns that are not used for reporting/analysis or calculations Why is it undesired? • Increases model size • Increases time to load into memory • Increases refresh time • May affect usability © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Avoid high precision/cardinality columns Scenario • Model contains columns at a higher precision than needed for analysis e.g. datetime in milliseconds, weight to 6 decimal places • Model contains columns that are highly unique Why is it undesired? • Less compression with high precision/cardinality • Increases time to load into memory • Increases refresh time Proposed Solution • Remove if not needed • Reduce precision • Split datetime into date and time © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Use integers instead of strings Why is it undesired? • Strings use dictionary encoding, integers use run length encoding which is more efficient Proposed Solution • Check data types and set to integer if known to be numeric © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Use integer surrogate keys, pre-sort them • Power BI compresses rows in segments of millions of rows • Integers use Run Length Encoding • Sorting will maximize compression when encoded as it reduces the range of values per segment © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Be careful with bi-directional relationships Scenario • Most relationships in the model are set to bi-directional Why is it undesired? • Applying filters/slicers traverses many relationships and can be slower • Some filter chains unlikely to add business value Proposed Solution • Only use bi-di where the business scenario requires it © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Set Default Summarization Scenario • Numeric columns in model that are purely informational (e.g. Account ID) • Default summarization is Sum Why is it undesired? • Power BI will try to sum the number when dropped into visuals. • Detailed tables/matrixes can be slower Proposed Solution • Set the default summarization to None © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Consider subsets for very large models Scenario • Large model – hundreds of tables and tens of GB • Large high grain fact tables – millions to billions Why is it undesired? • Aggregating/measures across large facts can affect performance • Large models become harder to maintain and use ad-hoc Proposed Solution • Consider aggregations and composite models features • Build manual summary tables with smart measures • Create smaller models for the most common business cases © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Calculated Column vs. Measure - When to Use What Calculated Column vs. Measure Slicer Rows Columns Values Rule of Thumb for Calculated Column vs Measure • Calculated Column – Use in Page, Report & Visual Filters as well as Slicers, Rows and Columns • Measures - Use in Values section © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Row Context and Multiple Tables – RELATED Function RELATED Function Sales[COGS] = RELATED(ProductDim[Unit Cost]) * Sales[Units] © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential RELATED Function Example RELATED Function Sales [City State]= RELATED(GeographyDim[City]) & “, ” & RELATED(GeographyDim[State]) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Measure, Use Case 1: Using One Measure in Another Measures [Profit] = SUM(Sales[Sales Amount])-SUM(Sales[COGS]) [Profit] = [Total Sales]- [Total COGS] © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Measure, Use Case 2: More Complex Calculations Measures [Profit Margin %] = [Profit] / [Total Sales] [Profit Margin %] = DIVIDE([Profit] , [Total Sales]) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Measure, Use Case 3: More Complex Calculations Using Variables Measures MobileSalesLastYear = VAR MobileProducts = FILTER( ALL('CampaignDim'[Device]), CampaignDim[Device]="Mobile" ) VAR LastYear = SAMEPERIODLASTYEAR('DateDim'[Date]) RETURN CALCULATE(SUM(Sales[Sales Amount]),MobileProducts,LastYear) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Why is CALCULATE Useful? CALCULATE You create a report of breakdown of Sales by Month Typical Business Question: Provide a break out of this Sales from Desktop © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Here is how you do it with CALCULATE CALCULATE [Desktop Sales] = CALCULATE([Total Sales], CampaignDim[Device] = "Desktop") • Use CALCULATE function to create a Measure which filters down to Desktop Sales © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Anatomy of CALCULATE CALCULATE CALCULATE(Expression, [Filter 1], [Filter 2]…..) Filter Arguments • EXPRESSION used as the first parameter is essentially the same as a measure • CALCULATE works differently from other DAX functions • The second set of arguments, i.e. the “Filter arguments,” are evaluated and applied first • Then the Expression is evaluated under new “Filter Context" © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential CALCULATE – Add Filter [Desktop Sales] = CALCULATE([Total Sales], CampaignDim[Device] = "Desktop") [Tablet Sales] = CALCULATE([Total Sales], CampaignDim[Device] = “Tablet") [Mobile Sales] = CALCULATE([Total Sales], CampaignDim[Device] = “Mobile") *When the Device Slicer is selected, only “Total Sales” changes. © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential CALCULATE – Ignore an Existing Filter [Total Sales All Geo] = CALCULATE([Total Sales], ALL(GeographyDim)) *Ignore filter on ANY column from the GeographyDim table, but allows filters from Year © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential CALCULATE – Ignore an Existing Filter [Total Sales All States] = CALCULATE([Total Sales], ALL(GeographyDim[State])) *Ignore filter on the STATE column from the GeographyDim table, but allows filters from Year © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential CALCULATE – Ignore Existing Filter [Total Sales All Selected States] = CALCULATE([Total Sales], ALLSELECTED(GeographyDim[State])) *Ignore filter on the STATE column from the GeographyDim table, but allows filters from Year © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential CALCULATE – Update Existing Filter [2014 Sales] = CALCULATE([Total Sales], DateDim[Year] = 2014) *Ignores filter on the Year Slicer © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Filter Context in a Table/Matrix Measure [Total Sales] = SUM(Sales[Sales Amount]) Filter Context for current coordinate Year = 2015, State = HI, Quarter = Q1 © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Filter Context in a Measure – Example 2 [Total Sales] = SUM(Sales[Sales Amount]) Filter Context : Year = 2015, Quarter = Q1 Filter Context : Year = 2015, Quarter = Q2 Filter Context in a Chart Measure © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Filter Context and Multiple Tables – Right Arrow Direction Arrow allows filters Arrow allows filters Cross filtering works properly Filter Context and Multiple Tables © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Filter Context and Multiple Tables – Wrong Arrow Direction Arrow does not allow filters to flow to DateDim Arrow allows filters Cross filtering does not work Filter Context and Multiple Tables © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential CALCULATE – Converting Row Context to Filter Context (Example 1) DAX Foundations Sales velocity Segment = IF( SUMX(RELATEDTABLE(Sales), Sales[Sales Amount])>=200000, “High Velocity”, “Low Velocity”) Sales Velocity (Using CALCULATE) = IF ( CALCULATE(SUM(Sales[Sales Amount])) >= 200000, "High Velocity", "Low Velocity") © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Basic Table Functions – Return Filtered Set of Rows FILTER(ALL(GeographyDim[Region], GeographyDim[State]), GeographyDim[Region] = “Central”) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Table Functions Application – Iterators [COGS] = SUMX(Sales, Sales[Units] * RELATED(ProductDim[Unit Cost])) Argument 2Argument 1 © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Table Functions Application – Iterators [COGS] = SUMX(Sales, Sales[Units] * RELATED(ProductDim[Unit Cost])) Iterate through each row in Argument 1 Sales Argument 1 © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential [COGS] = SUMX(Sales, Sales[Units] * RELATED(ProductDim[Unit Cost])) Sales ProductDim Argument 2 Table Functions Application – Iterators © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Row Context in a Measure – Iterator Functions [COGS] = SUMX(Sales, Sales[Units] * RELATED(ProductDim[Unit Cost])) SUM it up SUM up list obtained © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Before we get to Time Intelligence - Let us apply all of the DAX techniques Advanced DAX [SalesYTD] = CALCULATE ( [Total Sales], FILTER ( ALL ( DateDim), DateDim[Year] = MAX ( DateDim[Year] ) && DateDim[Date] <= MAX(DateDim[Date]) ) ) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Before we get to Time Intelligence - Let us apply all of the DAX techniques Advanced DAX [SalesYTD] = CALCULATE ( [Total Sales], FILTER ( ALL ( DateDim), DateDim[Year] = MAX ( DateDim[Year] ) && DateDim[Date] <= MAX( DateDim[Date] ) ) ) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Let us apply all of the data modeling techniques Advanced DAX [SalesYTD] = CALCULATE ( [Total Sales], FILTER ( ALL ( DateDim), DateDim[Year] = MAX ( DateDim[Year] ) && DateDim[Date] <= MAX( DateDim[Date] ) ) ) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Let us apply all of the data modeling techniques Advanced DAX [SalesYTD] = CALCULATE ( [Total Sales], FILTER ( ALL ( DateDim), DateDim[Year] = MAX ( DateDim[Year] ) && DateDim[Date] <= MAX(DateDim[Date] ) ) ) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential © 2019 Microsoft. All rights reserved. Advanced DAX [SalesYTD] = CALCULATE ( [Total Sales], FILTER ( ALL ( DateDim), DateDim[Year] = MAX ( DateDim[Year] ) && DateDim[Date] <= MAX(DateDim[Date] ) ) ) Let us apply all of the data modeling techniques

Classified as Microsoft Confidential Introducing Time Intelligence – There is an App for that!! Advanced DAX – Time Intelligence [SalesYTD Easier] = CALCULATE ( [Total Sales], DATESYTD(DateDim[Date]) ) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Advanced DAX – Month over Month Total Sales Last Month = CALCULATE([Total Sales], PREVIOUSMONTH(DateDim[Date])) MoM = DIVIDE([Total Sales] - [Total Sales Last Month], [Total Sales Last Month]) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Other Time Intelligence Functions Advanced DAX – Time Intelligence DATESINPERIOD DATESYTD DATESQTD NEXTMONTH NEXTYEAR PREVIOUSYEAR PREVIOUSMONTH SAMEPERIODLASTYEAR PARALLELPERIOD © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Use variables instead of repeating measures Ratio = IF([Total Rows] > 10, SUM(Revenue) /[Total Rows], 0) VAR totalRows = [Total Rows]; Ratio = IF(totalRows > 10, SUM(Revenue) / totalRows,0) © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Use DIVIDE() instead of / • DIVIDE() function has 3rd extra parameter which is returned in case of denominator being zero • It internally performs check to validate if the denominator is 0 • There is no need to use IF condition along with '/' operator to check for invalid denominator • DIVIDE() also checks for ISBLANK() : © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Don’t change blanks to zeros or other values • Sometimes people replace blanks with zeros or other strings • Power BI automatically filters out all the rows with blank values from query results • If the blanks are replaced, the query space is greatly increased © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Avoid using IFERROR() and ISERROR() • IFERROR() and ISERROR() are sometimes used in measures • These functions force Power BI engine to perform step by step execution of each row to check for errors as there is currently no way which directly states which row returned the error • FIND() and SEARCH() DAX functions provide an extra parameter which can be passed and is returned in case of the search string not present – avoids use of IFERROR/ISERROR • Both of this functions are currently also used to check for divide by zero error or along with values to check if more than one values are returned. • Can be avoided by using the correct DAX functions like DIVIDE() and SELECTEDVALUE() which performs the error check internally and returns the expected results © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Performance Analyzer Using Performance Analyzer: • You will know how each of your report elements, such as visuals and DAX formulas, are performing • You can see and record logs that measure how each of your report elements performs when users interact with them, and which aspects of their performance are most (or least) resource intensive © 2019 Microsoft. All rights reserved.

Classified as Microsoft Confidential Contact Support Report Errors, Issues – Support.PowerBI.com Resources use presentation mode to click the hyperlinks  Community.PowerBI.com – Community Forum  Data Stories Gallery – Get inspired with Data Stories by other Power BI users  R-Visuals Gallery – Get inspired by others use of R for analyzing their data  Visuals.PowerBI.com – Custom PBI visuals and R visuals you can download and use in your story  Power BI Blog - weekly updates  User Voice for Power BI – Vote on (or submit) your favorite new ideas for Power BI  Issues.PowerBI.Com – log issues with the community  Guided Learning Self Service Power BI training  DAX Formula Language – syntax for DAX  DAX Patterns – Great website to learn new patterns for the DAX Language  Power Query Formula Language – syntax for the “Query” language Power BI Support Resources Instructors: © 2019 Microsoft. All rights reserved.

Power BI Advanced Data Modeling Virtual Workshop

More Related Content

What's hot

Similar to Power BI Advanced Data Modeling Virtual Workshop

More from CCG

Recently uploaded

Power BI Advanced Data Modeling Virtual Workshop

Editor's Notes