FINANCE AND ACCOUNTING

INFORMATICS
Finance and Accounting
Informatics

Multidimensional data modelling and

2025

Business Intelligence (BI)

Video Lecture
Presented by: Gábor Dragonya
[email protected]

Created by: László SZÍVÓS Phd FCCA

[email protected]
 ERP
(Enterprise Resource Planning)
systems
Enterprise information systems of the 1980s

3
Advantages and disadvantages of implementing a new ERP

+ -
DATA INTEGRATION COMPLEX AND TIME-CONSUMING TASK

„ONE VERSION OF TRUTH” WITHIN THE

FEAR AND RESISTANCE AGAINST CHANGE
ORGANIZATION

REAL-TIME DATA ACCESS TECHNOLOGICAL COMPLEXITY

UNIFIED PROCESS AND DATA MODEL PROCESS MANGEMENT CAN BE HARMFUL

INDUSTRY-STANDARD BENCHMARK PROCESSES

4
Multidimensional data modelling
and
Business Intelligence (BI)
Why do we need a BI system if we have already
implemented an ERP?
MAIN GOAL OF AN ERP
ERPs help us to integrate all the different organizational functions of a company and to bring the
silo effect to an end by establishing a unified, centralized data architecture.

ENTERPRISE RESOURCE PLANNING BUSINESS INTELLIGENCE

• ERPs collect and process all data arising withing an • The main responsibility of a BI system is to analyze
organization. data and present information.
• ERPs help us to have more organized and effective • BI systems help us to interpret and understand the
business processes. performance of the company.
• ERPs mostly focus on what happened in the past. • BI systems place a high emphasis on the future and
on what is going to happen.

TRANSACTION-ORIENTED BUSINESS INSIGHT-ORIENTED 6

Business Intelligence

In 1958, IBM researcher Hans

Peter Luhn defined BI as “the
ability to apprehend the
interrelationships of presented
facts in such a way as to guide
action towards a desired goal”.

Peter Luhn, father of Business Intelligence

7
Business Intelligence

In 1989, Howard Dresner (later a Gartner

analyst) proposed business intelligence as an
umbrella term to describe "concepts and
methods to improve business decision
making by using fact-based support
systems." It was not until the late 1990s that
this usage was widespread.

Howard Dresner, Gartner analyst 8

Business Intelligence

Business intelligence (BI) is a set of methodologies, processes,

architectures, and technologies that transform raw data into
meaningful and useful information. It allows business users to make
informed business decisions with real-time data that can put.

Technologies used: data integration, data quality assurance, data

warehouses, masterdata management, text- data- and content mining

9
Data warehouse
Data warehouse is a subject-orientated, integrated, time variant,
non-volatile collection of data in support of management's decision-
making process.

Main charachteristics:
• Subject oriented
• Integrated
• Permament
• Time-dependency

William H. (Bill) Immon

father of data warehousing 10
OLAP vs OLTP

OLTP (Online Transactional Processing) is a type of data processing that

executes transaction-focused tasks. It involves inserting, deleting, or
updating small quantities of database data.

OLAP (Online Analytical Processing) performs multidimensional

analysis of business data and provides the capability for complex
calculations, trend analysis, and sophisticated data modeling.

11
Data warehousing
Data warehousing is the process of constructing and using a data
warehouse. A data warehouse is constructed by integrating data
from multiple heterogeneous sources that support analytical
reporting, structured and/or ad hoc queries, and decision
making. Data warehousing involves data cleansing, data
integration, and data consolidations.

1) Extract data from transactional databases

2) Transform data for data analysis
3) Analysis of data and presentation information for decision-
makers
12
Data warehouse - Architecture

OLTP OLAP
13
OLAP verus OLTP
CHARACHTERISTIC OLTP OLAP
Orientation Transaction processing Data analysis
Users Operational staff, administrators Decision-makers and analysts, financial
controllers
Tasks Record, extract and modify daily Business decision support
transactions
Database Relational database Subject-oriented, snowflake or start schema
Entity relationship diagram
Data Current state of data Past and current data
Level of aggregation Transactional data, not aggregated Aggregated data

View of data Relational Multidimensional

Users’ access Record and read Extract only, no modification in dataset

Scope Record and modification Process information

Number of records to Low Several million
be processed
Number of users High Low

Priority Reliability Flexibility, self-service

14
Types of data warehouse
1) Virtual 2) Centralized

15
Types of data warehouse
3) Two-tier 4) Hybrid

A data mart is limited to a single focus

