CS6905 - Programming OLAP
DANIEL LEMIRE
Research Officer, NRC Adjunct Professor, UNB
CS6905 - Programming OLAP
DANIEL LEMIRE
Research Officer, NRC Adjunct Professor, UNB
These slides will be made available on the web.
Overview
✔
Review of the industryOverview
✔
Review of the industry✔
Course presentationOverview
✔
Review of the industry✔
Course presentation✔
Homework AssignmentOverview
✔
Review of the industry✔
Course presentation✔
Homework Assignment✔
Motivation through exampleOverview
✔
Review of the industry✔
Course presentation✔
Homework Assignment✔
Motivation through exampleOLAP is important?
Historical Perspective
1970 Codd proposes relational model
&
1980 SQL becomes a commercial success (Oracle, IBM) 1993 Codd coined OLAP, Excel offers Pivot Tables1997 MOLAP vs ROLAP debate
1999 SQL-99 offers some OLAP functionality
Industry standards
Name Status Platform Proponent
OLE DB In use Wintel Microsoft
XML Analysis Prototypical SOAP Microsoft, Hyperion
JOLAP Prototypical Java (J2EE) IBM, Oracle, Hyperion, Sun
Who sells OLAP
Microsoft 24%
Hyperion 23%
Cognos 13%
BO 7%
MicroStrategy 5%
SAP 5%
Oracle 5%
PwC 3%
Applix 3%
Comshare 2%
IBM 2%
Course Presentation
✔
Look at print-out NOW!Course Presentation
✔
Look at print-out NOW!✔
On-line Analytical ProcessingCourse Presentation
✔
Look at print-out NOW!✔
On-line Analytical Processing or OLAPCourse Presentation
✔
Look at print-out NOW!✔
On-line Analytical Processing or OLAP✔
Programming OLAP:Course Presentation
✔
Look at print-out NOW!✔
On-line Analytical Processing or OLAP✔
Programming OLAP: under the hoodCourse Presentation
✔
Look at print-out NOW!✔
On-line Analytical Processing or OLAP✔
Programming OLAP: under the hood✔
Dark art of designing multidimensional database!This lecture?
✔
What kinds of problems can OLAP help me solve?This lecture?
✔
What kinds of problems can OLAP help me solve?✔
Can it help me figure out which products or cus- tomers are profitable?This lecture?
✔
What kinds of problems can OLAP help me solve?✔
Can it help me figure out which products or cus- tomers are profitable?✔
Can it help me pick better stocks?Amazon CEO
You are Amazon’s CEO. You’ve been told that cheaper items sell more. Is it true?Amazon CEO
You are Amazon’s CEO. You’ve been told that cheaper items sell more. Is it true? Used Amazon’s SOAP API7−→
data cube7−→
on- line answer!Amazon OLAP Results
Amazon CEO (part 2)
Ah. Yes. Well, this is nice. says the CEOAmazon CEO (part 2)
Ah. Yes. Well, this is nice. says the CEO Maybe price doesn’t impact sales for items that are highly rated?Amazon OLAP Results (part 2)
Amazon CEO (part 3)
Ok. I was wrong. Very nice. says the CEOAmazon CEO (part 3)
Ok. I was wrong. Very nice. says the CEO (CEO is now buying into the OLAP frame of mind.)Amazon CEO (part 3)
Ok. I was wrong. Very nice. says the CEO (CEO is now buying into the OLAP frame of mind.) Maybe price doesn’t impact sales for items that are poorly rated?Amazon OLAP Results (part 3)
Amazon CEO (part 4)
Ok. Something different is happening with poorly rated items. says the CEOAmazon CEO (part 4)
Ok. Something different is happening with poorly rated items. says the CEO (CEO thinks for a second or two.)Amazon CEO (part 4)
Ok. Something different is happening with poorly rated items. says the CEO (CEO thinks for a second or two.) How many poorly rated items are there compared to highly rated?Amazon OLAP Results (part 4)
Convenience?
This was all computed in a few seconds using our very own web interface (HOWLER). OLAP should be sexy, responsive, and convenient.Convenience?
This was all computed in a few seconds using our very own web interface (HOWLER). OLAP should be sexy, responsive, and convenient. Meant for business people.Howler
But what is OLAP exactly?
Short answer: a marketing term more catchy than multidimensional database.
But what is OLAP exactly?
Short answer: a marketing term more catchy than multidimensional database.
Providing OLAP (On-Line Analytical Pro- cessing) to User-Analysts: An IT Mandate.
1993
But what is OLAP exactly?
Short answer: a marketing term more catchy than multidimensional database.
Providing OLAP (On-Line Analytical Pro- cessing) to User-Analysts: An IT Mandate.
1993
Some of Codd’s definining conditions
✔
Multidimensional Conceptual ViewSome of Codd’s definining conditions
✔
Multidimensional Conceptual View✔
Generic DimensionalitySome of Codd’s definining conditions
✔
Multidimensional Conceptual View✔
Generic Dimensionality✔
Unlimited Dimensions and Aggregation LevelsSome of Codd’s definining conditions
✔
Multidimensional Conceptual View✔
Generic Dimensionality✔
Unlimited Dimensions and Aggregation LevelsSome of Codd’s definining conditions
✔
Unrestricted Cross-Dimensional OperationsSome of Codd’s definining conditions
✔
Unrestricted Cross-Dimensional Operations✔
Consistent Reporting PerformanceSome of Codd’s definining conditions
✔
Unrestricted Cross-Dimensional Operations✔
Consistent Reporting Performance✔
Dynamic Sparse Matrix HadlingSome of Codd’s definining conditions
✔
Unrestricted Cross-Dimensional Operations✔
Consistent Reporting Performance✔
Dynamic Sparse Matrix HadlingOther catchy names
✔
DOLAP: Database OLAP or Desktop OLAPOther catchy names
✔
DOLAP: Database OLAP or Desktop OLAP✔
MOLAP: Multidimensional OLAPOther catchy names
✔
DOLAP: Database OLAP or Desktop OLAP✔
MOLAP: Multidimensional OLAP✔
ROLAP: Relational OLAPOther catchy names
✔
DOLAP: Database OLAP or Desktop OLAP✔
MOLAP: Multidimensional OLAP✔
ROLAP: Relational OLAP✔
HOLAP: Hybrid OLAPOther catchy names
✔
DOLAP: Database OLAP or Desktop OLAP✔
MOLAP: Multidimensional OLAP✔
ROLAP: Relational OLAP✔
HOLAP: Hybrid OLAPOther catchy names
✔
DOLAP: Database OLAP or Desktop OLAP✔
MOLAP: Multidimensional OLAP✔
ROLAP: Relational OLAP✔
HOLAP: Hybrid OLAPDefinitions
Array Storage method where the elements of the array are placed sequen- tially in a contiguous region of storage (disk or RAM)
Definitions
Array Storage method where the elements of the array are placed sequen- tially in a contiguous region of storage (disk or RAM)
Index A structure used to locate values.
Definitions
Variable A unit-bearing data type, either measured or derived.
Definitions
Variable A unit-bearing data type, either measured or derived.
Attribute Information associated with an object.
Definitions
Variable A unit-bearing data type, either measured or derived.
Attribute Information associated with an object.
Dimension Collection of objects of the same type.
For our purposes, Variable = Attribute.
Dimension versus Variable
weight height John 160lbs 1.8m Maggy 125lbs 1.4m
Definitions
To Aggregate The process of combining two or more data items into a single item.
Definitions
To Aggregate The process of combining two or more data items into a single item.
Measure A unit-bearing data type.
Definitions
To Aggregate The process of combining two or more data items into a single item.
Measure A unit-bearing data type.
Cell A measure associated with one and only one member from each of multiple dimensions.
Definitions
To Aggregate The process of combining two or more data items into a single item.
Measure A unit-bearing data type.
Cell A measure associated with one and only one member from each of multiple dimensions.
