O.L.A.P. (Online Analytical Processing) By creating doubt you may find certainties. Certainties do not create enterprise. Doubt and questions do. Dedicated to Dr. Ing. Andrea Fraschetti, my uncle, a Ferrari man who personally circuit tested,

1
O.L.A.P. (Online Analytical Processing)
By creating doubt you may find
certainties.Certainties do not create
enterprise. Doubt and questions do.Dedicated to
Dr. Ing. Andrea Fraschetti, my uncle, a Ferrari
man who personally circuit tested, because he
had doubts, a racing car he designed and died
doing soRelease 13 August 2012
2
Thank you to the following reviewers of this
presentation Mr. Dean Tallam Senior Manager of
SciFinance, Inc "...SciFinance takes complex
mathematical models and translates them into
something a computer can solve, allowing banks to
flexibly change pricing models as they introduce
new products." Newsweek International Ing.
Filippo Heilpern - Consultant in BD
International, Corporate Executive Dr. Ignazio
Palau Consultant in BD International,
Corporate Executive My son Lorenzo Some, among
others, sources Introduction to OLAP - Slice,
Dice and Drill! - Hari MailvaganamBUSINESS
INTELLIGENCE for DUMMIES Swain Scheps
(2008)Data Warehousing Part 1 OLAP and OLTP
Mike BruntOLAP Workshop Basic overview of OLAP
Concepts Keith LakerMS SQL Server 7.0 OLAP
Services Microsoft Inc.http//whatis.techtarget
.com
3
Your multidimensional business query

• Given what are my needs, where can I find in 3
  areas/regions (France, Europe, South America) and
  from 2 countries (India and China) the offers
  that reflect the needs of the industry whom I can
  fulfill with my acquired educational skills ?
• This personal question describes both the data
  that you need to examine
• and the way you need the data structured
• Some of the questions contained in the above
  query
• What is my product ? (my needs)
• Where can I sell it ? (3 areas/regionsand from
  2 countries..)
• Who wants to buy it ( the offers)
• How much ? (you are too green, forget about it
  for the moment.)
• YOUR ANSWER TO THIS QUERY ?
• FOOD FOR YOUR THOUGHTS

4

• OLAP
• is
• working with data information - in business
  terms - without needing to
• understand the underlying storage mechanism
• as well as
• having the ability of intelligently and
  transparently working with the
• different types of business rules that exist
  within any organisation and
• sustain/support them

5
It has also been defined as Fast Analysis of
Shared Multidimensional Information Fast Delivers
information to the user at a fairly constant
rate. Most queries should be delivered to the
user in five seconds or less. Analysis Performs
basic numerical and statistical analysis of the
data, predefined by an application developer or
defined ad hoc by the user. Shared Implements
the security requirements necessary for sharing
potentially confidential data across a large user
population. Multidimensional Not bi-dimensional,
not tri-dimensional, multidimensional Information
Accesses all the data and information necessary
and relevant for the application, wherever it may
reside and not limited by volume
6
Keep well embedded in your mind the two
terms SHARED MULTIDIMENTIONAL
7

Multidimentional Data Model
DIMENSIONS descriptive
cathegories MEASURES

quantitative values
SHARED
MULTIDIMENTIONAL

8

• O.L.A.P is an approach that may quickly provide
  answers to analytical queries that are
  multi-dimensional in nature.
• Think at the queries you have about your future
  
• What do I need ?
• What do I want ?
• What does the market offer ?
• What is my offer to the market ?
• What are the skills that I can bring to the
  market ?
• How can I match these with the offer ?
• How do I find the sources of the offer ?
• When and how do we tango, the offer and me ?
• The typical applications of OLAP are in business
  reporting for sales, marketing, management
  reporting, business process management (BPM),
  budgeting and forecasting, financial reporting,
  etc.
• In your case finding a challenge which you will
  love !

9
It is used extensively by Intelligence Services
and Intelligence Agencies (a prime example, the
E.C.H.E.L.O.N evesdropping program from the
N.S.A. in the US, that along with the F.B.I.,
just detected massive intrusions in Obamas and
McCains campaigns data bases)
10
Databases configured for OLAP employ a
multidimensional data model, allowing for complex
analytical and ad-hoc queries with a rapid
execution time. They borrow aspects of
navigational databases and hierarchical databases
that are speedier than their relational kin
(proche). The output of an OLAP query is
typically displayed in a matrix (or pivot)
format. The dimensions form the rows and
columns of the matrix the measures, the values.
11
OLAP Data Model

• In an OLAP data model, information is
  conceptually viewed as cubes,which consist of
  descriptive categories (dimensions) and
  quantitative values (measures).
• The multidimensional data model makes it simple
  for users to formulate complex queries, arrange
  data on a report, switch from summary to detail
  data, and filter or slice data into meaningful
  subsets.
• Cubes is an easy expression to describe a form.
• In the real business world OLAP can be
  multi-dimentional multifaceted with 5,6,7,x
  dimensions and measures

12
To simplify

• Dimension is What Time
• Geography
• Product
• Channel
• Organization
• Scenario (budget or actual)
• Measure is How Much Sales
• Unit Sales
• Inventory
• Head counts
• Income
• Expenses
• Profits/Losses

