Help Needed for DataWarehousing Project 2

[scorpionking27]

Hi'll
I am new member of this forum. I have a question. I am currently pursuing MS ( Comp Sc) and as the part of the program, I am supposed to work on a Master's project.

I want to do my project in the field of Data Warehousing (be it DW or DM or Analytical Reporting). Currently my school doesnt have any infrastructure for this field . So I have to do everything by myself.

I do have access to Informatica(ETL), BO(Reporting) and 2-3 different databases. So I want to know what kind of project can I do in this field which is feasible and at a small level, so that it is achievable too

I would really appreciate , if anyone can give me some idea/suggestion in this field i.e. what to do and is that stuff achievable and any other imp information.
Any idea/suggestion will be of great help to me

Thanks and Regards,
Vikas R

 

[joe69008]

you can imagine a datamart about product sales with some measure but the matter is the ETL, you must have an OLTP database ... so you can create one, or take one somewhere.
Or according to the OLTP you will find, create a datawarehouse about it.

 

[hokah]

You can import data for example into PostreSQL database and simulate that is yours OLTP database. After it I would suggest simulate ETL process, on this stage I would focus on Transformation process and simulate some data differencess between OLAP and OLTP needs.
After setting up data loads for example every day, you canmake few reports and your project will be done.

regards
Peter

---------------
Piotr Kabath

 

[Chorlton]

Bit late on this.

Building on some of the other responses I would suggest the following:

Firstly build an OLTP data warehouse schema. So a fully normalised schema for instance with

Customer joining to customer address table
Customer joining to customer_group table
Customer joining to customer_account table

Have and order table with order_line table

Product table with product_group table
Product table joining to a supplier table (perhaps with intermediate table simulating 1 product : N suppliers)

Populate this with some data. Including some errored data for instance order date being 31/02/2005 etc

Then develop a star schema with: customer, product, supplier, date as your dimensions

create a fct_order_line table which contains the pertinent info from orders and order lines.

Then develop the ETL process to read the OLTP data, denormalise, cleanse and conform the data test for existing, new records etc and load into the DW.

I would then add new orders into the OLTP (manual SQL insert scripts) and have changes made to some of the dimensions (add customers, products) change existing customer details etc...

Then demonstrate how your ETL process handles these new/changed records and how you handle data issues.

A good one would be on the first run having an order with no corresponding customer record.

Having on the second run, this order going into the DW when the customer record is then present.

Hope this helps

 

[scorpionking27]

Hi,

I would like to thanks all of you guys for the invaluable advice. I am working on an idea of making a student warehouse .

Chorlton : your suggestion has given me good idea about how to do better designing. Will surely do more research on this.

Thanks

Vikas R

 

[johnherman]

You might want to check here for a jump start data model:

http://www.databaseanswers.org/data_models/index.htm

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw