Microsoft Power Pivot = Combine and Analyse your data
Good decisions are made when data is converted into meaningful information
What is Microsoft 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, Power Pivot is the worlds’ greatest data wardrobe!
- You simply load your clean data into Power Pivot 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 Power Pivot 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: Power Pivot is a powerful Data Analysis Engine built into Excel 2016 and Excel 2013. Power Pivot is designed to provide fast and flexible reporting. By building calculations (DAX measures) into Power Pivot you can begin to turn your data into actionable information. The Power Pivot Data model is also the engine in Power BI Desktop.
Key advantages of Microsoft Power Pivot in Perth
- Using Power Query you can load your data into Power Pivot 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 Power Pivot handle?
If you have a machine with 64 BIT Office installed then Power Pivot is only limited by the amount of RAM on your machine.
Millions of rows of data can be easily processed. We’ve worked on files with 4 million rows, but billions of rows are possible.
Technical Talk: If you have 32 BIT office installed then you are limited to 4GB of RAM in the latest version of Excel 2016.
Performance on 32 BIT machines may start to struggle with 1 million rows of data. It really depends on the structure of your data and complexity of connections and measures.
Compression: For those of you that care about file sizes the compression capabilities of Power Pivot are exceptional. A data set that would make old Excel 100MB in size could be shrunk to less than 2MB depending on data structure.
Power BI Desktop:
Power Pivot 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.
“Jeff and his team at Access Analytic have the ability to take complicated information and make it meaningful and useful. They know more about using Excel than anyone I know and they can either do the analysis for you, build tools so you can access the information easily or train you to do it yourself. If you need to better understand the numbers, then Jeff can help.” David Beard, Keynote Speaker, Consultant & HR Specialist
If you’d like to see how your organisation could benefit, we can help you achieve this quickly and at a very reasonable investment that will pay for itself in a short period of time.
What could these kinds of discoveries mean for your business?
Take the next step and model your data seamlessly today with Microsoft Power Pivot Perth.
Contact us on +61 8 6210 8500 or by email to arrange a confidential meeting or leave your details below.
What other sources of information are available?