Why use Excel Data Model (Power Pivot)

Excel Data Model has been there since 12 years. Despite that, it is not known to majority of Excel users. Even those who know about it do not use it fully. Here is a video which explains the importance and benefits of using Data model instead of importing data into Excel sheets.

Why use Excel Data Model: Video

Video Contents

1. Introduction (the problem) – 00:00
2. The solution – data model – 00:17
3. Benefits of data model – 00:46
4. Import data using Get and Transform (Power Query) – 00:57
5. View the data model – 02:22
6. Import one more table – 02:43
7. Create relationship (instead of Vlookup) – 02:59
8. Create Pivot Table from Data Model – 03:24
9. Create Measures – 03:51
10. Using Measures – 05:25
11. Importing more than million rows – 05:42
12. Check large data Pivot Performance – 06:36
13. Add local table to data model – 08:41
14. Related topics – 09:25
15. Summary – 09:47

Disadvantages of using Excel sheets for data import

1. File size increases as data grows
2. Everything becomes slows as data grows (open, close files, sorting/filtering, recalc, pivots, etc.)
3. If data has more than 1 million rows, we have to import it in pieces and consolidate the output manually
4. Adding formulas like VLOOKUP makes it even more slow
5. Lot of time is wasted in waiting for Excel to complete operations

Advantages of Excel Data Model

1. It is built into Excel. The file is still an XLSX file.
2. There is no row limit. Millions of rows can be managed easily.
3. Even if the data size is large, the file size remains small (due to excellent compression)
4. You can eliminate VLOOKUP by using Relationships in Data Model
5. Data model based pivot tables and charts are extremely fast
6. There is no hardware upgrade required to handle the same data which makes a regular Excel file sluggish.
7. If you use DAX, complex and powerful calculations can be done quite easily.
8. Date based calculations (TIME Intelligence) is extremely powerful and easy using DAX
9. Power BI can import data model from Excel. Therefore, you can reuse the data model and create Power BI reports without duplication of effort.