CAR DEALERSHIP

Document Sample
CAR DEALERSHIP Powered By Docstoc
					CAR DEALERSHIP
DATAWAREHOUSE IMPLEMENTATION
Presented To:
Ms. Elena Teodorescu
Presented By:
Moeen Khurshid
000476852-3
Table of Contents




Contents                              page

Section A

1.    Coursework specification               3

2.    Warehouse Modelling                    4

3.    Warehouse Model                        5

4.    Warehouse BI                           6

5.    Fact tables                            6

6.    Dimensions                             7

7.    Levels                                 8

8.    Hierarchy                              8

9.    Attributes                             8

10.   Bitmap Indexes                         8

11.   Data Loading using SQL Loader          9

12.   CEO Queries Result                     12

13.   PL/SQL                                 18

      1.       Tables                        18

      2.       Dimensions                    22

      3.       Materialized Views            23

      4.       Materialized views            24

      5.       Indexes                       21

      6.       Bitmap Indexes

14.   References                             30


Section B

1.    Critical review                        31




                                                  2
SECTION A
Warehouse Modeling

The    Datawarehouse        is     built   on    the    schemas       defined    in      this
documentation. It follows the OLAP system which is implemented in the
demonstration as assumed instead of the normal OLTP systems.

The warehouse consists of schema which is known as Star as all the
keys from the dimensions are linked into the fact table .

The warehouse assumes two fact tables named

1.     DW_CAR_SALE

2.     DW_TEST_DRIVE

The data stored in these fact tables are the foreign keys which are
taken from the tables

DW_CUSTOMER, DW_CAR, DW_SALEREP , DW_TIME but the DW_BANK
has only one foreign key in the fact table DW_CAR_SALE as it does not
need to concern and test drives as we have assumed that the test drive
is free.

The model can been seen clearly at the Warehouse model or the logical
model diagram given in the later section of this document.

All the tables (excluding fact tables) are placed in the tablespace
“DIMENSIONS”.         The   fact    tables      are    placed   in    the   table     space
“EASYDW_DEFAULT”.

All   the   indexes    including     the     bitmap     index   are    created      in   the
tablespace”SUMMARY”.

A column is left with the name of notes in each table so that if there is
a modification in the datawarehouse you can simple rename and use
those columns.




                                                                                           3
Warehouse Model




                  Figure 1.1




                               4
Warehouse BI

The warehouse contains several types of business Intelligence as we all
know and they are mainly

1.     DIMENSIONS

2.     FACT TABLES

3.     HIRARCHYS

4.     ATTRIBUTES

5.     FACT TABLES

I have implemented them so that we can process data according to the
business Intelligence using cubes and rollups. Also some grouping sets
are implemented to maintain data integrity.


Fact tables

The fact tables used in this case are the DW_CAR_SALE and
DW_TEST_DRIVE respectively.

     a. DW_CAR_SALE




       Figure                                                        1.2
The   “Foreign   Keys”   SALE_ID,   TIME_ID,    CUSTOMER_ID,   CAR_ID,
BRANCH_ID are coming from the tables DW_CUSTOMER, DW_TIME,
DW_BANK, DW_CAR and DW_SALEREP respectively.

And    the   values   CAR_SALE_PRICE   and     CAR_SALE_DATE   are   the
measurements which are there.


                                                                      5
     b. DW_TEST_DRIVE




       Figure 1.3



Similarly    the    values        of    “Foreign    Keys”    CAR_ID,CUSTOMER_ID,
TIME_ID,SALE_ID,BRANCH_ID                  ARE      coming     from     the     tables
DW_CUSTOMER, DW_TIME, DW_CAR and DW_SALEREP.                           The fact table
DW_TEST_DRIVE has MILES values as the measurement.


Dimensions

The system has a single dimension which is named as “view_data”. It
is   based   on    the    data    from    the    tables   DW_CUSTOMER,        DW_CAR,
DW_TIME respectively.

The dimension has been checked and found to be valid as by running

Select * from user_dimensions;

The reason for creating a single dimension on multiple tables was that
if we have about 2000 tables we want to retrieve the data from “we
can’t afford each of them as unique DIMENSION as it would cause a
performance breakdown and the datawarehouse might collapse”.

