Transcript Table 1. DWH vs OLTP:

Multidimensional Data Model for Marketing Information System
Zlatinka Svetoslavova Kovacheva
Centre for Information Technologies in Communications of
Bulgarian Telecommunications Company (BTC)
ABSTRACT
The present talk deals with the basic moments in the process of design and development of the
Marketing Information System (MkIS) of BTC. The MkIS analyses the evolution of marketing
indicators such as capacity, usage, revenue of the services, etc. on the base of monthly information
from BTC regions.
The Multidimensional data model design is considered. The program environment for developing
the model is based on the Data Warehouse technology and includes OLAP (On-Line Analytical
Processing) tools for structuring and analysis of the data into multidimensional arrays. This model
provides representation of the information in a lot of interconnected tables and graphs, which can be
viewed in different aspects according to the defined dimensions and their hierarchical levels. Data can
be easily aggregated, disaggregated and rotated according to the requirements of the experts and
managers. A generation of ad-hoc reports is available. It provides the users a fast direct access to that
part of the comprehensive data structure, which is useful for their concrete purposes.
One of the most interesting features of the multidimensional data model is what-if-analysis. It
provides managers creating hypothetical situations by changing the values of variables in the
multidimensional data model. These changes are temporary and concern all formulas including
corresponding variables. This is the way to observe the influence of changing some parameters to
other ones. It is particularly important for the marketing decision making.
Another advantage of the model is forecasting facility. The following basic forecasting methods
are available: linear trend, exponential trend, single, double and triple exponential smoothing,
percentage change, moving average, Holt-Winters.
The Multidimensional data model provides a powerful tool for the decision makers in the field of
marketing and other activities concerning the firm management.
MARKETING INFORMATION SYSTEM
areas of competitiveness in today’s market place are:
 Market awareness;
 Speed of response;
 Adaptability;
 Innovation;
 Efficiency.
The key
Marketing information system (MkIS) is an ongoing, organized
set of procedures and methods for creation, storage, retrieval,
dissemination and analysis of information for marketing decision
support.
DATA WAREHOUSE (DWH)
The data warehouse (DWH) is a process supported by products,
services and partners, that collects, integrates, stores and delivers data to
the organization (From a report produced by IDC: A Study of the Financial
Impact of Data Warehouses (1996)).
DWH is an enterprise structured repository of subject oriented, integrated,
non volatile, time variant data.
Types of Warehouse Data:





Fact data – Measures of the business (detail data);
Dimension data – Query drivers (an attribute by wich data may be analyzed);
Reference data – Text look up (contains relatively small volume of data);
Summary data – Precalculated data;
Metadata – Warehouse “map”.
DWH vs OLTP:
User activities
Operations
Response Time
Access
Sub. sec. to seconds
Read and write
DWH
Analysis, forecasting,
etc.
Sec. to hours
Primarily read only
Nature of data
(time period)
Current data (30-60
days)
Historical data
(snapshots over time)
Data sources
Internal
Internal and external
Database Size
Small to large (<100
GB)
Large to very large (50
GB to 2 TB)
Types of Decision
Making
Production
management
Strategic decisions
Property
Operational
DWH vs. DATA MARTS
DM1
Marketing
Legacy data
DWH
Operational data
External data
sources
DWH scope – enterprise
multiple subjects
DWH size – 100 GB
to more than 1 TB
DM2
…..
DMk
DMn
Finance
Personnel
DM scope – department single
subject
DM size – up to 100 GB
EXPRESS SERVER
Express Server
is a multidimensional engine for online
analytical processing (OLAP) with the following features:
 Multidimensional analysis;
 Measures with different dimensionality;
 SQL support;
 Robust development environment;
 Open API;
 Distributed;
 Scalable.
EXPRESS SERVER
APPLICATION
Product Manager view
Regional Manager view
Multidimensional
Data Base
Financial Manager View
Ad-hoc view
EXPRESS SERVER APPLICATIONS
Applications:
 Performing in-depth competitive analyses;
 Tracking new product introductions and promotional
response rates;
 Conducting pricing, distribution, and promotion
comparisons across regions;
 Analyzing income and expense ;
 Tracking manufacturing inventory.
EXPRESS SERVER
OBJECTS
 Dimensions
 Relations
 Variables
 Formulas
 Programs
 Composites
 Valuesets
 Worksheets
MOST TYPICAL DIMENSIONS :
 dimension time periods: years – quarters – months;
 dimension geographical regions – regions in the
country
 dimension countries, etc.
FOR THE MARKETING PURPOSES:
 dimension products or services
 dimension clients or types of clients
 dimension distributors, etc.
VARIABLES
FOR THE MARKETING PURPOSES:
 variable products or capacity – contains quantity
characteristics of products or services;
 variable sales or usage – characterizes the realization
of the products or services;
 variable costs – describes the expended resources;
 variable revenue – describes the financial results of
the firm activities;
MARKETING INFORMATION SYSTEM of BTC
Marketing manager
Queries
MkIS
DWH
Decision making
information
THE MAIN FUNCTIONS OF THE MKIS SYSTEM
 user friendly reports and ad hoc studies generation;
 historical and up to date data integration for the
purposes of tendency analysis and forecasting;
 real data mathematical models representation;
 what - if analysis.
THE MAIN VARIABLES IN THE MkIS
periods
regions
periods
regions
exchanges
exchanges
subscribers
subscribers
lines
capacity
changes
changed lines
periods
regions
periods
regions
exchanges
exchanges
subscribers
subscribers
services
services
usage
revenue
FORECASTING METHODS:
 LINEAR TREND – models the data as a straight line;
 EXPONENTIAL TREND – models the data as an exponential
curve;
 SINGLE, DOUBLE AND TRIPLE EXPONENTIAL SMOOTHING –
a system of weighted averages which effectively smoothes the
data;
 PERCENTAGE CHANGE – applies a variable’s observed periodto-period percentage changes directly to the user-defined set of
forecast time periods;
 MOVING AVERAGE – calculates a moving average of a set of
data;
 HOLT-WINTERS – decomposes data into three related
components: a “smoothed” series, a seasonal series, and a trend
series.
CHOOSING A FORECASTING METHOD
Method
Single
Exponentioal
Smoothing
Double
Exponentioal
Smoothing
Tripple
Exponentioal
Smoothing
Moving Average
Holt-Winters
Linear Trend
Exponential trend
Percentage change
Time Horizon
Data Pattern
Immediate, short
Stationary
Minimum number
of observations
2
Immediate, short
Linear
3
Immediate, short
Non-linear
4
Immediate, short
Short to medium
Medium, long
Medium, long
Medium, long
Stationary
Seasonal
Linear
Non-linear
Stationary, linear
3
2 seasons
3
3
2
USER ACCESS TO MARKETING DATA BASE
Windows Client
Applications
(Oracle Express
Objects)
User id
Oracle Express
Server Instance
SNAPI
Calls
Cached
data cubes
Marketing data base
Stored
procedures
REPORTS LIBRARY
LIBRARY
MAIN LIBRARY
LOAD REPORT
USERS LIBRARY
LOAD REPORT
SAVE REPORT
35 FREQUENTLY USED REPORTS