Forum Stats

  • 3,854,228 Users
  • 2,264,343 Discussions
  • 7,905,617 Comments

Discussions

Understanding Star Schema Operations

Sudipta Gupta
Sudipta Gupta Member Posts: 128
edited Aug 3, 2009 11:29AM in OLAP
Hi All,

I worked on Oracle Answers and have knowledge on Data Warehousing concepts. I have enough oracle documentation on OLAP, OBIEE, Data Warehousing.

But i 'm not able to understand the star schema concepts in and out. I'm lost on all documents, where i'm getting the simple answer on the following topics:

1. Is it necessary to create dimensions using CREATE DIMENTION or it can be a simple relational table structure with CREATE TABLE statement?

2. What will be the steps to create a very basic/simple star schema using DML statements not using any tool?

3. Where I can get very basic level step by step tutorial to create a very simple star schema by sql -> loading test data by sql commands -> then what queries we can use to get BI reports or using OBIEE to create a simple rpd.

Please give me some idea on this as I have to create a small OBIEE repository where i have to do create the warehouse and ETL process, everything.

Pls advise.

Thanks in Advance.
Sudipta
Tagged:

Answers

  • Brijesh Gaur
    Brijesh Gaur Member Posts: 495
    If you are interested in querying OLAP cubes using BIEE you can follow [this link|http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/olap/index.html]. This is for oracle10gR2.

    Same kind of tutorial is availble for 11g as well.

    does this help?

    Rgds,
    Brijesh
  • 611580
    611580 Member Posts: 12
    No my question was on creating the star schema. If i have to create a very simple star schema then can I create the star schema with the following relational tables?

    create table dim_products (
    product_id number primary key,
    product_name varchar2(32),
    brand varchar2(32),
    category_name varchar(32),
    sub_category_name varchar2(32)
    )

    create table dim_time (
    time_id number primary key,
    selling_date date,
    selling_day number(2),
    selling_month varchar2(12),
    selling_Quarter varchar2(6),
    year number(4)
    )

    create table dim_region (
    region_id number primary key,
    store_name varchar2(32),
    city_name varchar2(32),
    state_name varchar2(32),
    country_name varchar2(32),
    region_name varchar2(32)
    )

    create table fact_sales (
    sale_id number primary key,
    region_id number ,
    time_id number ,
    product_id number ,
    unit_sold number,
    constraint fact_sales1 foreign key (region_id) references dim_region (region_id),
    constraint fact_sales2 foreign key (time_id) references dim_time (time_id),
    constraint fact_sales3 foreign key (product_id) references dim_products (product_id)
    )

    Or I have to create the DIMENSIONS explicitly, in order to building the hierarchy levels?

    Regards
    Sudipta
  • Brijesh Gaur
    Brijesh Gaur Member Posts: 495
    These table looks fine. These tables(or views on top of it) then need to be source for loading your OLAP dimensions. for OLAP dimensions you need to define levels, hierarchies and mapping.

    The fact would be source of data for an OLAP cube.

    Rgds,
    Brijesh
  • 611580
    611580 Member Posts: 12
    Thanks for th reply :)

    I think i'm getting it, Just couple of very simple questions, If answered, the the whole picture will be clear to me...

    1. Do we always need to have Relational Star schema? then from there DIMENSIONS, CUBES [Which is part of Oracle OLAP] will be created [Yes/No]

    2. Is it possible to have Data Warehouse in relational model without using Oracle OLAP? If yes then how drill down intelligence will come?

    3. Do other databases like DB2 and SQL Server, have similar concepts like Oracle OLAP?

    Please let know know the answers as these are all things required to understand the whole picture.

    Thank in advance :)

    Regards
    Sudipta
  • Brijesh Gaur
    Brijesh Gaur Member Posts: 495
    edited Aug 3, 2009 7:13AM
    1. Do we always need to have Relational Star schema? then from there DIMENSIONS, CUBES Which is part of Oracle OLAP will be created Yes/No
    => You can have star or snowflake schema in relational side and same can be implemented in olap side(though I never used snowflake through OLAP).

    2. Is it possible to have Data Warehouse in relational model without using Oracle OLAP? If yes then how drill down intelligence will come?
    =>I am sure that the drill down can happen on relational star schema using OBIEE though not sure about discoverer.

    3. Do other databases like DB2 and SQL Server, have similar concepts like Oracle OLAP?
    => MS SQL server has olap(microsoft analysis services - MSAS).

    You can go through olap blogs to know more. They are very good and informative.

    http://oracleolap.blogspot.com/

    http://www.oracle.com/technology/products/bi/olap/index.html

    Rgds,
    Brijesh

    Edited by: Brijesh Gaur on Aug 3, 2009 4:41 PM
  • 611580
    611580 Member Posts: 12
    Thanks a lot Brijesh. It really helped me a lot :)

    One Last question: Do ETL process only loads data into the star schema (not directly into the dimensions)?

    Rgds
    Sudipta
  • Brijesh Gaur
    Brijesh Gaur Member Posts: 495
    Hi,

    If it is ROLAP dimension it will load into relational tables. If it is MOLAP dimension it will load into OLAP dimension. Please note that the OLAP dimension can be created through OWB.

    Thanks
    Brijesh
This discussion has been closed.