This   has   been        tested   and    using     the    OWB(ORACLE    WAREHOUSE
BUILDER) tool on local databases as well.

Dimensions are logical and are used to characterize the data according
to its ATTRIBUTES and LEVELS




                                                                                    6
Levels

The datawarehouse has different levels of abstraction. They are based
on the columns in the tables. They have been defined in the dimension
named “view_data”

The levels defined here are :

    LEVEL dw_customer is dw_customer.customer_id

    LEVEL dw_customer_city is dw_customer.customer_city

    LEVEL dw_customer_country is dw_customer.customer_country

    LEVEL dw_car is dw_car.car_id

    LEVEL dw_car_model is dw_car.car_model

    LEVEL dw_car_make is dw_car.car_manufacturer

    LEVEL dw_time is dw_time.time_id

    LEVEL dw_time_month is dw_time.month

    LEVEL dw_time_quarter is dw_time.quarter

    LEVEL dw_time_year is dw_time.year

Hierarchy

The warehouse has been also implementing the hierarchy like I want to
group my values I would use a hierarchy as shown in the figure below.




                                    VIEW_DATA




              DW_TIME             DW_CUSTOMER            DW_CAR




         TIME_YEAR              CUSTOMER_COUNTRY   CAR_MAKE
         TIME_QUARTER           CUSTOMER_CITY      CAR_MODEL
         TIME_MONTH             CUSTOMER           CAR


                                                                        7
                                    Figure 1.4



The hierarchys used in this project are :

dw_customer CHILD OF dw_customer_city CHILD OF
dw_customer_country

dw_car CHILD OF dw_car_model CHILD OF dw_car_make

dw_time CHILD OF dw_time_month CHILD OF dw_time_quarter CHILD
OF dw_time_year

hierarchy defines how the levels can be used in the warehouse when
the data is being queried.


Attributes

The attributes in the project determines the uniqueness of the
hierarchys and how they can be linked up together.

Like I have used

    ATTRIBUTE dw_customer DETERMINES

      dw_customer.customer_id

    ATTRIBUTE dw_car DETERMINES

      dw_car.car_id

these are just examples of the code used to create a dimension in the
warehouse the complete code can be shown at the end of the
document.


Bitmap Indexes

The bitmap indexes are placed on the columns which are used most
frequently   in   the    database   although     the   B-tree   are   automatically
created when a table is there but still the bitmap indexes are used to
speed up the performance further.

The indexes created on the table are there on columns like

 TABLE NAME                            COLUMNS                            INDEX
DW_CUSTOMER             DW_TEST_ID,DW_CAR_ID,CUSTOMER_ID                BITMAP
DW_CAR                  DW_TEST_ID,DW_CAR_ID,CAR_ID                      BITMAP


                                                                                  8
DW_TIME               TIME_ID,DW_TEST_ID,DW_CAR_ID                      BITMAP
DW_SALEREP            SALE_ID,DW_TEST_ID,DW_CAR_ID                      BITMAP



                                     Figure 1.5


Data Loading using SQL Loader

1- the bank table is populated using the sql loader from the tools
   which oracle provides.
2- add   the   data   into   the    database   created   in   the   access   named
   “loading.mdb”
3- The database has columns named BRANCH_ID,BRANCH_NAME,
   BRANCH_TYPE.
4- Added   few    records     and    implemented     the      primary   keys     on
   BRANCH_ID which can be referred to as cleaning before loading the
   data into the warehouse.
5- The same structure in the ORACLE named “DW_BANK” and the table
   is EMPTY
6- Select and see data




                                     Figure 1.6



                                                                                 9
7- Export data to a text file which would be used in the loading of data
   from Microsoft Access and name it as “BRANCH.txt” using the
   default comma delimiter.
8- Sample data in text file:

      201,"pass","personal"

      202,"fail","personal"

      203,"pass","personal"

      204,"pass","corporate"

      205,"pass","corporate"

9- Create a control file named “Myfile.ctl” the name of the file would
   be given to the sql loader at the time of loading of data from access
   to warehouse.


