|We are trying to create a policy on information reporting that would identify the types of reports that should come from our DW vs. those that should come from our operational systems. Can you provide some guidance on how we could create such a policy?|
Les Barbusinski’s Answer: Here are some simple guidelines for deciding which system should generate a certain type of report:
Mike Jennings’ Answer: Depending on your business requirements, amount of operational systems, amount of data and complexity of data transformation and integration processes, all of your reporting could be done from your data warehousing environment. Data from your operational systems can be extracted and loaded into an operational data store (ODS) into your warehousing environment on an intra-day basis. This operational data should go through minimum transformation processing, except where integration and consolidation of core data between operational systems is required (e.g., customer, product, etc.). By off loading data from your operational systems into the ODS your operational systems can perform their primary function, transactional processing, without being affected by varying reporting processing loads.
On-demand reports can be run by users against your ODS. These reports allow users to select filters, constraints, and columns they wish to see in the report based on their security privileges and needs. Scheduled reports can be run periodically against the ODS and pushed out to users based on their subscription. These reports have a standard format and are constrained by the security of the user receiving the report. Data from the ODS is periodically (weekly, monthly) extracted and transformed into the enterprise data warehouse (EDW). Data from the EDW is then processed into either a data mart or OLAP cubes (or both) for analytical reporting needs.
Joe Oates’ Answer: Here are some general guidelines that I recommend to clients.
Reporting needs best satisfied by a data warehouse (and the appropriate presentation tools):
Reporting needs best satisfied by operational system reports:
The key thing to remember is that a data warehouse is designed for analytical needs to help management understand how the whole organization is performing and to provide information to help make better tactical and strategic decisions about the organization as a whole. So anything that is analytical, is usually best handled by a data warehouse.
Clay Rehm’s Answer: You probably have heard or read somewhere that you should not use the data warehouse as a source for operational type reporting. I am not sure that I subscribe to this logic. Here is why: Typically the data in the operational systems is the data your users need because of its current nature. The problem is that the operational database is quite normalized and very difficult to use for a non-technical person. On the other hand, the data warehouse design is very ease to use and if designed correctly is integrated with other data sources. The data warehouse becomes very attractive for reporting then and these users wonder why they can’t use it. After all the money that has been spent on the data warehouse, why can’t they build reports off of it? The data warehouse becomes the logical place to do any query and reporting from since it was designed to do so.
It boils down to data warehouse design and semantics. For example, your data warehouse could have database tables that are updated more frequently than others. Some tables will have lots of detail, and other tables will be summarized. It is a collection of different types of tables, that is more current data verses historical data, more detail data verses summary data and so on.
The reason I mention semantics is that you can call this database anything you want data warehouse, reporting database, decision support database, etc., but the bottom line is that all of your users query and reporting needs are met.
Regarding your question on how to establish a policy, the first task to be accomplished is to create a query and report inventory of each and every query and report that currently exists. The following table provides an example:
Nancy Williams’ Answer: There are a number of guidelines that are commonly used for creating policy in this area. Since the purview of operational systems is supporting the day-to-day functioning of specific business process areas e.g., order tracking, operational system reports are best suited to support the information needed for this purpose. By definition, operational system reporting will be limited to the information that resides in each individual operational system database. While the operational system focus is current and has a specific business process focus, the data warehouse provides an integrated, historical perspective of the business. Its strength is in supporting analysis and reporting for strategic and tactical decision making which requires an integrated historical perspective of the business. The data warehouse also commonly supports operational reporting that requires integration of data across multiple operational systems.
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
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access