Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access & Data Warehousing

Access & Data Warehousing

Access & Data Warehousing

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.



RE: Access & Data Warehousing

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".


RE: Access & Data Warehousing

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.

RE: Access & Data Warehousing

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.

RE: Access & Data Warehousing

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close