for one line of business; a data
warehouse is typically enterprise-wide
and ranges across multiple areas.
16
Multidimensional data model
A multidimensional model views data in the form of a data-cube. A data
cube enables data to be modeled and viewed in multiple dimensions. It is
defined by dimensions and facts.
The dimensions are the perspectives or entities concerning which an
organization keeps records. For example, a shop may create a sales data
warehouse to keep records of the store's sales for the dimension time, item,
and location.
A multidimensional data model is organized around a central theme, for
example, sales. This theme is represented by a fact table. Facts are numerical
measures. The fact table contains the names of the facts or measures of the
related dimensional tables.
17
Multidimensional data model - data cube

Fact: SALES
Components of a data cube:
• Fact
• Dimension
• Dimension measure
• Dimension hierarchy
• Attribute
• Cell

18
Multidimensional data model - data cube
SALES

INVENTORY RECEIPT

SALES ORDER

Time Product Shop Campaign Warehouse Employee Customer

19
Multidimensional data model – snowflake
scheme

CAMPAIGN_DIM PRODUCT_DIM

Multidimensional
model: presenting
ORDER_FACT
business transactions in
SALESPERSON_DIM CUSTOMER_DIM
way that enables analysis

DAY_DIM

20
Multidimensional data model – star schema (1)
Star Schema in data warehouse,
in which the center of the star can
have one fact table and a number
of associated unnormalized
dimension tables.
It is known as star schema as its
structure resembles a star. The
Star Schema data model is the
simplest type of data warehouse
schema.
In all dimension tables there is a
simple (not composit) primary
key that appears as a foreign key
in the fact table. 21
Multidimensional data model – star schema (2)
Fact table holds the data to be
analyzed. The fact table describes
historical transactions that remain
constant.

The fact table usually contains huge

data volume.

Fact data is always read-only data that

cannot be modify in the data-cube.

There shall be at least one

summarisable value in each fact table.

22
Multidimensional data model – star schema (3)
Dimension table stores data about the
ways in which the data in the fact table
can be analyzed.

The dimension tables’ fields are used

as conditions in the data warehouse
queries.

In the star schema we improve query

performance by putting the query
criterion in denormalized dimension
tables.

23
Multidimensional data model – star schema (4)

Dimension tables have a surrogate key

column that is the primary key of that
dimension. A fact table may use these
dimension surrogate IDs as foreign
keys to the dimension table.

24
Multidimensional data model – snowflake
schema

Normalized

Snowflake Schema in data warehouse

is a logical arrangement of tables in a
Denormalized multidimensional database such that
the ER diagram resembles a snowflake
shape. A Snowflake Schema is an
extension of a Star Schema, and it adds
additional dimensions. The dimension
tables are normalized which splits data
into additional tables. 25
Multidimensional data model

SalesLoction (SLID, SLname, city, county, street,……)

Product (ProdID, ProdName, UnitPrice,…)
Sales (ProdID, SLID, date, quantity, category,…..)

Example: we would like to present the sales of the last

two month for a specific county.

26
Data cube operations
1. Slice and dice
2. Drill down
3. Drill up
4. Drill cross
5. Unfold
6. Fold
7. Pivot
27
Multidimensional data model - data cube

Components of a data cube:

• Fact
• Dimension
• Dimension measure
• Attribute
• Cell
• Dimension hierarchy

28
1. Slice and dice
The slice operation creates
a sub-cube by selecting a
single dimension from the
main OLAP cube.
The dice operation isolates
a sub-cube by selecting
several dimensions within
the main OLAP cube.

29
 2. Drill down
The drill-down operation
converts less-detailed data
into more-detailed data
through one of two
methods—moving down in
the concept hierarchy or
adding a new dimension to
the cube.

30
 3. Drill up
Drill up is the opposite of the
drill-down function—it
aggregates data on an OLAP
cube by moving up in the
concept hierarchy or by
reducing the number of
dimensions.

31
 4. Drill across
Sales + Customer
Sales Customer complaints complaints

+ =
Time Time Time

Product Product Product

The operation drill across reconciles cells from

several data cubes which share the same scheme.

32
 5. Unfold
Sales Location Sales Location

SALES SALES Time

- Value - Value
- Time

Product Product

33
 6. Fold
Sales Location
Sales Location

Time SALES
SALES
- Value
- VALUE - Date

Product
Product

34
Data warehousing in business
- Decision support: seeing aggregated data, trends and spotting
recurring or unusual patterns in data
- Data mining: is the process of analyzing hidden patterns of data
according to different perspectives for categorization into useful
information, which is collected and assembled in common areas, such
as data warehouses, for efficient analysis, data mining algorithms,
facilitating business decision making and other information
requirements to ultimately cut costs and increase revenue.
- Common data mining techniques: cluster analyses, classification and
regression trees, and neural networks
35
Data warehouse, data mart, data lake
DATA WAREHOUSE DATA MART DATA LAKE

