dimensional modeling in oracle sql developer

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