slide1 l.
Download
Skip this Video
Download Presentation
Andy Witkowski, Architect Thomas Kyte, VP Oracle Corporation

Loading in 2 Seconds...

play fullscreen
1 / 65

Andy Witkowski, Architect Thomas Kyte, VP Oracle Corporation - PowerPoint PPT Presentation


  • 162 Views
  • Uploaded on

40166. Oracle Database 10 g SQL Model Clause. Andy Witkowski, Architect Thomas Kyte, VP Oracle Corporation. What’s now in SQL for Modeling. Aggregation Enhancements Cube, Rollup, Grouping Sets New aggregates: Inverse Distribution, FIRST/LAST,etc Analytic Functions

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Andy Witkowski, Architect Thomas Kyte, VP Oracle Corporation' - tilly


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2

40166

Oracle Database 10gSQL Model Clause

Andy Witkowski, Architect Thomas Kyte, VP

Oracle Corporation

what s now in sql for modeling
What’s now in SQL for Modeling
  • Aggregation Enhancements
    • Cube, Rollup, Grouping Sets
    • New aggregates: Inverse Distribution, FIRST/LAST,etc
  • Analytic Functions
    • Window Functions: Rank, Moving, Cumulative
    • Statistical Functions: Correlation, Linear Regression,etc
  • Old tools still have more modeling power than SQL
    • Spreadsheets, MOLAP engines

SQL Model enhances SQL with modeling power

case study modeling with excel
Case Study – Modeling with Excel
  • Excel fits well at the personal scale
    • UI and Formatting
    • Calculations (build-in functions, formulas)
    • What-If analysis
  • Excel fits poorly at corporate scale for modeling
    • Cryptic row-column addressing
    • No metadata, No standards, No mathematical model
    • 100s of spreadsheets and consolidation by hand
    • Does not scale (1000’s formulas, TB of data)
    • Perpetual data exchange: databases->Excel

Replace Excel Modeling with SQL Modeling

modeling with sql model
Modeling with SQL Model
  • Language: Spreadsheet-like calculations in SQL
    • Inter-row calculation. Treats relations as an N-Dim array
    • Symbolic references to cells and their ranges
    • Multiple Formulas over N-Dim arrays
    • Automatic Formula Ordering
    • Recursive Model Solving
    • Model is a relation & can be processed further in SQL
    • Multiple arrays with different dimensionality in one query
  • Performance
    • Parallel Processing in partitioning & formulas
    • Multiple-self joins with one data access structure
    • Multiple UNIONs with one data access structure
  • Why Better?
    • Automatic Consolidation (models as views – combine using SQL)
    • Self Adjusting (as database changes no need to re-define)
    • One version of truth (calc directly over data base, no exchange)
slide6

SQL Model

  • Concepts
define relation as array

prod time s

vcr 2001 9

dvd 2001 0

Define Relation as Array

Relation

SELECT prod, time, s FROM sales

Array

1 2 3 4

1999

5 6 7 8

2000

DIMENSION BY (prod, time) MEASURES (s)

time

9 0 1 2

2001

vcr

dvd

tv

pc

prod

define business rules

prod time s

vcr 2001 9

dvd 2001 0

Define Business Rules

Relation

SELECT prod, time, s FROM sales

Array

1 2 3 4

1999

5 6 7 8

2000

DIMENSION BY (prod, time) MEASURES (s)

time

9 0 1 2

2001

vcr

dvd

tv

pc

RULES UPSERT

(

s[ANY, 2000] = s[CV(prod), CV(time) - 1] * 2,

s[vcr, 2002] = s[vcr, 2001] + s[vcr, 2000],

s[dvd, 2002] =AVG(s) [CV(prod), time<2001]

)

prod

Sales in 2000 2x of previous year

Predict vcr sales in 2002

Predict dvd sales in 2002

evaluate formulas 1 st

prod time s

vcr 2001 9

dvd 2001 0

Evaluate Formulas – 1st

Relation

SELECT prod, time, s FROM sales

Array

1 2 3 4

1999

2 4 6 8

2000

DIMENSION BY (prod, time) MEASURES (s)

9 0 1 2

2001

vcr

dvd

tv

pc

RULES UPSERT

(

s[ANY, 2000] = s[CV(prod), CV(time) - 1] * 2,

s[vcr, 2002] = s[vcr, 2001] + s[vcr, 2000],

s[dvd, 2002] = AVG(s) [CV(prod), time<2001]

)