10-   Sample data in Myfile.ctl

      load data

      infile 'J:\BRANCH.TXT' "str '\r\n' "

      insert into table DW_BANK

      fields terminated by ','

      (      BRANCH_ID INTEGER,

             BRANCH_APP_STATUS CHAR,

             BRANCH_TYPE CHAR
      )

11-   Used command prompt to create a batch file(a file which can self
      execute ) named “runner.bat” with the following code.
12-   sqlldr control=j:\myfile.ctl
13-   here j is the name of the drive the control file is on.
14-   Give the username and password on the command prompt and
      would show something like




                                                                     10
                              Figure 1.7


15-   When the loader executes as in the above screen shot it has
  loaded data in this.




                                                                    11
Queries Result

   a) How much each agent sold per month/ quarter/year?

select ss.sale_rep_name NAME, t.month PER_MONTH, t.quarter
PER_QARTER, t.year PER_YEAR, COUNT(s.dw_sale_id) TOTAL_SALE

from dw_time t,dw_car_sale s,dw_salerep ss

where t.time_id=s.time_id

and s.sale_id= ss.sale_id

group by grouping sets
((ss.sale_rep_name,t.month),(t.quarter,ss.sale_rep_name),(t.year,ss.s
ale_rep_name))

      OUTPUT:




                              Figure 1.8

   b) How many customers have test driven at least 2 different
      models of cars?
      select tstdrv.CUSTOMER_ID, COUNT(tstdrv.car_id) Total,
      c.CAR_MODEL
      from dw_test_drive tstdrv, ( select customer_id, car_id from
      dw_test_drive) tdr, dw_car c
      where tdr.customer_id = tstdrv.customer_id
      and tstdrv.car_id <> tdr.car_id
      and c.car_id = tstdrv.car_id
      group by tstdrv.CUSTOMER_ID, c.car_model
      having COUNT (tstdrv.car_id)>=2
      order by tstdrv.customer_id




                                                                     12
OUTPUT:




                             Figure 1.9
  c) What   is   the   average   number   of   test-drives   per   car
     manufacturer?


SELECT C.CAR_MAKE ,COUNT(C.CAR_MAKE)/(select
count(distinct(car_make)) from dw_car)
 FROM DW_CAR C,DW_TEST_DRIVE T
 WHERE C.CAR_ID=T.CAR_ID
 GROUP BY C.CAR_MAKE




                                                                   13
OUTPUT:




                                Figure 2.0
  d) What    is   the   total   amount       of   sales   per   month,   per
     manufacturer? What about per quarter of the year and the
     overall total sales?


select t.month,t.quarter,c.car_make,SUM(s.sale_price)
from dw_car_sale s,dw_time t,dw_car c
where s.time_id=t.time_id and c.car_id=s.car_id
group by cube(t.quarter,t.month,c.car_make)




                                                                         14
OUTPUT:




                           Figure 2.1
  e) From all customers and for each month, find those who
     have bought the highest priced car for each model.
select cs.customer_name,cr.car_model,tm.month
from dw_car_sale ss,dw_car cr,dw_customer cs, dw_time tm
where cr.car_id= ss.car_id
and cs.customer_id = ss.customer_id
and tm.time_id = ss.time_id
and ss.sale_price= (
select MAX(s.sale_price)
from dw_car_sale s,dw_customer c,dw_car r,dw_time t
where s.customer_id=c.customer_id
and r.car_id=s.car_id
and t.time_id=s.time_id)




                                                             15
OUTPUT:




                             figure 2.2

  f) For each month and for each city, find the make of car that
        sold most.



Select t.MONTH, c.CUSTOMER_CITY CITY, cr.CAR_MAKE
MANUFACTURER,count(ds.CAR_ID) COUNTS

From dw_time t, dw_customer c, dw_car_sale ds, dw_car cr

Where

T.TIME_ID = ds.TIME_ID

And cr.CAR_ID = ds.CAR_ID

Group by t.MONTH, cr.CAR_MAKE, c.CUSTOMER_CITY




                                                              16
OUTPUT:




          Figure 2.3




                       17
PL/SQL CODE
Tables

DW_BANK

