relational olap and aggregate navigators
DESCRIPTION
Relational OLAP and Aggregate Navigators. Data Warehousing Lab. M.S. 2 Hyeyoung Cho. OLAP. OLAP(OnLine Analytical Processing) One technology for querying the warehouse Suited for summarizing and analyzing huge quantities of data 4 basic functions Multidimensionality Drill down - PowerPoint PPT PresentationTRANSCRIPT
Relational OLAP and Aggregate Navigators
Data Warehousing Lab.M.S. 2 Hyeyoung Cho
LaboratoryData Warehousing
OLAP
OLAP(OnLine Analytical Processing) One technology for querying the warehouse Suited for summarizing and analyzing huge q
uantities of data 4 basic functions
Multidimensionality Drill down Rotation Multiple modes of view
LaboratoryData Warehousing
OLAP basic functions
Multidimensionality provide data about performance
measures, broken down by one or more dimensions
dimensions combine to describe measures vary according to industry, strategy, kinds of
information that systems capture
filtered by dimension and/or measure value
LaboratoryData Warehousing
OLAP basic functions
LaboratoryData Warehousing
OLAP basic functions
Drill down break a summary item into its detailed
components multiple parallel hierarchies
DATE dimension(year vs. seasonal year)
both share a common atomic data element : day
LaboratoryData Warehousing
OLAP basic functions
view highly summarized data and then navigate down to less summarized data
drill across:drill down into a different dimension
rolling up:drill from a detail back up to a total
LaboratoryData Warehousing
OLAP basic functions
Rotation
rotation
month on the Y-axisproduct on the X-axis
month on the X-axisproduct on the Y-axis
LaboratoryData Warehousing
OLAP basic functions
change view perspective drill down and rotation
LaboratoryData Warehousing
OLAP basic functions
Multiple modes of view View data in a variety of
formats(graph,chart,etc.)
LaboratoryData Warehousing
OLAP database technologies
OLAP application for querying and viewing data regardless of how that data is stored
ROLAP and MOLAP ROLAP (Relational OLAP) :RDB기반 MOLAP (Multidimensional OLAP) :MDDB기반
data marts tuned for specific subject areas more normalized than simple star schema
LaboratoryData Warehousing
OLAP database technologies
Cube(dataset to analyze) three dimension :
height,width,depth(dice) Comprised of any number of
dimensions
LaboratoryData Warehousing
MDDB
Array RDB : store data in tables and columns MDDB : store data in large multidimensional arrays
Multidimensional Database Vendors Oracle Express Server(OES) Essbase(Hyperion Software) Powerplay(Cognos) Gentia(Gentia Software)
Suitable for analyzing narrowly focused sets of data
LaboratoryData Warehousing
RDB
Aggregate Navigation Oracle 8i ’s query rewrite capabilities Materialized views in Oracle 8i
ease the construction and maintenance of the table
speed up query performance
LaboratoryData Warehousing
aggregate navigators select the best table for each query know which summaries exist and the size
RDB
LaboratoryData Warehousing
ROLAP Vs. Online Report Writing support
Generate SQL calls, Format the results not support
OLAP basic function, Aggregate navigation ROLAP tool vendors
Oracle Discoverer MicroStrategy DSS Agent, Computer Associates DecisionBase
RDB
LaboratoryData Warehousing
ROLAP Vs.MOLAP
query performance query response time
precalculate all combinations and summaries of data
three dimensions: 1 개의 3D atomic level array 3 개의 2D atomic level array 3 개의 1D summary array broken by each dimension
ROLAP MOLAP
hard to predict performance
predictable, fast response to queryprecalculate possible values in the hypercubes!
LaboratoryData Warehousing
ROLAP Vs.MOLAP
LaboratoryData Warehousing
ROLAP Vs.MOLAP
load performance populate data structures and perform
calculations timeROLAP MOLAP
loaded more quickly, refresh refreshed on a daily basis not build all possible summaries!
long load time, not preferable for frequent updates, refreshed on a monthly basisprecalculating data takes time!
LaboratoryData Warehousing
ROLAP Vs.MOLAP
Analytic capability
ROLAP MOLAP
Sometimes hampered by the limitations of SQL(ignore the limitations,put intermediate results into temporary database tables,use three-tier architectures)Oracle 8i provides terrific SQL extensions(cube,rollup,etc.)!
better support for time series and statistical analysis
LaboratoryData Warehousing
ROLAP Vs.MOLAP
Dataset Sizes
Dimension Handling
ROLAP MOLAP
not incur sparsity penaltiesvirtually unlimited growth
Grow in size very rapidlySparsity:null, Add dimensions(doubles in size)precalculated summary valuesPhysical limitations
ROLAP MOLAP
construct star schemas and query, summarize, drill down on any of dimension columns
not provide such flexibility with dimensionsproblem of database size explosions as dimensions are added!
LaboratoryData Warehousing
ROLAP Vs.MOLAP
Maintenance Effort
ROLAP MOLAP
more effort to populate and maintainmultiple structures must be filledindexes and constraints need to be turned on or off during the processAdditional indexes and summary tables may need to be created
strong in the area of maintenance: self-maintaining
LaboratoryData Warehousing
ROLAP and MOLAP Harmony
Which technology wins? relational database : a large, cross-functional, e
nterprise data warehouse multidimensional database : a well-defined, high
ly targeted analysis-focused data mart limited dimensionality and little need for detailed, atomic-level data
A corporate data warehouse feeds smaller, narrowly focused or stand alone data marts→ ROLAP and MOLAP are complementary!
LaboratoryData Warehousing
ROLAP and MOLAP Harmony
drill through integrate ROLAP and MOLAP
technologies drill down in the MDDB until reach in the
lowest level of detail query to RDB that contains very detailed
atomic-level data not perfect yet so require the custom code
each drill-through query
LaboratoryData Warehousing
Conclusion
OLAP is a user interface, not a data storage, concept
OLAP basic functions: multidimensionality, Drill down, Rotation, Multiple modes of view
ROLAP Vs. MOLAP What is a best approach to OLAP?