Building OLAP Cubes

1
Building OLAP Cubes

• Val Matison
• Matison Consulting Group Inc.

2
Who Am I?

• Val Matison MCP, MCT
• President, Matison Consulting Group Inc.
• Database application services company focusing on
  OLAP, Data Mining, Insights into data
• http//www.dbcentral.com
• matisonv_at_dbcentral.com

3
OLAP Services Architecture
OLAP Manager
Server
Client
Client App
DSO
Source
Relational
Client App
ADO MD
Database
ROLAP
OLAP Server
OLE DB
PivotTable Service
HOLAP
MOLAP
4
ADO and ADO MD

• Provide access to information supplied by OLE DB
  and OLE DB for OLAP providers
• ADO is an application level programming interface
  to OLE DB
• ADO MD is an application level programming
  interface to OLE DB for OLAP

5
OLAP Cube Components

• Fact table
• Measures
• Dollar sales
• Units
• Earned income
• Dimensions
• Time
• Customer
• Products
• Calculated Members

6
Aggregations

• Greatly assist query speed
• Level of detail must be determined
• Additive Semi additive Measures
• End of month inventory
• Bank balance
• Must consider data grain and data explosion

7
Storage Options

• Molap
• Aggregations stored in multidimensional structure
• No join operations required
• Storage requirements reduced
• Fast data retrieval
• Rolap
• Data stored in relational tables
• Standard Transact SQL can be used
• Rolap cubes do not require extra storage space

8
Storage Options

• Holap
• Hybrid
• Retrieves data quickly by using Molap
• Data stored in relational tables

9
Processing

• Incremental Updates
• Allowable if new data added but existing
  unchanged
• Can be done while users continue to use the cube
• Refreshing Data
• Existing data deleted
• Aggregations are recalculated
• Used for nonstructural and non-data changes

10
Virtual Cubes

• Logical combinations of multiple physical cubes
• Similar to a view between relational tables
• No additional storage space required other than
  the view definition

11
PivotTable Service Architecture
ActiveX control
ISV apps
ADO MD
Excel
ISV apps
OLE DB with OLAP extensions
Local cube files
PivotTable Service
Client
Server
OLAP Server
SQL Server OLE DB provider
SQL Server
12
Interface

• Examples
• Excel
• Knosys
• Internet
• Digital Dashboard

13
Process

• Identify Requirements
• Design Target Systems
• Identify Source Systems
• Transformation and Cleansing of Data
• Data Verification
• User Interface Design
• Change Implementation
• Rollout
• Support

14
MDX

• Multi Dimensional Expressions
• Syntax for modeling and querying an OLAP database
• Part of OLE DB for OLAP Spec
• Supported by Multiple Providers
• It is the key for all advanced analytical
  capabilities of OLAP services

15
OLAP Services Structures

• Dimensions
• Time or Measures
• Hierarchies
• Dimension name followed by . name of
  hierarchy
• Time.Quarter
• Levels
• Hierarchy name followed by . name of level
• Members
• Level name name followed by . name of member

16
MDX Query

• Select, From, Where
• Select Measures.Units Shipped,
  Measures.Units Ordered on columns,
• Store.Store Name.members on rows from
  Warehouse

17
Simple MDX

• , comma and colon
• Members
• Crossjoin()
• Filter()
• Order()

18
ADO MD Object Hierarchy
ADODB Connection
Catalog
CubeDef
Cellset
Axis
Dimension
Cell
Position
Hierarchy
Position
Member
Level
Member
Member