Data warehouse Design Using Oracle

Unit Objective:

5.1 Introduction

With the SQL model clause you build one or more matrixes with a variable number of dimensions. This is called the model. The model uses a subset of the available columns from your FROM clause.  It contains at least one dimension, at least one measure and optionally one or more partitions.

create table salesorder(ord number(4) primary key,prodno number(3),suppno number(3),time number(5),location varchar2(20),sales number(6));

insert into salesord values(1,1,1,2000,'Sohar',5);

Partition columns

Partition columns divide the result set into blocks. Rules defined in the model clause are applied independently of other partitions to each partition.

Dimension columns

Dimension columns define how cells within a partition can be accessed.

Measure columns

The columns defined as measures can be assigned new values in the rules section of the model clause.

5.2 Dimension by

dimension by

In the model clause, the dimension components are used to define multi-dimension array and it is also useful for identifying cells or column within the partition. By default it will identify at least one cell in a partition.

5.2.1 Dimension by- unique column

The dimension components have the column name which has unique value.

dimension by unique

Column value given in the dimension is not unique then it will give "non unique addressing" error. 

non unique column

5.2.2 Dimension by Multiple dimension using unique keys

It is possible to use more than one dimension column be specified. But all the combination gives the unique row identification.

multi dimension using unique key

5.2.3 Dimension by aliasing

In the result set, the user desired column name can be displayed by using aliasing "as" in the dimension. 

aliasing

5.2.4 Dimension by Expression

It is possible to use expression in the dimension component.  In the given query, value of the ord column is multiplied by 10. So, column value 3 is displaying as 30.

dimension by column values

5.3 Measures

Measure is a data column of the table. It is similar to measure in the star schema based fact table. Generally measures have numeric based values like cost or amount. Each cell or column mentioned in the measures is accessed by specifying its full combination of dimensions.

Measures

Like dimension it is possible to write expression in the measure column also.  

expression of measure

5.4 Partition by

The Oracle data warehousing describes partition as a logical block of the result set.  It is similar to how partition in the analytical functions. Each partition is viewed by the formula mentioned in the partition by component and treated as an independent array. 

partition by

5.5 Rules

In the model clause, rules hold the expressions that assign values to measures. A rule is an assignment statement whose left side represents a cell or a range of cells and whose right side is an expression involving constants, bind variables or individual cell or aggregate function. There are three terms used in rules namely cell reference, dimension reference, cell assignment.

Rules

The term "newsal" is called a "cell reference, Cell references (i.e newsal) can only refer to measure cells, not dimension cells.  The dimension reference is part between the square bracket [] in the cell reference.  When a cell reference is used as the assignment target on the left side of a rule equation is called cell assignment.

cell assignment

5.5.1  Rules create new row

The RULES clause also used to create new rows in the result set.

create new row

Adding values to newly created row

It is also possible to add values to newly created row by using Rules Clause

Adding new values

5.6 RETURN UPDATED ROWS

Suppose in the previous query the entire row those satisfying the condition location = Sohar and prodno=1.  But the output will show newly created row or inserted row by using rule clause, then use “return updated rows”.  Then it will display only the newly created row.

create new row

5.7 RETURN All ROWS

By default “return all rows” is used in the model queries Rules clause. Generally if it is mentioned as return all rows also it will display both updated or non-updated rows also. 

return all rows

5.8 Don't use same column in dimension and measure

Same column for both a dimension and a measure are used in the given below query i.e  "ord" column used in both measure and dimension. Therefore it is giving the error message. 

measure and dimension not same name

5.9 NULL not allowed in DIMENSION BY or MEASURES clauses

Null value not allowed in dimension. Because, in general dimension have primary key column name. So, it doesn't have null values. If null values are allowed then it is difficult to identify the cell i.e. a reason null value not allowed in the dimension. So, it is giving error message. 

null value in dimension

5.10 Empty String (' ')

Empty string also not allowed in both dimension and measure. The purpose of the dimension and measure gives multi-dimensional based result set based on given column values. Then value is empty, the purpose is not fulfill. So, it is giving error message of empty string or empty column values.

empty string

5.11 Using For Loop

Using Model clause, looping operation is possible. Using For constructs the single formula can generate multiple formulas with positional references and it is also used to create new values. 

Using For construct can be used for dimension of numeric, date and date time data types. 

Increment and decrements expression can be used in numeric dimensions and interval for dimensions are used date or date time data types.  In the below query dimension has order id is numeric data type.  Here it creates the new value to column Test. 

for..loop

5.12 ITERATION

For repeating the rules for execution need iterate feature. In this example, sales [19] || 8 using rules creating new values for sales [19] and assigning value 8. This value will be execute exactly 5 times given as per condition in iterate (5) in sales [19].

iteration

5.13 Iteration using until Clause

The UNTIL clause is used in the iterate feature for checking at the end of each and every iteration. It shows iterated rules applied and executed at least once. It also do early termination based on condition given in the UNTIL clause, because in this below example iterate (4) times is given. But actually it is doing only one execution of the iteration because until condition is specified with value 8. So, it do early termination. 

