multiple excel spread sheets as a data source

[saumil287]

Hi All,
My client is having a multiple excel spreadsheets as a source.
it is necessary to use a datawarehouse or we can directly connect to cognos using framework manager.
If datawarehouse is needed, what etl tools can work with cognos.


your guidance i appreciated.

Thanks in advance

[tjohnson3050]

It is possible to connect to Excel from Framework Manger by setting up and using an ODBC connection, but it is not advisable for several reasons.

1.  An excel file can be easily altered, and will break all the reports using it.
2.  If someone opens a the excel file, it will be locked and any reports using it will fail.
3.  Windows serving up data from an excel file will never perform as well as a database.

It would be much better to use an etl tool to extract the data into a database (data mart / data warehouse).

The ETL doesn't matter, Cognos will connect to the database, not the ETL tool.  Choose a supported database:

http://www-01.ibm.com/support/docview.wss?uid=swg27021368

[saumil287]

Hi sir,
then should I go for sql server or a datawarehouse

Is etl tools work with sql server or datawarehouse is required.

Can you list some of the datawarehous to work with cognos.

Appreciated your response.

Thanks again

[tjohnson3050]

Data warehouse is a concept, not a particular product:

http://en.wikipedia.org/wiki/Data_warehouse

You can build a data warehouse with a database provided by any database vendor. 

Again, the link below lists all of the databases that Cognos supports.  Go to the section that lists Relational Databases.

http://www-01.ibm.com/support/docview.wss?uid=swg27021368

If you have never built a data warehouse, I would recommend learning a lot more about it before beginning the process, or finding a partner to work with that has experience building data warehouses.

[saumil287]

Hi Johnson,

I am new to datawarehouse and never worked before.

appreciated your reply a lot.

I just go through the links you forwarded.

Thank you so much.

[tjohnson3050]

Good Luck.

There is an IBM Book that describes dimensional modeling (for a data warehouse) in detail.  It is free to download.  It is over 600 pages, but I recommend you take the time to read it in full:

http://www.redbooks.ibm.com/abstracts/sg247138.html

Also, the Kimball Group has some great data warehouse design tips:

http://www.kimballgroup.com/category/design-tips/