Synetic College Logo
Unit 3 of 4 · M.Sc IT Sem 4

Unit 3: Multidimensional data modeling

Business Intelligence notes · PTU syllabus (PGCA1942)

3 min read5 topics9 exam questions
On this page
  1. Unit summary
  2. ER modelling vs multidimensional modelling
  3. Dimensions, facts, cubes, attributes and hierarchies
  4. Star and snowflake schemas
  5. Business metrics and KPIs
  6. Creating cubes using Microsoft Excel
  7. Key terms
  8. Quick revision
  9. Important questions

Unit summary

Multidimensional models let users analyse measures by any combination of dimensions. This unit covers data and dimensional modelling, ER vs multidimensional modelling, dimensions, facts, cubes, attributes and hierarchies, star and snowflake schemas, business metrics and KPIs, and building cubes in Excel.

After this unit you can

  • Compare ER and multidimensional modelling
  • Identify facts, dimensions, attributes and hierarchies
  • Design star and snowflake schemas
  • Define KPIs and build a cube in Excel

PTU syllabus topics

  • Data and dimension modeling
  • ER modeling vs multidimensional modeling
  • dimensions/facts/cubes/attributes/hierarchies
  • star and snowflake schema
  • business metrics and KPIs
  • creating cubes using Microsoft Excel
ClassificationMultidimensional model
Sales cube

  • Facts

    Measures: revenue, units

  • Dimensions

    Time, product, region

  • Hierarchies

    Year → quarter → month

  • Attributes

    Product colour, region manager

  • KPIs

    Targets for measures

1

Topic 1

ER modelling vs multidimensional modelling

ComparisonModelling
ER (normalised)
Multidimensional (dimensional)

Purpose

Transaction processing

Analysis and reporting

Structure

Many normalised tables

Fact table surrounded by dimensions

Queries

Complex joins

Simple, fast aggregations

Users

Developers

Business users

2

Topic 2

Dimensions, facts, cubes, attributes and hierarchies

Key termsDimensional concepts
Fact
Numeric measure of a business event (sales amount, quantity)
Grain
Level of detail of a fact row (one line item per day per store)
Dimension
Context for facts (date, product, store, customer)
Attribute
Descriptive field of a dimension (brand, colour)
Hierarchy
Levels within a dimension (day → month → quarter → year)
Cube
Facts viewed across multiple dimensions
Key termsFact types
Additive
Summable across all dimensions (sales)
Semi-additive
Not across time (account balance)
Non-additive
Ratios, percentages
3

Topic 3

Star and snowflake schemas

ComparisonWarehouse schemas
Structure
Pros and cons

Star schema

One central fact table linked to denormalised dimension tables

Simple, fast joins; some redundancy

Snowflake schema

Dimension tables normalised into sub-tables (city → state)

Less redundancy, easier maintenance; more joins, slower

Fact constellation (galaxy)

Several fact tables sharing dimension tables

Models several business processes (sales and shipping); complex

Example

Star schema for sales: fact table SALES(time_key, item_key, branch_key, location_key, units_sold, rupees_sold) with dimension tables TIME, ITEM, BRANCH and LOCATION.

4

Topic 4

Business metrics and KPIs

  • Metric: any measurement; KPI: a metric tied to a strategic goal with a target.
Key termsSMART KPIs
Specific
Clear definition
Measurable
Quantified
Achievable
Realistic target
Relevant
Linked to strategy
Time-bound
Period defined

Example

KPI: "Customer retention rate ≥ 85% per quarter" = (customers at end − new customers) / customers at start × 100.

5

Topic 5

Creating cubes using Microsoft Excel

ProcessExcel cube
  1. 1

    Load tables with Power Query

  2. 2

    Add them to the Data Model (Power Pivot)

  3. 3

    Create relationships between fact and dimension tables

  4. 4

    Define measures with DAX (Total Sales = SUM(Sales[Amount]))

  5. 5

    Insert a PivotTable from the Data Model

  6. 6

    Slice by dimensions; add hierarchies and slicers

  • Excel's Data Model behaves like an in-memory OLAP cube; the same skills carry over to Power BI.

Key terms

Grain
Level of detail of a fact table
Hierarchy
Ordered levels in a dimension
Semi-additive fact
Cannot be summed across time
KPI
Metric linked to a goal and target
DAX
Formula language of Power Pivot and Power BI

Quick revision

  • ER vs dimensional modelling.
  • Facts, grain, dimensions, attributes, hierarchies, cubes; fact types.
  • Star vs snowflake.
  • Metrics vs KPIs; SMART.
  • Power Query, Data Model, DAX measures, PivotTables.

Important exam questions

Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).

Short-answer questions

  1. Q1.Distinguish ER and dimensional modelling.
  2. Q2.What is grain?
  3. Q3.Give an example of a semi-additive fact.
  4. Q4.Distinguish a metric and a KPI.
  5. Q5.What is a hierarchy?
  6. Q6.How do you create a cube in Excel?

Long-answer questions

  1. Q1.Explain multidimensional modelling concepts.
  2. Q2.Explain star and snowflake schemas with an example.
  3. Q3.Explain business metrics and KPIs, and building a cube in Excel.

Stuck on this unit?

Message SBS on WhatsApp for help with Business Intelligence, or to ask about studying M.Sc IT at Synetic.

WhatsApp us