Analytics Engineering with Microsoft Fabric and Power BI. The Definitive Guide for Building Enterprise-Grade Analytics Solutions Shabnam Watson, Nikola Ilic

Dostępność produktu

Produkt nie został jeszcze oceniony pod kątem ułatwień dostępu lub nie podano żadnych informacji o ułatwieniach dostępu lub są one niewystarczające. Prawdopodobnie Wydawca/Dostawca jeszcze nie umożliwił dokonania walidacji produktu lub nie przekazał odpowiednich informacji na temat jego dostępności.

Spis treści książki

• Foreword
• Preface
  • Who Should Read This Book
  • Navigating This Book
  • Conventions Used in This Book
  • Using Code Examples
  • OReilly Online Learning
  • How to Contact Us
  • Acknowledgments
• I. Introduction to Analytics Engineering
• 1. Understanding the Role of an Analytics Engineer
  • The Making of Analytics Engineering
    • What Exactly Does an Analytics Engineer Do?
    • The Big ETL-to-ELT (R)evolution
    • Analytics Engineer Versus Data Engineer Versus Data Analyst
      • Data engineer
      • Data analyst
      • Analytics engineer
  • A Day in the Life of an Analytics Engineer
  • Whats in the Analytics Engineers Toolbox?
  • What About dbt (Data Build Tool)?
  • Summary
• 2. Core Analytics Engineering: Data Architectures
  • The Data Architecture Evolution
    • Relational Operational Database
    • Relational Data Warehouse
      • Top-down approach
      • Bottom-up approach
    • Data Lake
    • Data Lakehouse
    • Data Mesh
    • Event-Driven Architecture
  • Summary
• 3. Core Analytics Engineering: Data Modeling
  • Conceptual Model
  • Logical Model
  • Physical Model
  • Online Transaction Processing (OLTP) Systems
    • Normalization
    • Normal Forms
  • Online Analytical Processing (OLAP) Systems
    • Denormalization
    • Dimensional Data Model
    • Star and Snowflake Schemas
    • Slowly Changing Dimensions
      • Slowly changing dimension Type 1
      • Slowly changing dimension Type 2
    • Different Types of Fact Tables
      • Transactional fact table
      • Periodic snapshot table
      • Accumulating snapshot table
      • Factless fact table
      • Picking the right fact table type
    • Summary
• II. Working with Microsoft Fabric
• 4. Introduction to Microsoft Fabric
  • Fundamental Fabric Concepts
    • Tenant
    • Capacity
    • Workspace
  • Fabric Components
    • Foundation Components
      • OneLake
      • Copilot
      • Purview
    • Workload Components
      • Data Factory
      • Data Warehouse
      • Data Engineering
      • Data Science
      • Databases
      • Real-Time Intelligence
      • Power BI
      • Fabric IQ
      • Industry Solutions and Partner Workloads
  • Fabrics Cost Model
    • Reserved Monthly Capacity Pricing
    • Pay-as-You-Go Capacity Pricing
    • Storage Costs
    • User Licenses
    • Other Costs
  • Getting Started with Fabric
  • Following in Power BIs Successful Footsteps
  • Summary
• 5. Data Ingestion and Data Access
  • Sample Data for the Examples
  • Sample Architecture
  • Creating a Workspace
  • Setting Up Lakehouses and a Warehouse for Our Examples
  • Uploading CSV Files to Tables
  • Copy Job
    • When to Use Copy Job
    • Using Copy Job to Load Bronze_Lakehouse
  • Dataflows
    • When to Use Dataflows
    • Creating a Dataflow
    • Using Copilot in Dataflows
    • Scheduling a Dataflow
    • To Stage or Not to Stage
    • Fast Copy
    • Best Practices for Dataflows
  • Pipelines
    • When to Use Pipelines
    • Creating a Pipeline
    • Scheduling and Triggering a Pipeline
    • Activity Chaining: On Success, on Failure, on Completion
    • Using Copilot in Pipelines
    • Best Practices for Pipelines
  • Apache Airflow
    • When to Use Apache Airflow Jobs
    • Creating a Simple Airflow Job
  • Shortcuts
  • Mirroring
  • Summary
