book

Expert Data Modeling with Power BI - Second Edition

Name: Expert Data Modeling with Power BI - Second Edition
Author: Soheil Bakhshi
ISBN: 9781803246246

by Soheil Bakhshi

April 2023

Intermediate to advanced

692 pages

16h 10m

English

Packt Publishing

Read now

Unlock full access

Preface
Who this book is forWhat this book coversTo get the most out of this bookGet in touchMaking the Most Out of This Book – Get to Know Your Free Benefits
Section I: Data Modeling in Power BI
Introduction to Data Modeling in Power BI
Understanding the Power BI layersThe data preparation layer (Power Query)The data model layerThe Data viewThe Model viewThe data visualization layerThe Report viewHow data flows in Power BIWhat data modeling means in Power BISemantic modelBuilding an efficient data model in Power BIStar schema (dimensional modeling) and snowflakingTransactional modeling versus star schema modelingSnowflakingUnderstanding denormalizationPower BI licensing considerationsMaximum size of an individual datasetIncremental data loadHybrid tablesCalculation groupsShared datasetsPower BI DataflowsPower BI DatamartsThe iterative data modeling approachConducting discovery workshopsData preparation based on the business logicData modelingTesting the logicDemonstrating the business logic in basic data visualizationsThinking like a professional data modelerSummary
Data Analysis eXpressions and Data Modeling
Understanding virtual tablesCreating calculated tablesVisually displaying the results of virtual tablesCreating calculated tables in Power BI DesktopUsing DAX StudioUnderstanding relationships in virtual tablesTime intelligence and data modelingDetecting valid dates in the date dimensionPeriod-over-period calculationsImplementing dynamic measure selection with Fields ParametersGenerating the Date dimension with DAXMarking a Date table as a date tableCreating a time dimension with DAXSummary
Section II: Data Preparation in Query Editor
Data Preparation in Power Query Editor
Introducing the Power Query M formula language in Power BIPower Query is CaSe-SeNsItIvEQueriesExpressionsValuesPrimitive valuesStructured valuesTypesPrimitive typesCustom typesIntroduction to Power Query EditorQueries paneTablesCustom functionsQuery parametersConstant valuesGroupsQuery Settings paneQuery PropertiesApplied StepsData View paneStatus barAdvanced EditorIntroduction to Power Query features for data modelersColumn qualityColumn distributionColumn profileUnderstanding query parametersUnderstanding custom functionsRecursive functionsSummary
Getting Data from Various Sources
Getting data from common data sourcesFoldersCSV/Text/TSVExcelExcel file stored in local driveExcel file stored in SharePoint OnlinePower BI datasetsPower BI dataflowsPower BI DatamartsSQL ServerSQL Server Analysis Services and Azure Analysis ServicesSSAS multidimensional/tabularAASOData feedDataverseUnderstanding data source certificationBronzeSilverGold/PlatinumWorking with connection modesData ImportApplicationsLimitationsDirectQueryApplicationsLimitationsConnect LiveApplicationsLimitationsWorking with storage modesUnderstanding dataset storage modesSummary
Common Data Preparation Steps
Data type conversionSplitting a column by delimiterMerging columnsAdding a custom columnAdding a column from examplesDuplicating a columnFiltering rowsWorking with Group ByAppending queriesMerging queriesDuplicating and referencing queriesReplacing valuesExtracting numbers from textDealing with Date, DateTime, and DateTimeZonePivoting tablesSummary
Star Schema Preparation in Power Query Editor
Identifying dimensions and factsUnderstanding business requirementsNumber of tables in the data sourceThe linkages between existing tablesFinding the lowest required grain of Date and TimeDefining dimensions and factsDetermining the potential dimensionsDetermining the potential factsCreating Dimension tablesGeographySales orderProductCurrencyCustomerSales DemographicDateTimeCreating Date and Time dimensions – Power Query versus DAXCreating fact tablesSummary
Data Preparation Common Best Practices
Consider loading a proportion of dataAppreciate case sensitivity in Power QueryBe mindful of query folding and its impact on data refreshUnderstanding query foldingDirectQuery and Dual storage modes and query foldingData sources and query foldingIndications for query foldingQuery folding best practicesUsing SQL statementsPush the data preparation to the source system when possibleDisabling View Native Query does not necessarily mean a transformation step is not foldedOrganize queries in the Power Query EditorFollow data type conversion best practicesData type conversion can affect data modelingAvoid having columns with any data typeInclude the data type conversion in the step when possibleConsider having only one data type conversion stepOptimize query sizeRemove unnecessary columns and rowsSummarization (Group by)Disabling query loadUse query parametersParameterizing connectionsRestricting the row counts in development for large tablesDefine key columns in queriesUse naming conventionsSummary

