Overview
Any BI application's main role is to show information based on some measurements. These measurements are metrics. E.g. you measure how much sale you have done, so total sales revenue is your metrics. In this article I am focusing on the types of metrics and when and how to use a proper one in the application.

Details
There are three main types of metrics you can use in your application:

1.Leading Indicators: If you want to measure activities like how many touches are required to convert a prospect into a customer; leading indicator metrics are used, which measures activities. Generally these indicators will show how many calls/activities you need to do to achieve you goal.

2.Lagging Indicators: If you want to measure any business financial amounts like sales revenue; lagging indicators are used, which measures outcome of the activities. Generally these indicators will show where you stand currently.

3.Key Performance Indicators (KPI): If you want to see how is your performance and where you stand, is it good or bad then Key Performance Indicators are used which measure the performance. E.g. If you want to see how is sales revenue with respect to sales quota

Conclusion
A proper metrics is used based on which application you are designing for. If it is a BAM-Business Activity Monitoring then Lagging Indicators or KPI will convey the information.

Overview
In the Data warehousing project you have dimensions and facts tables. And when you design a Data warehouse or Data Mart you come across many Date data type attributes. In this article I have pointed out a design technique for Date columns which gives highest performance.

Design
Consider a data mart having a fact table "Order" with many columns like Order Number, Order Date, Shipped Date and Amount and a "Time" dimension which have an entry for each day. You use "time_id" for "Order Date" however most of the time "Shipped Date" is kept as a Date column.

Consider in your reporting system you want to design a report to report number of orders shipped in a particular year. Now you will have format the shipped date column so that you can compare its year portion to get result. If you have a massive fact table this query is going to take more time as it will not be using any index, well you can create index to solve this problem.

Now consider you have a report which report number of orders shipped in a particular month, day, quarter etc. To speed up this operation you will have create index probably more than one. However if we use the id column and index on that column then you can avoid the above problem

Add shipped_date_id column along with the Shipped Date column in the fact table. Derive the value by using Time dimension. So whenever you query you always use index.

Conclusion
This way you can achieve maximum performance without adding more indexes. You can just go to your time dimension get the required ids and join it with your fact table which will use index defined on "shipped_date_id" column.

In the Data warehousing project you have dimension and fact tables. Usually, if data is coming from a single table, we can use the approach what I have presented in the last article "Change data capture for Oracle 9i database without adding triggers on the source table".

There are also plenty of other options available like CDC, using timestamp etc. However the problems comes when you have a dimension table which is constructed based on a multi-table query. In this case none of the above approach can work directly.

Overview
Consider a case of Sales Representative dimension, this dimension is based several attributes like area, login etc. These attributes are coming from different tables. Now we will see what we can use to have an incremental update of this table.

The examples shown in this article is for Oracle database however same concept can be used for other database engines.

Step 1: Creating a Function which will return hash value
We will be using a hash value technique to compare the rows. Well we really have one more option, compare each field and see if any one of them is changed and that way determine the changed row.

However hash value method is faster than the above approach and code also become manageable with less conditional statements. Both methods are same though.

Create a function such that it will read a value as a text parameter and will return a hash value for it.

e.g.
FUNCTION salesrep_hashvalue (p_input_str VARCHAR2)
RETURN VARCHAR2 IS
l_str VARCHAR2(20);
BEGIN
l_str := dbms_obfuscation_toolkit.md5(input_string => p_input_str);
RETURN l_str;
END salesrep_hashvalue;

Step 2: Add a new column in the dimension table to hold a hashvalue
Create a new column "hashvalue" in the dimension table. And update its value by using the above created function using the required columns.

Make sure you use the same set of columns and in the same sequence in the ETL logic to create a hash value for a new row.

Step 3: Write ETL code
In the ETL code read the records from this multi-table SQL in a cursor loop. For each record find out the hash key value. Get the old hash key value by selecting the record from the dimension table using a key. If no record exist then insert the record. If record exists compare these tow hash keys if it is different update the record otherwise skip it.

Conclusion
This way you can achieve change data capture for a multi-table select statement query used for creation of a dimension table.

