Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


Escape from Big Data Restrictions by Leveraging Advanced OLAP Cube Techniques

SAS Global Forum Paper 047-2013. Utilizing member properties, defining dynamic measures and dimensions using the MDX language, and improving performance for high data volumes.

Stephen Overton

on 21 May 2015

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Escape from Big Data Restrictions by Leveraging Advanced OLAP Cube Techniques

Number of aggregations depends on:
End user performance required
Number of levels in cube
Time to build aggregations depends on:
Number and complexity of levels defined in cube
What other aggregates are built (including NWAY)
Can substantially increase physical size of the cube
ARM Logging to learn usage patterns and build aggregates
Always build top level aggregates
Build using separate step from primary cube for reliability
Concurrency options
Design considerations
Size and complexity
Build process versus end user performance
OLAP Development & Cube Build Process
Member Properties (demo)
Advanced MDX functionality (demo)
Design - how reporting data is structured
Size - volume of data
Complexity - cardinality of dimensional data
Quality - garbage in garbage out
Usage - Measurable change through business decisions made from data
OLAP Cube Build Process
Things to Remember:
Define Custom MDX
Is it worth crossing every level, especially with BIG data?
Depends on the complexity of the data
Build NWAY?
The Life of
an OLAP Cube

OLAP Cube Studio
Tools to Build Cubes
SAS Programming
Easy to use
Power of SAS Code
Full control
More complex for beginners
Enterprise Guide recommended
Cardinality of source data in dimensions increases build time and memory consumption
Member Properties are defined in this phase
Escape from BIG Data Restrictions
by Leveraging Advanced
OLAP Cube Techniques
Stephen Overton
Overton Technologies, LLC
Raleigh, North Carolina
Things to Remember:
OLAP will only display summary data in reports
NOT detailed records, or listings
Aggregations needed for performance
Avoid displaying high cardinality dimensions
Not effective visually
Tuple size of results consumes memory
Filter, slice, or drill on high cardinality dimensions
Consuming Data with OLAP
Star Schema
Most efficient for OLAP and BIG data consumption
Logically very similar to structure of an OLAP cube
Measures (facts)
Ralph Kimball methodology
High cardinality dimensions will consume much more
millions of distinct values
Optimize consumption by tuning MEMSIZE and SUMSIZE
Hard disk space
Building physical pieces of cube
NWAY and aggregations
Is OLAP the best route?
Build Process
Understand the Data
Can data be summarized efficiently as is?
Can an OLAP cube support visual requirements for reporting?
Can an OLAP cube summarize data in the required amount of time?
Can the available hardware support building a cube?
Are there any dimensions with BIG data?
How much data needs to be aggregated?
Do I need to build the NWAY?
Questions to Ask:
Everything Starts and Ends with the Data
Design Considerations
Design Considerations
Overall build time
How the cube is updated
Frequency cube is updated
Building the NWAY
Divide and Conquer
How cube is built
How code is written
End User
Major Considerations
More Dimensions
More Complexity
More levels to cross in aggregations
Longer Build Time
Larger Cube
OLAP Member Properties
Advanced MDX Techniques
Property Name
Level property is for
Hierarchies property is available for
Source column used to lookup member properties
Writing OLAP Member Properties
Eliminates redundant levels
Extra attributes can be created as member properties
Reduces complexity of OLAP cube
Reduces number of levels to cross
Reduces build time
Reduces physical size
Source column used to lookup member property must have 1:1 relationship with actual level in OLAP cube dimension
Member Properties can be searched using BI tools
Example: Web Report Studio
Things to Remember
Enterprise Guide OLAP Explorer
Web Report Studio
MS Excel Addin
Things to Remember:
Custom MDX statements are "views" of slices or crossings of data in an OLAP cube
Add no physical size
Dynamic, robust capabilities
TIP: Define separately when building in code form
Things to Remember:
Built by default
Majority of OLAP cube build process
Hard disk IO intensive
Network intensive depending on data tier architecture
Complexity of cube and cardinality of levels determines size and time to build
Provides aggregate crossing of every level defined in OLAP cube
Initial cube defined much faster
Aggregates needed when summarizing BIG data
Can be very intensive (hard disk, network, CPU) if threading and concurrency used
PROC OLAP Statement
OLAP Cube Studio
MDX = Multi-Dimensional eXpression
Programming language used to query OLAP cubes (similar to SQL)
Web Report Studio and Enterprise Guide generate behind the scenes
Select crossings of data on ROWS, COLUMNS, and SLICER
Adds no physical size to OLAP cube
Dynamic functions which reference different aggregations of data at different levels in the cube
Create complex metrics easier with minimal data infrastructure changes
Conditional logic functionality
Statistical functions
Good reference - http://www.mdxpert.com
Take baby steps for complex measures
Build MDX expressions based on other expressions
Define the TIME dimension
Learn common functions
.CurrentMember (TIME)
.PrevMember (TIME)
.Parent / .Child
.Children / .Members
Use Enterprise Guide to learn MDX by exploring an OLAP cube and viewing the MDX
Programming Tips
MDX Solutions to Common Challenges
Dynamic objects for robust reporting
Don't need to change the filter
Rolling 12 Months
Rolling 7 days
Aggregation of Rolling 12 Months
Rolling Time Sets & Aggregates
Compare value of a level to the group or cluster it belongs to
Identify how to cluster/group
Compare people
Compare companies
Useful for performance evaluation or detecting behavior change
Peer Comparison
SAS Enterprise Guide OLAP Explorer
.Members function returns Transaction Types on ROWS
.Children function returns MONTHS within ALL YEARS
Transaction Amount measure crossed with MONTHS using CROSSJOIN function
Months crossed with Transaction Amount on COLUMNS
Building the NWAY
Not Building the NWAY
Performance Tuning
Number of aggregates to build in parallel to 4
Limit number of indices created in parallel to 2
Limits max threads to spawn to 2
Full transcript