What is a Data Cube?
A data cube is a live window into your database/dataset via Excel that allows for fast and efficient analysis. The cube will enable consolidation or aggregation of data for which you can then drill down, slice and dice the data to analyze at different angles and levels of granularity. The data cubes function similarly to a pivot table.
What is a Pivot Table?
A pivot table is a data summarization tool that can automatically sort, total or give the average of data stored in the Data Mart. A user can set up and change the data's structure by dragging and dropping fields graphically. This "rotation" or pivoting of the summary table gives the concept its name.
Working with the PivotTable Field List:
- Excel displays the PivotTable Field List so that you can add fields to the PivotTable, rearrange and reposition them as needed, or remove them from the PivotTable. By default, the PivotTable Field List displays two sections:
- A field section at the top for adding fields to and removing fields from the PivotTable.
- A layout section at the bottom for rearranging and repositioning fields.
- You can dock the PivotTable Field List to either side of the Excel window and horizontally resize it. You can also undock the PivotTable Field List, in which case, you can resize it both vertically and horizontally.
- If you don't see the PivotTable Field List, make sure that you click anywhere in the PivotTable.
- If you close the PivotTable Field List, you can display it again. Right-click the PivotTable, and then click Show Field List. You can also click Field List on the Ribbon (PivotTable Tools, Options tab, Show group for a PivotTable; PivotChart Tools, Analyze tab, Show/Hide group for a PivotChart).
- If you don't see the fields that you want to use in the PivotTable Field List, right click in the pivot table and select Refresh.
How the PivotTable Field List Works:
It's important to understand how the PivotTable field List works and the ways that you can arrange different types of fields so that you can achieve the results that you want when you create the field layout of a PivotTable or PivotChart report.
- Move a field to the Column Label area in the Field List, which simultaneously moves the field to the Column Label area in the PivotTable report.
- Move a field to the Row Label area in the Field List, which simultaneously moves the field to the Row Label area in the PivotTable report.
- Move a field to the Values area in the Field List, which simultaneously moves the field to the Values area in the PivotTable report. NOTE: the only fields you will be able to place in the values section are those listed within the 'Measures' section of the pivot table field list.
Remove fields from the PivotTable or PivotChart:
To remove a field, in the PivotTable Field List, do one of the following:
- In the respective layout area, left click and hold the dimension that you want to remove, and then drag it outside the PivotTable Field List.
- In the Pivot Table Field List, clear the check box of the field you want to remove.
- ***Note Clearing a check box removes all instances of the field from the report.
- In the respective layout area, click the down arrow on the dimension, and then click Remove Field.
Show/Hide Detail (Expand or Collapse Levels):
- Double-click the item that you want to expand or collapse.
- Click the expand or collapse button (plus sign, minus sign) next to the item that you want to expand or collapse.
- Right-click the item, click Expand/Collapse, and then do one of the following:
- To see the details for the current item, click Expand.
- To hide the details for the current item, click Collapse.
- To hide the details for all items in a field, click Collapse Entire Field.
- To see the details for all items in a field, click Expand Entire Field.
- To see a level of detail beyond the next level, click Expand To "".
- To hide to a level of detail beyond the next level, click Collapse To "".
How to Refresh the Report:
To refresh your pivot tables follow one of the steps outlined below:
-
- Within the EBM Office Bridge ribbon in Excel - select 'Refresh All Cube(s)'
- In the PivotTable Analyze tab, click refresh - refresh all
- You can also right-click the PivotTable, and then click Refresh
Add/Remove Subtotals:
You can display or hide subtotals for individual column and row fields, display or hide column and row grand totals for the entire report, and calculate the subtotals and grand totals with or without filtered items.
- In a PivotTable, select an item (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) of a row or column field. This displays the PivotTable Tools, adding the Options and Design tabs.
- On the Options tab, in the Active Field group, click Field Settings.
- In the Field Settings dialog box, under Subtotals, click Automatic. To remove subtotals, click None.
Display or hide grand totals:
- Click anywhere in the PivotTable report. This displays the PivotTable Tools, adding the Options and Design tabs.
- On the Design tab, in the Layout group, click Grand Totals, and then select the grand total display option that you want.
Freeze Panes:
- On the worksheet, do one of the following:
- To lock rows, select the row below the row or rows that you want to keep visible when you scroll.
- To lock columns, select the column to the right of the column or columns that you want to keep visible when you scroll.
- To lock both rows and columns, click the cell below and to the right of the rows and columns that you want to keep visible when you scroll.
- On the View tab, in the Window group, click the arrow below Freeze Panes.
- Do one of the following:
- To lock one row only, click Freeze Top Row.
- To lock one column only, click Freeze First Column.
- To lock more than one row or column, or to lock both rows and columns at the same time, click Freeze Panes
Best Practices:
Turn Off 'GetPivotData' functions: Excel automatically defaults to showing a long 'GetPivotData' reference for any formula built off of a pivot table rather than using the local cell references. It is recommended that this setting be turned off for easier navigation and audit of formulas within schedules. This function can be turned off by navigating to Excel Options --> Formulas ---> Working With Formulas--> uncheck the box for 'Use GetPivotData' functions for Pivot Table references. See below for a before and after.
Before:
After:
Set Default Pivot Table Settings to User Preference:
Within Excel Options > Data > Data Options > select 'Edit Default Layout' > select 'PivotTable Options...' you are able to set your default pivot table options to your preference. Common / recommended settings are outlined below.
1. Select your preference for when there are error values - e.g. zero, dash or blank
e.g. this is useful for managing divide by zero or other errors when utilizing calculated fields within a pivot table report
2. Select your preference for when there are empty cells - e.g. zero, dash or blank
3. Turn off 'Autofit column widths on update' to maintain formatting upon refreshing data cubes
4. To turn off the expand or collapse (+/-) buttons navigate to 'Display' and deselect 'show expand/collapse buttons'. This may give you a more presentation ready output.
5. If you would always like to see all attributes presented regardless of whether or not there is data within the boundaries you have selected in your current pivot table field list, it may be helpful to select the 'show items with no data on rows' or 'show items with no data on columns' found within the Display tab. This is often useful when you have similar tabs within a document using slightly different filtering that may cause a certain month or year to drop out if there is not data for the respective channel, customer, product category, etc.
Other Relevant Articles:
Cube Basics - Accessing & Modifying the Cubes
Comments
0 comments
Article is closed for comments.