13
Multidimentio
nal Data Model
MEASURES quantitative
DIMENSIONS values descriptive
cathegories

14

• OLAP environment is centred around use of the
  term business intelligence where the emphasis
  is on
• online or active access
• dynamic
• analytical in terms of the reports that are
  generated.

15
online WHAT ? dynamic WHAT ? analytical WHAT ?
16

• Online
• Live access to data rather than static reporting.
  
• Analytic queries are submitted against the
  database in real time, and the results are
  returned in real time.
• Analytical processing
• Easily navigate multidimensional data to perform
  unpredictable ad hoc queries and display the
  results in a variety of different layouts
• Transparently manage business rules across
  dimensions and cubes
• Drill through levels of detail to uncover
  significant aspects of data
• Rapidly and efficiently obtain the results of
  sophisticated data calculation and selection
  across multiple dimensions of data

17
A few definitions A metadata repository is a
database of data about data (metadata). The
purpose of the metadata repository is to provide
a consistent and reliable means of access to
data. The repository itself may be stored in a
physical location or may be a virtual database,
in which metadata is drawn from separate sources.
Metadata may include information about how to
access specific data, or more detail about it,
among a myriad of possibilities. A data
warehouse is an Enterprise reporting solution.
It will typically hold all historical data for
the company for all time.A datamart is a
smaller version of the data warehouse. It's going
to hold a year or two's worth of information, and
may not hold all the tables in the data
warehouse. While the data warehouse is for the
enterprise, a datamart is typically for a
departments use. Source http//whatis.techtarge
t.com
18

Output
Analytical processing
Online
19

• One standard transactional report or query will
  ask the following question
• When was order number 84305 shipped?
• This simple, down-to-earth, two-dimensional query
  reflects basic
• mechanics/data of doing business.
• Date of shipment
• Order Number
• It involves simple data selection and little or
  no calculation processing.
• It can be answered directly from the
  transactional system without any impact
• other operations.
• No organisation can survive without this basic
  level of information.

20

• OLAP systems on the other hand - allow an
  organizations to answer a
• much broader multi-dimentional range of
  business queries about the data they
• are collecting in their transactional systems
• How do same quarter sales for our top 10 most
  profitable products across EMEA Region for this
  quarter compare with sales a year ago?
• What are the differences in the product-sales mix
  between Regions Scandinavia, North, Central and
  South Europe , in context to the global European
  sales mix?
• What are our forecast units, unit price per
  service, unit cost per product, sales, cost
  trends, and profit for the next 12 months?
• In what ways does the mix vary by salesperson,
  and what is the relative performance of our
  salespeople?
• What are , year to date, the products making up
  to 40 of our gross profit for each Region over
  the period 2006 to 2008?

21
Two illustrations of OLAP
scenarios/architecture that can allow broad
multi-dimentional business queries
22
                                                
                                                  
                                                  
                Figure 1. Data Model for OLTP
23
(No Transcript)
24

• Two dimentional transactional query
• vs. broader multi-dimentional queries
• The main differences between a simple two
  dimentional transactional query
• and broader multi-dimentional queries are
• the fact that the latter are much more analytical
  and quite complex,
• that the answer to one question often leads
  immediately to another question as the user
  follows a train of thought in addressing a
  business problem/issue or scouting for an
  opportunity.

25

• OLAP is designed to make it easy for end users to
  ask broader multi-dimentional range of
  analytical queries and enhance its day-to-day use
  without requiring
• Assistance from the IT department
• Programming skills
• Technical knowledge about the organization of
  the database
• The results of queries also need to be rapid so
  that the analysts train of thought is not
  interrupted and the value of the analysis is not
  diminished.
• Time and reaction time is of essence in any
  business scenario. Information is old the minute
  it is generated.
• If it is generated late it could be obsolete.

26
A typical multidimensional business query

• For each region of France, what was the
  percentage change in revenue for our top 15
  products, over a rolling three-month period this
  year starting March compared to the same period
  last year?
• This rather simple business question describes
  both the data that the user wants to
• examine and they way he wants the data structured
  (i.e. structural form of that data).
• Business users typically want to answer questions
  that include terms such as
• what, where, who, when and, above all, how much !
  
• You find the following essential questions
  contained in the above query
• What products are selling best? (top 15)
• Where are they selling? (each region France)
• When have they performed the best? (over a
  rolling period.starting March)
• How much ? (percentage change in revenue)

27
Your multidimensional business query

• Given what are my needs, where can I can find in
  3 areas/regions (France, Europe, South America)
  and from 2 countries (India and China) the offers
  that reflect the needs of the industry whom I can
  fulfill with my acquired educational skills ?
• This personal question describes both the data
  that you need to examine
• and the way you need the data structured
• Some of the questions contained in the above
  query
• What is my product ? (my needs)
• Where can I sell it ? (3 areas/regionsand from
  2 countries..)
• Who wants to buy them ( the offers)
• How much ? (you are too green, forget about it
  for the moment.)
• YOUR ANSWER ????? FOOD FOR YOUR THOUGHTS
• GOOD HUNT WOLF PACK !