Sales in 2000 2x of previous year

Predict vcr sales in 2002

Predict dvd sales in 2002

evaluate formulas 2 nd

prod time s

vcr 2001 9

dvd 2001 0

Evaluate Formulas – 2nd

Relation

SELECT prod, time, s FROM sales

1 2 3 4

1999

2 4 6 8

2000

DIMENSION BY (prod, time) MEASURES (s)

9 0 1 2

2001

2002

11

RULES UPSERT

(

s[ANY, 2000] = s[CV(prod), CV(time) - 1] * 2,

s[vcr, 2002] = s[vcr, 2001] + s[vcr, 2000],

s[dvd, 2002] = AVG(s) [CV(prod), time<2001]

)

vcr

dvd

tv

pc

Sales in 2000 2x of previous year

Predict vcr sales in 2002

Predict dvd sales in 2002

evaluate formulas 3 rd

prod time s

vcr 2001 9

dvd 2001 0

Evaluate Formulas – 3rd

Relation

SELECT prod, time, s FROM sales

1 2 3 4

1999

2 4 6 8

2000

DIMENSION BY (prod, time) MEASURES (s)

9 0 1 2

2001

2002

11 3

RULES UPSERT

(

s[ANY, 2000] = s[CV(prod), CV(time) - 1] * 2,

s[vcr, 2002] = s[vcr, 2001] + s[vcr, 2000],

s[dvd, 2002] = AVG(s) [CV(prod), time<2001]

)

vcr

dvd

tv

pc

Sales in 2000 2x of previous year

Predict vcr sales in 2002

Predict dvd sales in 2002

return as relation

prod time s

vcr 2001 9

dvd 2001 0

Return as Relation

Relation

SELECT prod, time, s FROM sales

1 2 3 4

1999

2 4 6 8

2000

DIMENSION BY (prod, time) MEASURES (s)

9 0 1 2

2001

2002

113

RULES UPSERT

(

s[ANY, 2000] = s[CV(prod), CV(time) - 1] * 2,

s[vcr, 2002] = s[vcr, 2001] + s[vcr, 2000],

s[dvd, 2002] = AVG(s) [CV(prod), time<2001]

)

vcr

dvd

tv

pc

Self-join.

join + UNION

join + UNION

Relation again

vcr 2001 9

dvd 2001 0

Rows updated & inserted by the Model clause

vcr 2002 11

dvd 2002 3

model clause components

Partitioning

SELECT region, prod, time, s

FROM sales

GROUP BY region, prod, time

MODEL PARTITION BY (region) DIMENSION BY (prod, time)

MEASURES (sum(sales) s, count(sales) c)

RULES ITERATE ( ) UNTIL ( )

(

s[ANY, 2000] = s[CV(prod), CV(time) - 1],

s[dvd, 2003] = s[dvd, 2002] + s[dvd, 2001],

UPSERT s[vcr, 2003] = AVG(s) [vcr, time < 2001]

)

ORDER BY region, product, time, s;

Dims of array

Model options

Formulas

Formula Options

Model Clause – Components

Model clause

key concepts 1
Key Concepts (1)
  • New SQL Model Clause:
    • Data as N-dim arrays with DIMENSIONS & MEASURES
    • Data can be PARTITION-ed - creates an array per partition
    • Formulas defined over the arrays express a (business) model
  • Formulas within a Model:
    • Use symbolic addressing using familiar array notation
    • Can be ordered automatically based on dependency between cells
    • Can be recursive with a convergence condition – recursive models
    • Can UPDATE or UPSERT cells
    • Support most SQL functions including aggregates
key concepts 2
Key Concepts (2)
  • Result of a SQL Model is a relation
    • Can participate further in processing via joins, etc.
    • Can define views containing Model computations
  • SQL Model is the last query clause
    • Executed after joins, aggregation, window functions
    • Before ORDER BY
  • Main Model and Reference Models
    • Can relate models of different dimensionality
formula fundamentals 1
Formula Fundamentals (1)
  • Formulas: SQL expressions over cells with aggs, functions, etc.
  • Formula has a left and right side and represents assignment
    • s[‘vcr’, 2002] = s[‘vcr’, 2001] + s[‘vcr’, 2000] – single ref
    • s[‘vcr’, 2002] = AVG(s)[‘vcr’, t<2002] – multi ref on right
  • Left side can qualify multiple cells
    • s[p IN (‘vcr’,’dvd’), t<2002] = 1000 – multi ref on left
    • s[ANY, t=2002] = 2 * s[CV(p), CV(t)-1] – left-right correlation
    • s[p IN (SELECT prod FROM prod_tb), 2000] = 1000
  • Formula can operate in update or upsert mode
    • update s[‘vcr’, 2002] = s[‘vcr’, 2001] + s[‘vcr’, 2000]
    • upsert s[‘vcr’, 2002] = s[‘vcr’, 2001] + s[‘vcr’, 2000]
formula fundamentals 2
Formula Fundamentals (2)
  • Function CV(dimension) propagates values from left to the right side. In example, products in 2002 are sum of two previous years.

s[ANY, 2002] = s[CV(p), CV(t)-1] +s[CV(p), CV(t) – 2]

formula fundamentals 218
Formula Fundamentals (2)
  • Function CV(dimension) propagates values from left to the right side. In example, products in 2002 are sum of two previous years.

s[ANY, 2002] = s[CV(p), CV(t) -1] + s[CV(p), CV(t) – 2]

  • Formula result can depend on processing order. Can specify order in each formula. E.g., shift by time:

s[‘vcr’, ANY] ORDER BY t = s[‘vcr’, CV(t) - 1]

formula fundamentals 219
Formula Fundamentals (2)
  • Function CV(dimension) propagates values from left to the right side. In example, products in 2002 are sum of two previous years.

s[ANY, 2002] = s[CV(p), CV(t) -1] + s[CV(p), CV(t) – 2]

  • Formula result can depend on processing order. Can specify order in each formula. E.g., shift by time:

s[‘vcr’, ANY] ORDER BY t = s[‘vcr’, CV(t) - 1]

vcr 2001 300.00 0

vcr 2002 350.00 300.00

vcr 2003 400.00

vcr 2004 450.00

vcr 2005 500.00

ORDER BY t

formula fundamentals 220
Formula Fundamentals (2)
  • Function CV(dimension) propagates values from left to the right side. E.g, products in 2002 are sum of two previous years

s[ANY, 2002] = s[CV(p), CV(t) -1] + s[CV(p), CV(t) – 2]

  • Formula result can depend on processing order. Can specify order in each formula. E.g., shift by time:

s[‘vcr’, ANY] ORDER BY t = s[‘vcr’, CV(t) - 1]

vcr 2001 300.00 0

vcr 2002 350.00 300.00

vcr 2003 400.00 350.00

vcr 2004 450.00

vcr 2005 500.00

ORDER BY t

formula fundamentals 221
Formula Fundamentals (2)
  • Function CV(dimension) propagates values from left to the right side. E.g, products in 2002 are sum of two previous years

s[ANY, 2002] = s[CV(p), CV(t) -1] + s[CV(p), CV(t) – 2]

  • Formula result can depend on processing order. Can specify order in each formula. E.g., shift by time:

s[‘vcr’, ANY] ORDER BY t = s[‘vcr’, CV(t) - 1]

vcr 2001 300.00 0

vcr 2002 350.00 300.00

vcr 2003 400.00 350.00

vcr 2004 450.00 400.00

vcr 2005 500.00 450.00

ORDER BY t

model options fundamentals

MODEL

[ UNIQUE DIMENSIONS | UNIQUE SINGLE REFERENCE ]

[ IGNORE NAV | KEEP NAV ]

PARTITION BY (r) DIMENSION BY (p, t) MEASURES (s)

RULES [ UPDATE | UPSERT ]

[ AUTOMATIC ORDER | SEQUENTIAL ORDER ]

(

s[ANY, 2002] = 1.2 * s[CV(product), 2002],

s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001],

s[‘vcr’, 2003] = AVG(s) [‘vcr’, t BETWEEN 1995 AND 2000],

s[‘video’, 2003] = s[‘dvd’, 2003], s[‘vcr’, 2003]

)

Model Options – Fundamentals

global options

rule options

nav options handling sparse data

MODEL KEEP NAV PARTITION BY (r) DIMENSION BY (p, t) MEASURES (s)

RULES UPSERT

(

s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001]

s[‘tv’ ,2003] = sum(s) [‘tv’, t BETWEEN 2001 AND 2002]

)

NAV Options: Handling Sparse Data

West dvd 2001 300.00

West tv 2002 500.00

West vcr 2001 200.00

West vcr 2002 400.00

keep nav

2001

?

West dvd 2001 300.00

West tv 2002 500.00

West dvd 2003 -

