1 / 65

Andy Witkowski, Architect Thomas Kyte, VP Oracle Corporation

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

tilly
Download Presentation

Andy Witkowski, Architect Thomas Kyte, VP Oracle Corporation

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 40166 Oracle Database 10gSQL Model Clause Andy Witkowski, Architect Thomas Kyte, VP Oracle Corporation

  2. 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

  3. 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

  4. 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)

  5. SQL Model • Concepts

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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]

  16. 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]

  17. 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]

  18. 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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

  30. 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

  31. 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

  32. 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

  33. 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

  34. 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

  35. 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

  36. 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

  37. 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

  38. 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

  39. 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

  40. SQL Model • Business Examples

  41. 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

  42. 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 );

  43. 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

  44. 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

  45. 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

  46. 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)

  47. 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

  48. 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

  49. 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

More Related