book

Expert Data Modeling with Power BI - Second Edition

by Soheil Bakhshi

April 2023

Intermediate to advanced

692 pages

16h 10m

English

Packt Publishing

Read now

Unlock full access

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

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
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
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
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 SCDsSCD type zero (SCD 0)SCD type 1 (SCD 1)SCD type 2 (SCD 2)Dealing with degenerate dimensionsSummary
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
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
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

Content preview from Expert Data Modeling with Power BI - Second Edition

13 Introduction to Dataflows

In the previous chapter, we learned some more advanced data modeling techniques that are more related to data warehousing: dealing with SCDs and degenerate dimensions. This chapter briefly introduces Dataflows, another available feature in the Power BI platform that can become handy in real-world scenarios. To get the best out of Dataflows, we need to pay attention to numerous technicalities that directly impact our design and implementation, such as the storage mechanism and licensing details making it virtually impossible to cover all in a single chapter. So, this chapter is designed to cover the basics and help you to build robust building blocks for your learning journeys. This chapter covers the following areas: ...

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.

Start your free trial

Publisher Resources

ISBN: 9781803246246Supplemental Content

Expert Data Modeling with Power BI - Second Edition

by Soheil Bakhshi

13

Introduction to Dataflows

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.

You might also like

Data Modeling with Microsoft Power BI