Section III: Data Modeling
Data Modeling Components
Data modeling in Power BI DesktopUnderstanding tablesTable propertiesFeatured tablesCalculated tablesUnderstanding fieldsData typesCustom formattingColumnsCalculated columnsGrouping and binning columnsColumn propertiesHierarchiesMeasuresImplicit measuresExplicit measuresTextual measuresUsing relationshipsPrimary keys/foreign keysHandling composite keysRelationship cardinalitiesOne-to-one relationshipsOne to many relationshipsMany to many relationshipsFilter propagation behaviorBidirectional relationshipsSummary
Star Schema and Data Modeling Common Best Practices
Dealing with many-to-many relationshipsMany-to-many relationships using a bridge tableHiding the bridge tableAvoiding bidirectional relationshipsDealing with inactive relationshipsReachability via multiple filter pathsMultiple direct relationships between two tablesUsing configuration tablesSegmentationDynamic color coding with measuresAvoiding calculated columns when possibleOrganizing the modelHiding insignificant model objectsHiding unused fields and tablesHiding key columnsHiding implicit measuresHiding columns used in hierarchies when possibleCreating measure tablesUsing foldersCreating a folder in multiple tables in one goPlacing a measure in various foldersCreating subfoldersReducing model size by disabling auto date/timeSummary
Section IV: Advanced Data Modeling
Advanced Data Modeling Techniques
Using aggregationsImplementing aggregations for non-DirectQuery data sourcesImplementing aggregation at the Date levelImplementing aggregation at the Year and Month levelUsing Agg AwarenessCreating an aggregation tableLoading tables in DirectQuery modeCreating relationshipsSetting the aggregation table and its related dimensions’ storage modeManaging aggregationTesting the aggregationImplementing multiple aggregationsImportant notes about aggregationsIncremental refresh and hybrid tablesConfiguring incremental refresh policy and hybrid table in Power BI DesktopTesting the incremental refreshImportant notes about incremental refresh and hybrid tablesParent-Child hierarchiesIdentify the depth of the hierarchyCreating hierarchy levelsImplementing roleplaying dimensionsUsing calculation groupsRequirementsTerminologyImplementing calculation groups to handle time intelligenceTesting calculation groupsFixing the format string issueDAX functions for calculation groupsSummary
Row-Level and Object-Level Security
What RLS and OLS mean in data modelingTerminologyRolesRulesEnhanced row-level security editorValidating rolesAssigning members to roles in the Power BI ServiceAssigning members to roles in Power BI Report ServerRLS implementation flowCommon RLS implementation approachesStatic RLS implementationDynamic RLS implementationRestricting unauthorized users from accessing dataManagers can access their team members’ data in parent-child hierarchiesGetting the user’s login data from another sourceIntroduction to OLSOLS implementation flowOLS implementationValidating rolesAssigning members and validating roles in the Power BI ServiceRLS and OLS implementation in a single modelConsiderations in using RLS and OLSSummary
Dealing with More Advanced Data Warehousing Concepts in Power BI
Dealing with SCDsSCD type zero (SCD 0)SCD type 1 (SCD 1)SCD type 2 (SCD 2)Dealing with degenerate dimensionsSummary
Introduction to Dataflows
Introduction to DataflowsScenarios for using DataflowsDataflow terminologyCreate DataflowsCreate new entitiesCreate linked tables from other DataflowsCreate computed entitiesConfigure incremental data refresh in DataflowsExport/import DataflowsExport DataflowsImport DataflowsNo-code/low-code experienceQuery plans in DataflowsSummary
DirectQuery Connections to Power BI Datasets and Analysis Services in Composite Models
Introduction to composite modelsEnabling DirectQuery for live connectionsAllow DirectQuery connections to Power BI datasets in the Power BI serviceNew terminologiesChainingChain lengthRLS in composite models with DirectQuery to Power BI datasetsSetting dataset permissions for contributors (report writers)Summary
New Options, Features, and DAX Functions
Field parametersIntroduction to Power BI DatamartsWhat is a Datamart?What is Power BI Datamarts?Demystifying Power BI Datamart misunderstandings The Datamart EditorCreate a simple Power BI DatamartLoad the data into the DatamartBuild the data model in DatamartsAnalyze Datamarts in the Datamart EditorAnalyze Datamarts in SQL client toolsRLS in DatamartsNew DAX functionsNETWORKDAYS()EVALUATEANDLOG()Window functionsPARTITIONBY()ORDERBY()INDEX()OFFSET()WINDOW()Summary
Other Books You May Enjoy
Index

Overview

Expert Data Modeling with Power BI, Second Edition, serves as your comprehensive guide to mastering data modeling using Power BI. With clear explanations, actionable examples, and a focus on hands-on learning, this book takes you through the concepts and advanced techniques that will enable you to build high-performing data models tailored to real-world requirements.

What this Book will help me do

Master time intelligence and virtual tables in DAX to enhance your data models.
Understand best practices for creating efficient Star Schemas and preparing data in Power Query.
Deploy advanced modeling techniques such as calculation groups, aggregations, and incremental refresh.
Manage complex data models and streamline them to improve performance.
Leverage data marts and data flows within Power BI for modularity and scalability.

Author(s)

Soheil Bakhshi is a seasoned expert in data visualization and analytics with extensive experience in leveraging Power BI for business intelligence solutions. Passionate about educating others, he combines practical insights and technical knowledge to make learning accessible and effective. His approachable writing style reflects his commitment to helping readers succeed.

Who is it for?

This book is ideal for business intelligence professionals, data analysts, or report developers with basic knowledge of Power BI and experience with Star Schema concepts. Whether you're looking to refine your data modeling skills or expand your expertise in advanced features, this guide aims to help you achieve your goals efficiently.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781803246246

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills