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

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.  

Ken  

From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com] Sent: Thursday, July 05, 2007 10:25 AM
To: kennaim_at_gmail.com
Cc: robertgfreeman_at_yahoo.com; oracle-l_at_freelists.org 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.

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

cheers

On 7/5/07, Ken Naim <kennaim_at_gmail.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Robert Freeman
Sent: Wednesday, July 04, 2007 2:33 PM
To: oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> 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.

Thoughts?

RF

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 Amazon.com! Sig V1.1

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

Niall Litchfield
Oracle DBA
http://www.orawin.info

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jul 05 2007 - 15:46:53 CDT