Ensuring rapid rollout of an ad hoc query

Time is imperative in business and ad hoc query solutions are sought to respond to the changing market. Here is how business and IT can be aligned to achieve this.

Businesses today are facing an ever increasing demand for faster turnaround time on their ad hoc queries from data warehouse (DW) platforms. This has resulted in the emergence of real-time business intelligence (BI) solutions. Here is an attempt to look at how best business and information technology (IT) can be aligned to cater to the changing business requirements.

Key business issues:

To illustrate, let’s consider a fictitious credit card company - A1 Credit Card Inc and the typical processes it follows. A1 Credit Card Inc has an enterprise DW (EDW) as a central reporting platform for most of its business needs and few reporting tools. The organization has a well-defined IT support team, which manages its EDW environment and reporting platforms.

These are the major challenges confronted by A1 Credit Card Inc:

• The business gets impatient as the time taken by IT to answer ad hoc queries is long.

• EDW is unable to cater to changing business needs as the data aggregation requirements change. This prohibits the usage of business entities exposed using reporting tools.

If IT fails to provide solution to an ad hoc query, the business would have to settle for MS Excel or MS Access databases residing in user desktops. This may potentially result in compliance reporting issues – isolated data islands, multiple versions of truth, data lineage, and questionable data consistency.

The DW environment in A1 Credit Card Inc is depicted in Figure 2, which shows the information flow from source systems to the reporting platforms. The arrows point out areas where IT processes kick in delivering a quality solution for any business requirement, based on processes and guidelines to gather requirements and implementation life cycle.

Figure 2 – Current DW Environment at A1 Credit Card Inc

(click to enlarge)

Create a ZION environment

To reduce the turnaround time by IT to answer an ad hoc query and offer incentives to the business to move away from desktop-based BI to enterprise-based BI, the following approach can be utilized:

An enterprise can create a ZION1 environment in the database as well as reporting solutions to handle ad hoc queries. ZION’s methodology and comparison to the production environment are depicted in Figure 3. The changes, in comparison to the current working environment, are shown as circled.

The ZION environment should be supported by a small IT team aligned with each business unit or line of business (LOB) managers to work on the ad hoc query. This team would primarily be an extended arm of the support team, providing answers to ad hoc queries not directly catered to by existing reporting environment and EDW.

1 Zion is a fictional city in The Matrix films. It is the last human city on earth after a cataclysmic nuclear war between mankind and sentient machines, which results in artificial life forms dominating the world. (https://en.wikipedia.org/wiki/Zion(The_Matrix)       

(click to enlarge)

Figure 3 – Adding ZION environment to cater to ad hoc reporting needs

Migrating from ZION to production environment

After the business sends a request along with the timeline for the ad hoc query, the IT team undertakes basic feasibility analysis and notifies business about the time required for the solution delivery. In case IT is unable to honor the timeline, it will provide a time-boxed access to the business for development of ZION data marts and can thus, deliver the solution as per requirement. The time-boxed approach will help IT in maintaining consistency of solutions as within the time box, the ZION business solution has to move to enterprise production environment. Moreover, as the business solution is developed by IT professionals, the migration from ZION to production environment in terms of data marts, universes and ad hoc reporting is easier than developing the solution starting from scratch.  

About the authors:

Yogesh Bhatt is a principal architect, Information Management Consulting team of SETLabs, at Infosys Technologies Ltd., in Pune, India. He specializes in BI and data warehousing, corporate performance management, data management, IM architecture, and framework. Yogesh can be reached at yogesh_bhatt@infosys.com

Nirupam Acharya is a project manager with Systems Integration - BI team at Infosys Technologies Ltd., in Richmond-Virginia, USA. He specializes in Agile BI DW implementation, BI process optimization, open source BI implementation, and non-linear pricing of BI. Nirupam can be reached at nirupam_acharya@infosys.com

Read more on Business intelligence and analytics

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.