Oracle Modeling

You guys can use shared link <=> http://docs.oracle.com/cd/E11882_01/server.112/e25554/sqlmodel.htm#DWHSG022

It's so simple to use it.join !!!

The MODEL clause enables you to create a multidimensional array by mapping the columns of a query into three groups: partitioning, dimension, and measure columns. These elements perform the following tasks:

• Partition columns define the logical blocks of the result set in a way similar to the partitions of the analytical functions described inChapter 22, "SQL for Analysis and Reporting". Rules in the MODEL clause are applied to each partition independent of other partitions. Thus, partitions serve as a boundary point for parallelizing theMODEL computation.

• Dimension columns define the multi-dimensional array and are used to identify cells within a partition. By default, a full combination of dimensions should identify just one cell in a partition. In default mode, they can be considered analogous to the key of a relational table.

• Measures are equivalent to the measures of a fact table in a star schema. They typically contain numeric values such as sales units or cost. Each cell is accessed by specifying its full combination of dimensions. Note that each partition may have a cell that matches a given combination of dimensions.

SELECT SUBSTR(country, 1, 20) country,
       SUBSTR(product, 1, 15) product,
       year,
       sales
  FROM sales_view
 WHERE country IN ('Italy', 'Japan') MODEL PARTITION BY(country) DIMENSION
 BY(product, year) MEASURES(sales sales) RULES(sales 'Bounce',
             2002 = sales 'Bounce',
             2001 + sales 'Bounce',
             2000,
             sales 'Y Box',
             2002 = sales 'Y Box',
             2001,
             sales 'All_Products',
             2002 = sales 'Bounce',
             2002 + sales 'Y Box',
             2002)
 ORDER BY country, product, year;