Data Modeling with Microsoft Power BI

Spis treści ebooka

• Foreword
• Preface
  • Who Is This Book For?
  • What Is Data Modeling?
  • What Is Power BI?
  • What Is So Special About a Power BI Data Model?
  • What Is DAX?
  • What Is Power Query?
  • What Is SQL?
  • A New Release Every Few Weeks
  • How to Read This Book
  • Installing Necessary Software
  • Additional Tools
  • Demo Files
  • Conventions Used in This Book
  • Using Code Examples
  • OReilly Online Learning
  • How to Contact Us
  • Acknowledgments
• I. Data Modeling 101
• 1. What Is a Data Model?
  • Data Model
  • Basic Components
    • Entity
    • Tables
    • Relationships
    • Primary Keys
    • Surrogate Keys
    • Foreign Keys
    • Cardinality
  • Combining Tables
    • Set Operators
    • Joins
    • Join Path Problems
      • Loop
      • Chasm trap
      • Fan trap
    • Entity Relationship Diagrams
  • Data Modeling Options
    • Types of Tables
    • A Single Table to Store It All
    • Normal Forms
    • Dimensional Modeling
    • Granularity
    • Extract, Transform, Load
    • Ralph Kimball and Bill Inmon
    • Data Vaults and Other Anti-Patterns
  • Key Takeaways
• 2. Building a Data Model
  • Normalizing
  • Denormalizing
  • Calculations
  • Flags and Indicators
  • Time and Date
  • Role-Playing Dimensions
  • Slowly Changing Dimensions
    • Type 0: Retain Original
    • Type 1: Overwrite
    • Type 2: Add New Row
    • Type 3: Add New Attributes
    • Type 4: Add Mini-Dimensions
    • Types 5, 6, and 7
  • Hierarchies
  • Key Takeaways
• 3. Real-World Examples
  • Binning
    • Adding a Column to a Fact Table
    • Creating a Lookup Table
    • Describing the Ranges of the Bins
  • Budget
    • Identifying the Granularity
    • Handling Fact Tables of Different Cardinality
  • Multi-Language Model
  • Key-Value Pair Tables
  • Combining Self-Service and Enterprise BI
  • Key Takeaways
• 4. Performance Tuning
  • Key Takeaways
• II. Data Modeling in Power BI
• 5. Understanding a Power BI Data Model
  • Data Model
  • Basic Concepts
    • Tables and Columns
    • Relationships
    • Primary Keys
    • Surrogate Keys
    • Foreign Keys
    • Cardinality
  • Combining Tables
    • Set Operators
    • Joins
    • Join Path Problems
    • Entity Relationship Diagrams
  • Data Modeling Options
    • Types of Tables
    • A Single Table to Store It All
    • Normal Forms
    • Dimensional Modeling
    • Granularity
    • Extract, Transform, Load
  • Key Takeaways
• 6. Building a Data Model in Power BI
  • Normalizing and Denormalizing
  • Calculations
  • Time and Date
    • Turning off Auto Date/Time
    • Marking the Date Table
  • Role-Playing Dimensions
  • Slowly Changing Dimensions
  • Hierarchies
  • Key Takeaways
• 7. Real-World Examples Using Power BI
  • Binning
    • Lookup Table
    • Range Table
  • Budget
  • Multi-Language Model
    • Dimension Table for the Available Languages
    • Visual Elements
    • Text-Based Content
    • Numerical Content
    • Data Models Metadata
    • UI of Power BI Desktop (Standalone)
    • UI of Power BI Desktop (Windows Store)
    • UI of the Power BI Service
    • UI of Power BI Report Server
  • Key-Value Pair Tables
  • Combining Self-Service and Enterprise BI
  • Key Takeaways
• 8. Performance Tuning in the Power BI Data Model
  • Storage Mode
  • Partitioning
  • Pre-Aggregating
  • Composite Models
  • Dual Mode
  • Hybrid Tables
  • Key Takeaways
• III. Data Modeling for Power BI with the Help of DAX
• 9. Understanding a Data Model from the DAX Point of View
  • Data Model
  • Basic Components
    • Tables
    • Relationships
    • Primary Keys
  • Combining Queries
    • Set Operators
      • UNION
      • INTERSECT
      • EXCEPT
    • Joins
      • NATURALLEFTOUTERJOIN
      • NATURALINNERJOIN
      • CROSSJOIN
    • Extract, Transform, Load
  • Key Takeaways