create table dw_bank

  (   branch_id number not null enable,

      branch_app_status varchar2(30 byte),

      branch_type varchar2(30 byte),

      Notes varchar2(30 byte),

      constraint dw_bank_pk primary key ( branch_id )

  )

Tablespace dimensions ;


DW_CAR

create table dw_car

  (   car_id number not null enable,

      car_model varchar2(30 byte),

      car_make varchar2(30 byte),

      car_horsepower number,

      Notes varchar2(30 byte),

      constraint dw_car_pk primary key ( car_id )

  )

 tablespace dimensions ;



DW_CAR_SALE

create table dw_car_sale

  (   sale_id number,

      time_id number,

      customer_id number,

      branch_id number,

      car_id number,

      dw_sale_id number not null enable,

                                                        18
      sale_date date,

      sale_price number,

      Notes varchar2(30 byte),

      constraint dw_car_sale_pk primary key ( dw_sale_id),

      constraint dw_customer foreign key ( customer_id )

      references dw_customer ( customer_id ) enable,

      constraint dw_car foreign key ( car_id )

      references   dw_car ( car_id ) enable,

      constraint dw_time foreign key ( time_id )

      references   dw_time ( time_id ) enable,

      constraint dw_sale_rep foreign key ( sale_id )

      references   dw_salerep ( sale_id ) enable,

      constraint dw_car_sale_dw_bank_fk1 foreign key ( branch_id )

      references   dw_bank ( branch_id ) enable

 )

Tablespace easydw_default ;


DW_CUSTOMER

create table dw_customer

 (    customer_id number not null enable,

      customer_name varchar2(30 byte),

      customer_city varchar2(30 byte),

      customer_country varchar2(30 byte),

      customer_gender varchar2(30 byte),

      customer_occupation varchar2(30 byte),

      customer_region varchar2(30 byte),

      Notes varchar2(30 byte),

      constraint dw_customer_pk primary key ( customer_id )

 )

 tablespace dimensions ;




                                                                19
DW_SALEREP

create table dw_salerep

  (   sale_id number not null enable,

      sale_rep_name varchar2(30 byte),

      sale_rep_position varchar2(30 byte),

      sale_rep_address varchar2(30 byte),

      sale_rep_city varchar2(30 byte),

      sale_rep_dob date,

      Notes varchar2(30 byte),

      constraint dw_salerep_pk primary key ( sale_id )

  )

 tablespace dimensions ;



DW_TEST_DRIVE

create table dw_test_drive

  (   car_id number,

      customer_id number,

      time_id number,

      sale_id number,

      branch_id number,

      miles number,

      dw_test_id number not null enable,

      Notes varchar2(30 byte),

      constraint dw_test_drive_pk primary key ( dw_test_id ),

      constraint dw_car_id1 foreign key ( car_id )

       references   dw_car ( car_id ) enable,

      constraint dw_customer_id1 foreign key ( customer_id )

       references   dw_customer ( customer_id ) enable,

      constraint dw_time1 foreign key ( time_id )

       references   dw_time ( time_id ) enable,

      constraint dw_sale1 foreign key ( sale_id )



                                                                20
         references    dw_salerep ( sale_id ) enable,

        constraint dw_branch1 foreign key ( branch_id )

         references    dw_bank ( branch_id ) enable

    )

    tablespace easydw_default ;




DW_TME

create table dw_time

    (   time_id number not null enable,

        day number,

        month number,

        year number,

        quarter number,

        Notes varchar2(30 byte),

        constraint dw_time_pk primary key ( time_id )

)

Tablespace dimensions ;



Indexes

Dw_car

 Create bitmap index      dw_car_ind on     dw_car ( car_model ,
car_make )

    tablespace summary ;



Dw_customer

Create bitmap index   dw_cust_ind on         dw_customer (
customer_city , customer_country )

    tablespace summary ;



Dw_salerep




                                                                   21
Create bitmap index   dw_sale_ind on     dw_salerep (
sale_rep_name , sale_rep_position )

 tablespace summary ;

Dw_time

 Create bitmap index   dw_time_ind on     dw_time ( month , year ,
quarter )

 tablespace summary ;

Dimension

