Data Virtulization or near real time data for reporting with Data Warehouse

By: Milind Zodge

Business requirement

Need to report near real time data segment along with consolidated data

Details

Data virtualization is getting lot of attention now-a-days. In current world business need of a data is changing. Previously Data warehouse used to support DSS applications and reporting tool like Dashboard and Scorecards which preliminary need summarized snapshot of a data.

However now the trend looks like going towards having a mix of consolidated data and near-real time data. There are few EII techniques and tools available for this however if you have to deliver this without spending a fortune you can leverage user database layer.

You can create a Data warehouse either top down or bottom up modeled and can have an ODS tables/schema to hold the ODS data without transformation. Various change data capture techniques can be used to keep ODS data in sync with the source, like in Oracle you can use Change data capture or Streams method.

Now since we are not transforming the ODS data it need to be transformed virtually. We can create a view layer combining these two layers to deliver data for some operational reporting and near real time data needs. Key is to transform the ODS data and fit it together with the data mart or data warehouse data.

How to use Oracle's Metadata package for impact analysis

By: Milind Zodge

Overview

Business is always changing and you have to make some changes based on the business requirement.

Before doing any change you want to perform an impact analysis. Most of the data modeling tools have provision to do it. I am focusing in the article how you perform this task if you don't have a tool.

Details

Consider a case, we have Oracle database and wants to alter a column width and would like to see wherever this column is used/ referenced.
We can use Oracle's metadata package as indicated below
SET pagesize 0

SET long 90000

SET feedback off

SET echo off
SELECT DBMS_METADATA.GET_DDL('TABLE',ut.table_name)

FROM USER_TABLES ut;

This will give DDL scripts for all the tables. Now you can use any text tool like Notepad to search for the required column and find out the references.

Conclusion

There are various ways to do it. This is one of them. This will help you determining the impact exposure.

Deciding which type of Dashboard is good

By: Milind Zodge
Overview
There are many times a question surface, which dashboard should we use. In this article I am focusing on what are the different types of Dashboards and how to choose a right one. For more detail reading please read Performance Dashboards book by Wayne W. Eckerson.

Details
There are two ways one can deliver the information. First more current snapshot of the data, second trends of data over a period of time window like 12 months.
There are following well known types of Dashboards:

1. Operational Dashboard: This is simply known as Dashboard. If there is a need to report more current information like, where do I stand now with respect to my quota, this type of dashboard can be used. These dashboards usually have gauges to indicate the KPIs or even stop lights to indicate the current state.

2. Strategic Dashboard: This type is known as Scorecard. If there is a need to monitor a performance of certain activity over the period of several months/ day etc, these dashboards are well suited. These dashboards usually show graphs for a period of time which indicates the trend. E.g. A scorecard to report OTD% , here it doesn’t make any sense if you say OTD% is 80 this month. The question comes is, how is 80, good or bad, whether this is following a natural wave hence even if it is low it is fine. To answer these questions you need have trends instead of just current snapshot.

3. Tactical Dashboard: It is a combination of above which is delivered through a portal interface, which provides much flexibility of adding and removing parts. E.g. you want to show a trend as well also want to have alerts for some current activities.
Most of the time you end up having two or more as your BI application, means combination of option1, 2 and 3 as an application.

Conclusion
Information is effective if it is delivered in right format. Choosing a right dashboard type is crucial for the success of the BI system.