West tv 2003 500.00

West vcr 2001 200.00

West vcr 2002 400.00

nav options handling sparse data24

MODEL INGNORE NAV PARTITION BY (r) DIMENSION BY (p, t) MEASURES (s)

RULES UPSERT

(

s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001]

s[‘tv’ ,2003] = sum(s) [‘tv’, t BETWEEN 2001 AND 2002]

)

NAV Options: Handling Sparse Data

West dvd 2001 300.00

West tv 2002 500.00

West vcr 2001 200.00

West vcr 2002 400.00

ignore nav

assume 0

West dvd 2001 300.00

West tv 2002 500.00

West dvd 2003 300.00

West tv 2003 500.00

West vcr 2001 200.00

West vcr 2002 400.00

nav options handling sparse data25

MODEL KEEP NAV PARTITION BY (r) DIMENSION BY (p, t) MEASURES (s)

RULES UPSERT

(

s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001]

s[‘tv’ ,2003] = sum(s) [‘tv’, t BETWEEN 2001 AND 2002]

)

NAV Options: Handling Sparse Data

West dvd 2001 300.00

West tv 2002 500.00

West vcr 2001 200.00

West vcr 2002 400.00

keep nav

ignore nav

assume 0

2001

?

West dvd 2001 300.00

West tv 2002 500.00

West dvd 2003 -

West tv 2003 500.00

West vcr 2001 200.00

West vcr 2002 400.00

West dvd 2001 300.00

West tv 2002 500.00

West dvd 2003 300.00

West tv 2003 500.00

West vcr 2001 200.00

West vcr 2002 400.00

automatic formula ordering

MODEL PARTITION BY (r) DIMENSION BY (p, t) MEASURES (s)

RULES UPDATE AUTOMATIC ORDER

(

F1: s[‘vcr’, 2003] = AVG(s) [‘vcr’, t BETWEEN 1995 AND 2002],

F2: s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001],

F3: s[ANY, 2002] = s[CV(p), 2001] + s[CV(p), 1999]

)

Automatic Formula Ordering
automatic formula ordering27

MODEL PARTITION BY (r) DIMENSION BY (p, t) MEASURES (s)

RULES UPDATE AUTOMATIC ORDER

(

F1: s[‘vcr’, 2003] = AVG(s) [‘vcr’, t BETWEEN 1995 AND 2002],

F2: s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001],

F3: s[ANY, 2002] = s[CV(p), 2001] + s[CV(p), 1999]

)

F1 depends on F3 and F2 depends on F3, thus F3 automatically first:

Automatic Formula Ordering
automatic formula ordering28

MODEL PARTITION BY (r) DIMENSION BY (p, t) MEASURES (s)

RULES UPDATE AUTOMATIC ORDER

(

F1: s[‘vcr’, 2003] = AVG(s) [‘vcr’, t BETWEEN 1995 AND 2002],

F2: s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001],

F3: s[ANY, 2002] = s[CV(p), 2001] + s[CV(p), 1999]

)

F1 depends on F3 and F2 depends on F3, thus F3 automatically first:

RULES UPDATE AUTOMATIC ORDER

(

F3: s[ANY, 2002] = s[CV(p), 2001] + s[CV(p), 1999]

F2: s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001],

F1: s[‘vcr’, 2003] = AVG(s) [‘vcr’, t BETWEEN 1995 AND 2000]

)

Automatic Formula Ordering
update upsert partitions

MODEL PARTITION BY (r) DIMENSION BY (p,t) MEASURES (s)

RULES UPDATE IGNORE NAV

(

UPDATE s[‘vcr’, 2002] = s[‘vcr’, 2002] * 1.2,

UPSERT s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001]

)

UPDATE, UPSERT & Partitions

Region Product Time s

East dvd 2001 100

East dvd 2002 150

East vcr 2002 100

West dvd 2001 200

update upsert partitions30

MODEL PARTITION BY (r) DIMENSION BY (p,t) MEASURES (s)

RULES UPDATE IGNORE NAV

(

UPDATE s[‘vcr’, 2002] = s[‘vcr’, 2002] * 1.2,

UPSERT s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001]

)

UPDATE, UPSERT & Partitions

Region Product Time s

East dvd 2001 100

East dvd 2002 150

East vcr 2002 100

West dvd 2001 200

Region Product Time Old s New s

East dvd 2001 100 100

East dvd 2002 150 100

East vcr 2002 100 120

West dvd 2001 200 200