Create dimension view_data

    LEVEL dw_customer is dw_customer.customer_id

    LEVEL dw_customer_city is dw_customer.customer_city

    LEVEL dw_customer_country is dw_customer.customer_country

    LEVEL dw_car is dw_car.car_id

    LEVEL dw_car_model is dw_car.car_model

    LEVEL dw_car_make is dw_car.car_make

    LEVEL dw_time is dw_time.time_id

    LEVEL dw_time_month is dw_time.month

   LEVEL dw_time_quarter is dw_time.quarter

    LEVEL dw_time_year is dw_time.year

    HIERARCHY dw_customer1

     ( dw_customer CHILD OF dw_customer_city CHILD OF
dw_customer_country )

    HIERARCHY dw_car1

      ( dw_car CHILD OF dw_car_model CHILD OF dw_car_make )

    HIERARCHY dw_time1

      ( dw_time CHILD OF dw_time_month CHILD OF dw_time_quarter
CHILD OF dw_time_year)

    ATTRIBUTE dw_customer DETERMINES

      dw_customer.customer_id

    ATTRIBUTE dw_car DETERMINES

      dw_car.car_id

    ATTRIBUTE dw_time DETERMINES

    dw_time.time_id

                                                                22
Materialized View Logs

DW_BANK

 Create materialized view log on    dw_bank

pctfree 60 pctused 30 initrans 1 maxtrans 255 logging

 storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 tablespace easydw_default

 with primary key ( branch_app_status , branch_type , notes )
excluding new values;



DW_CAR

 create materialized view log on   dw_car

pctfree 60 pctused 30 initrans 1 maxtrans 255 logging

 storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 tablespace easydw_default

  with primary key ( car_horsepower , car_make , car_model , notes
) excluding new values;



DW_CUSTOMER

 create materialized view log on   dw_customer

pctfree 60 pctused 30 initrans 1 maxtrans 255 logging

 storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 tablespace easydw_default

 with primary key ( customer_city , customer_country ,
customer_gender , customer_name , customer_occupation ,
customer_region , notes ) excluding new values;




                                                                    23
DW_SALEREP

 create materialized view log on   dw_salerep

pctfree 60 pctused 30 initrans 1 maxtrans 255 logging

 storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 tablespace easydw_default

 with primary key ( notes , sale_rep_address , sale_rep_city ,
sale_rep_dob , sale_rep_name , sale_rep_position ) excluding new
values;



DW_TEST_DRIVE

 create materialized view log on   dw_test_drive

pctfree 60 pctused 30 initrans 1 maxtrans 255 logging

 storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 tablespace easydw_default

 with primary key ( branch_id , car_id , customer_id , miles ,
sale_id , time_id ) excluding new values;



DW_TIME

 create materialized view log on   dw_time

pctfree 60 pctused 30 initrans 1 maxtrans 255 logging

 storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 tablespace easydw_default

 with primary key ( day , month , notes , quarter , year )
excluding new values;



Materialized views

All the materialized views are for the queries which are giving the
output for the user viewing and have been categorized as per query.



                                                                      24
Some of the views are taken as from the sql developer due to the
shortage of time.



A) CREATE MATERIALIZED VIEW             QUERYA

 organization heap pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging

 storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 tablespace ism_1

 build immediate

 Using index

 Refresh complete on demand

 Using default local rollback segment

 Enable query rewrite

 As select ss.sale_rep_name name, t.month per_month, t.quarter
per_qarter, t.year per_year, count(s.dw_sale_id) total_sale

From dw_time t,dw_car_sale s,dw_salerep ss

Where t.time_id=s.time_id

And s.sale_id= ss.sale_id

Group by grouping sets
((ss.sale_rep_name,t.month),(t.quarter,ss.sale_rep_name),(t.year,ss.s
ale_rep_name));



B) CREATE MATERIALIZED VIEW             QUERYB

 Organization heap pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging

 Storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 Tablespace ism_1

 Build immediate

 Using index

 Refresh complete on demand

 Using default local rollback segment


                                                                    25
 Enable query rewrite

 As select tstdrv.customer_id, count(tstdrv.car_id) total, c.car_model

From dw_test_drive tstdrv, ( select customer_id, car_id from
dw_test_drive) tdr, dw_car c

