Do I really need to learn PowerPivot?

This is a question you might asked to yourself. Why should I learn powerPivot? Well yes or no both are correct. Then again question is remain standing, what shall I do? Let me help you out with this question. There are many factors need to understand before going to answer this question. 

• First you must understand what is powerpivot?
• Second question might be how many tools insight in it?
• What is a learning curve?
• I am from finance, do I have to learn IT stuff, I think I am focusing on different direction?
• Does it has any weight in my resume or any add-in to my business?

Let us deal each question one by one and based on given insight we will remedy our big question.

What is PowerPivot?

PowerPivot is an add-in for Microsoft Excel since version 2010 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts, and then further analyze the data so that you can make timely business decisions without requiring IT assistance. 

The given definition raise more questions, for example.

• How many rows the PowerPivot supports?
• Creating relationship in data, I do not know what is this?
• Different type of data can be related?
• I know calculated column but what is measures using formulas?
• And if at the end I have to use pivot table then why PowerPivot?

How many rows the PowerPivot supports?

Microsoft excel team has done excellent work to make Excel as business intelligence (BI) tool. To do so they have to improve the data capacity in Excel (approximately 2 billion rows). In normal Excel you can have 1 million records in one sheet. And if you have more than million records then you have to leave Excel and may be use Microsoft access or any other database. Excel introduce two tools, a) Power Query b) PowerPivot.  We already discussed PowerPivot now, let us understand Power Query.

Power Query is an Excel add-in that can be used to extract data from many different data sources, clean, transform and aggregate that data if required, and finally load the results into a table on the worksheet, or into a table in the Excel Data Model. Each job, or unit of work, in Power Query is called a query. A query is made up of one or more steps which can connect to a data source, or to apply changes to that data. A workbook can contain multiple Power Query queries.

With Power Query, you can

Find and connect data across a wide variety of sources.

• Merge and shape data sources to match your data analysis requirements or prepare it for further analysis and modeling by tools such as PowerPivot.
• Create custom views over data.
• Perform data cleansing operations.
• Import data from multiple log files.
• Create a query from your Facebook likes that render an Excel chart.
• Pull data into PowerPivot from new data sources, such as XML, Facebook, and File Folders as refreshable connections.

Power Query Data Sources

• Web page
• Excel or CSV file
• XML file
• Text file
• Folder
• SQL Server database
• Microsoft Azure SQL Database
• Access database
• Oracle database
• IBM DB2 database
• MySQL database
• PostgreSQL Database
• Sybase Database
• Teradata Database
• SharePoint List
• OData feed
• Microsoft Azure Marketplace
• Hadoop File (HDFS)
• Microsoft Azure HDInsight
• Microsoft Azure Table Storage
• Active Directory
• Microsoft Exchange
• Facebook

Different type of data can be related?

This is an interesting point. Power Query allow you to perform ETL process. Extract, transform and load into power pivot. Excel is give many option to download data or connect data in multiple sources. For example CVS/txt file, Microsoft access database, SQL server database, oracle, web pages, online internet databases for example azure, sharepoint, social media like facebook.

Creating relationship in data:

Well this is not very technical, it is an everyday links we are using in our daily life. Like one doctor has many patients, this is called one to many (1 – m) relationship. Similarly one patient has many issues that is why the patient has to contact different doctors for each issue. This is called many to many (m – m) relationship.

In Excel each data has unique value this uniqueness create a key. For example in a country table this is country code. Similarly if you see a customer profile, you will find his country and customer code. Now to connect with country table you use country field in the both data and create unique key as customer. When you have sales data you must have customer code in it and to connect with customer table you use it. It will be dame easy when you see the diagram.

Now the next logical question arise if I upload that much data from many places what will be my machine performance, while I am not using latest hardware?

Indeed this is a very relevant point to understand because in my experience and with many others as well, if you load huge data in Excel then the application take most of the RAM and consequently the machine performance degraded. Well the PowerPivot comes with xVelocity mini server in the memory. On average, you can expect a data model to be 7 to 10 times smaller than the same data at its point of origin. For example, if you’re importing 7 MB of data from a SQL Server database, the data model in Excel could easily be 1 MB or less. The degree of compression actually achieved depends primarily on the number of unique values in each column. The more unique values, the more memory is required to store them.

What is measures and formulas?

Measure

A measure is a formula that is created specifically for numeric data that you want to summarize or analyze in a PivotTable, PivotChart, report that uses PowerPivot data. Measures can be based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX.

Measures are either implicit or explicit, which affects how you use them in a PowerPivot workbook, and in other applications that use PowerPivot data.

Implicit Measure

An implicit measure is created by Excel when you drag a field, such as Sales Amount, to the Values area of the PowerPivot Field List. Because implicit measures are generated by Excel, you might not be aware that a new measure has been created. But if you examine the Values list closely, you will see that the Sales Amount field is actually a measure named Sum of Sales Amount and appears with that name in both the Values area of the PowerPivot Field List, and on the PivotTable itself.

Implicit measures can only use a standard aggregation (SUM, COUNT, MIN, MAX, DISTINCTCOUNT, or AVG), and must use the data format that is defined for that aggregation. In addition, implicit measures can only be used by the PivotTable or chart for which they were created.

An implicit measure is tightly coupled with the field upon which is it based, affecting how you delete or modify the measure later.

Explicit Measure

An explicit measure is created by you when you type or select a formula in a cell in the Calculation Area, or when you click New Measure in the PowerPivot ribbon.

Explicit measures can be used by any PivotTable or chart in the workbook. Moreover, they can be extended to become a KPI, or formatted using one of the many formatting strings available for numeric data. Context menu commands for Create KPI and Format are only available when you are using an explicit measure.

At the End I am again using Pivot table then why PowerPivot?

Pivot table is a data summarization tool. With Powerpivot where we define the data model we can utilize BI power. Here you can utilize the dimension, using multiple source of data for pivoting reports.

Now I confuse you again, what is dimension? A Data Dimension is a set of data attributes pertaining to something of interest to a business. Dimensions are things like "customers", "products", "stores" and "time". For users of Data model, data dimensions are entry points to numeric facts (e.g. sale, profit, revenue) that a business wishes to monitor.

What is a learning curve?

Well this depend upon individual time and excel background. DAX you may learn basic and then need to utilization basis. With Basic DAX, Power Query and Pivot is a two to six hours course. But if you are a good learner then I am sure you might cover basic understanding less than two hours. If you are a daily user of excel data analysis then you might develop expertise in the area within a month.

The last dilemma I am not an IT professional should I learn and focus on this new technology?

Well, if you are performing an everyday data analysis and you need to improve your efficiency and effectiveness then you must learn. But if you are at the consuming area like you are just reviewing the dashboard, and reports then it may not be for you.

Last advise for students, you must learn new excel reason being this is a present technology. You have to learn the new technology either you are from finance or non IT background.

Conclusion:

Let conclude, it is clear now that this tool has good return of our time because of two reasons a) you have not to buy any expensive tool to perform data analysis b) It is a great start for manage huge data and create efficiency and effectiveness.

If you are interested to learn PowerPivot and Power Query, please show me you interest in comments box.

Note: many of the definition I presented in my article took from several websites.