updated

update upsert partitions31

MODEL PARTITION BY (r) DIMENSION BY (p,t) MEASURES (s)

RULES UPDATE IGNORE NAV

(

UPDATE s[‘vcr’, 2002] = s[‘vcr’, 2002] * 1.2,

UPSERT s[‘dvd’, 2003] = s[‘dvd’, 2002] + s[‘dvd’, 2001]

)

UPDATE, UPSERT & Partitions

Region Product Time s

East dvd 2001 100

East dvd 2002 150

East vcr 2002 100

West dvd 2001 200

Region Product Time Old s New s

East dvd 2001 100 100

East dvd 2002 150 100

East vcr 2002 100 120

East dvd 2003 - 250

West dvd 2001 200 200

West dvd 2003 - 200

updated

upserted

different dimensions reference

SELECT c, p, t, s FROM sales

MODEL

REFERENCE convert ON (SELECT c, ratio FROM conv) DBY (c) MEASURES(r)

MAIN DIMENSION BY (c,p,t) MEASURES (s)

RULES UPSERT

(

s[ANY, ANY, ANY] = r[CV(c)] * s[CV(c), CV(p), CV(t)]

)

Different dimensions: Reference

Relate Models with different dimensions. Represent each as n-dimensional array: one main, others as reference or lookup arrays.

Sales Table

Conv table converts currency to $

c p t s

USA dvd 2001 300.00 $

USA tv 2001 500.00 $

Poland vcr 2001 200.00 zl

France vcr 2001 100.00 fr

c ratio

USA 1

Poland 0.24

France 0.12

different dimensions reference33

SELECT c, p, t, s FROM sales

MODEL

REFERENCE convert ON (SELECT c, ratio FROM conv) DBY (c) MEASURES(r)

MAIN DIMENSION BY (c,p,t) MEASURES (s)

RULES UPSERT

(

s[ANY, ANY, ANY] = r[CV(c)] * s[CV(c), CV(p), CV(t)]

)

Different dimensions: Reference

Sales Table

Conv table converts currency to $

c p t s

USA dvd 2001 300.00 $

USA tv 2001 500.00 $

Poland vcr 2001 200.00 zl

France vcr 2001 100.00 fr

c ratio

USA 1

Poland 0.24

France 0.12

USA dvd 2001 300.00 $

USA tv 2001 500.00 $

Poland vcr 2001 48.00 $

France vcr 2001 12.00 $

Converted values

recursive model solving

SELECT x, s FROM dual

MODEL DIMENSION BY (1 x) MEASURES (1024 s)

RULES ITERATE (8)

(

s[1] = s[1] / 2

)

Recursive Model Solving
  • Model can contain cyclic (recursive) formulas. - If cyclic formulas desired, use ITERATE option- If ITERATE not present, cyclic formulas automatically detected, and an error reported.
    • Use ITERATE clause to specify # of iterations or
    • Use UNTIL clause to specify convergence conditions

Iteration 1 2 3 4 5 6 7 8

S value 1024 512 128 64 32 16 8 4

recursive model solving35

SELECT x, s FROM dual

MODEL DIMENSION BY (1 x) MEASURES (1024 s)

RULES ITERATE (8)

(

s[1] = s[1] / 2

)

Recursive Model Solving
  • Model can contain cyclic (recursive) formulas. - If cyclic formulas desired, use ITERATE option- If ITERATE not present, cyclic formulas automatically detected, and an error reported.
    • Use ITERATE clause to specify # of iterations or
    • Use UNTIL clause to specify convergence conditions

Iteration 1 2 3 4 5 6 7 8

S value 1024 512 128 64 32 16 8 4

recursive model solving36

SELECT x, s FROM dual

MODEL DIMENSION BY (1 x) MEASURES (1024 s)

RULES ITERATE (8)

(

s[1] = s[1] / 2

)

Recursive Model Solving
  • Model can contain cyclic (recursive) formulas. - If cyclic formulas desired, use ITERATE option- If ITERATE not present, cyclic formulas automatically detected, and an error reported.
    • Use ITERATE clause to specify # of iterations or
    • Use UNTIL clause to specify convergence conditions

Iteration 1 2 3 4 5 6 7 8

S value 1024 512 128 64 32 16 8 4

recursive model solving37

SELECT x, s FROM dual

MODEL DIMENSION BY (1 x) MEASURES (1024 s)

RULES ITERATE (8)

(

s[1] = s[1] / 2

)

