Continue in 2 seconds

Managers have always received financials and management reports in Excel spreadsheets that were prepared by the end-user community.

By
  • Sid Adelman, Les Barbusinski, Scott Howard
Published
  • October 03 2002, 1:00am EDT

Q:

Managers have always received financials and management reports in Excel spreadsheets that were prepared by the end-user community. Those users get their reports from many systems and they use Excel to merge it. I have to mention that sometimes they add new information in Excel that is not in the OLTP systems. We started a data warehouse project two years ago (that is still very small) and we want to add new information in the DW to include many "management indicators" in order to replace many Excel spreadsheets. Should a data warehouse be used to produce management reports in specific subject areas that will be sent to the managers each week or month? Our managers think that the data warehouse should not produce those reports. Where can I find documentation on how the data warehouse should be used?

A:

Sid Adelman’s Answer: It sounds like your managers are putting their own spin on the numbers they get from the OLTP systems and they want those numbers to become the numbers of record. These managers will resist your attempts to keep them from cooking their numbers (maybe not cooking their numbers – just warming them up a bit). My guess is that you would eventually lose anyway so it's not worth your energy and reputation on this fight.

Les Barbusinski’s Answer: Data warehousing is a collection of techniques and technologies that can be applied to any situation where large amounts of disparate data need to be integrated and reduced to a manageable set of actionable information. This includes management indicators, key performance indicators, operating ratios and/or anything else about an enterprise that can be measured.

Although data warehouses and data marts are best known for their analysis of sales performance, company financials and customer behavior, they’ve been successfully employed in a variety of unusual analytical applications such as measuring the yields of oil and natural gas fields, analyzing the results of clinical trials for new pharmaceuticals and studying factors affecting the MTBF (mean time between failures) for jet engine components. There are few limits to how this technology can be applied.

If your management thinks that using your data warehouse is overkill for these reports, perhaps you could reset their vision by demonstrating how the reports would be enhanced with the application of business intelligence technology. I once saw the executive management of a large mortgage lender abruptly discard their treasured monthly G/L reports in favor of an online BI application after they were shown how drilling on any metric in a P&L Statement (e.g., by product, office or customer demographic) significantly enhanced the meaningfulness of the numbers. They just needed a little demonstration to help them visualize the capability.

Scott Howard’s Answer: The detailed answer to your question along with some justification is included in my answer to the question from this month that begins, "I have been challenged by a big manufacturing company…" To net it out, information, like knowledge, is power. Management is seldom quick to relinquish control of their power easily. The end-user community has the option as to which source systems to choose for their report base. Quite often the source system is chosen from ones most likely to support a business unit's current position or enhance their current operational appearance. Now I’m not suggesting that managers are always seeking to mislead by controlling such reporting processes, it’s just the way it’s been done for years. Many managers are unaware of the inconsistencies persistent in the ad hoc report generation process and would be quick to support you if you point them out.

The purpose of the data warehouse is to consolidate and reconcile enterprise data into a consistent trusted set of corporate information. Each department, creating their own ad hoc reporting structure creates individual islands of management "knowledge," or should I say misinformation, all due to a lack of standard and conformed information. Your DW should provide this single source of trusted and consistent information for all of your enterprise, or at a minimum, your business units.

You can imagine the chaos should each business unit load their own unique and unreconciled measures into a common EDW. Let’s assume for a moment that the measures are indeed standardized. Would analyzing these standard measures between business units with different business operational and reporting standards make sense? Would comparing weekly totals for two business units, one for which the week begins on Monday the other beginning on Saturday lead to comparable results? That’s exactly why the business units also need to agree on dimension standards such as time, geography, customer and product related dimensions to name a few." That is the very reason that the DW and not the individual end-user communities should be the source for all your decision support processing. That's not considering the additional exposure that the manual processing required to source and produce each and every report introduces. When compared to the systematic approaches used in data warehousing, manual processing is much more susceptible to human error.

Joe Oates’ Answer: By the term management indicators, I assume that you are referring to some sort of management performance indicators, often referred to as key performance indicators, or KPIs, for short. In the BI/data warehouse world, these are often referred to as metrics. Whether one calls them KPIs, metrics or some other term, these are the raison d’etre of BI/data warehouse initiatives.

Most books and articles on data warehousing tell us that the purpose of a data warehouse is to provide a platform for analyses that help management make good, informed decisions. The specific things that are used for these decisions are analyses of these management indicators.

Clay Rehm’s Answer: I believe the data warehouse should house the data for the reports, but it will take a structured process to load the data there and validate it. It will also take a creative viewpoint – a different mind-set to appreciate the need for a new process for that data.

One of the goals of a data warehouse is to house certain historical performance indicators. The beauty of it is that the data is stored, it is easy to use and access, is reliable since it went through tough standards, testing and verification to be there – and that any query/reporting tool can be used to retrieve and display this data.

The data in the Excel systems are "data islands" and are not benefiting anybody; in fact it makes the data integration problem worse. One of your goals is to get the data from those islands integrated into the ETL processes so that data can be stored in the data warehouse.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access