Multidimensional Analysis also known as OLAP (Online Analytical Processing), or Pivot Data Analysis, or Cube Data Analysis is a powerful analysis tool that will help you explore, view and analyze spreadsheet data from different angles and perspectives (dimensions), find actionable insights and improve descriptive analytics. In this article we will use the terms pivot, multidimensional analysis, OLAP analysis and pivot analysis interchangeably.
Creating pivot tables for further analysis can be a complicated and time-consuming endeavor, especially if you have to export data from one modeling platform (e.g., a spreadsheet) to an analytics or data visualization platform.
Pivot tables and multidimensional analysis can be powerful tools that will help you with different tasks, from creating performance reports and portfolio assets analysis to aggregating accounting data.
Visyond brings to pivot and cube analysis its signature automated point & click approach you may have experienced in all Visyond modules.
If you organize your spreadsheet by worksheet (e.g., business units, department, countries, etc.), rows (e.g., revenues, costs, profits, etc.) and columns (months, quarters, years, etc.) you can click on the Pivot analysis tab, select the worksheets you want to see in the cube analysis and Visyond will automatically look at the row names and column names to construct an initial view.
Then you can drag, drop and filter to customize your view.
Visyond’s pivot table & multidimensional analysis can be used to:
- Summarize and group your data by categories (i.e. sales per country or number of orders per product type)
- Analyze spreadsheet data and identify trends
- Explore your data sets and find unobvious correlations as well as uncover ‘hidden’ insights about your business
- Present summarized data as reports or interactive dashboards that allow to slice & dice and drill-up & drill-down your data sets
Unlike other products, Visyond pivot tables/charts are synchronized with your spreadsheet in real time and do not require any additional processing, data migration to other external systems or data preparation before the changes in data appear in the pivot tables.
Besides the automatic creation of Pivot tables using worksheets, rows and columns, you can use columns to add extra dimensions to the analysis.
If you want to use columns as extra attributes (e.g., variable vs fixed costs, revenues or costs by product type, customer segment, region, etc.), select a free column or use an existing one in each of the worksheet.
Then, in the setting panel of the Pivot module, specify the attribute column and Visyond will show you this extra dimension so you can slice costs based on cost type.
Because Visyond’s pivot tables and charts are directly connected to the spreadsheet, you can have access not only to flat numbers (as in other database-driven BI) tools but also to calculations and assumptions behind the numbers which allows you to build forward-looking projections and forecasts. Any changes to the formulae in the model will immediately propagate to the cube you have built.
Below you will find a step-by step tutorial on how to create pivot tables in Visyond and perform multidimensional analysis of your data without long manual setups.
Prepare your data set or import it in Visyond
If you decide to build your spreadsheet from scratch, Visyond is compatible with Excel and features a familiar spreadsheet environment so you will be able to start working efficiently right away.
If you have an existing data set in Excel which you would like to import, see this article on how import your Excel file to Visyond.
Add column and row names for easier readability
Visyond supports human-readable names for formulas so you can easily identify the context behind values.
To learn more about natural names, see this article.
Make sure that repeated data is consistent (i.e. categories, location names, etc.)
Make sure that repeated data in your spreadsheet is consistent. For example, ‘US’ and ‘United States’ will be treated as separate locations.
Structure your worksheet to have consistent structures
For Visyond to process your data set and build a cube out of it, you might need to break it down into separate worksheets. For example, if you would like to create a pivot table/report that takes data from P&L statements across your company’s business units, then each of the worksheets might represent a different business unit.
For example, worksheet one will be ‘Accounting’, worksheet two will be ‘Operations’, etc.
Make sure that rows and columns structure is consistent. For example, if worksheet one lists data for current year in the second column, to properly aggregate data from all worksheets, each of them must have data for current year recorded in the second column. Rows will be indexed based on their natural names so, depending on the model, profits may end up on row 100 in worksheet “Accounting” and on row 50 in worksheet “Operations”.
Note that, if you are using natural names, they don’t have to be the same across all worksheets you want to aggregate. Visyond will process them even if they are different (i.e. ‘Revenue’ vs. ‘Revenues’) as long as values are on the same row and column, and will use the first name it encounters. If one of the worksheets does not have a name for the column or row, than Visyond will use the name it finds in the first worksheet.
Build Pivot Table in Few Clicks
After you’ve prepared your data set and made sure the structure of your data is consistent across all worksheets you want to use in your pivot tables, you can start creating the pivot table.
The process is quick is simple – go to the Report tab and select Pivot.
In the settings column on the left side, select worksheets you would like to use in your pivot table.
You can add custom dimensions to your pivot which you can use to filter, sort and slice & dice the data. To do this, simply specify columns that you would like to use separated by comma. For example, specifying ‘H’ would mean that the pivot will use column H as a dimension.
Select pivot calculation function
You can select among different mathematical functions that will be used to build the pivot. The most commonly used function is Sum.
Below is a list of available functions:
- Count Unique Values
- List Unique Values
- Integer Sum
- Sample Variance
- Sample Standard Deviation
- Sum over Sum
- 80% Upper Bound
- 80% Lower Bound
- Sum as Fraction of Total
- Sum as Fraction of Rows
- Sum as Fraction of Columns
- Count as Fraction of Total
- Count as Fraction of Rows
- Count as Fraction of Columns
Filter and organize your data
You can change the appearance of your pivot table by moving dimensions to rows and columns and organizing them in different order.
To include or exclude certain values from dimensions in pivot, click the arrow on to the right of the dimension name and select the values that you want to appear in pivot.
Change View Modes
You can view the data in your pivot table in different table modes or visualize it using a variety of charts:
- Table Barchart
- Row Heatmap
- Col Heatmap
- Horizontal Bar Chart
- Horizontal Stacked Bar Chart
- Bar Chart
- Stacked Bar
- Line Chart
- Area Chart
- Scatter Chart