Recursive Model Solving
  • Model can contain cyclic (recursive) formulas. - If cyclic formulas desired, use ITERATE option- If ITERATE not present, cyclic formulas automatically detected, and an error reported.
    • Use ITERATE clause to specify # of iterations or
    • Use UNTIL clause to specify convergence conditions

Iteration 1 2 3 4 5 6 7 8

S value 1024 512 128 64 32 16 8 4

recursive model solving with until

SELECT x, s FROM dual

MODEL DIMENSION BY (1 x) MEASURES (1024 s)

RULES ITERATE (10000) UNTIL (PREVIOUS(s[1]) – s[1] <= 1)

(

s[1] = s[1] / 2

)

Recursive Model Solving with Until
  • Model can contain cyclic (recursive) formulas. - If cyclic formulas desired, use ITERATE option- If ITERATE not present, cyclic formulas automatically detected, and an error reported.
    • Use ITERATE clause to specify # of iterations or
    • Use UNTIL clause to specify convergence conditions

Iteration 1 2 3 4 5 6 7 8 9 10

S value 1024 512 256 128 64 32 16 8 4 2

previous(s[1])

- s[1] = 512

recursive model solving with until39

SELECT x, s FROM dual

MODEL DIMENSION BY (1 x) MEASURES (1024 s)

RULES ITERATE (10000) UNTIL (PREVIOUS(s[1]) – s[1] <= 1)

(

s[1] = s[1] / 2

)

Recursive Model Solving with Until
  • Model can contain cyclic (recursive) formulas. - If cyclic formulas desired, use ITERATE option- If ITERATE not present, cyclic formulas automatically detected, and an error reported.
    • Use ITERATE clause to specify # of iterations or
    • Use UNTIL clause to specify convergence conditions

Iteration 1 2 3 4 5 6 7 8 9 10

S value 1024 512 256 128 64 32 16 8 4 2

previous(s[1])

- s[1] = 256

recursive model solving with until40

SELECT x, s FROM dual

MODEL DIMENSION BY (1 x) MEASURES (1024 s)

RULES ITERATE (10000) UNTIL (PREVIOUS(s[1]) – s[1] <= 1)

(

s[1] = s[1] / 2

)

Recursive Model Solving with Until
  • Model can contain cyclic (recursive) formulas. They are automatically detected, and error is reported. Unless cycles are intentional which is indicated with ITERATE option
    • Use ITERATE clause to specify # of iterations or
    • Use UNTIL to specify convergence conditions. Stop if true.

Iteration 1 2 3 4 5 6 7 8 9 10

S value 1024 512 256 128 64 32 16 8 4 2

previous(s[1])

- s[1] = 4

slide41

SQL Model

  • Business Examples
time series calculation 1
Time Series Calculation (1)

Compute the ratio of current month sales of each product to sales one year ago, one quarter ago and one month ago.

Assume: Sales cube with product sales per year, quarter, and month & a time table mapping periods to prior year, quarter and month

time table: maps t to y_ago, q_ago, m_ago

Sales cube: prod sales per y, q, m

time series calculation 2
Time Series Calculation (2)
  • Reference model with Time table acts like look-up table
  • CV carries values from the left side to the right side
  • Without Model, you need 3 outer joins and a regular join

SELECT product, sales, r_y_ago, r_q_ago, r_m_ago

FROM sales_cubeMODEL REFERENCE r ON (SELECT * from time) DIMENSION BY (t) MEASURES (y_ago, q_ago, m_ago)MAIN PARTITION BY (product) DIMENSION BY (t) MEASURES (sales, 0 r_y_ago, 0 r_q_ago, 0 r_m_ago)RULES

( r_y_ago[ANY] = s[CV(t)] / s[ y_ago[CV(t)] ], -- year ago

r_q_ago[ANY] = s[CV(t)] / s[ q_ago[CV(t)] ], -- quarter ago r_m_ago[ANY] = s[CV(t)] / s[ m_ago[CV(t)] ] -- month ago

);

time series calculation 3
Time Series Calculation (3)

Compute the ratio of current period sales of each product to sales a year ago, quarter ago and a month ago.

For each row, we use the reference Model to find 3 other rows.

Sales cube: prod sales per y, q, m

time series calculation 345
Time Series Calculation (3)

Compute the ratio of current period sales of each product to sales a year ago, quarter ago and a month ago.

For each row, we use the reference Model to find 3 other rows.

Sales cube: prod sales per y, q, m