A data warehouse is the core analytics A data mart is very similar to a data A data lake stores an organization’s raw and
system of an organization. The data warehouse. Like a data warehouse, the data processed (unstructured and structured) data
warehouse will frequently work in mart will maintain and house cleaned data at both large and small scales. Unlike a data
conjunction with an operational data store ready for analysis. However, unlike a data warehouse or database, a data lake captures
(OLTP) to ‘warehouse’ data captured by the warehouse, the scope of visibility is limited. anything the organization deems valuable for
various databases used by the business. future use.

• Stores large quantities of historical data so old • Focuses on one subject matter or business unit • Collects all data from many disparate data
data is not erased when new data is updated sources over an extended period
• Acts as a mini-data warehouse, holding
• Captures data from multiple, disparate aggregated data • Meets the needs of various users in the
databases • Data is limited in scope organization
• Works with ODS to house normalized, cleaned • It is uploaded without an established
• Often uses a star schema or similar structure
data methodology
• Reports and dashboards use the data from the
• Organized by subject • Processes and cleans data and stores it in the
data mart
data lake
• OLAP (online analytical processing) application
• The primary data source for data analytics
• Reports and dashboards use data from data
warehouses

36
A Gartner Magic Quadrant: Who are the leader vendors in Business
Intelligence?
Microsoft Power Bi (2013)

Tableau (2003)

QlikSense / QlikView (1993)

37
Implementation of BI systems
How does the information need of users change the
landscape of business analysis?

39
 Requirements for a successful BI implementation

FLEXIBLE
APPLICATION

INSIGHT CUSTOMI-
CENTRIC ZATION

BUSINESS USER
RELIABLE DATA
FOCUS

40
Five challanges of BI implementation projects
CHALLANGES

1. We don’t really know what we should

measure.

2. We have data but there is a huge uncertainty

about the reliability and consistency of data.

3. We have data and the BI platform is available

but there is no one who can write codes.

4. We don’t receive answers for our questions

in time.

5. The system is not scalable with the growth of

the organization.
 Changes in business reporting

Self-service
Action
oriented

Continuous
and instant
Dynamic

Histor-
ical data
Static
Business Intelligence EXTENDED AI BASED
Top (BI)
down

Traditional 42
 Definition of dashboards

„A dashboard is a visual display of the most important

information needed to achieve one or more objectives
that has been consolidated and arranged on a single
screen so the information can be monitored at a glance.„

Stephen Few
Operational dashboard: aims at tracking on-going operations in a real-time
manner
Analytical dashboard: aims at supporting decision-making based on historical
data
Strategic dashboard: aims at conveying information on the organiztaion as a
whole from the viewpoint of the key performance indicators.
Power BI
Traditional and modern management reporting

ERP
Processing in
Excel
CRM, SCM, MES (Pivot, Chart stb.)
.csv,
.xls,
.txt
Other
tables,systems Manual
data
extraction
in case of any update

Static reports that can only be updated if we go back to the „drawing board”.
Reports cannot be changed flexibly by users.
Reporting evolution at Microsoft: from Excel to Power BI

Manual
Pivot Power Power
reporting
tables Pivot Query
in Excel
Traditional and modern reporting

Dynimic reporting with real-time up-dates.

Reporting evolution at Microsoft: from Excel to Power BI

• Data import from a great variety of

sources.

• Built-in integration settings.

• Splendid visualization tools.

• Real-time report up-dates.

• RLS for securing data privacy and

confidentiality.

• Powerful analysis toolkit.

• Wide options for sharing reports.

 Excel versus Power BI: Which is better?
POWER BI EXCEL
Learning curve Not so easy to use (Power Query, Almost everyone knows it.
Power Pivot, DAX etc.)
Costs Power BI Desktop is free, however Part of Office 365
min. 10 EUR/user if we want to share
reports
Flexibility Low Almost everything is possible.
Visualisation Wide variety of visualisation. Only limited.
Dashboard High level (filters, slicers, reporting Limited (filters and slicers)
interactivity filters, dimension filters)
Size of data 1 GB – 100 GB 1 million row, 16 thousand
columns
Formula language DAX -
Sharing mobil, web, laptop, tablet e-mail
Thanks for
your
attention
Presented by: Gábor Dragonya
Q&A [email protected]

Created by: László SZÍVÓS Phd FCCA

[email protected]