Sign In Start Free Trial
Account

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Data Modeling with Microsoft Excel
  • Table Of Contents Toc
  • Feedback & Rating feedback
Data Modeling with Microsoft Excel

Data Modeling with Microsoft Excel

By : Bernard Obeng Boateng
4.6 (8)
close
close
Data Modeling with Microsoft Excel

Data Modeling with Microsoft Excel

4.6 (8)
By: Bernard Obeng Boateng

Overview of this book

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. Note: To access the supplemental material, subscribers should purchase a print copy of the book. The ebook can be accessed through the QR code or link provided inside the Print book. Proof of purchase is mandatory to access the ebook.
Table of Contents (16 chapters)
close
close
1
Part 1: Overview and Introduction to Data Modeling in Microsoft Excel
6
Part 2: Creating Insightful Calculations from your Data Model using DAX and Cube Functions
9
Part 3: Putting it all together with a Dashboard

Best practices with Power Pivot

To get the best out of your Power Pivot and data model, there are some best practices you need to adopt to ensure optimum performance. We discuss some of these best practices here:

  • Ideally, all datasets that are added to the data model should be named tables. This makes it easy to identify the tables when creating your DAX formulas.
  • Update your source data to limit the number of columns and rows you import into Power Pivot. This will improve performance and give you a better response for your calculations. You can achieve this by normalizing your data. We will discuss this in the next chapter.
  • Avoid creating calculations that shape and transform your data in Power Pivot. You can do all the data transformation and shaping in Power Query and then after, load it to Power Pivot. We will discuss Power Query in detail later in the book.
  • Use the Diagram view in View to get an overview of your datasets and how they connect to each other and the Data view to audit or explore the content of each dataset.
  • Ensure that the data type in each column is consistently formatted. For example, a column that contains dates should not have text input.

Sticking to these rules will greatly improve the performance of Power Pivot.

Create a Note

Modal Close icon
You need to login to use this feature.
notes
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected

Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Delete Note

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Edit Note

Modal Close icon
Write a note (max 255 characters)
Cancel
Update Note

Confirmation

Modal Close icon
claim successful

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY