Multidimensional Analysis

An overview of multidimensional analysis using Pivot Table or Power BI Matrix - updated in 2024.
About Jack Zheng

Faculty of IT at Kennesaw.edu
pivot table dimension Data http Analysis Power BI Excel Multidimensional Query Drill Aggregate http www youtube Cube PivotTable Multidimensional Data
An overview of multidimensional analysis using Pivot Table or Power BI Matrix - updated in 2024.
About Jack Zheng

Faculty of IT at Kennesaw.edu
pivot table dimension Data http Analysis Power BI Excel Multidimensional Query Drill Aggregate http www youtube Cube PivotTable Multidimensional Data
Multidimensional Data
Query and Analysis
Jack G. Zheng
Spring 2024
http://zheng.kennesaw.edu/teaching/it7123
IT 7123 BI
Overview
• What is multidimensional data query and 
analysis?
• Key concepts and operations
• Exemplar analysis cases
2
Multidimensional Data Analysis (MDA)
• Multidimensional data analysis is based on the 
dimensional model (concepts of dimensions and 
measures)
– Review the concepts in module 3 data model
•
It is a kind of descriptive analysis based on historical 
data, with a focus on aggregated results (sum, count, 
average, etc.)
• Multidimensional queries can be conducted on 
spreadsheets (data table), data mart or any data model 
based on star schema, or special multidimensional 
database (OLAP database)
• MDA is one of the most important and common analysis 
and reporting method in business and other domains. It 
can also be the basis for more advanced analysis.
3
Multidimensional Query and Analysis
• Multidimensional analysis, or dimensional analysis, is a data 
analysis technique to assess data from different points of views
– Perspectives, or “dimensions” in a more technical term
– A dimension is a particular way (or an attribute) of describing and 
categorizing data
– Such queries are usually arithmetic aggregation operations (sum, 
average, etc.) on records grouped by multiple dimensions 
(attributes) at different aggregation levels.
– A pivot table or crosstab is usually used for OLAP result view 
(aggregated data)
• Example question
– "What is the total sales amount grouped by product line (dimension 
1), location (dimension 2), time (dimension 3) and … (other 
dimensions)?"
– "Which segment of business provides the most revenue growth?"
4
Descriptive and 
operational report
More open and 
exploratory analysis
Typical MDA Results Presentation
• Pivot table: Results are commonly presented in a two- dimension table, called
– Pivot table, PivotTable (Excel)
– Matrix (Power BI)
– Cross tab (Access)
– https://en.wikipedia.org/wiki/Pivot_table
– The table can then be part of a dashboard or a report
• Pivot chart
• Results can also be a single or an array of numbers from a direct query. Presentations can be 
customized (i.e. it does not have to be a table.)
5
Dimension
Pivot Table vs Flat Table
6
A pivot table or crosstab is usually 
used for multidimensional result view 
(aggregated data by dimensions).
Dimension
Dimension
Aggregated data by 
dimension members
Compare to a transactional 
data table with line-by-line 
data. It is a normal flat table 
without aggregates.
• Same data aggregation 
presented in Pivot Table 
vs regular table
• https://inforiver.com/blog/
general/table-and-
matrix-as-visuals-the-
same-but-different-use-
wisely/
7
Multidimensional Query in Action
• Query tool (and data): https://react-pivottable.js.org
• Analysis: is there a difference between men and women paying tips on different days?
• Query question: what is the total of tips paid by gender and day of week?
• Query result display: a cross-table showing sum of tips by gender (rows) and day of week 
(columns)
8
Dimension
Dimension
Presentation Style
Aggregate 
function
Measure
Filters
Quick Examples of Uses
• General insights at high level
– https://www.realtyincome.com/our-portfolio/portfolio-
diversification-overview
– https://datareportal.com/essential-facebook-stats
• Why pivot table (video)
– https://exceljet.net/plc/why-pivot-tables
• Demo: Explore Adventure Works in Excel by using an 
OLAP PivotTable report
– https://www.youtube.com/watch?v=v7fAjZxAtLI
• Quickly Discover Patterns and Trends in Your Data using 
PivotTables in Excel
– https://www.youtube.com/watch?v=LY883VM7_Ro – a 
webinar by Bill Jelen (Mr. Excel), demonstrating Pivot Table 
multidimensional analysis.
9
Why Pivot Tables (Crosstabs)
• Why pivot table (video)
– https://exceljet.net/plc/why-pivot-tables
• The first stage in the evolution of reporting is cross-tabulation. 
– “It is probably not an exaggeration to say that without the cross-tab, there 
would be no reporting, no Excel, probably not even multidimensional 
databases.” 
– This means that no BI or analytics software can do without them even 
today. Why? Because the cross-tab has one absolutely unbeatable feature: 
everyone can read them, intuitively, and without instructions.
– https://blogs.sas.com/content/hiddeninsights/2017/04/18/evolution-of-
reporting/
• Easy to use
– You can see all the totals and the details at a glance, which is the great 
strength of cross-tabs. 
• Dynamic
–
interactive filter option and with some scaling
– conditional formatting
10
MDA Query Concepts and Operations
• Cube
• Slice-and-dice
• Drill-up and drill-down
• Aggregate (Roll-up or Consolidate)
• Pivot/Rotate
11
Cube
• A cube is a presentation of the chosen measure with 
associated dimensions.
– Measure is the data item (fact) of interest: sales, cost, etc.
– Dimension is the characteristic of a measure: time, location, 
etc.
– Cube is a representation of certain view points.
• Cell
– A single data point that occurs at the intersection defined by 
selecting one member from each dimension in a multi-
dimensional structure.
• Data cubes aren't restricted to just three dimensions. 
Most systems can build data cubes with many more 
dimensions allows up to 64 dimensions. 
– In practice, we often construct data cubes with many 
dimensions, but we tend to look at just three at a time.
12
Cube Visualizations
13
2D Pivot Table view
3D cube view
1D relational table view
Slice and Dice
• Slice
– Slice is the act of picking 
subset of a cube by 
choosing a single value for 
one of its dimensions, 
creating a new cube with 
one fewer dimension
– Like a filter on one 
dimension
• Dice
– The dice operation 
produces a sub-cube by 
allowing the analyst to pick 
specific values of multiple 
dimensions
– Like multiple filters on 
more dimensions
14
Slice and Dice is a term for the user-initiated process of data browsing 
and analysis.
Slice and Dice in Pivot Table
• Slice
• Dice
15
Applying filter on one 
dimension.
Applying filter on more 
than one dimension.
Drill-Up/Down
• Drilling down or up is a specific analytical technique whereby the 
user navigates among levels of data ranging from the most 
summarized (up) to the most detailed (down) level.
• The drilling paths may be defined by the hierarchies within 
dimensions or other relationships that may be dynamic within or 
between dimensions.
16
Drill down along the 
product type dimension 
to see data by sub 
types.
Drill Up/Down in a Pivot Table
17
Drill down
Drill up
Drill down between 
dimensions (region 
and product)
Drill down along the 
hierarchy of the same 
dimension (region: 
country, state, city)
Pivot/Rotate
• To change the dimensional orientation of a report or page display. 
• Examples 
– swapping the rows and columns
– moving one of the row dimensions into the column dimension
– swapping an off-spreadsheet dimension with one of the dimensions in the 
page display (either to become one of the new rows or columns), etc.
18
Pivot/Rotate in Pivot Table
• To change the 
dimensional orientation of 
a report or page display. 
• Examples 
– swapping the rows and 
columns
– moving one of the row 
dimensions into the 
column dimension
– swapping an off-
spreadsheet dimension 
with one of the 
dimensions in the page 
display (either to become 
one of the new rows or 
columns), etc.
19
Pivot of the “Meal” 
dimension from rows 
to columns
Aggregate
•
Involves calculating a summary metric for a set of values.
• There are different ways of aggregation, called aggregate function/formula
– Sum, count, average, max, min, etc.
– Other aggregation defined by custom formula.
• Summation is most often used aggregate function. For example, sum of sales in 
each state.
– However, sum does not always applicable; it depends on the measure type
– Additive
• Can be added over all dimensions
• Example: sales amount
– Semi-additive 
• Cannot be added (but may be averaged) over some dimensions - typically time
• Example: inventory, enrollment, class size, account balance, etc.
– Non-additive
• Cannot be added over any dimension. Can use other functions like count or average.
• Example: product unit price, download speed, network quality, signal strength, ratios, etc.
• Accumulation query (particularly along the time): Year-to-Date, Month-to-Date
20
PivotTable Data Source
• Multidimensional query and analysis through 
pivot tables can use the following types of 
data sources 
– Spreadsheet or flat tables with attributes used 
as measures and dimensions (like Excel or 
Google Spreadsheet)
– In-memory casual data models based on 
star/snowflake schema (like Power BI/Tableau)
– highly structured data mart designed based on 
star or snowflake schema
– highly structured and specifically modeled 
multidimensional databases (OLAP server)
21
Common Analysis based on MDA
• Pattern or distribution analysis
– Comparison among members of a dimension: geographic/location, 
category, etc.
• Drill down analysis
– Strength or problem area (weakness) discovery
– Driving/impact (or differentiating) factor: sales growth factor
– Abnormity analysis
• Time dimension analysis
– Trend (time serial) analysis, forecasting
• Analysis along the time dimension
• Typical variations
– YTD accumulation analysis
– YoY growth analysis
• Slicing-based analysis
– Key products analysis
– Key time period analysis
22
Multidimensional Query Tools
•
Traditional OLAP is a typical client/server system. 
– An OLAP server (e.g. SSAS Multidimensional Model) is a high-capacity, multi-user data management 
engine specifically designed to support and operate on multi-dimensional data structures. It defines and 
prepares OLAP cubes, with measures, dimensions, and other data model elements.
– OLAP client applications can query and present MDA results.
• Newer generation of self-service BI tools has built-in engine to process multidimensional data.
•
In either way, user-facing client tools
– query the data model (server or local) and present results to users in various format (pivot tables, charts, 
etc.)
–
support user interactions, such as data exploration, sorting, custom calculation, user defined data, 
formatting, etc.
• Client query tools may be a general-purpose BI or spreadsheet program, or a more targeted 
special application.
– General-purpose 
• These clients work with various data sources and provide generic operations for all kinds of data and cubes
• Are usually off-the-shelf commercial products
• Example: Excel, SSRS, Cognos Analysis Studio
– Targeted system
• These clients work with a particular data source or data set, usually customized for a particular business domain
• Are usually developed in-house or provided as a special service
• Example: LexisNexis InsurView
23
MDA Tools
• Desktop applications
– Power BI Matrix Visual
– Tableau Table
– Excel Pivot Table (and third party embedded components in Excel)
• Power BI Service Dataset 
– Explore this data
– Analyze in Excel
• Web applications and portals
– Cognos, OBIEE
– http://www.pyramidanalytics.com
– http://reportportal.com
– Tableau cloud
– SharePoint services
• Programming libraries and components
– https://pivottable.js.org
– https://react-pivottable.js.org/
– https://js.syncfusion.com/demos/jquery/#!/bootstrap/pivotgrid/relational
– https://demos.devexpress.com/ASPxPivotGridDemos/Default.aspx
– ADOMD.Net https://docs.microsoft.com/en-us/bi-reference/adomd/developing-with-adomd-
net
24
Conduct MDA using Power BI
• Power BI Matrix Visual
– See examples and lab 4
• Power BI Service Dataset provides two ways
– Explore this data (a web-based tool for quick MDA 
query)
– Analyze in Excel (then using the Excel Pivot Table)
• Power BI third party components
– https://appsource.microsoft.com/en-
us/product/power-bi-visuals/wa200002526
• Excel connects to Power BI Dataset
– Using Pivot Table (or other third party embedded 
components in Excel)
25
Core Readings and Resources
• Dimensional query and pivot table
– Why pivot table (video): 
https://exceljet.net/plc/why-pivot-tables
– Demo: Explore Adventure Works in Excel by using an OLAP 
PivotTable report https://www.youtube.com/watch?v=v7fAjZxAtLI
• Power BI features
– https://k21academy.com/microsoft-azure/data-analyst/table-and-
matrix-visualization-in-power-bi/
• Power BI: Table vs Matrix
– https://inforiver.com/blog/general/table-and-matrix-as-visuals-the-
same-but-different-use-wisely/
– https://medium.com/@raghu.949/power-bi-table-vs-matrix-
9512c2da90ab
• Use the matrix visual in Power BI
– https://learn.microsoft.com/en-us/power-bi/visuals/desktop-matrix-
visual
26
Additional Good Learning Resources
• Business Intelligence: Multidimensional Analysis 
https://www.youtube.com/watch?v=IhFkNmVmwn4
• Drill-through and drill-across https://www.dundas.com/resources/blogs/introduction-to-business-
intelligence/drilling-into-the-differences-between-drill-down-drill-through
• Excel pivot table
– https://pragmaticworks.com/blog/advanced-pivot-table-enhancements-in-excel
– https://edu.gcfglobal.org/en/excel2016/intro-to-pivottables/1/
– https://edu.gcfglobal.org/en/excel2016/doing-more-with-pivottables/1/
– Excel Dashboards – PivotTables 
https://www.tutorialspoint.com/excel_dashboards/excel_dashboards_pivot_tables.htm
– Power PivotTables & Power PivotCharts: 
https://www.tutorialspoint.com/excel_dashboards/excel_dashboards_power_pivot_tables_and_charts.ht
m
•
https://myabcm.com/how-can-multidimensional-analysis-help-the-company/
• Pivot table http://en.wikipedia.org/wiki/Pivot_table
• Quickly Discover Patterns and Trends in Your Data using PivotTables in Excel 
https://www.youtube.com/watch?v=LY883VM7_Ro – a webinar by Bill Jelen (Mr. Excel), 
demonstrating Pivot Table multidimensional analysis.
• BI capabilities in Excel and Office 365 https://support.office.com/en-us/article/bi-capabilities-in-
excel-and-office-365-26c0548e-124c-4fd3-aab3-5f64568cb743
27