• 6. Working with the Lakehouse in Microsoft Fabric
  • Understanding the Microsoft Fabric Lakehouse
    • The OneLake Foundation
    • Delta Lake: The Secret Sauce
    • The Two Faces of the Lakehouse
    • A Real-World Success Story
  • The Lakehouse Versus the Warehouse in Fabric
    • The Integration Advantage
  • Creating and Configuring Your First Lakehouse
    • Planning Your Lakehouse Structure
    • Step-by-Step Lakehouse Creation
      • Step 1: Navigate to your workspace
      • Step 2: Create the lakehouse
      • Step 3: Understand the initial structure
      • Step 4: Configure the properties
    • The SQL Analytics Endpoint Magic
    • Common Pitfalls and How to Avoid Them
      • Pitfall 1: The everything-in-one-lakehouse approach
      • Pitfall 2: Ignoring file organization
      • Pitfall 3: Failing to set up monitoring early
    • Configuration Checklist
  • Managing Bronze, Silver, and Gold Layers
    • Bronze Layer (Raw Zone)
    • Silver Layer
    • Gold Layer
  • Working with Delta Tables
    • Creating and Managing Delta Tables
      • Method 1: Converting files to tables
      • Method 2: Creating tables with partitioning
    • Time Travel and Versioning
    • Schema Evolution and Enforcement
    • Optimization Techniques
      • OPTIMIZE: The file consolidator
      • V-Order: The query accelerator
      • VACUUM: The janitor
  • The SQL Analytics Endpoint
    • T-SQL Capabilities and Limitations
    • Connecting Power BI to the Lakehouse
    • Cross-Database Queries
  • Advanced Patterns and Best Practices
    • Implementing the Medallion Design Pattern
      • Working with raw data in the bronze layer
      • Transforming data for the silver layer
      • Implementing a star schema in the gold layer
    • Data Quality and Validation Strategies
      • Validation at ingestion (bronze layer)
      • Data quality enforcement (silver layer)
      • Monitoring and reporting (gold layer/Power BI)
      • Data remediation workflow
    • Using Copilot in Lakehouse
  • Summary
• 7. Working with the Data Warehouse in Microsoft Fabric
  • Getting Started with Warehouse Explorer
    • SQL Query Editor
    • Visual Query Editor
    • SQL Query in Notebook
  • Copilot for Data Warehouse
    • Example 1: Fixing Query Errors
    • Example 2: Explaining a Query
    • Example 3: SQL Code Completion
    • Example 4: Using the Chat Window
  • Other Ways to Connect to a Warehouse
  • Warehouse-Specific Capabilities
    • Time Travel with T-SQL
    • Zero-Copy Table Cloning
    • Cross-Database Querying
    • Data Warehouse Collation
  • Working with Keys and Constraints
    • Unique Identifiers
      • The built-in IDENTITY property
      • The NEWID() function
    • Primary Keys, Foreign Keys, and Unique Constraints
  • Loading Data into the Warehouse with SQL
    • CREATE TABLE AS (CTAS)
    • INSERT SELECT
    • COPY INTO
    • OPENROWSET
  • Data Recovery and Point-in-Time Access
    • Restore Points
    • Warehouse Snapshots
    • Security and Monitoring
  • Summary
• 8. Working with Real-Time Intelligence in Microsoft Fabric
  • What Is Stream Processing?
  • Real-Time Hub
  • Eventstreams
  • The Eventhouse and the KQL Database
    • Eventhouse
      • The difference in architecture
      • The Eventhouse versus the KQL Database
    • KQL Database
    • OneLake Availability
    • Eventhouse Endpoint for Lakehouse and Warehouse
  • Query and Visualize Data in Real-Time Intelligence
    • KQL Queryset
    • Do I Need KQL If I Already Know SQL?
    • Real-Time Dashboards
    • Visualize Data with Power BI
  • Activator
  • Fabric IQ
  • Build Your First Ontology
  • Summary
• 9. Microsoft Fabric Decision Guide: When to Choose What
  • Choosing an Analytical Engine
    • Data Volume
    • Supported Data Types
    • Supported Programming Languages
    • Supported Data Ingestion and Data Access Methods
    • Access Control
    • OneLake Interoperability
  • Scenario-Based Decision Guide
  • Mirrored Azure SQL Database Versus SQL Database
    • Scenario 1: Web Application with Operational Data
    • Scenario 2: Big Data Containing Sensitive Information
  • The SQL Database in Fabric Versus Fabric Warehouse
    • Scenario 1: Aggregating Big Data for Analytical Reports
    • Scenario 2: Near-Real-Time Operational Reporting with Enforced Database Constraints
  • Direct Lake Versus Import Mode for Semantic Models
    • Scenario 1: Self-Service Business Intelligence with Power Query
    • Scenario 2: Near-Real-Time Reporting Requirement
    • Scenario 3: Resource-Consuming Data Refresh Process
    • Scenario 4: Using DAX Calculated Tables/Columns
    • Scenario 5: Using T-SQL Views
    • Scenario 6: Row-Level Security/Object-Level Security Enforced in the Warehouse/SQL Analytics Endpoint of the Lakehouse
  • All Roads Lead to OneLake, but Which One Is the Right Road?
  • Dataflow Versus Notebook Versus Pipeline Versus Mirroring Versus Shortcut
    • Scenario 1: Ingesting Data As Is from an On-Premises Data Source
    • Scenario 2: Customizing the Data-Writing Process
    • Scenario 3: Transforming Data for the Serving Layer
  • To V-Order or Not to V-Order?
  • Summary
