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