Expert Data Modeling with Power BI: Get the best out of Power BI by building optimized data models for reporting and business needs

Cover
Title Page
Copyright and Credits
Dedicated
Foreword
Contributors
Table of Contents
Preface
Section 1: Data Modeling in Power BI
Chapter 1: Introduction to Data Modeling in Power BI
	Understanding the Power BI layers
		The data preparation layer (Power Query)
		The data model layer
		The data visualization layer
		How data flows in Power BI
	What data modeling means in Power BI
		Semantic model
		Building an efficient data model in Power BI
		Star schema (dimensional modeling) and snowflaking
	Power BI licensing considerations
		Maximum size of individual dataset
		Incremental data load
		Calculation groups
		Shared datasets
		Power BI Dataflows
	The iterative data modeling approach
		Information gathering from the business
		Data preparation based on the business logic
		Data modeling
		Testing the logic
		Demonstrating the business logic in a basic data visualization
		Thinking like a professional data modeler
	Summary
Chapter 2: Data Analysis eXpressions and Data Modeling
	Understanding virtual tables
		Creating a calculated table
		Using virtual tables in a measure – Part 1
		Using virtual tables in a measure – Part 2
		Visually displaying the results of virtual tables
		Relationships in virtual tables
	Time intelligence and data modeling
		Detecting valid dates in the date dimension
		Period-over-period calculations
		Generating the date dimension with DAX
		Creating a time dimension with DAX
	Summary
Section 2: Data Preparation in Query Editor
Chapter 3: Data Preparation in Power Query Editor
	Introduction to the Power Query M formula language in Power BI
		Power Query is CaSe-SeNsItIvE
		Queries
		Expressions
		Values
		Types
	Introduction to Power Query Editor
		Queries pane
		Query Settings pane
		Data View pane
		Status bar
		Advanced Editor
	Introduction to Power Query features for data modelers
		Column quality
		Column distribution
		Column profile
	Understanding query parameters
	Understanding custom functions
		Recursive functions
	Summary
Chapter 4: Getting Data from Various Sources
	Getting data from common data sources
		Folder
		CSV/Text/TSV
		Excel
		Power BI datasets
		Power BI dataflows
		SQL Server
		SQL Server Analysis Services and Azure Analysis Services
		OData Feed
	Understanding data source certification
		Bronze
		Silver
		Gold/Platinum
	Working with connection modes
		Data Import
		DirectQuery
		Connect Live
	Working with storage modes
	Understanding dataset storage modes
	Summary
Chapter 5: Common Data Preparation Steps
	Data type conversion
	Splitting column by delimiter
	Merging columns
	Adding a custom column
	Adding column from examples
	Duplicating a column
	Filtering rows
	Working with Group By
	Appending queries
	Merging queries
	Duplicating and referencing queries
	Replacing values
	Extracting numbers from text
	Dealing with Date, DateTime, and DateTimeZone
	Summary
Chapter 6: Star Schema Preparation in Power Query Editor
	Identifying dimensions and facts
		Number of tables in the data source
		The linkages between existing tables
		Finding the lowest required grain of Date and Time
		Defining dimensions and facts
	Creating Dimensions tables
		Geography
		Sales order
		Product
		Currency
		Customer
		Sales Demographic
		Date
		Time
		Creating Date and Time dimensions – Power Query versus DAX
	Creating fact tables
	Summary
Chapter 7: Data Preparation Common Best Practices
	General data preparation considerations
		Consider loading a proportion of data while connected to the OData data source
		Appreciating case sensitivity in Power Query saves you from dealing with issues in data modeling
		Be mindful of query folding and its impact on data refresh
		Organizing queries in Query Editor
	datatype conversion
		Data conversion can affect data modeling
		Include the datatype conversion in a step when possible
		Consider having only one datatype conversion step
	Optimizing the size of queries
		Removing unnecessary columns and rows
		Summarization (Group by)
		Disabling query load
	Naming conventions
	Summary
Section 3: Data Modeling
Chapter 8: Data Modeling Components
	Data modeling in Power BI Desktop
	Understanding tables
		Table properties
		Featured tables
		Calculated tables
	Understanding fields
		Data types
		Custom formatting
		Columns
		Hierarchies
		Measures
	Using relationships
		Primary keys/foreign keys
		Handling composite keys
		Filter propagation behavior
		Bidirectional relationships
	Summary
Chapter 9: Star Schema and Data Modeling Common Best Practices
	Dealing with many-to-many relationships
		Many-to-many relationships using a bridge table
		Hiding the bridge table
	Being cautious with bidirectional relationships
	Dealing with inactive relationships
		Reachability via multiple filter paths
		Multiple direct relationships between two tables
	Using configuration tables
		Segmentation
		Dynamic conditional formatting with measures
	Avoiding calculated columns when possible
	Organizing the model
		Hiding insignificant model objects
		Creating measure tables
		Using folders
	Reducing model size by disabling auto date/time
	Summary
Section 4: Advanced Data Modeling
Chapter 10: Advanced Data Modeling Techniques
	Using aggregations
		Implementing aggregations for non-DirectQuery data sources
		Using the Manage Aggregations feature
	Incremental refresh
		Configuring incremental refresh in Power BI Desktop
		Testing the incremental refresh
	Understanding Parent-Child hierarchies
		Identifying the depth of the hierarchy
		Creating hierarchy levels
	Implementing roleplaying dimensions
	Using calculation groups
		Requirements
		Terminology
		Implementing calculation groups to handle time intelligence
		Testing calculation groups
		DAX functions for calculation groups
	Summary
Chapter 11: Row-Level Security
	What RLS means in data modeling
		What RLS is not
		RLS terminologies
		Assigning members to roles in the Power BI service
		Assigning members to roles in Power BI Report Server
	RLS implementation flow
	Common RLS implementation approaches
		Implementing static RLS
		Implementing dynamic RLS
	Summary
Chapter 12: Extra Options and Features Available for Data Modeling
	Dealing with SCDs
		SCD type zero (SCD 0)
		SCD type 1 (SCD 1)
		SCD type 2 (SCD 2)
	Introduction to OLS
		Implementing OLS
		Validating roles
		Assigning members to roles in the Power BI service
		Validating roles in the Power BI service
	Introduction to dataflows
		Scenarios for using dataflows
		Dataflow terminologies
		Creating dataflows
	Introduction to composite models
		New terminologies
	Summary
About Packt
Other Books You May Enjoy
Index