• III. Working with Microsoft Power BI
• 10. Understanding Power BI Core Components
  • Power BI Desktop
    • Power BI Storage Modes
      • Import
      • DirectQuery
      • Direct Lake
    • Live Connection
    • Comparing Import, DirectQuery, Direct Lake, and Live Connection
    • The Languages of Power BI: Power Query, M, and DAX
  • The Power BI Service
    • Workspaces
    • Power BI Apps
    • Power BI Dashboards
    • Deployment Pipelines
    • Lineage and Impact Analysis
    • Governance
    • OneLake Catalog
  • Other Tools in the Power BI Ecosystem
    • Power BI Report Server
    • Power BI Report Builder
    • Power BI Mobile
    • Power BI Gateway
  • Copilot in Power BI
  • Power BI Cost Model
    • Workspace Type
      • My workspace
      • Power BI Pro (shared capacity) workspace
      • Power BI Premium Per User workspace
      • Power BI Premium workspace
      • Power BI Embedded workspace
      • Fabric workspace
      • Fabric Trial workspace
    • User Licenses
      • Pro license
      • PPU license
    • Workspace Types and User License Requirements
  • Summary
• 11. Design and Build Semantic Models
  • Getting Data
    • Connecting to a Lakehouse
    • Understanding Lakehouse Connection Options
      • Connect to OneLake
      • Connect to SQL endpoint
    • Creating a Direct Lake Model
  • The Model View
  • Relationships
    • Cardinality
    • Cross-Filter Direction
    • Active or Inactive
    • Assuming Referential Integrity
    • Creating the Rest of the Relationships
  • Measures
  • Hierarchies
  • Date Table
  • Column Properties
  • Opening the Model from the Fabric Portal
  • Creating a Direct Lake Model in the Fabric Portal
  • Creating a Simple Report from the Fabric Portal
  • Creating a Simple Report from Power BI Desktop
  • Import Models
  • DirectQuery Models
  • Direct Lake Fallback to DirectQuery
  • Summary
• 12. Enhance Semantic Models with DAX
  • Starting with Simple Measures (Aggregation Functions)
    • Basic Aggregation Functions
    • Aggregation Functions with an A Suffix
    • X Aggregation Functions (Iterators)
      • SUM versus SUMX when the expression is a single column
    • COUNTROWS and APPROXIMATEDISTINCTCOUNT
  • Understanding Context in DAX
    • Row Context
    • Filter Context
  • Introducing CALCULATE
  • Filter Modifier Functions
    • REMOVEFILTERS
    • ALL
    • ALLEXCEPT
    • KEEPFILTERS
  • Time Intelligence Functions
    • Year-to-Date (YTD)
    • Prior Year Comparisons
    • Percent Change Calculations
  • Relationship Navigation Functions
    • RELATED
    • RELATEDTABLE
    • USERELATIONSHIP
  • Table and Filter Functions
    • FILTER
    • VALUES
    • DISTINCT
    • SUMMARIZECOLUMNS
    • ADDCOLUMNS
  • Variables
  • Summary
• 13. Optimize Enterprise-Scale Semantic Models
  • What Makes a Model Enterprise-Scale
  • Memory Versus Compute, and Why Memory Is the Harder Problem
  • Whats Actually Using Your Memory: A Quick Tour of VertiPaq
  • Column-Level Optimizations: The Cheap Fixes First
    • Pattern 1: Remove What Nobody Is Using
    • Pattern 2: Reduce Cardinality
    • Pattern 3: Disable Unnecessary Attribute Hierarchies
    • Pattern 4: Optimize for Run-Length Encoding (RLE)
  • When Column-Level Fixes Arent Enough: Architectural Patterns
    • Pattern 5: User-Defined Aggregations
    • Pattern 6: Split the Model (the Master Model Pattern)
  • The Other Front: Refresh Memory
    • Pattern 7: Refresh Less Data with Partitioning
    • Query Folding: Make the Source Do the Work
    • Hybrid Tables: Incremental Refresh Plus DirectQuery
    • Pattern 8: Refresh Scale-Out
    • The Temporary Scale-Up Workaround
  • Direct Lake: Changing the Rules of the Game
    • Memory Residency States
    • Why the Delta Table Layout Matters
    • Update Patterns That Play Nicely (and Ones That Dont)
    • Maintenance: Dont Skip It!
  • Anti-Patterns: Things That Look Like Optimization but Arent
  • Summary
