Decode Function

n Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

expression is the value to compare.

search is the value that is compared against expression.

result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

For example:

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,
decode(supplier_id,	10000,	'IBM',
	10001,	'Microsoft',
	10002,	'Hewlett Packard',
		'Gateway') result
FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
result := 'IBM';

ELSIF supplier_id = 10001 THEN
result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';

ELSE
result := 'Gateway';

END IF;

The decode function will compare each supplier_id value, one by one.

second Example:

Using date compare two dates

Using the decode function to compare two dates (D1 and D2), where if D1 > D2, the decode function should return D2. Otherwise, the decode function should return D1.

decode((D1 - D2) - abs(D1 - D2), 0, D2, D1)

for more examples .............click to new window

Read more »

Differences between OLTP and OLAP databases.

Differences between OLTP and OLAP databases.

Relational Database (OLTP)
Data is atomized
Data is current
Processes many records at a time
Process oriented
Designed for highly structured repetitive processing

Dimensional Database (OLAP)

Data is summarized
Data is historical
Processes one record at a time
Subject oriented
Designed for highly unstructured analytical processing

Read more »

Operational data store (ODS)

Operational data store

A subject-oriented system that is optimized for looking up one or two records at a time for decision making. An operational data store is a hybrid form of data warehouse that contains timely, current, integrated information. The data typically is of a higher level granularity than the transaction. You can use an operational data store for clerical, day-to-day decision making. This data can serve as the common source of data for data warehouses.

Read more »

Data Mart

What is Data mart?

A subset of data warehouse that is stored in a smaller database and that is oriented toward a specific purpose or data subject rather than for enterprise-wide strategic planning. A data mart can contain operational data, summarized data, spatial data, or metadata. Typically, you use a dimensional data model to build a data mart.

Read more »

The Star Schema

A good dimensional data warehouse has a simple database structure. Technically, a simple structure means faster queries. In a dimensional data warehouse, the implementation relational database has two types of tables, the fact table and the dimension table. The fact table consists of the facts (or measures) of the business. Dimension tables contain descriptions for querying the database.

The tables in a data warehouse are related such that the schema looks like a star, hence the term star schema


A star consists of a fact table surrounded by two or more dimension tables. A one-star structure has one fact table only. A multi-star structure has multiple fact tables, one for each star

dimension tables may be shared among multiple fact tables.

In Data warehouse the star structure, the snowflake structure could also be used However, it is more difficult to model than the star structure. As well, the snowflake structure is not that easy to understand and implement and the performance of its queries is slower than that of the star structure. These drawbacks make the snowflake structure unsuitable for dimensional data warehousing.

The Dimension, Fact table used term to suffix the table _dim, _fact.

Every Dimension table has one or more primary key which is reference to fact table. so based on this we can build query to retrieve the date what we required.

Above figure..

The customer_dim (customer dimensional table) is reference the Sales_order_fact table so that we can trace customer information. By using the Product_id in sales_order_fact table, we can retrieve the product information in production_dim table. By joining the sales_order_fact table and the date_dim table on the date_id column, you’ll get the order date. And by joining the fact table and the order_dim table on the order_id column, you obtain the order number.

Read more »