Building a data warehouse from a single flat file Options

[El77433]

I am a newbie in OracleDB and Warehouse Builder.
I am trying to build a data warehouse for Consumer Price Index and so I
have downloaded data from the Bureau of Statistics.It is in excel format
and since I am working with Oracle Warehouse Builder I have converted it
to .csv file so that I can use it as a data source.

Question1: Is it practical to use single .csv file as a source of data for a data
warehouse?

Question2: I have 3 dimensions tables and a fact table.The dimensions
are one for the Region(as the date is organized in region,states etc),two is
the consumer goods and sevices(as the data is organized in groups of
goods and services,services/goods types) and finally time(year and month),
now how am I going to do the mapping here?Is it possible to do a one to
one mapping here as all data required by the dimensions is located in the
.csv file

Please assist.Thanks in advance!

[burleson]

Hi,

>> I am a newbie in OracleDB

Get the Oracle utilities book, lots of working scripts in the code download:

http://www.rampant-books.com/book_0801_oracle_utilities.htm

>> It is in excel format and since I am working with Oracle Warehouse Builder

The super-easy way is to use Excel-DB to load the data:

http://www.excel-db.net/oracle.htm

Another way is to save the apreadsheet as a comma-delimited flat file (a .csv file), and use the sqlldr utility.

http://www.dba-oracle.com/tips_sqlldr_loader.htm

You can also read the csv directly, as an external table:

http://www.dba-oracle.com/art_ext_tabs_spreadsheet.htm

OWB is just a framework, you are better off writing the programs yourself . . . .

[michaelasc]

Hello
You can build a data warehouse from any source you like, csv included. This is a very common source and you should have no problems getting OWB to read it. You do of course have to make sure that you always have the same number of columns and that the data types for each do not change.

As has already been commented, you can also use a myriad of different tools so if you think OWB is overkill for a simple solution you do not have to use it.

Should you wish to use and all of the data that you need is inside a single csv file you can create multiple targets out of the same file. Whether it wise to do so only you can decide as you know what the data looks like.

Personally, and rememeber this is just my opinion, I would do separate maps. This way should you decide to switch the source in the future you do not have to worry about the impact to the other maps.

Hope this helps
Best wishes
Michael

[El77433]

Hi,

>> I am a newbie in OracleDB

Get the Oracle utilities book, lots of working scripts in the code download:

http://www.rampant-books.com/book_0801_oracle_utilities.htm

>> It is in excel format and since I am working with Oracle Warehouse Builder

The super-easy way is to use Excel-DB to load the data:

http://www.excel-db.net/oracle.htm

Another way is to save the apreadsheet as a comma-delimited flat file (a .csv file), and use the sqlldr utility.

http://www.dba-oracle.com/tips_sqlldr_loader.htm

You can also read the csv directly, as an external table:

http://www.dba-oracle.com/art_ext_tabs_spreadsheet.htm

OWB is just a framework, you are better off writing the programs yourself . . . .

Thanks you,will take a look at the links!

[El77433]

Hello
You can build a data warehouse from any source you like, csv included. This is a very common source and you should have no problems getting OWB to read it. You do of course have to make sure that you always have the same number of columns and that the data types for each do not change.

As has already been commented, you can also use a myriad of different tools so if you think OWB is overkill for a simple solution you do not have to use it.

Should you wish to use and all of the data that you need is inside a single csv file you can create multiple targets out of the same file. Whether it wise to do so only you can decide as you know what the data looks like.

Personally, and rememeber this is just my opinion, I would do separate maps. This way should you decide to switch the source in the future you do not have to worry about the impact to the other maps.

Hope this helps
Best wishes
Michael

Thank you so much Michael for clearing my doubts!