• IV. Maintaining an Analytics Solution
• 14. Implement Security and Governance
  • A Layered Access Security Model
  • Entra ID
  • Network Security
    • Inbound Protection
      • Workspace inbound protection
      • Tenant inbound protection
    • Managed Virtual Networks
    • Outbound Protection
      • Managed private endpoints
      • Trusted workspace access
      • Outbound data connection rule
  • Workspace Roles
  • Item Permissions
  • Data-Level Security
  • Data Encryption at Rest
  • Governance
    • Admin Portal
    • Microsoft Purview
    • OneLake Catalog
    • Admin Monitoring Workspace
    • Monitoring Hub
  • Summary
• 15. Manage the Analytics Development Lifecycle
  • Setting the Stage: Application Lifecycle Management, Continuous Integration, Continuous Delivery, and What Fabric Actually Gives You
  • Git Integration: The Continuous-Integration Half of the Story
    • Connecting a Workspace and the Initial Synchronization
    • Supported Items
    • Branches: The Mental Model That Actually Works
    • Branching Out to a New Workspace and Selective Branching
    • Compare Code Changes: Finally, a Real Diff Experience
    • Folders in Git
    • Permissions: Who Can Do What
  • Deployment Pipelines: The Continuous Delivery Half
    • Stages, Pairing, and Autobinding
      • Pairing
      • Autobinding
    • Full, Selective, and Backward Deployment
    • Deployment Rules: The Configuration Layer
    • The Permission Model
    • What Gets Copied and What Doesnt
  • Variable Libraries: The Parameterization Weve Been Waiting For
    • Creating a Variable Library
    • Value Sets: The Environment Switch
    • Consuming Variables in Other Items
    • The New Item Reference Variable Type
    • Variable Libraries Versus Deployment Rules: When to Use Which
  • Continuous Integration/Continuous Deployment Across the Data Engineering Surface
    • Notebooks and Environments
    • Data Pipelines
    • Dataflow Gen2
    • User Data Functions: Git Integration, Deployment, and the Moving Parts
      • Git integration for user data functions
      • Deployment pipelines for user data functions
    • Other Item Types
  • The Fabric Command-Line Interface: Automation from Your Terminal
    • The Filesystem Mental Model
    • Installing and Authenticating
      • Service principal with client secret
      • Service principal with federated credentials
      • Managed identity
    • The fab deploy Command: Continuous Integration/Continuous Deployment in One Command
    • Beyond Deployment
  • Putting It Together: A Reference Shape
  • Summary
• V. Putting It All Together
• 16. Semantic Link
  • Visualize Dependencies in the Semantic Model
  • Optimize Semantic Models with Best Practices Analyzer Rules
  • Translate Semantic Models
  • Migrate Existing Semantic Models to Direct Lake
  • Augment the Gold Layer
  • Summary
• 17. Create Your Dream Fabric Architecture
  • Project 1: A Logistics Group Goes from Spreadsheets to Fabric
    • Decision 1: The Silver Layer Should Be a Warehouse, Not a Lakehouse
    • Decision 2: Keep the Bronze Layer in a Lakehouse; Just Put It Next to the Warehouse
    • Decision 3: Workspaces Organized by Domain
      • Option A: A single workspace per environment
      • Option B: One workspace per medallion layer
      • Option C: One workspace per business subsidiary
      • Option D: Domain-organized workspaces with role-based separation
    • Decision 4: The Three-Capacity Model
    • Decision 5: Semantic Model StrategyWho Builds, Who Certifies, Who Consumes
    • What Wed Do Differently If We Started Over
  • Project 2: A Sports Organization That Loves Data
    • What Is SL Benfica, and Why This Case Study?
    • Choosing Fabric and the Operating Model
    • Ingestion: Standardize the Outcome, Not Every Connector
    • Real-Time Intelligence for Operations, Commercial Moments, and Sport
    • Gold Autonomy and Governed Distribution
    • Capacity, Security, and Delivery at Enterprise Scale
    • Outcomes, Lessons, and What Comes Next
  • Synthesis: A Way of Arriving at the Right Shape
    • Question 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: Whats the Freshness Requirement, Honestly?
    • Question 4: Whats the Blast Radius of a Bad Change?
    • Question 5: Whats the Constraint Thats Not on the Slide?
  • What Gets Decided First, What Stays Reversible
  • A Closing Thought
  • Summary
• A. Going Above and Beyond with External Tools
  • Understanding XMLA
  • DAX Studio
  • Tabular Editor
  • SQL Server Management Studio
  • Summary
• Index

pokaż cały spis treści

ukryj recenzje