Scenario for Using SQL Scripts to Create Disconnected Tables and Indexes

A simple retail business application might keep track only of products sold and the stores where sales occur. A disconnected version of this application might consist of two dimension tables (Product and Store) and a central fact table called SalesFact. Each of these three tables would need a corresponding SQL Script file for the disconnected application.

This section describes the SQL Script files for these three tables.

Product.sql

drop table Product;

create table Product (

P_ID integer,

P_Name char(30),

);

Store.sql

drop table Store;

create table Store (

S_ID integer,

S_Name char(30),

S_City char(20),

S_State char(2)

);

SalesFact.sql

drop table SalesFact;

create table SalesFact (

F_ProductID integer,

F_StoreID integer,

F_Timestamp datetime,

F_Quantity smallint,

F_Price smallint

);

Optionally, Oracle BI Disconnected Analytics applications might have SQL script files for creating one or more indexes on a given table. Continuing to use the simple retail business example, this section describes a SQL script for creating an index on the P_Name column of the Product table.