Access as a small-scal BI front-end

[GPO]

Hi,
I've been away from Access for a while (since Access 2000), but I am
considering doing some work (business analysis role) for a small health
service organisation with a reasonably compact management structure (15 to 20
management staff, a few hundred other staff). At the moment their management
reporting is a mess.They have:
1. No BI front-end tools (like Business Objects, Cognos, or even Crystal
Reports),
2. No data warehouse strategy or infrastructure and...
3. No money.
BUT they do have MS Access (2003 I think), and a series of federated
operational databases (both Access and proprietory) that don't talk well to
each other.

Could Access 2003 be used as a front-end BI tool for an organisation this
size? Would deploying an mdb file to each user's local machine be a
reasonable option?
Could SQL Express be worth considering as a "mini-warehouse" backend? I
don't like the idea of Access as a backend mainly because of security
considerations.

From what I can gather, the volume of data in the backend would not be that
great. To consolidate their reporting data, we'd be talking 20 or 30 fact
tables and tens to (maybe) hundreds of thousands of records, in these tables,
never millions.

If Access was able to cope with this role what are some of the publication
issues to consider. Access reports have never been my cup of tea because they
(I'm most familiar with Access 2000) have seemed clumsy to distribute (eg
saving as PDF or formatted HTML has not always been straightforward).

Regards

 

[Matt]

Hi,
I've been away from Access for a while (since Access 2000), but I am
considering doing some work (business analysis role) for a small health
service organisation with a reasonably compact management structure (15 to 20
management staff, a few hundred other staff). At the moment their management
reporting is a mess.They have:
1. No BI front-end tools (like Business Objects, Cognos, or even Crystal
Reports),
2. No data warehouse strategy or infrastructure and...
3. No money.
BUT they do have MS Access (2003 I think), and a series of federated
operational databases (both Access and proprietory) that don't talk well to
each other.

Could Access 2003 be used as a front-end BI tool for an organisation this
size? Would deploying an mdb file to each user's local machine be a
reasonable option?
Could SQL Express be worth considering as a "mini-warehouse" backend? I
don't like the idea of Access as a backend mainly because of security
considerations.

From what I can gather, the volume of data in the backend would not be that
great. To consolidate their reporting data, we'd be talking 20 or 30 fact
tables and tens to (maybe) hundreds of thousands of records, in these tables,
never millions.

If Access was able to cope with this role what are some of the publication
issues to consider. Access reports have never been my cup of tea because they
(I'm most familiar with Access 2000) have seemed clumsy to distribute (eg
saving as PDF or formatted HTML has not always been straightforward).

Regards

We use Access in the same exact manne as you are thinking about. We
gather information from 5 databases and reort on it ... and you can run
Crystal Reports on an Access database if you dont like the Access
reports. Also, we import thousands of rows daily, and when we start to
have performance issues because of the size of the tables, I just
archive the data ... Compact & Repair can only do so much.