until clause

Unit summary

This unit describes SQL Model clause syntax like dimension by, measure by and Rules. It also explains how to create new row and values. It also discuss about restriction about Null values, empty values in the dimension. 

Practical Exercise

Write appropriate Model Clause statement for below given Queries:

Acase_Model table

1)  Using SQL Model Clause display Accident_id, No_of_Accident from Acase_model table. Create new record Accident_id is 1017. The Accident_id 1017 has No_of_Accident value is total of No_of_Accident values of Accident_id 1004 and 1003

2) Using SQL Model Clause display Accident_type, Nationality, Gender, No_of_Accident. Dimension must have Nationality and gender.      

Question

Video Tutorial

Part1 SQLmodel clause

https://www.youtube.com/watch?v=qpMFvUZVnSU&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=1

Part2 Dimension by unique name

https://www.youtube.com/watch?v=x5dgt-thM_4&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=9

Part3 Model clause multiple dimension

https://www.youtube.com/watch?v=cRXSjU_LbYY&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=2

Part4 Model clause dimension aliasing

https://www.youtube.com/watch?v=YylylVDdFLg&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=5

Part5 dimension by expression

https://www.youtube.com/watch?v=5XgW91-I2pQ&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=3

Part6 model clause measures

https://www.youtube.com/watch?v=vst1IvDNyKQ&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=8

Part 7 model clause partition by

https://www.youtube.com/watch?v=3UlN77vryEc&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=7

Part 8 model clause rules 

https://www.youtube.com/watch?v=V2OkQ-m7TQI&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=6

part 9 model clause create new row 

https://www.youtube.com/watch?v=X0hmIL95WJE&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=4

Part10 SQL model clause return updated rows

https://www.youtube.com/watch?v=iaSP6iaAdxk&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=10

Part 11 SQL model clause return all rows

https://www.youtube.com/watch?v=ZmeC78VwGHs&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=11

Part 12 SQL same col name in dim and measures

https://www.youtube.com/watch?v=Rn_DdzWTf08&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=12

Part13 SQL model not null in dim by and measures

https://www.youtube.com/watch?v=V5QWhyw4rkI&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=14

Part 14 SQL model empty string

https://www.youtube.com/watch?v=8ktOwbTtUfU&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=13

Part 15 SQL model using for loop

https://www.youtube.com/watch?v=sRyIUFR7m-s&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=15

Part 16 SQL model iterate

https://www.youtube.com/watch?v=nMvta_8JdJA&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=16

Part 17 SQL model iterate until 

https://www.youtube.com/watch?v=st5ry-KGMeU&list=PLV3NgZiVnUZn6uZGXw5k60a2SJA1Bmb39&index=17

Lecturer Notes

OER UNIT 5 -SQL MODEL CLAUSE

Part-1-SQL-model-clause

https://www.slideshare.net/nbgirija/part-1-sql-model-clause

Part2-SQL-modeldimension-by

https://www.slideshare.net/nbgirija/part2-sql-modeldimension-by

Part-3-SQL-modelmultiple-dimension

https://www.slideshare.net/nbgirija/part-3-sql-modelmultiple-dimension

Part-4-dimension-by-aliasing

https://www.slideshare.net/nbgirija/part-4-dimension-by-aliasing

Part-5-dimesnion-by-expression

https://www.slideshare.net/nbgirija/part-5-dimesnion-by-expression

Part-6-SQL-modelmeasures

https://www.slideshare.net/nbgirija/part-6-sql-modelmeasures

Part-7-SQL-modelpartition-by

https://www.slideshare.net/nbgirija/part-7-sql-modelpartition-by

Part-8-SQL model rules

https://www.slideshare.net/nbgirija/part-8-sql-modelrules

Part-9-SQL-modelrules-create-new-row

https://www.slideshare.net/nbgirija/part-9-sql-modelrules-create-new-row

Part-10-SQL-model-clausereturn-updated-rows

https://www.slideshare.net/nbgirija/part-10-sql-model-clausereturn-updated-rows

Part-11-SQL-model-clausereturn-all-rows

https://www.slideshare.net/nbgirija/part-11-sql-model-clausereturn-all-rows

Part-12-SQL-model-clause-same-col-dim-and-measure

https://www.slideshare.net/nbgirija/Part-12-sql-model-clause-same-col-dim-and-measure

Part-13-SQL-model-clause-null-not-allowed-in-dim-and-measures

https://www.slideshare.net/nbgirija/part-13-sql-model-clause-null-not-allowed-in-dim-and-measures

Part-14-SQL-model-empty-string

https://www.slideshare.net/nbgirija/part-14-sql-model-empty-string

Part15-SQL-model-for-loop

https://www.slideshare.net/nbgirija/part15-sql-model-for-loop

Part-16 SQL-model-iteration

https://www.slideshare.net/nbgirija/part-16-sql-model-iteration

Part17-SQL-model-iterate-until

https://www.slideshare.net/nbgirija/part17-sql-model-iterate-until

References