Member Avatar for haria_kishore

Hello guys

Actually I am new to the world of datawarehousing. In fact today is my third day.

Actually I am working in an organization where I have been given the responsibility of designing a datawarehouse.

The operational database is already being created and I have to post the past from OD to DW

Can u guys just give me a summary of what exactly I have to do(We may use Datawarehouse Builder for DW).How shall I go about designing my DW? What are the key things I need to consider?

Pls help me out

Thank You

  • 2 Contributors
  • 5 Replies
  • 138 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by autocrat
Member Avatar for autocrat

Sorry... I can't help... but if I could ask some details.... what is data warehousing?
Operational DB.... the DB a company uses during the day?
Warehouse DB.... somewhere you put backups?

Member Avatar for haria_kishore

Actually datawarehouse is nothing but a huge database(or just a database) with contains summarized information,historical data and is mainly used to create reports based on the data available.

OD is an operational database which is used to store transactional data(all the data intergity and data validation is done in OD). Once the data is consistent,valid it is send from operational db to datawarehouse.

In other words if you want to modify data, you can do that only in OD and not in DW

DW is mainly used to view data through reports or any other tool

Hope this information is enough.

Member Avatar for autocrat

well.... the cheating method would be to copy the structure from the OD DB, then alter the permissions....turn of edit, update and append, then generate the various reports....like I said, cheating!

The design approach relies on the original too! You need to know the names of every field and table, and probably most of the coding/script that is used as well, before you can alter settings... basically you'd just program/create a copy of the structure and then work through turning things on/off.

Two dfifferent approaches, yet both fdoing identical things.

The third relies on the output format of the OD DB.... if they compute the rcords then produce a new record.... a flat file, then post that to the WH DB, then you jest create a flat DB.... one that will read releveant titles/labels/field, and is able to pull those figure up indivudally, over a date period etc. Nothing complicated there!
(Pray for this one! EASY!)

To save yourself alot of headaches.... you'd probably be best of waiting for the OD DB to be made, then alter that.... that way you ensure that all titles/labels are the same etc. ALternative, talk to those developing the OD DB, and see what their intentions are.... it would save both groups a lot of design problems igf you know who each DB is intending to work!

So, go talk over coffe etc, and see what they plan. Ask if they can tell you about any changes, problems or bugs, so that you are aware of them!

Member Avatar for haria_kishore

Thanks buddy for ur help

Ur suggestion atleast gave me a clear picture of what to do

Actually what i thought was that we already created the master and child tables in the OD DB. So when creating facts and dimensions in DW, why not use the master tables of OD as facts and child tables as dimensions

What do u suggest mate?

Member Avatar for autocrat

That would make refrencing much easier, particularly if you ensure search capabilities and queries geared towards that design mode.......

To be honest, there is nothing stopping you from designing the DW DB completely differently from the OD DB, so long as their output files can be read .... so either they need to output in you format, or you need to input in their format..... otherwise there is the danger of misplacing or misreading data.... whic h will contaminate or worse, stuff access!

Personally, I use pen and paper.

Jot down all the details that will be inc. in the OD DB, check that all of these will be needed in the WD DB, and no new one s added, (if so, add them!). Then check and see that the table design is the same.

There should be little difference..... unmless the output/input file is flat (all tables are compiuled into a few etc.), in which your'e life just got really simple! LOL

Still, best of luck... and let me know how it goes!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.