dimensional modeling in oracle sql developer
DESCRIPTION
An overview of dimensional data modeling with examples of how to build a dimensional model in Oracle SQL Developer Data ModelerTRANSCRIPT
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.1
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.2
Dimensional Modeling in Oracle SQL Developer Data ModelerPhilip Stoyanov Senior Software Development Manager
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.3
Legal
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.4
Agenda
Dimensional model
Reporting
Dimensions in physical model for Oracle
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.5
Models
Dimensional Models 1..n
LogicalModel
Relational Model 1
Relational Model n
Data Types Model
DB Site 1Oracle 9i
Process ModelData flow
diagramming
DB Site nOracle 12c
DB Site 1DB2/390 v.8
DB Site nMS SQLS 2005
…
…Subject Area 1
Subject Area n
…
Subject Area 1
Subject Area n
…
…
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.6
Relational model without relationships
Even if we add
foreign keys still
there are
dependencies
that remain
hidden
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.7
Dimensional model can show more
Extends definitions in
Logical and
Relational models –
additional 1:1 and 1:n
dependencies in the
scope of one table
can be described
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.8
Can be mapped to Logical or Relational model
Relational model – direct
mappings to tables and views
Logical model – used entities
need to be engineered to
relational model in order to
have mapping to specific tables
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.9
Dimensional model elements
Dimension
Level
Hierarchy
Link
Cube
Measure
Slice – subset of cube dimensions and measures
Oracle names (short, long, plural) can be defined for each element and exported to Oracle OLAP
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.10
Dimension
Can be Regular or Time
Consists of levels and hierarchies
Concept of merging dimensions is
supported – hierarchies can span
levels from more than one
dimension
Dimension has one base level that
defines dimension’s grain
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.11
Merging dimensions example
Level “Sales
Territory Region”
belongs to three
dimensions
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.12
Level
Level is mapped to exactly one entity (table or view). Several levels can be mapped to one entity
Can be Regular level or level that represents value based (parent-child) hierarchy
Has a key defined over one or several attributes Has descriptive attributes that map to exactly one
column from dimension table
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.13
Level
Mapping of optional short description and long description attributes can be defined and mapping of mandatory ‘time span” and “time end” attributes should be defined for level belonging to time dimension
Has calculated attributes – expressions over dimension table columns
Value based hierarchy is defined with “level key” and “parent key” attributes
Member selection criteria can be defined
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.14
Level - calculated attribute
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.15
Hierarchy
Defines roll-up path and can connect levels belonging to different dimensions (merging dimensions)
Hierarchies are built and modified automatically during the process of connecting levels and dimension node
Three types of hierarchies – Parent-Child (value based hierarchy), Level based and Ragged
Joins are defined for levels mapped to different tables
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.16
Hierarchy
Hierarchy is created when dimension shape is connected (using “New Link”) to base level.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.17
Link
Used to define hierarchies and dimension usage. Child to parent attributes can be defined when link
connects levels defined on different entities
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.18
Value based hierarchy
Level is used to define key, parent key and descriptive attributes
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.19
Ragged hierarchy
Corresponds to skip level hierarchy in Oracle OLAP – use “New Ragged Hierarchy Link”
tool in order to mark and represent a “jump” over level(s) – dashed green link below
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.20
Dimension usage
link between cube and dimension – defines child to parent attributes (join of fact entity to base level of
dimension), whether it’s role playing dimension and other related properties
Separate dimension is created in Oracle OLAP for each role playing dimension
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.21
Cube
Defines fact entities and joins between them
Container for measures defined on facts from fact entities
Defines dimensions usage and joins to dimensions – support for role playing dimensions
Container for “Slices” based on cube definition
Abbreviations:• A – measure based on fact• FX – measure based on formula
expression• R – reference to dimension
Cube defined on two fact entities:FactInternetSales and FactResellerSales
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.22
Cube - partitioning
Partitioning
dimension,
hierarchy, level
Settings for
composites
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.23
Cube – summary levels (pre-compute)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.24
Measure
Measure can be based on single fact (direct mapping to column) or expression involving facts and other measures
Measure can be Full-Additive, Semi-Additive or None-Additive
One (at least) or more aggregation functions can be defined for Full-Additive and Semi-Additive measure.
Semi-Additive measure has a list of dimensions on which it is valid
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.25
Basic measure
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.26
Calculated measure
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.27
Reports
There are no standard reports for Dimensional model
Custom reports integrated in Search functionality can be created –
two levels of details are supported for cubes and dimensions (in
Data Modeler 4.0)
Custom reports can be generated in HTML, PDF or Excel format;
the latter one can be used to change properties in Excel file and
then to import them back into model
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.28
Custom report template
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.29
Custom report – dimensions with details for hierarchies and levels
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.30
Dimensional Model - usage
Communication with customers – custom reports, detailed and
compact diagrams – for compact diagram use “Show Compact
model” in context menu for dimensional model node in the
browser
Can be exported to Oracle AW
Dimensions in Oracle physical model can be created using
“Engineer to Oracle Model” functionality
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.31
Detailed diagram
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.32
Compact diagram
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.33
Dimensions in Oracle physical model
Used by database
engine for query rewrite
related to materialized
views
Can be imported from
database dictionary or
DDL script
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.34
SQL Dimension
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.35
Dimension – physical model
Dimensions, levels and
hierarchies can be
created/edited manually
Can be used as starting
point to create new
Dimensional model
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.36
Dimensional model – engineer from physical model
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.37
Dimensional model – based on Sales History (SH) sample schema
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.38
Example
Oracle SQL Developer Data Modeler 3.3 (4.0) is
distributed with sample design and complementary
description located in datamodeler\datamodeler\samples\
Dimensional_model directory
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.39
Learn More.. SQL Developer Data Modeler on OTN -
http://www.oracle.com/technetwork/developer-tools/datamodeler SQL Developer Data Modeler forum
https://forums.oracle.com/community/developer/english/development_tools/application_development_in_pl_sql/sql_developer_data_modeler Oracle Learning Library
http://apex.oracle.com/pls/apex/f?p=44785:2:0:FORCE_QUERY::2,RIR,CIR:P2_TAGS:Data%20Modeler
Jeff Smith http://www.thatjeffsmith.com/ Ashley Chen http://oracletoolsgirl.blogspot.co.uk/ Kris Rice http://krisrice.blogspot.co.uk/
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.40
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.41