Access & Data Warehousing

[SDS100UK]

Hi,
My boss does not want to spend much money so I have been tasked with setting up a Data Warehouse. She stipulates that it has to be done in MS Access. Is this possible? Is this right? What are the pros and cons?
Any help at all would be appreciated.

TIA

Steven

 

[tdgulbra]

Your definition of "Data Warehouse" will determine whether or not MS Access could be the appropriate tool. All of the warehouses I've worked on would choke MS Access. They are too large and require too many simultaneous connections for MS Access to handle.

I can probably give you more when I understand what you (or your boss) means by "data warehouse".

Tony

 

[hfitze]

All depends of three basic topics:
1)How big is the data Warehouse, ms access did not work well with more than 900.000 registers in a table.
2)how complex are the routins that filter and import data from the sources and what type of data sources you have.
3)what kind of client server structure were used to implement the end users interfaces.

in my opinion, ms access is not the adecuate tool for development a data warehouse because is not a client server dbms, all the routins involved in the proceses for importing data into the data warehouse become complex for this contruccion and access is very low performance tool for databases managment.

 

[DataArch]

No, MsAccess is probably not the right technology. The issues are 3-fold:

(1) There are significant possibilities of corruption when an mdb is hit by multiple users concurrently. Data corruption is a bad thing.

(2) Additionally, most low-end backup systems will not backup open files. If you are using MsAccess, it is probably because of funding, which tells me your disaster recovery ability is probably not there, either.

(3) It is slow. The memory handling and performance of MsAccess is brutal and dependent on the desktop accessing the data. Heavy use of index Seeks can mitigate this somewhat, but then you are into a lot of recordset manipulation instead of SQL.

I strongly recommend for and data mart with over three users or 500meg you consider going one more level up to SqlServer. It will address many of your concerns, run only about $1500, and be an order of magnitude more reliable and usable system.

 

[lynchkm]

A datawarehousing solution is normally implemented for the purpose of answering mission critical decision support questions promptly. Access does not meet the criteria for a datawarehouse per industry experts. It is not scalable and up to Access 97 could have a maximum of a 1 Gig database. I am not sure what the max for Access 2000 is. Another question is data explosion. Access is not very efficient in terms of data storage and has to be routinely compacted. Have you ever tried to compact a 1 Gig Access database. Not a good idea on a PC espacially if the database gets corrupted in the process.

If access is being used a backend solution for an ASP (Active server page) decision support type environment, then maybe one could state that the tool is supporting the decision making process directly and in a timely fashion. However, as mentioned by some of the other contributors, you will benefit from scaling up to SQL Server 2000. SQL Server 2000 provides OLAP Analysis services in the Enterprise version and is extremely scalable. It allows a mix of either Decision Support type Ad Hoc queries or an OLTP environment.

The ultimate answer to this question is very much dependant on your bosses understanding of what a datawarehouse is. My first initial response to a question like this from my boss would be laughter if I knew that he/she actually knew what a datawarehouse was.

I hope this helps somewhat.

Kevin