Oracle Database 11g New Features for BI DW

1
Oracle Database 11gNew Features for BI / DW
2
Agenda

• New Partitioning Methods
• Invisible indexes
• PIVOT / UNPIVOT
• OLAP and SQL

3
Oracle Product Stack

• Database Enterprise Edition 11g (11.1.0.7.0)
• SQLDeveloper 11g (1.5.1)
• Warehouse Builder 11g (11.1.0.7.0)
• Analytical Workspace Manager (11.1.0.7.0B)

4
About Me

• Oracle Experience
• Oracle Education 2001 - 2006
• Oracle Consulting 2006 - 2007
• Bicon 2007
• Oracle Certifications
• Oracle Certified Professional DBA 8i
• Oracle Certified Professional DBA 9i
• Oracle Certified Professional DBA 10g
• Oracle Certified Associate PL/SQL 10g

5
About Bicon AS

• Started in 2005
• Currently 8 employees
• Consulting
• Business Intelligence
• DBA
• Project Management

6
Demo Case

• Simulated Sales-application
• Customer
• Geography (Region / Warehouse / Customer)
• Market Segment (Market / Account /Customer)
• Product
• Category (Class / Family / Product)
• Channel

7
Demo Case
CUSTOMER DIMENSION
CUSTOMERS
UNITS FACT
TIME DIMENSION
CHANNEL DIMENSION
OWB
PRODUCT DIMENSION
CHANNELS
ORDERS
AWM
PRODUCTS
UNITS CUBE
8
Partitioning

• New Partition / Subpartition combinations
• LIST - HASH
• LIST LIST
• LIST RANGE
• RANGE RANGE
• New Partitioning Methods
• System Partitioning
• Interval Partitioning
• Virtual Partitioning
• Reference Partitioning

9
Interval Partitioning

• Range Partitioning
• Specify the first partition
• Specify the interval for new partitions
• New partitions generated when needed

10
Interval Partitioning
PARTITION BY RANGE (YEAR_NUMBER) INTERVAL
(1) ( PARTITION BEFORE_2007 VALUES
LESS THAN (2007) )
TIME DIMENSION
TIME DIMENSION
TIME DIMENSION
2006
BEFORE_2007
2007
SYS_P1004
2008
SYS_P1013
11
Virtual Partitioning

• Virtual column
• Not stored in the database
• Calculated on the fly
• Cant be manipulated (DML)
• Virtual Partitioning
• Partioning on the virtual column
• All Partitioning methods

12
Virtual Partitioning
CREATE TABLE CUSTOMER ( SHIP_TO_ID VARCHAR2(16
BYTE) NOT NULL, .. ..., WAR
EHOUSE_ID VARCHAR2(3 BYTE) NOT
NULL, MARKET_SEGMENT_ID VARCHAR2(3 BYTE) NOT
NULL , WAREHOUSE_MARKET_ID VARCHAR2(7)
GENERATED ALWAYS AS (
WAREHOUSE_ID' 'MARKET_SEGMENT_ID )
VIRTUAL ) PARTITION BY HASH(WAREHOUSE_MARKET_ID)
PARTITIONS 5
13
Reference Partitioning

• Uses Foreign Key to partition a table
• Partition key can be any column in the referenced
  table (not just the Primary Key)
• Partition operations in the referenced table is
  automatic propagated

14
Reference Partitioning
CREATE TABLE PRODUCT_DIM (ITEM_ID
VARCHAR2(12) PRIMARY KEY, .......
................. .......................,
CLASS_ID VARCHAR2(4) NOT NULL) PARTITION BY
LIST (CLASS_ID) ( PARTITION p_hrd
VALUES('HRD'), PARTITION p_sft VALUES('SFT')
)
PRODUCT DIMENSION
P_HRD
P_SFT
15
Reference Partitioning
CREATE TABLE UNITS_FACT ( ITEM_ID
VARCHAR2(12 BYTE) .......
................. CONSTRAINT
UNITS_FACT_PROD_FK FOREIGN KEY (ITEM_ID)
REFERENCES PRODUCT_DIM (ITEM_ID) )
PARTITION BY REFERENCE (UNITS_FACT_PROD_FK
)
PRODUCT DIMENSION
UNITS_FACT
UNITS_FACT_PROD_FK
P_HRD
P_HRD
P_SFT
P_SFT
16
Reference Partitioning
ALTER TABLE PRODUCT_DIM ADD PARTITION
P_DEF VALUES(DEFAULT)
PRODUCT DIMENSION
UNITS_FACT
PRODUCT DIMENSION
UNITS_FACT
UNITS_FACT_PROD_FK
P_HRD
P_HRD
P_SFT
P_SFT
P_DEF
P_DEF
17
Reference Partitioning

• Problem Maintenance of the referenced table
• Cannot disable Foreign Key
• Cannot disable Primary Key (parent-table)
• You can drop the Primary Key (parent-table) but

18
Reference Partitioning
19
Invisible Indexes

• Indexes can be made invisible for the Optimizer
• Invisible Indexes will still be maintained
• Optimizer_use_invisible_indexes
• Controls the use of Invisible Indexes
• Is modifiable in each session
• TRUE Optimizer will consider Invisible indexes
• FALSE Optimizer will not consider Invisible
  Indexes

20
PIVOT / UNPIVOT

• New Syntax for Pivot / Unpivot
• Use the original query as an Inline View
• Benefits
• More readable queries (Both PIVOT / UNPIVOT)
• No need to use a dummy resultset. (UNPIVOT)
• Full flexibility using XML as resultset

21
SQL and OLAP

• Use SQL to access OLAP-objects (CUBE_TABLE)
• Use Materialized View Refresh for OLAP-objects
• Use Query Rewrite against OLAP-cubes

22
SQL and OLAP Cube Table

• Access OLAP-objects with new table-function
• FROM TABLE(CUBE_TABLE(dimension))
• FROM TABLE(CUBE_TABLE(dimensionhierachy))
• FROM TABLE(CUBE_TABLE(cube))
• Access OLAP-objects with default-views
• Generated when creating OLAP-objects
• Uses CUBE_TABLE

23
SQL and OLAP - Materialized View

• Dimensions Materialized View Refresh
• Complete
• On Demand / Scheduled
• Trusted / Enforced Constraints
• Cubes Materialized View Refresh
• Complete / Fast / Force
• On Demand / Scheduled / On Commit
• Trusted / Enforced Constraints

24
SQL and OLAP - Materialized View
SELECT COL1, COL2, COL3, SUM(COL4) FROM D1, D2,
D3, D4, F WHERE D1.PKF.FK1 AND D2.PKF.FK2 AND
D3.PKF.FK3 AND D4.PKF.FK4 GROUP BY COL1,
COL2, COL3
Query Rewrite
MATERIALIZED VIEW
OLAP CUBE
D1
D2
F
D3
D4
25
SQL and OLAP - Materialized View

• General limitations
• Cubes must be compressed
• Cubes must have
• Dimensions
• Normal Hierarchies (Not ragged / skip)
• Levels
• Measures
• Query Rewrite Limitations
• All dimensions aggregated in the same way

26

• Q/A