Overview
I recently worked on creating a dashboard application which show contents based on who is logged in. In short that person will have his/her own personal dashboard.
There are various ways to do this. One can use Virtual Private Databases technique to achieve this. What I am focusing in this article is a case where you don't want to create hundreds of database users which is a maintenance problem, rather using your company's existing security structures. Let's consider for this purpose we have an active directory structure or Oracle Portal's Internet Directory structure. We have one database user and hundreds of internet users.

Design
1. Create a table which will hold loginname, user role like "Regional Manager", "Area Manager", "Sales Rep", "Analyst" etc. and particular code like region code, area code, salesrep code
2. Create a welcome page which will prompt for entering a password
3. Once the user entered their network credential, capture the entered user id and call other page by passing the user id as a parameter as shown by the below MOD-PL/SQL code:
HTML
BODY
ORACLE
DECLARE
l_user_info varchar2(50) := sys_context('USERENV', 'CLIENT_INFO');
l_user varchar2(50):= SUBSTR(l_user_info, 3, INSTR(l_user_info, ',') - 3); -- This will extract the userid
BEGIN
IF UPPER(l_user) = 'PUBLIC' THEN
NULL;
ELSE
IF UPPER(l_user) = 'PORTAL' THEN
NULL;
ELSE
Here you can call other page and pass the user id as a parameter
END IF;
END IF;
END;
/ORACLE
/BODY
/HTML
4. Now you can use this parameter and table created in the first step to determine the level of the user and how to display the data using proper region code or area code or other ids

Conclusion
This way you can create Personal Dashboards for your users without creating hundreds of database users.

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.

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

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.

Overview
Whenever we start designing a Data Mart/ Data Warehouse environment first thing comes is staging area. In this article I am focusing on what different types of Data Staging Techniques are there and how to define a Data Staging Strategy.

Detail
While defining the strategy you will have to focus on
What technique you will be using?
What kind of data load it will be, full data or incremental?
Where the staging data will reside?
Where should aggregation be performed?

There are following well-known techniques are available:
1. Store and Forward: In this technique, a data is stored in staging area and then used for transformation and loading into Data Warehouse environment
2. Direct Database insert/update: In this technique, a data is directly read from ODS and directly will be inserted or updated in the Data Warehouse environment

There are following Data Load types:
1. Full Data: Here you use all the rows and update Data Warehouse environment with the data. This is time consuming process and the processing time will gradually increase because of data growth rate
2. Delta or Incremental: Here you only get the changed/new records and you process these records so that information is passed to Data Warehouse environment

Types Staging Data Stores:
1. File: Data can be placed in File. If more sorting operation needs to be performed then storing data in this format is beneficial
2. Table: Staging data can be stored in the table either permanently or for some time till it gets published to the Data Warehouse area

Where to perform aggregation: If the aggregation is required by the Data Warehouse process. It can be either performed while loading the stage area or loading Data Warehouse area. Decide where you want to perform this.

Conclusion
Thus considering all the sides you can prepare a good Data Staging Strategy.

Please refer to "Data Strategy" book by Sid, Larissa and Majid for more details.

Overview
While designing a data mart/ warehouse sometimes you get cases where you find many-to-many relationship exists between dimension and fact table. Usually you have one-to-many relationship between dimension and fact table which is best for good OLAP/ cubes. In this article I am focusing on such M2M cases and how to design to resolve these cases.

Design
Let’s consider a case. We have a time dimension, which have regular attributes, like year, month, date, day, WOY, DOW etc. And we have a fact table which have following date columns, schedule date, shipped date, order date and promised date.
As per the regular design we can either store these dates as it is or can store particular time_id from a Time dimension. If we store a time dimension id (surrogate key) we will create many to many relationship. If we don’t store ids we will not be able to use these columns easily for data analysis.
To resolve this we can use a "Role Based Dimension" concept.
1. Create following views "schedule_date_vw", "shipped_date_vw" and "promised_date_vw" using the SELECT * FROM dim_time. This will create different roles of time dimension
2. Now use these views as dimensions in your schema
3. Add dim_time dimension table in the design
4. Use surrogate keys from the above created views/ dimension along with dim_time and store it in the fact table

Conclusion
This will resolve a problem of many to many relationship and can easily use in OLAP and cubes for better data analysis.

Most of the time we have companies doing business in more than one country. If you have KPIs which demands to do a comparison in original currency you will need to have a provision in your design. In this article I am focusing on this case.