book

Analytics Engineering with Microsoft Fabric and Power BI

by Shabnam Watson, Nikola Ilic

October 2026

Beginner to intermediate

472 pages

16h 6m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Includes

Quizzes

Foreword
Preface
Who Should Read This BookNavigating This BookConventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
I. Introduction to Analytics Engineering
1. Understanding the Role of an Analytics Engineer
The Making of Analytics EngineeringWhat Exactly Does an Analytics Engineer Do?The Big ETL-to-ELT (R)evolutionAnalytics Engineer Versus Data Engineer Versus Data AnalystA Day in the Life of an Analytics EngineerWhat’s in the Analytics Engineer’s Toolbox?What About dbt (Data Build Tool)?Summary
2. Core Analytics Engineering: Data Architectures
The Data Architecture EvolutionRelational Operational DatabaseRelational Data WarehouseData LakeData LakehouseData MeshEvent-Driven ArchitectureSummary
3. Core Analytics Engineering: Data Modeling
Conceptual ModelLogical ModelPhysical ModelOnline Transaction Processing (OLTP) SystemsNormalizationNormal FormsOnline Analytical Processing (OLAP) SystemsDenormalizationDimensional Data ModelStar and Snowflake SchemasSlowly Changing DimensionsDifferent Types of Fact TablesSummary
II. Working with Microsoft Fabric
4. Introduction to Microsoft Fabric
Fundamental Fabric ConceptsTenantCapacityWorkspaceFabric ComponentsFoundation ComponentsWorkload ComponentsFabric’s Cost ModelReserved Monthly Capacity PricingPay-as-You-Go Capacity PricingStorage CostsUser LicensesOther CostsGetting Started with FabricFollowing in Power BI’s Successful FootstepsSummary
5. Data Ingestion and Data Access
Sample Data for the ExamplesSample ArchitectureCreating a WorkspaceSetting Up Lakehouses and a Warehouse for Our Examples Uploading CSV Files to TablesCopy JobWhen to Use Copy JobUsing Copy Job to Load Bronze_LakehouseDataflowsWhen to Use DataflowsCreating a DataflowUsing Copilot in DataflowsScheduling a DataflowTo Stage or Not to StageFast CopyBest Practices for DataflowsPipelinesWhen to Use PipelinesCreating a PipelineScheduling and Triggering a PipelineActivity Chaining: On Success, on Failure, on CompletionUsing Copilot in PipelinesBest Practices for PipelinesApache AirflowWhen to Use Apache Airflow JobsCreating a Simple Airflow JobShortcutsMirroringSummary
6. Working with the Lakehouse in Microsoft Fabric
Understanding the Microsoft Fabric LakehouseThe OneLake FoundationDelta Lake: The Secret SauceThe Two Faces of the LakehouseA Real-World Success StoryThe Lakehouse Versus the Warehouse in FabricThe Integration AdvantageCreating and Configuring Your First LakehousePlanning Your Lakehouse StructureStep-by-Step Lakehouse CreationThe SQL Analytics Endpoint MagicCommon Pitfalls and How to Avoid ThemConfiguration ChecklistManaging Bronze, Silver, and Gold LayersBronze Layer (Raw Zone)Silver LayerGold LayerWorking with Delta TablesCreating and Managing Delta TablesTime Travel and VersioningSchema Evolution and EnforcementOptimization TechniquesThe SQL Analytics EndpointT-SQL Capabilities and LimitationsConnecting Power BI to the LakehouseCross-Database QueriesAdvanced Patterns and Best PracticesImplementing the Medallion Design PatternData Quality and Validation StrategiesUsing Copilot in LakehouseSummary

