July 9, 2007
What is metrics and what are the different types of metrics
By: Milind Zodge
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.
Posted by Milind Zodge at 9:00 PM | Comments (0)
June 20, 2007
Design technique for Date type columns in fact table for maximum performance
By: Milind Zodge
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.
Posted by Milind Zodge at 7:30 PM | Comments (0)
May 5, 2007
CDC Technique for dimension table which is based on a multi-table query
By: Milind Zodge
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.
Posted by Milind Zodge at 10:30 PM | Comments (0)
April 11, 2007
How to create Personal Dashboard using Oracle Portal
By: Milind Zodge
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.
Posted by Milind Zodge at 10:15 PM | Comments (0)
March 3, 2007
Different types of Dashbords, how to choose a required one
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.
Posted by Milind Zodge at 10:00 PM | Comments (1)