• 10. Building a Data Model with DAX
  • Normalizing
  • Denormalizing
  • Calculations
    • Simple Aggregations for Additive Calculations
    • Semi-Additive Calculations
    • Re-create the Calculation as a DAX Measure
    • Time-Intelligence Calculations
      • Explicit measures
      • Calculation groups
  • Flags and Indicators
    • IF Function
    • SWITCH Function
    • SWITCH TRUE Function
    • Lookup Table
    • Treating BLANK values
  • Time and Date
  • Role-Playing Dimensions
  • Slowly Changing Dimensions
  • Hierarchies
  • Key Takeaways
• 11. Real-World Examples Using DAX
  • Binning
    • Lookup Table
    • Range Table
  • Budget
  • Multi-Language Model
  • Key-Value Pair Tables
  • Combining Self-Service and Enterprise BI
  • Key Takeaways
• 12. Performance Tuning with DAX
  • Storage Mode
  • Pre-Aggregating
  • Aggregation-Aware Measures
  • Key Takeaways
• IV. Data Modeling for Power BI with the Help of Power Query
• 13. Understanding a Data Model from the Power Query Point of View
  • Data Model
  • Basic Components
    • Tables or Queries
    • Relationships
    • Primary Keys
    • Surrogate Keys
  • Combining Queries
    • Set Operators
    • Joins
    • Query Dependencies
    • Types of Queries
    • Extract, Transform, Load
  • Key Takeaways
• 14. Building a Data Model with Power Query and M
  • Normalizing
    • Column Quality
    • Column Distribution
    • Column Profile
    • Identifying the Columns to Normalize
    • Creating a Query per Dimension
    • Creating One Common Dimension Query
  • Denormalizing
  • Calculations
  • Flags and Indicators
  • Time and Date
  • Role-Playing Dimensions
  • Slowly Changing Dimensions
  • Hierarchies
  • Key Takeaways
• 15. Real-World Examples Using Power Query and M
  • Binning
    • Create a Bin Table by Hand
    • Deriving the Bin Table from the Facts
    • Create a Bin Table in M
    • Create a Bin Range Table in M
  • Budget
  • Multi-Language Model
  • Key-Value Pair Tables
    • Using the GUI
    • Using M Code
    • Writing an M Function
  • Combining Self-Service and Enterprise BI
  • Key Takeaways
• 16. Performance Tuning the Data Model with Power Query
  • Storage Mode
  • Partitioning
  • Pre-Aggregating
  • Key Takeaways
• V. Data Modeling for Power BI with the Help of SQL
• 17. Understanding a Relational Data Model
  • Data Model
  • Basic Components
    • Tables
    • Relationships
    • Primary Keys
    • Surrogate Keys
    • Foreign Keys
  • Combining Queries
    • Set Operators
      • UNION
      • INTERSECT
      • EXCEPT
    • Joins
      • INNER JOIN
      • OUTER JOIN
      • Anti-join
      • CROSS JOIN
      • SELF JOIN
      • EQUI-JOIN
      • NON-EQUI-JOIN
      • Natural joins
    • Join Path Problems
      • Loop
      • Chasm trap
      • Fan trap
    • Entity Relationship Diagrams
    • Extract, Transform, Load
  • Key Takeaways
• 18. Building a Data Model with SQL
  • Normalizing
    • Persisting into a Table
    • Creating a View
    • Creating a Function
    • Creating a Procedure
    • Creating a Filter Dimension
  • Denormalizing
  • Calculations
  • Flags and Indicators
  • Time and Date
  • Role-Playing Dimensions
  • Slowly Changing Dimensions
    • Type 0: Retain Original
    • Type 1: Overwrite
    • Type 2: Add New Row
  • Hierarchies
  • Key Takeaways
• 19. Real-World Examples Using SQL
  • Binning
    • Deriving the Lookup Table from the Facts
    • Generating a Lookup Table
    • Range Table
  • Budget
  • Multi-Language Model
  • Key-Value Pair Tables
  • Combining Self-Service and Enterprise BI
  • Key Takeaways
• 20. Performance Tuning the Data Model with SQL
  • Storage Modes
    • Table
    • Index
    • Compression
    • View
    • Function
    • Stored Procedure
  • Partitioning
  • Pre-Aggregating
  • Key Takeaways
• Epilogue
• Index

pokaż cały spis treści

ukryj recenzje