Definitions:
“Power Pivot is an Excel 2013 add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily. In both Excel and in Power Pivot, you can create a Data Model, a collection of tables with relationships. The data model you see in a workbook in Excel is the same data model you see in the Power Pivot window. Any data you import into Excel is available in Power Pivot, and vice versa.”
Importing Data
Right-click and drag this file to the documents folder below, then choose “copy here”. Choose “Manage” Button in the upper left of the screen under the Power Pivot menu (the right-most menu tab) Select Get External Data->From Database->From Access Choose browse, and pick the ContosoSales (in documents now), then click Next. Choose “Select from a list…” and click Next again.
Choose each table then click finish • Dbo_factSales will take a good deal of time to finish importing
Managing Data
Create Relationships between tables by going to Design->Create Relationship, and picking corresponding keys in FactSales The table DimProductSubcategory links to Dim product rather than DimFactSales In general, it should be fairly clear which columns need to be linked to each other. At least one of the columns needs to have no repeated values; it acts as the key column. For example, here we create a relationship between dbo_FactSales and dbo_DimChannel by linking the Channel Key column in one to the Channel Key column in the other. Once this is done, if we want to look up, say, the combined sales of all items from a particular sales channel, Excel can do so. Without the relationship, Excel couldn’t tell which products in the FactSales table sold in which channel
Diagram View
You can also manage relationships from diagram view by clicking on a column and dragging it to the appropriate column in the master table. • By default, the tables will be arrayed in a straight line along the top of the workspace. You may find it easier to move them so they take up a single screen, as I have done here.
Arranging Data
In DimDate click add column, and in the formula bar put “=month([DateDescription])” Title this column MonthNumber
Next, pick an item in the CalendarMonthLabel column and choose “Sort by Column”, choosing MonthNumber as your column to sort by
- Otherwise, trying to sort our data by month will do so alphabetically by month
Creating a Pivot Table
Finally, create a pivot table using this data by clicking the PivotTable icon and choosing where you want it to go. (In this case, since it should be a blank workbook, choose “Existing Worksheet” and cell $A$1.)
PowerPivot Table
We’ll make a pivot table to show total sales by year and month and by channel We can further use the “slicer” to dynamically filter the table Open the FactSales menu and drag total sales to value. By default you’ll see the sum; that is the total sales across all channels, stores, times, etc. You can change it to see the average sale, count of sales, or other statistics by clicking and choosing “Value Field Settings” • If some of these numbers seem weird, it’s because the data are made up You can drag as many fields as you want to values, or the same field multiple times (to see total and average, for example) Now drag “CalendarYearLabel” from DimDate to rows and “Channel Name” to columns Next, drag “CalendarMonthLabel” To columns You can click on the names of fields to move them up or down, or to different areas. Moving Months up will cause excel to sort by months first, then by year
Slicers
Finally, right click “Product Subcategory Name” and choose “Add as Slicer”. You can now filter your results by product subcategory. You may wish to resize your slicer tool and change the number of columns it admits, under Slicer Tools->Options. You can pick and choose one or multiple fields to filter your data through by ctrl or shift-clicking. Here I’ve chosen to show only the total sales amount for Boxed Games and Movie DVDs for some reason
To get in-depth knowledge on Power BI, you can enroll for live Power Bi training by OnlineITGuru with 24/7 support and lifetime access
Filters
Filters can serve a similar role as slicers but may be easier to use when we’re looking at too many items to use a slicer effectively. To get rid of the slicer, click the icon in the upper right to remove the filter then right click the slicer and choose “Remove Product -SubcategoryName”.
Two types of filters are label filters and value filters. Because PivotTables are poorly designed, you can’t have more than one of a single kind of filter on a single field. If you click on your pivot table, and choose Analyze under PivotTable Tools, then pick Options under PivotTable, you can at least have one of each kind of filter on a single field
Let’s add a value filter. Drag “ProductName” from dbo_DimProduct to the rows column, then mouse over it under dbo_DimProduct and click the little arrow to the right. Select “Between…” and choose 100000 and 110000. Depending on where you put the filter, the pivot table will show you only those items that have sold between these values over the whole period, per year, or per quarter.
Calculated Columns
You can add columns to your tables by going to the table (under the Manage button in the PowerPivot menu) and clicking adds a column, like the example with months. We’re going to determine what products have the highest ratio of price to cost. To start, click the column header and title it “Price/Cost”, then click on the formula bar. Note when writing formulas, you can click column headers to write them into the formula without having to type them in (like cells in Excel). So either uses this or just type “=[UnitPrice]/[UnitCost]” into the formula bar. You should now see the new column under dbo_DimProduct in Excel. Clear the pivot table and create a top ten value filter to see which items have the highest ratio. Alternatively, you can right-click one of the items on the list and choose sort highest to lowest, or you can do the same direction in the PowerPivot window. 16 Calculated Columns
We’d hope that each product name would be unique, so the “Sum” of Price/Cost over Product names would just return a single Price/Cost, but someone messed up compiling the data, so there’s one repeated product name. This could be solved in this instance by looking at the Max rather than the sum of Price/Costs.