time series calculation 346
Time Series Calculation (3)

Compute the ratio of current period sales of each product to sales a year ago, quarter ago and a month ago.

For each row, we use the reference Model to find 3 other rows.

Sales cube: prod sales per y, q, m

recursive model solving ledger 1

SELECT account, b FROM ledgerMODEL IGNORE NAV DIMENSION (account) MEASURES (balance b)

RULES ITERATE (..) UNTIL ..

(

b[‘interest’] = b[‘net’] * 0.30, --F1

b[‘net’] = b[‘salary’] – b[‘interest’] – b[‘tax’], --F2

b[‘tax’] = (b[‘salary’] – b[‘interest’]) * 0.38 + b[‘capital_gain’] *0.28 --F3

)

Recursive Model Solving: Ledger (1)

In my ledger, I have accounts: Net income, Interest, Taxes, etc.

  • I want to have 30 % of my Net income as Interest (F1)
  • My Net income is Salary minus Interest, minus Tax (F2)
  • Taxes are 38% of Gross (salary–interest) and 28% of Capital_gain (F3)
recursive model solving ledger 148

SELECT account, b FROM ledgerMODEL IGNORE NAV DIMENSION (account) MEASURES (balance b)

RULES ITERATE (..) UNTIL ..

(

b[‘interest’] = b[‘net’] * 0.30, --F1

b[‘net’] = b[‘salary’] – b[‘interest’] – b[‘tax’], --F2

b[‘tax’] = (b[‘salary’] – b[‘interest’]) * 0.38 + b[‘capital_gain’] *0.28 --F3

)

Recursive Model Solving: Ledger (1)

In my ledger, I have accounts: Net income, Interest, Taxes, etc.

  • I want to have 30 % of my Net income as Interest (F1)
  • My Net income is Salary minus Interest, minus Tax (F2)
  • Taxes are 38% of Gross (salary–interest) and 28% of Capital_gain (F3)

net

two cycles in the formulas

F1

F2

F3

interest

tax

interest

recursive model solving ledger 2

SELECT account, b FROM ledgerMODEL IGNORE NAV DIMENSION BY (account) MEASURES (balance b)

RULES ITERATE (10000) UNTIL (ABS(b[‘net’] - PREVIOUS(b[‘net’])) < 0.01)

(

b[‘interest’] = b[‘net’] * 0.30,

b[‘net’] = b[‘salary’] – b[‘interest’] – b[‘tax’],

b[‘tax’] = (b[‘salary’] – b[‘interest’]) * 0.38 + b[‘capital_gain’] *0.28

)

Recursive Model Solving: Ledger (2)

In my ledger, I know Salary & Capital_gains. What are my Net income, Interest expense & Taxes?

Input Ledger

Output

Account Balance

salary 100,000

capital_gains 15,000

net 0

tax 0

interest 0

Account Balance

salary 100,000

capital_gains 15,000

net 100,000

tax 42,220

interest 30,000

Iterate till accuracy of .01

after 1st iteration

recursive model solving ledger 250

SELECT account, b FROM ledgerMODEL IGNORE NAV DIMENSION BY (account) MEASURES (balance b)

RULES ITERATE (10000) UNTIL (ABS(b[‘net’] - PREVIOUS(b[‘net’])) < 0.01)

(

b[‘interest’] = b[‘net’] * 0.30,

b[‘net’] = b[‘salary’] – b[‘interest’] – b[‘tax’],

b[‘tax’] = (b[‘salary’] – b[‘interest’]) * 0.38 + b[‘capital_gain’] *0.28

)

Recursive Model Solving: Ledger (2)

In my ledger, I know Salary & Capital_gains. What is my Net & Taxes?

Input Ledger

Output

Account Balance

salary 100,000

capital_gains 15,000

net 0

tax 0

interest 0

Account Balance

salary 100,000

capital_gains 15,000

net 27,800

tax 30,800

interest 8,340

Iterate till accuracy of .01

after 2nd iteration

recursive model solving ledger 251

SELECT account, b FROM ledgerMODEL IGNORE NAV DIMENSION BY (account) MEASURES (balance b)

RULES ITERATE (10000) UNTIL (ABS(b[‘net’] - PREVIOUS(b[‘net’])) < 0.01)

(

b[‘interest’] = b[‘net’] * 0.30,

b[‘net’] = b[‘salary’] – b[‘interest’] – b[‘tax’],

b[‘tax’] = (b[‘salary’] – b[‘interest’]) * 0.38 + b[‘capital_gain’] *0.28

)