Where tdr.customer_id = tstdrv.customer_id

And tstdrv.car_id <> tdr.car_id

And c.car_id = tstdrv.car_id

Group by tstdrv.customer_id, c.car_model

Having count (tstdrv.car_id)>=2;



C) CREATE MATERIALIZED VIEW QUERYC

 Organization heap pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging

 Storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 Tablespace ism_1

 Build immediate

 Using index

 Refresh complete on demand

 Using default local rollback segment

 Enable query rewrite

 As select c.car_make ,count(c.car_make)/(select
count(distinct(car_make)) from dw_car)

From dw_car c,dw_test_drive t

Where c.car_id=t.car_id

Group by c.car_make;

Create materialized view   querye

 Organization heap pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging

 Storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 Tablespace ism_1


                                                                     26
 Build immediate

 Using index

 Refresh complete on demand

 Using default local rollback segment

Enable query rewrite

 As select t.month,t.quarter,c.car_make,sum(s.sale_price)

From dw_car_sale s,dw_time t,dw_car c

Where s.time_id=t.time_id and c.car_id=s.car_id

Group by cube(t.quarter,t.month,c.car_make);



F) CREATE MATERIALIZED VIEW             QUERYF

 Organization heap pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging

 Storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 Tablespace ism_1

 Build immediate

 Using index

 Refresh complete on demand

 Using default local rollback segment

Enable query rewrite

 As select cs.customer_name,cr.car_model,tm.month

From dw_car_sale ss,dw_car cr,dw_customer cs, dw_time tm

Where cr.car_id= ss.car_id

And cs.customer_id = ss.customer_id

And tm.time_id = ss.time_id

And ss.sale_price= (

Select max(s.sale_price)

From dw_car_sale s,dw_customer c,dw_car r,dw_time t

Where s.customer_id=c.customer_id

And r.car_id=s.car_id



                                                                    27
And t.time_id=s.time_id);

G-) create materialized view queryg

 Organization heap pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging

 Storage(initial 57344 next 57344 minextents 1 maxextents
2147483645

 Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

 Tablespace "ism_1"

 Build immediate

 Using index

 Refresh complete on demand

 Using default local rollback segment

 Disable query rewrite

 As select t.month, c.customer_city city, cr.car_make
manufacturer,count(ds.car_id) counts

From dw_time t, dw_customer c, dw_car_sale ds, dw_car cr

Where

T.time_id = ds.time_id

And cr.car_id = ds.car_id

Group by t.month, cr.car_make, c.customer_city;




                                                                    28
References

  1- Some of the material has been taken from online ORACLE
     documentation.
  2- Some of the references given here might be linked with the
     tutorials as the data warehouse was implemented keeping the
     class tutorials in front




                                                             29
SECTION B

I have created the datawarehouse by putting dimensions and facttable
etc but was unable to do the partitioning according to the regions i.e.
east, west, north, south due to the short time.I have placed columns
shown in screen shots of data that they contain east,west,north and
south regions but the true functionality is achieved only when I have
replication (DBA RIGHTS) in both databases OBIWAN and QUIGON and
my north,south are in OBIWAN and my east,west are in QUIGON. I can
write a trigger to tell the database which database to use to insert data
with the if/else condition.

I have left a column with the name of notes in each table except for
the fact tables so that if there is a modification in the design of the
warehouse you can easily modify the names and use data from there.

I could have created more dimensions but as in the OWB and also as i
have quoted only one dimension fulfilled my tasks.

Further more i think that the students could have done it in a better
way if there was some front end interface to be implemented so that
every one knows how the actual implementation of a warehouse is
going to be it can be in ORACLE FORMS and REPORTS which can be
used to display the queried data.

The teaching method was very good and couldn’t be better to get the
understanding of the coursework implementation every thing is there
which was in tutorials and lectures to help complete the course work.
The structure of the course was very well designed and patterned.

Hoewever   I think that the    replication using some    agents on   the
materialized view can be implemented to make it even better.

All the outputs from the queries are give from the screen shots
taken from the oracle reports.




                                                                      30

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:57
posted:2/21/2011
language:English
pages:30