7. Working with the Data Warehouse in Microsoft Fabric
Getting Started with Warehouse ExplorerSQL Query EditorVisual Query EditorSQL Query in NotebookCopilot for Data WarehouseExample 1: Fixing Query ErrorsExample 2: Explaining a QueryExample 3: SQL Code CompletionExample 4: Using the Chat WindowOther Ways to Connect to a WarehouseWarehouse-Specific CapabilitiesTime Travel with T-SQLZero-Copy Table CloningCross-Database QueryingData Warehouse CollationWorking with Keys and ConstraintsUnique IdentifiersPrimary Keys, Foreign Keys, and Unique ConstraintsLoading Data into the Warehouse with SQLCREATE TABLE AS (CTAS)INSERT … SELECTCOPY INTOOPENROWSETData Recovery and Point-in-Time AccessRestore PointsWarehouse SnapshotsSecurity and MonitoringSummary
8. Working with Real-Time Intelligence in Microsoft Fabric
What Is Stream Processing?Real-Time HubEventstreamsThe Eventhouse and the KQL DatabaseEventhouseKQL DatabaseOneLake AvailabilityEventhouse Endpoint for Lakehouse and WarehouseQuery and Visualize Data in Real-Time IntelligenceKQL QuerysetDo I Need KQL If I Already Know SQL?Real-Time DashboardsVisualize Data with Power BIActivatorFabric IQBuild Your First OntologySummary
9. Microsoft Fabric Decision Guide: When to Choose What
Choosing an Analytical EngineData VolumeSupported Data TypesSupported Programming LanguagesSupported Data Ingestion and Data Access MethodsAccess ControlOneLake InteroperabilityScenario-Based Decision GuideMirrored Azure SQL Database Versus SQL DatabaseScenario 1: Web Application with Operational DataScenario 2: Big Data Containing Sensitive InformationThe SQL Database in Fabric Versus Fabric WarehouseScenario 1: Aggregating Big Data for Analytical ReportsScenario 2: Near-Real-Time Operational Reporting with Enforced Database ConstraintsDirect Lake Versus Import Mode for Semantic ModelsScenario 1: Self-Service Business Intelligence with Power QueryScenario 2: Near-Real-Time Reporting RequirementScenario 3: Resource-Consuming Data Refresh ProcessScenario 4: Using DAX Calculated Tables/ColumnsScenario 5: Using T-SQL ViewsScenario 6: Row-Level Security/Object-Level Security Enforced in the Warehouse/SQL Analytics Endpoint of the LakehouseAll Roads Lead to OneLake, but Which One Is the Right Road?Dataflow Versus Notebook Versus Pipeline Versus Mirroring Versus ShortcutScenario 1: Ingesting Data As Is from an On-Premises Data SourceScenario 2: Customizing the Data-Writing ProcessScenario 3: Transforming Data for the Serving LayerTo V-Order or Not to V-Order?Summary
III. Working with Microsoft Power BI
10. Understanding Power BI Core Components
Power BI DesktopPower BI Storage ModesLive ConnectionComparing Import, DirectQuery, Direct Lake, and Live ConnectionThe Languages of Power BI: Power Query, M, and DAXThe Power BI ServiceWorkspacesPower BI AppsPower BI DashboardsDeployment PipelinesLineage and Impact AnalysisGovernanceOneLake CatalogOther Tools in the Power BI EcosystemPower BI Report ServerPower BI Report BuilderPower BI MobilePower BI GatewayCopilot in Power BIPower BI Cost ModelWorkspace TypeUser LicensesWorkspace Types and User License RequirementsSummary
11. Design and Build Semantic Models
Getting DataConnecting to a LakehouseUnderstanding Lakehouse Connection OptionsCreating a Direct Lake ModelThe Model ViewRelationshipsCardinalityCross-Filter DirectionActive or InactiveAssuming Referential IntegrityCreating the Rest of the RelationshipsMeasuresHierarchiesDate TableColumn PropertiesOpening the Model from the Fabric PortalCreating a Direct Lake Model in the Fabric PortalCreating a Simple Report from the Fabric PortalCreating a Simple Report from Power BI DesktopImport ModelsDirectQuery ModelsDirect Lake Fallback to DirectQuerySummary
12. Enhance Semantic Models with DAX
Starting with Simple Measures (Aggregation Functions)Basic Aggregation FunctionsAggregation Functions with an A SuffixX Aggregation Functions (Iterators)COUNTROWS and APPROXIMATEDISTINCTCOUNTUnderstanding Context in DAXRow ContextFilter ContextIntroducing CALCULATEFilter Modifier FunctionsREMOVEFILTERSALLALLEXCEPTKEEPFILTERSTime Intelligence FunctionsYear-to-Date (YTD)Prior Year ComparisonsPercent Change CalculationsRelationship Navigation FunctionsRELATEDRELATEDTABLEUSERELATIONSHIPTable and Filter FunctionsFILTERVALUESDISTINCTSUMMARIZECOLUMNSADDCOLUMNSVariablesSummary
13. Optimize Enterprise-Scale Semantic Models
What Makes a Model Enterprise-ScaleMemory Versus Compute, and Why Memory Is the Harder ProblemWhat’s Actually Using Your Memory: A Quick Tour of VertiPaqColumn-Level Optimizations: The Cheap Fixes FirstPattern 1: Remove What Nobody Is UsingPattern 2: Reduce CardinalityPattern 3: Disable Unnecessary Attribute HierarchiesPattern 4: Optimize for Run-Length Encoding (RLE)When Column-Level Fixes Aren’t Enough: Architectural PatternsPattern 5: User-Defined AggregationsPattern 6: Split the Model (the Master Model Pattern)The Other Front: Refresh MemoryPattern 7: Refresh Less Data with PartitioningQuery Folding: Make the Source Do the WorkHybrid Tables: Incremental Refresh Plus DirectQueryPattern 8: Refresh Scale-OutThe Temporary Scale-Up WorkaroundDirect Lake: Changing the Rules of the GameMemory Residency StatesWhy the Delta Table Layout MattersUpdate Patterns That Play Nicely (and Ones That Don’t)Maintenance: Don’t Skip It!Anti-Patterns: Things That Look Like Optimization but Aren’tSummary
IV. Maintaining an Analytics Solution
14. Implement Security and Governance
A Layered Access Security ModelEntra IDNetwork SecurityInbound ProtectionManaged Virtual NetworksOutbound ProtectionWorkspace RolesItem PermissionsData-Level SecurityData Encryption at RestGovernanceAdmin PortalMicrosoft Purview OneLake CatalogAdmin Monitoring WorkspaceMonitoring HubSummary
15. Manage the Analytics Development Lifecycle
Setting the Stage: Application Lifecycle Management, Continuous Integration, Continuous Delivery, and What Fabric Actually Gives YouGit Integration: The Continuous-Integration Half of the StoryConnecting a Workspace and the Initial SynchronizationSupported ItemsBranches: The Mental Model That Actually WorksBranching Out to a New Workspace and Selective BranchingCompare Code Changes: Finally, a Real Diff ExperienceFolders in GitPermissions: Who Can Do WhatDeployment Pipelines: The Continuous Delivery HalfStages, Pairing, and AutobindingFull, Selective, and Backward DeploymentDeployment Rules: The Configuration LayerThe Permission ModelWhat Gets Copied and What Doesn’tVariable Libraries: The Parameterization We’ve Been Waiting ForCreating a Variable LibraryValue Sets: The Environment SwitchConsuming Variables in Other ItemsThe New Item Reference Variable TypeVariable Libraries Versus Deployment Rules: When to Use WhichContinuous Integration/Continuous Deployment Across the Data Engineering SurfaceNotebooks and EnvironmentsData PipelinesDataflow Gen2User Data Functions: Git Integration, Deployment, and the Moving PartsOther Item TypesThe Fabric Command-Line Interface: Automation from Your TerminalThe Filesystem Mental ModelInstalling and AuthenticatingThe fab deploy Command: Continuous Integration/Continuous Deployment in One CommandBeyond DeploymentPutting It Together: A Reference ShapeSummary
V. Putting It All Together
16. Semantic Link
Visualize Dependencies in the Semantic ModelOptimize Semantic Models with Best Practices Analyzer RulesTranslate Semantic ModelsMigrate Existing Semantic Models to Direct LakeAugment the Gold LayerSummary
17. Create Your Dream Fabric Architecture
Project 1: A Logistics Group Goes from Spreadsheets to FabricDecision 1: The Silver Layer Should Be a Warehouse, Not a LakehouseDecision 2: Keep the Bronze Layer in a Lakehouse; Just Put It Next to the WarehouseDecision 3: Workspaces Organized by DomainDecision 4: The Three-Capacity ModelDecision 5: Semantic Model Strategy—Who Builds, Who Certifies, Who ConsumesWhat We’d Do Differently If We Started OverProject 2: A Sports Organization That Loves DataWhat Is SL Benfica, and Why This Case Study?Choosing Fabric and the Operating ModelIngestion: Standardize the Outcome, Not Every ConnectorReal-Time Intelligence for Operations, Commercial Moments, and SportGold Autonomy and Governed DistributionCapacity, Security, and Delivery at Enterprise ScaleOutcomes, Lessons, and What Comes NextSynthesis: A Way of Arriving at the Right ShapeQuestion 1: Who Owns the Platform After We Leave?Question 2: Where Does the Data Live Today, and Exactly How Do We Read It?Question 3: What’s the Freshness Requirement, Honestly?Question 4: What’s the Blast Radius of a Bad Change?Question 5: What’s the Constraint That’s Not on the Slide?What Gets Decided First, What Stays ReversibleA Closing ThoughtSummary
Appendix. Going Above and Beyond with External Tools
Understanding XMLADAX StudioTabular EditorSQL Server Management StudioSummary
Index
About the Authors

Overview

While Microsoft Power BI has dominated the business intelligence market for years and is a go-to tool for creating visually appealing, interactive reports and dashboards, it's now an integral part of Microsoft Fabric, the end-to-end analytics platform that offers unprecedented flexibility and scalability for building enterprise-grade data analytics solutions.

This book covers everything analytics engineers need to know to design and implement robust and efficient analytics solutions using Microsoft Fabric and Power BI.

Explore the core components of Microsoft Fabric
Implement, manage, and optimize Power BI semantic models
Discover numerous architectural solutions with Microsoft Fabric and Power BI
Build Fabric items such as lakehouses, warehouses, semantic models, and more, and share them within your organization
Identify when to use a particular Fabric item or implement a particular design pattern
Implement the analytics development lifecycle

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: 9798341645868Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills