RE: Oracle - MS Access - Does your company have a policy about local vs. centralized data storage? (Perhaps a bit off topic?)

  • From: "Ken Naim" <kennaim@xxxxxxxxx>
  • To: "'Niall Litchfield'" <niall.litchfield@xxxxxxxxx>
  • Date: Thu, 5 Jul 2007 16:46:53 -0400

I can't tell you how many times I was asked to prove the warehouse against a
printed copy of an excel document from a few years back without "an as of
date", with column headers that have different meanings to different people
and the person who initially wrote the report is no longer with the company
and they don't understand why my (the data warehouse) numbers are wrong.


Also knowing which numbers are right are another story, while gathering
requirements for the warehouse, I asked to see the reports that they
currently use and built the warehouse to the match the logic in report 123
for example. And then I get a complaint that it doesn't match report 456
which is correct, so now I talk to the owners of both 123 and 456 and
realize that they both are wrong and I correct the data and logic in the
warehouse and produce a much more correct number. 6 months go by and they
want to know why the warehouse doesn't match the old reports and breakdown
the difference by each individual issue. SO after repeating this process at
least 7 or 8 times the warehouse finally got some respect.


I never understood why they were so hesitant to trust the warehouse when the
reason I was hired was that they couldn't trust operation reporting <shrugs>


While I consider myself a realist and not a cynic, in most circumstance the
two realities are identical. In the case of this company revenues were being
misreported as well but to the downside and the company took it in stride,
many of the division chiefs were very upset and it took a long time to
convince them that the warehouse was correct but we spun it to them saying
profits were actually higher as percentage as they now had a smaller
divisor. The data warehouse project is still ongoing and more and more data
marts are being added, now I am not sure if this error was found during a
bad year instead of a good year what would have happened.





From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx] 
Sent: Thursday, July 05, 2007 10:25 AM
To: kennaim@xxxxxxxxx
Cc: robertgfreeman@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Oracle - MS Access - Does your company have a policy about
local vs. centralized data storage? (Perhaps a bit off topic?)


Great story Ken and a good example of the value of 'single point of truth'.
I'm glad you mentioned excel as well, since my experience is that that is
even more widely distributed and to people whose skills are even further
removed from data analysis. Spreadsheets get taken to meetings, in print
form, as well. 

of course if the $6m error had been the other way around perhaps the DW
project would have been canned


On 7/5/07, Ken Naim <kennaim@xxxxxxxxx> wrote:

Access (and excel) has been the bane of one of my clients existence, as many

bad  reports have come out of them, and while they are not banned they are
highly discouraged and are being replaced by a central oracle data warehouse
that I built. Once a data mart is built for a purpose that an access 
database was used for in the past any feeds that were used to supply data
into access is stopped, hopefully forcing people to use the warehouse.

We store data at our most granular level in the data marts and we use 
business objects as a front end tool while it has numerous flaws, one of
them works to our advantage. Out of the box it does support exporting to a
text file so data has to be aggregated to some degree before it can be 
extracted as excel still only support 65k rows.

We discourage access databases by not supporting their reports. While this
was a drastic measure, it was necessary as some diehards used to say that
the warehouse was wrong and their access reports were correct which made 
management reluctant to use the warehouse. And after repeated validations of
the warehouse and deciphering and disproving of the access data and reports
all the diehards either quit or switched to the warehouse. 

One of the first datamarts, originally built using SAS, found a reporting
issue that correct a several basis point error that had been accumulation
over the 14 year history of the company and added $6 million to the bottom 
line for that year; paying for the warehouse project for that and many years
to come.

Ken Naim

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Robert Freeman
Sent: Wednesday, July 04, 2007 2:33 PM
To: oracle-l@xxxxxxxxxxxxx  <mailto:oracle-l@xxxxxxxxxxxxx> 
Subject: Oracle - MS Access - Does your company have a policy about local
vs. centralized data storage? (Perhaps a bit off topic?)

I'm trying to do a bit of research in my effort to put together a project to

move from localized to centralized data storage for Access applications and
other ODBC type things. I'm just wondering if any of you have any related
policies at your companies? Do you allow localized Access data stores or do 
you centralize those data stores? I'm looking at allowing access
applications, but providing a means to easily request/create a data store
for those applications in Oracle.



Robert G. Freeman
Oracle Consultant/DBA/Author
Principal Engineer/Team Manager
The Church of Jesus Christ of Latter-Day Saints
Father of Five, Husband of One,
Author of various geeky computer titles
from Osborne/McGraw Hill (Oracle Press) 
Oracle Database 11g New Features Now Available for Pre-sales on!
Sig V1.1



Niall Litchfield
Oracle DBA 

Other related posts: