| Description |
1 online resource (267 pages) |
| Note |
Includes index. |
| Summary |
Microsoft Excel's BI solutions have evolved, offering users more flexibility and control over analyzing data directly in Excel. Features like PivotTables, Data Model, Power Query, and Power Pivot empower Excel users to efficiently get, transform, model, aggregate, and visualize data. Data Modeling with Microsoft Excel offers a practical way to demystify the use and application of these tools using real-world examples and simple illustrations. This book will introduce you to the world of data modeling in Excel, as well as definitions and best practices in data structuring for both normalized and denormalized data. The next set of chapters will take you through the useful features of Data Model and Power Pivot, helping you get to grips with the types of schemas (snowflake and star) and create relationships within multiple tables. You'll also understand how to create powerful and flexible measures using DAX and Cube functions. By the end of this book, you'll be able to apply the acquired knowledge in real-world scenarios and build an interactive dashboard that will help you make important decisions. |
| Contents |
Cover -- Title Page -- Copyright and Credits -- Foreword -- Contributors -- Table of Contents -- Preface -- Part 1: Overview and Introduction to Data Modeling in Microsoft Excel -- Chapter 1: Getting Started with Data Modeling -- Overview and Importance -- Understanding the concept of data modeling -- Comparing a one-table analysis to multiple-table analysis in Microsoft Excel -- Practical use cases for a data model -- The accountant -- The salesperson -- Introduction to Power Pivot, Excel versions, and installation -- How do I install Power Pivot? -- Exploring the features of Power Pivot |
|
What is DAX? -- Best practices with Power Pivot -- Summary -- Questions for discussion -- Chapter 2: Data Structuring for Data Models -- Data structuring -- understanding the three golden rules -- Understanding data redundancy -- Problems caused by data redundancy -- What is data normalization? -- Denormalized and normalized data -- Understanding table relationships -- One-to-many relationship -- Many-to-many relationship -- One-to-one relationship -- Understanding dimension and fact tables -- Fact table -- sales -- Dimension table -- product -- Dimension table -- customer -- Dimension table -- date |
|
Understanding the role of primary keys and foreign keys -- Summary -- Key terms in this chapter -- Questions for discussion -- Chapter 3: Preparing Your Data for the Data Model -- Cleaning and Transforming Your Data Using Power Query -- Understanding connections and queries -- Connections -- Queries -- An overview of the Power Query editor -- Getting your data type right -- Add Column or Transform? -- Merging and appending data using Power Query -- Summary -- Chapter 4: Data Modeling with Power Pivot -- Understanding How to Combine and Analyze Multiple Tables Using the Data Model |
|
Adding queries/tables to your data model -- Adding columns to your data model -- Creating calendar tables -- Creating relationships using primary and foreign keys -- Understanding the different types of schemas (snowflake and star) -- Creating hierarchies -- Hiding fields and tables from client tools -- Summary -- Section 2: Creating Insightful Calculations from your Data Model using DAX and Cube Functions -- Chapter 5: Creating DAX Calculations from Your Data Model -- Introduction to Measures and Calculated Columns -- DAX as a calculated column or measure |
|
Creating your first measure -- where to go -- Common DAX functions (time intelligence, FILTER, CALCULATE, and so on) -- Understanding row and filter contexts -- Editing your DAX formulas -- Summary -- Chapter 6: Creating Cube Functions from Your Data Model -- a Flexible Alternative to Calculations in Your Data Model -- What are cube functions? -- When do we use cube formulas? -- Exploring cube functions in Microsoft Excel -- CUBEVALUE -- Adding slicers to your cube formulas -- CUBEMEMBER -- CUBESET -- CUBESETCOUNT -- CUBERANKEDMEMBER -- Spilling cube functions with dynamic array formulas -- Summary |
| Subject |
Microsoft Excel (Computer file)
|
|
Data structures (Computer science)
|
|
Electronic spreadsheets -- Computer programs.
|
|
Structures de données (Informatique) |
|
Tableurs -- Logiciels. |
|
Electronic spreadsheets -- Computer programs |
| Other Form: |
Print version: BOATENG, BERNARD OBENG. DATA MODELING WITH MICROSOFT EXCEL. [Place of publication not identified] : PACKT PUBLISHING LIMITED, 2023 1803240288 (OCoLC)1348138711 |
|
More Resources