-- Fact table to be partitioned (range partition shown below)

CREATE TABLE fact_sales_detail
(sales_transaction_id    NUMBER CONSTRAINT sales_trx_id_nn NOT NULL,
 country_id              VARCHAR2(2) CONSTRAINT country_id_nn NOT NULL,  -- cc_TLD
 transaction_type_id     NUMBER CONSTRAINT trx_type_id_nn NOT NULL,
 product_id              NUMBER CONSTRAINT product_id_nn NOT NULL,
 billing_address_id      NUMBER CONSTRAINT bill_addr_id_nn NOT NULL,
 shipping_address_id     NUMBER,
 sales_rep_id            NUMBER,
 transaction_date        DATE CONSTRAINT trx_date_nn NOT NULL,
 invoice_number          VARCHAR2(30) CONSTRAINT invoice_num_nn NOT NULL,
 sales_amount            NUMBER CONSTRAINT sales_amount_nn NOT NULL,
 sales_discount          NUMBER DEFAULT 0,
 sales_commission        NUMBER DEFAULT 0,
 sales_tax               NUMBER,
 freight_amount          NUMBER,
 sales_desc              VARCHAR2(500),
 created_date            DATE DEFAULT SYSDATE NOT NULL)
COMPRESS
PARTITION BY RANGE(transaction_date)
(PARTITION fsdtsp_200501 VALUES LESS THAN (TO_DATE('02/01/2005','DD/MM/YYYY')),
 PARTITION fsdtsp_200502 VALUES LESS THAN (TO_DATE('03/01/2005','DD/MM/YYYY')),
 PARTITION fsdtsp_200503 VALUES LESS THAN (TO_DATE('04/01/2005','DD/MM/YYYY')),
 PARTITION fsdtsp_200504 VALUES LESS THAN (TO_DATE('05/01/2005','DD/MM/YYYY')),
 PARTITION fsdtsp_all    VALUES LESS THAN (MAXVALUE)
);

-- Dimension table (dim_locations)
-- There can be dimension tables for other identifers
-- product_id, transaction_type_id etc. with Primary Key
-- foreign key referencial constraints

CREATE TABLE dim_locations
(country_id              VARCHAR2(2) NOT NULL,
 country_code            VRACHAR2(30),
 country_desc            VARCHAR2(100) NOT NULL,
 created_date            DATE DEFAULT SYSDATE NOT NULL
);

-- Create constraints (primary key)
   ALTER TABLE dim_locations
   ADD CONSTRAINT dim_locations_pk
   PRIMARY KEY (country_id)
   DISABLE VALIDATE;


-- Partitioned table analysis
SELECT table_name, partition_name, high_value, partition_position, num_rows, last_analyzed
FROM   user_tab_partitions
WHERE  table_name = 'FACT_SALES_DETAIL'
;

-- Create constraints (primary/foreign key)

   ALTER TABLE fact_sales_detail
   ADD CONSTRAINT fact_sales_pk
   PRIMARY KEY (sales_transaction_id)
   DISABLE VALIDATE;

-- The values for primary key are created by use of a sequence and a before insert trigger.

   ALTER TABLE fact_sales_detail
   ADD CONSTRAINT fs_country_id_fk
   REFERENCES dim_locations(country_id)
   ENABLE NOVALIDATE;

-- Bitmap indexes on low cardinality columns (gender, country code etc.)
-- Indexes are also required on other columns based on BI reporting requirements.

   CREATE BITMAP INDEX fs_country_id_bidx ON
   fact_sales_detail(country_id)
   TABLESPACE fact_sales_idx 
   NOLOGGING PARALLEL 8 
   COMPUTE STATISTICS;

-- Loading data from flat files Using SQLLDR
 CSV file input
 Fixed Width file input
 Skip, ContinueIF option
 Streaming Option

-- Loading data from flat files Using External Table
 Oracle External Table

-- Basic Summarized Materialized View (country, product, date)

CREATE MATERIALIZED VIEW product_sales_mv
PCTFREE 0
TABLESPACE ts_salesdw_mv
STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0)
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS 
SELECT
 l.country_desc,
 p.product_name,
 TO_CHAR(s.transaction_date,'mm') transaction_month,
 TO_CHAR(s.transaction_date,'yyyy') transaction_year,
 SUM(s.sales_amount-s.sales_discount)  dollar_sales,
 SUM(s.sales_tax)  sales_tax,
 COUNT(*) AS cnt
FROM fact_sales_detail s,
     dim_products p,
     dim_locations l
WHERE s.product_id = p.product_id 
AND   s.country_id = l.country_id
GROUP BY
 l.country_desc,
 TO_CHAR(s.transaction_date,'mm'),
 TO_CHAR(s.transaction_date,'yyyy'),
 p.product_name;