Recursive Model Solving: Ledger (2)

In my ledger, I know Salary & Capital_gains. What is my Net & Taxes?

Input Ledger

Output

Account Balance

salary 100,000

capital_gains 15,000

net 0

tax 0

interest 0

Account Balance

salary 100,000

capital_gains 15,000

net 48,735

tax 36,644

interest 14,620

Iterate till accuracy of .01

after reaching accuracy

(26 iterations)

financial functions npv
Financial Functions: NPV

NPV – net present value of a series of periodic cash flows.

Cash_Flow table

financial functions npv53
Financial Functions: NPV

NPV – net present value of a series of periodic cash flows.

Cash_Flow table

amount[1]/power(1+rate,1) + npv[1-1]

financial functions npv54
Financial Functions: NPV

NPV – net present value of a series of periodic cash flows.

Cash_Flow table

amount[2]/power(1+rate,2) + npv[2-1]

financial functions npv55
Financial Functions: NPV

NPV – net present value of a series of periodic cash flows.

Cash_Flow table

amount[3]/power(1+rate,3) + npv[3-1]

financial functions npv56

npv[ANY] ORDER BY i

= amount[ CV(i) ] / power(1+rate, CV(i))

+ npv[CV(i) – 1]

Financial Functions: NPV

NPV – net present value of a series of periodic cash flows.

Cash_Flow table

amount[i]/power(1+rate, i) + npv[i-1]

financial functions npv 2

SELECT year, i, prod, amount, npv

FROM cash_flow

MODEL PARTITION BY (prod)

DIMENSION BY (i)

MEASURES (amount, 0 npv, year)

RULES (

npv[ 0] = amount[0],

npv[i !=0] ORDER BY i

= amount[ CV() ] / POWER(1.14, CV() ) +

npv[CV() - 1]

)

Financial Functions: NPV (2)

NPV – Net present value of a series of periodic cash flows.

Cash_Flow table and npv for rate = 0.14

slide58

SQL Model

  • Performance
sql model time series
SQL Model – Time Series

Earlier example: ratio of sales to year, quarter and month ago

SELECT product, sales, r_y_ago, r_q_ago, r_m_ago

FROM sales_cubeMODEL REFERENCE r ON (SELECT * from time) DIMENSION BY (t) MEASURES (y_ago, q_ago, m_ago)MAIN PARTITION BY (product) DIMENSION BY (t) MEASURES (sales, 0 r_y_ago, 0 r_q_ago, 0 r_m_ago)RULES

( r_y_ago[ANY] = s[CV(t)] / s[ y_ago[CV(t)] ], -- year ago

r_q_ago[ANY] = s[CV(t)] / s[ q_ago[CV(t)] ], -- quarter ago r_m_ago[ANY] = s[CV(t)] / s[ m_ago[CV(t)] ] -- month ago

);

  • ANSI SQL version needs outer join for each formula plus a join for reference model.
  • N formulas, M reference models  N+M joins  4 joins in this example:
  • sales_cube  time  sales_cube  sales_cube  sales_cube
sql model vs ansi joins
SQL Model vs. ANSI Joins

Query response time

400

ANSI joins

350

300

250

200

SQL Model

150

100

50

Number of rules or joins

1

2

3

4

5

6

7

8

9

10

11

12

13

14

summary
Summary
  • New facility for spreadsheet-like computations in SQL
  • High Performance
    • Replaces multiple joins, unions
    • Scalable in size and parallel processing
    • Powerful optimizations
  • Collaborative analysis
  • Move external processing such as spreadsheets into RDBMs for manageability and consolidation
next steps
Next Steps….
  • Demonstration at Oracle DEMOgrounds
    • Exhibit hall, Booth 1326, Database Area
    • Monday: 5:00 PM - 8:00, Tuesday: 10:30 - 1:00, 3:00 - 6:00, Wednesday: 11:00 - 4:30, Thursday: 10:30 - 2:00
  • Hands-on Lab
    • Marriott Hotel - Golden Gate B1
    • Lab Section: Use Information from your Data WarehouseLesson 1: Using the SQL Model clause
    • Monday: 10:30 - 5:00, Tuesday: 8:30 - 12:30, 3:00 - 5:00,Wednesday: 8:30 - 4:30, Thursday: 8:30 - 2:30
slide64

Q

&

Q U E S T I O N S

A N S W E R S

A