Good decisions are made when data is converted into meaningful information

What is The Data Model (aka Power Pivot)?

Quite simply it’s an amazing utility that is built into Excel and Power BI Desktop that everyone should know about.

Plain Speaking:  On our Power Query page we described Power Query as the world’s greatest washing machine that is capable of taking data from multiple locations and cleaning it up ready for analysis.

So if Power Query is a washing machine, The Data Model is the worlds’ greatest data wardrobe!

• You simply load your clean data into the Data Model and then connect the relevant pieces together using drag and drop.
• Reporting is then a breeze as you can access all the tables of data in the Data Model and use drag and drop to create your report.
• Finally you can add filter buttons to present different cuts or slices of your information.

Imagine a wardrobe where you can click a button for “Evening Wear” and it just displays those required items. You can then click a different button and hey presto… beachwear!

 

Technical Talk:   The Data Model is a powerful Data Analysis Engine built into Excel 2016 and Excel 2013.   The Data Model is designed to provide fast and flexible reporting.  By building calculations (DAX measures) into the Data Model you can begin to turn your data into actionable information.  The Data Model is also the engine in Power BI Desktop.

Key advantages of The Data Model in Perth

• Using Power Query you can load your data into the Data Model as tables of clean data.
• Then you can simply use drag and drop to connect the tables together by their common fields.  NO MORE VLOOKUPS!
• After all of your data is connected together you can start to create basic measures and KPIs. For example Variances, Growth rates, Product and Channel Mix, Margins, Profitability, the list is endless.
• In the same way Excel provides formulae as building blocks for complex reporting, so DAX formulae allow you to compose amazing measures to highlight business performance.  e.g these are just two of the hundreds of built in DAX formulae:  =YEARTODATE , =SAMEPERIODLASTYEAR.
• Once you’ve mastered those then these DAX formulae open up an endless list of possibilities.

How much data can The Data Model handle?

LOTS!

Power BI Desktop is 64 bit by default so can access all the RAM on your Machine, so the amount of data you can process quickly is limited only by the amount of RAM.

8GB is OK and you can do a lot (hundreds of thousands and potentially millions of rows of data).  We’d recommend 16GB RAM to really start crunching those numbers quickly.

Excel works best if you have the 64 bit version and then you are able again able to access all the available RAM on your machine (whatever is left after other applications and Windows use up their chunk).  16GB RAM is recommended.

Excel 32 bit (historically the most common version) is not as robust or powerful and is limited to accessing 4GB RAM on your machine.  Ask your IT team to change over to 64 Bit Office now!

Performance on a 32 bit machine may struggle with 1 million rows of data, but it really depends on the data structure and complexity of connections and calculations.

Power BI Desktop:

The Data Model is also built into Power BI Desktop so by learning it you can immediately apply your knowledge to another software solution.

Benefits of Power Pivot in Perth

Our clients often experience benefits such as

• Robust and flexible reports that allow them instant insight into different slices of business information.
• They have the ability to compare KPI performance between periods instantly.
• They can click a simple single REFRESH button to update reports within seconds.
• They are able to make better decisions that are based on facts, rather than opinions.

What other sources of information are available?

Data Model training  | Data Model articles  | Data Model books