Business Intelligence

R - Turorial II

2011-10-12T01:22:00.000-07:00

Lists

Collection of Objects of same or mixed type.List may also contain vector or other lists.
Create a list using the funtion list().
The following example shows operations that can be performed on a list.

> alphabets=list(a="apple",b="ball",c="cat")
> alphabets
$a
[1] "apple"
$b
[1] "ball"
$c
[1] "cat"

> alphabets[[1]]
[1] "apple"

> alphabets$a
[1] "apple"

> length(alphabets)
[1] 3

> alphabets[["a"]]
[1] "apple"

Double brackets [[.]] are used for getting a single element. names are not returned. single brackets [.] can be used for subscripting.
When lists are formed from objects, the elements use to form the list are copied.
function c() can be used to combine lists to form an object of type list.

Data Frame

Lists having class 'data.frame' are called data frames.
the components of the list must be vectors, factors, numeric matrices, lists or other data frames. vector structures appearing as variables in the dataframe must all have same length. Matrix structures appearing as variables in the dataframe must all have same row size.
data frames can be created using function data.frame

R - Tutorial I

2011-10-06T23:57:00.000-07:00

Basics

Start R in Windows using the program menu.
To quit : q().
to call help for a function. help([function]) or ?[function]. use double quotes to escape special characters and tokens. e.g. ?"for".
objects() or ls() to obtain list of objects stored. rm([object]) to remove object.
x= 1:4 create sequence of numbers. 1 2 3 4. colon has highest priority in expressions.
seq() function can also be used to generate sequences. it has five arguments to, from, by (increment), length.out (length of seq), along.with (take length from length of this argument).
R objects have mode i.e type for "atomic" objects ( numeric, complex, logical, character and row ). Mode for list objects is list. Other modes are functions and expressions. Objects also have a property called length.
as.integer(x) coerces x to integer. There are numerous othe functions of type as.* for different coercions. Type apropos("as.*") in R windows to know more.
Each object has a class. for vectors it is the same as mode. class can be used for object oriented type of programming. method dispatch is based on the class of the first variable passed to the method. an example of a class is the value returned from fitting linear model using 'lm'. The output of the method is an object of class 'lm'.
Factors can by used to prepare categorical values for statistical analysis. the function factor(a) assigns integers to unique values (levels) in the vector a and stores the variable factor(a) as a vector of integers and also stores mapping between the integer values to the actual values in a. factor(a) can then be used in statistical analysis (summary function etc). the factors are stored in natural order of the elements in a. To explicitely provide another order use the ordered function. Read more at: link1 , link2

Vectors

Create a vector named a. a = c(1,2,3,4). c is the generic function to combine arguments. output type is the highest of NULL < raw < logical < integer < real < complex < character < list < expression

b=c("!",2,a) = "!" "2" "1" "2" "3" and b=c(2,a) = 2 1 2 3.

Vector arguments can have names. names(a) = c("first","second","third")

> a
first second third
1 2 3

+,-,*,/ : Individual elements are added. shorter vector are recycled

> a=c(1,2,3)
> b=c(4,5)
> a+b
[1] 5 7 7
Warning message:
In a + b : longer object length is not a multiple of shorter object length

Functions - max (maximum value), min (minimum value), range =c(min(x).max(x)), length (length of vector), sum (total of all elements), prod (product of all elements), mean = sum/length, var(simple variance), sort (sorting in ascending order), pmax (vector or higest elements in individual vectors of the argument)
Logical vectors contain TRUE, FALSE and NA.
missing values are given as NA. is.na(a) returns a vector of same length as a and values FALSE if a contains 'NA' or 'NaN' , and TRUE otherwise.
function paste() can be used to combine strings of two or more vectors one by one. vectors are recycled if required

> paste(c("X","Y"), 1:10,2:5)
[1] "X 1 2" "Y 2 3" "X 3 4" "Y 4 5" "X 5 2" "Y 6 3" "X 7 4" "Y 8 5" "X 9 2" "Y 10 3"

Index Vector: in the example below, b is the index vector in various forms.

> a=c(1,2,3,4,5,6,7,8,9,10)
> b=c(TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,FALSE,TRUE,FALSE)
> a[b]
[1] 1 4 5 6 8 10
> b=c(1:5)
> a[b]
[1] 1 2 3 4 5
> b=-(1:5)
> a[b]
[1] 6 7 8 9 10

R and Java - JRI Using Netbeans

2011-09-30T02:08:00.000-07:00

Setting up R-Java in Netbeans is pretty straight forward. For those who need a walkthough here are the steps.

Download and install R (for this example version 2.12.2). install rJava package.
Download and install Netbeans.
In Netbeans Create a new java project. we will call it RJava.
From the [R_HOME]>/library/rJava/jri/examples folder, copy the file rtest.java into the project.
Create a library in NetBeans and add the following jars to the library. - JRI.jar, JRIEngine.jar, REngine.jar.These files are present in [R_HOME]/library/rJava/jri/.Here's how the project looks like
Minimize Netbeans. Add R_HOME variable (right click my computer-> properties-> Advanced ->Environment variables) R_HOME should point to the location where R is installed.
edit the PATH variable and append the following (i) [R_HOME]\library\rJava\jri and (ii) [R_HOME]\bin

That's it. Right click on rtest.java and click on 'Run File'.

R and Java - JRI using eclipse on 64 bit machines

2011-04-11T01:17:00.000-07:00

The steps to install rjava on a 64 bit machine is not very different from installing it on a 32 bit machine. however, here are the exact steps.

1. Install 64 bit java.
2. Install R 2.12.X
3. Start R and install the rjava package using the package installer.
4. Start eclipse. Create a project called RTest. copy the Rtest.java and RTest2.java files from the examples folder of rjava (R-2.12.2\library\rJava\jri\examples)
5. Create a lib folder in the RTest project and copy the jri.jar file from R-2.12.2\library\rJava\jri into it.
6. add jri.jar to the classpath of the project. ensure that the project compiles without error.
Here's the folder structure

7. Before running the RTest.java class, the run configuration needs to be edited. open the run configuration by clicking on run->run configuration. select Rtest as project and rtest as the main class. click on environment and add the variable 'PATH' . the path should contain paths to the following
a. the bin directory of R (64) : \R-2.12.2\bin\x64;

b. The jri directory for rjava (64bit) : \R-2.12.2\library\rJava\jri\x64.

8. click on apply and then run.

Oracle BI applications - Installation

2010-11-16T01:46:00.000-08:00

In this post we look at installing and configuring Oracle BI Applications. I managed to install the application on an intel core 2 duo laptop with 2 GB RAM, Windows Vista machine.
We would need source data to view the dashboard for BI apps, the source data can be obtained from Oracle E business suite vision database. This post will explain how to install the Oracle E business suite for the purpose of getting the vision database up and running.

Step 1: Installing the Oracle E Business Suite (EBS).

EBS can be downloaded from the oracle site. It is a huge download with multiple files (42G). Here's an article that describes what files to download.
Once the files are downloaded we need to extract them to a staging directory. the extract can be done manually using an unzip utility.

Extract the files to the folder structure as shown above.
Start the rapidwiz wizard for ebs installation

If the system uses another instance of oracle it would be a good idea
to shift the ports by 3. in this example, since we would be using
other oracle instances for informatica and dac, i have shifted the ports
by 3

For this example i have installed the ebs instance on an external USB
hard drive since the install requires a lot of space.

Select Vision Demo Database for database type, this will install the sample
data that we required to load into OBIA

EBS installation requires cygwin and VB. provide the appropriate paths.

The OS User group check fails for Vista for some reason.
lets ignore them for now.

THe installation process may take a long time. My installation failed after
step 3 above. however, the vision database is installed before that.
since we need only the database, we can ignore the rest of the install.

Step 2: The next step is to prepare the oracle instances that will be used by informatica and DAC. It would be a good idea to use different users for different areas

Database type	Name
Informatica repository	dwa
DAC repository	dacrep
OBIA DATAwarehouse	obdw
source	vis

THe Name refers to the oracle user. vis refers to the vision database instance. note that
the database can be accessed by apps/apps

Create and SSE_ROLE as described in section 4.4.1.1 of Installation Guide for Informatica PowerCenter Users Version 7.9.6.1 (install guide)
set the NLS_LANG variable as described in section 4.4.2.1 of install guide.

STEP 3 : Installing Oracle BI APPS

OBIEE - Configuring the publisher/scheduler for MySql

2010-07-25T21:41:00.000-07:00

Publisher can be used to share and distribute reports. Scheduler is a quartz based scheduler for reporting jobs. In this post we will look at sending a report to a user via mail using the scheduler and publisher.
Steps
1. Login to BI publisher. If you face any problems during login, use the following troubleshooting options
http://forums.oracle.com/forums/thread.jspa?threadID=582633&start=0&tstart=60
http://oraclebizint.wordpress.com/2007/11/06/oracle-bi-publisher-and-bi-ee-invisible-admin-tab/
http://onlineappsdba.com/index.php/2009/01/15/oracle-bi-publisher-admin-console-xmlpserver-login-issue-administratoradministrator/
2. Note that the scheduler tab will be inactive.
3. To configure the scheduler. Open the Administration tool. Connect to the repository. Click on Manage->Jobs to launch the Job Manager.
4. In the job manager, Click on File -> configuration options.
5. Click on the Scheduler tab on the tab.
6. Click on the Database tab on the second row. Fill in the relevant information as shown in the screenshot. The DSN can be created as shown in earlier posts.

7. Click on the general tab on the second row and fill in the username and password. Check the scheduler script path and default script path (the default values should be ok).

8. Click on OK to close the job manager dialog.
9. Open BI publisher. Click on the Admin tab (If admin tab is not visible look at the links posted in step 1).
10. In the datasources section of the admin tab click on the link that says 'JDBC connection'.
11. Click on the tab that says JDBC.
12. The default jdbc connections will be visible. We need to add the mysql connection to this list. This link explains how to do that
http://www.iwarelogic.com/blog/how-to-configure-mysql-database-connectivity-in-bi-publisher-485
13. Once that is done, go to the jdbc tab and create a new datasource for mysql.
. The radio button that says 'user proxy authentication' should not be checked.
14. Next, click on the Admin tab in BI publisher and select that link that says 'Scheduler configuration' in System maintenance section. Click on the tab that says scheduler configuration. Fill in the mysql jdbc connection settings as shown.

15. click on 'test connection'. Once the connection is successful. click on install schema. This will install the scheduler related schema in the foodmart database of mysql.
16. We need to create some more tables so that the scheduler service works. go to /server/Schema location. There will be a file called SAJOBS.Oracle.sql Modify this file to suit Mysql and use this file to create tables in the foodmart schema of mysql.
17. we will now attempt to start the scheduler service. stop the BI server . start the scheduler service followed by the BI server (make sure mysql is running). It is important to start the scheduler service before BI server otherwise the schedule tab in BI publisher will not be enabled.
18. We will now create the report. Click on the Reports tab in BI publisher.
19. Click on create a new report. Once the report is created, open it in edit mode.
20. Add a data model to the report.
21. In the new data model page, select SQL query as the Type. User oracle BI EE as the datasource. click on query builder to build the report query.

22. click on layout and create a new layout based on default data model.
23. save the report. click on view. the report should be visible.
24. To schedule the report, click on schedule when the report is in view or edit mode.
25. Fill in the relavant details. Use E-mail as notification channel. enter the destination e-mail address in e-mail delivery.
26. Before scheduling the report, however, the e-mail options need to be set in BI publisher. To do so, click on the admin tab and click the link that says Email in the delivery section. Click on Add server and add the connection info for the smtp server that will be used for sending the mail.
27. once scheduling is complete, look at the schedules tab for a list of jobs that have been scheduled.

OBIEE - Session variables and row level security

2010-07-21T23:51:00.001-07:00

In this post we will look at creating and using a session variable to implement row level security in answers. Situations where this is useful is:
1. Allowing user to see data that she has access to.
2. Showing data based on current date.
3. A sales manager can be shown data in his region only. A CEO can be shown data for all regions.

In this post we look at showing units ordered in the current month. we use a security filter to filter data for the current month.
Steps:
1. The first step is to create the session variable for the current month. To do so
a. In the Administration window, click on Action - > New -> Session -> Variable.Give CURRENT_MONTH as the name of the variable. Click on 'New' near the initialization block.
b. Give CURRENT_MONTH_INIT as the name of the initialization block. Click on Edit Data Source.
c. A new window opens. Select the connection pool by using the browse button.
d. Use database as the data source type.
e. Type in the following query :" select month(curdate()); " in the default initialization string.

f. Click Ok to close the dialog.
g. In the Session variable initialization block, click on edit data target.
h. select the CURRENT_MONTH variable. CLick on Ok.
i. Click on ok to create the session variable.
2. The next step is to use this session variable to filter the result for this month.
a. In the Administration tool. click on Manage -> Security.
b. Create a new User called MonthlyUser.
c. Create a new group called MonthlyUserGroup. Assign MonthlyUser to this group.
d. Open the MonthlyUserGroup dialog and click on Permissions.

e. Click the tab that says filters. Click on 'Add'
f. In the name of the filter select the name of the table that you want to apply the filter on. in this case we select Foodmart.store
g. Click on the ellipsis in the business model filter column. Apply the following filter "FoodMart"."time_by_day"."month_of_year" = VALUEOF(NQ_SESSION.CURRENT_MONTH)

h. the group is now created.
3. Login to BI answers using the MonthlyUser user. Select the columns from the store database. view results. You will notice that the results show data for the current month only.

If you login by a user from the administrators group, data for all months will be visible.

OBIEE - Creating and using dynamic repository variable

2010-07-21T02:42:00.000-07:00

We will create and use a dynamic repository variable in the dashboard. The aim is to show current time in the dashboard.
We use the variable in two places. First is in the title of a report

The second is in the narrative text.

Steps:
1. Create a dynamic repository variable that obtains the latest time. we will need to write an initialization block to get initialize and update this variable. To create the variable
a. In the Administration tool. click on Manage-> variables.
b. Click on Action-> New -> Repository -> Variable.
c. Give 'Date' as the name of the variable. Select the radio button that says 'Dynamic' . The Initialization block variable drop down will be enabled.
d. In the initialization block variable click on New. A window for creating a new initialization block opens.
e. Write 'CurrentTime' as the name of the block. refresh interval one minute. start on - leave default.
f. click on edit datasource and enter the following query (MySql)
SELECT DATE_FORMAT(now(),'%a %D %b %Y %H:%i %p');
g. click on browse button near the 'Connection pool' and select the foodmart connection pool.

h. click on 'Test'.Results should show the current time.
i. click on OK.
j. in the initialization dialog. click on edit data target. Select the Date variable and click on OK.
h. close the initialization dialog by clicking OK.
i. close the dynamic repository dialog by clicking OK.

We have now created a repository variable that obtains the current time from the database every minute.
Check in the changes.

2. Open the Dashboard Page.
3. To add this variable to a title (e.g "Units Ordered"). Click on the rename button on the title and change the title to
Units Ordered -@{biServer.variables['Date']}
4. To add the variable to a narrative text. add the text dashboard object to the dashboard. click on properties of the text object and write
The current time is [b]@{biServer.variables['Date']}[/b]

This completes the tutorial

OBIEE - Managing Cache

2010-07-09T01:55:00.000-07:00

Caching can be used for queries that are run repeatedly. Look at this blog to understand how to enable and configure caching. In this post we will look at pre-caching a query. Lets look at the foodmart catalog that we created in the earlier post

We will pre-cache the result of this report
Steps
1. Create a text file to purge all cache and then create a new cache from the Sales presentation table. Lets call the file SalesCache.txt. The contents of the file are
Call SAPurgeAllCache();
select SALES.* from foodmart;

The first line purges all cache and the second line creates the cache for all columns of the Sales table.

2. run this command from the command line
nqcmd -d FoodMartBI -u Administrator -p [password] -s [pathToCacheFile]/SalesCache.txt

3. Open the Administrator tool. click on Manage-> Cache. The cache created is visible

4. Create a new report using answers

View the results of the report. Since the report has columns that are part of the cache, the results should have been obtained from the cache. To verify, open the Cache management dialog again and look at the last used timestamp of the cache that we have created. The timestamp should have been updated with the time when the new report was viewed.

Also the 'Use count' number will be incremented by one.

Next, let us see how to disable caching for a physical table. Lets disable caching for the sales_fact_1997 table. right click on the table in the physical layer and go to the general tab. unselect the checkbox that says 'cacheable'. now run the SalesCache.txt file again. Open the cache manager. Notice that no cache is created.

OBIEE - Creating a Rank Measure

2010-06-28T21:53:00.000-07:00

We look at creating a rank measure in this post. We will use the sales_fact_1997 table for this example. Join the sales_fact_1997 table with the product, store, time_by_day and promotion table in the physical layer. Drag the sales_fact_1997 and product table to the logical layer and create appropriate joins.
We will create a salesRank logical column in the sales_fact_1997 logical table. Follow this steps to create the column.
1. Right click on the logical table and select new logical column. Enter the name of the column as SalesRank and select on the check box that says 'use existing logical column as source'. use the expression builder to build the rank expression as shown

Create a sales table in the presentation catalog and add the salesrank unit_sales and the product name columns.

Open Answers and create a new request. Drag the columns from the sales table to the request.

This creates a report of products with ranked sales. To select the top 10 use a filter.

OBIEE - Using a Column Selector for additional Drill down

2010-06-27T22:37:00.000-07:00

This post continues the idea of drill down developed in the previous post. Lets say that in addition to the country and city we also need to drill down on year->month. We will add a column selector to the existing report. here are the steps to do it.
1. Add the time_by_day table to the business model. join the inventory_fact_1997 and the time_by_day table using time_id column.
2. Drag the the_year and the_month column from the business layer to the presentation layer.
3. In the BI answers screen go to the criteria view and refresh the metadata to view the new columns. Add the_year column to the report. Drag it to be the first column.
3.Go to the results view and select 'column selector' from the drop down

4. select the 'include selector' checkbox in the first column that says the_year. click on the_month to add it to the the_year selector column

5. In the results view select 'Compound layout' and add the 'column selector' to the report. Drag it to the top

The report can now be viewed year wise or month wise. Also the drill down on country is still enabled

OBIEE - Creating Hierarchy and Drill Down Table

2010-06-24T22:37:00.000-07:00

In this post we look at creating a level based hierarchy and generate a drill down report off that. We will use the FoodMart database (described in previous posts).

Take a look at the inventory_fact_1997 table and the store table. our aim is to find the units_ordered for the following hierarchy in the store table country -> City -> store id.
Here are the steps to create the hierarchy.
1. Right Click on the FoodMart Business Model in the Business Modeling Layer. Select New Object-> Dimension.

2) Enter 'storeDim' as the name and click OK.
3) Right click on storeDim -> New Logical Level. Enter 'storeToal' as the name. select 'Grand Total Level' check box. this level is the top of the hierarchy.

4) Right click on storeTotal and select New Object -> Child Level. Enter 'Store_Country' as the name. select 'Supports roll up...'. Enter Number of elements at this level as 2. Click on Ok.
5) Right click on Store_Country level. Select New Object-> Logical Key. Enter 'countryKey' as the name. To add a column, click on Add. select the store_country column from the store table. select the checkbox that says 'Use For DrillDown'. Make sure the the store_country column is selected. Click on ok.

6) Open the Store table in the business modeling view. double click on the store_country column. select the levels tab and select store_country as the level for the storeDim dimension.

7) This creates the store country level. similarly create the store city level as the child of store country and store id as the child of store city.

8) Next we will prepare the units_ordered column to be used for aggregation. Double click on the units_ordered column in the inventory_fact_1997 table and select the Aggregation tab. select 'sum' as the default aggregation rule.
9) Next create a new presentation catalog called FoodMart. Create a new presentation table called store. Add the store_country, store_city, store_id and units_ordered column to the presentation table.

This completes our configuration in the administration window.
Open the Answers tool and add store_country and units_ordered columns to the criteria

Click on results. a table is created. Links are present to allow user to drill down. The units_ordered column is summed according the the level on which the user has drilled down

OBIEE - creating logical model and presentation catalog

2010-06-16T23:15:00.000-07:00

In the previous post we created the physical model by importing the table schema from the database. Next we will create the logical model for the inventory_fact_1997 table and connected dimensions.
1. Select the following tables from the physical layer and drag them to the logical model layer.
inventory_fact_1997
product
store
time_by_day
warehouse
make sure that you drag all the tables at the same time, this will preserve the keys and the joins as well.
2. creating the presentation catalog : Right click on the presentation layer area and click on 'New Presentation Catalog'. Name the new catalog FoodMartInventory. Drag all tables from the logical layer to the presentation layer. You can also right click on the logical layer business model and select 'duplicate with presentation catalog'.
the presentation catalog is now ready

We will also cover certain set up steps required before we start creating the dashboard.

We need to set the default repository. Edit the C:\OracleBI\server\Config\NQSConfig.ini file and modify the repository entry to
[REPOSITORY]
FoodMart = FoodMart.rpd, DEFAULT;
Restart the BI server and oc4j server.

The next step is to create a datasource that the presentation server can use to connect to the BI server. A default datasource called AnalyticsWeb is created during installation. We will create a new datasource for our use. The steps to create the datasource are
1. Click on Start-Control Panel-Administrative tools->Data Sources (ODBC).
2. click on the second tab called System DSN.
3. Click on add.
4. Select Oracle BI server from the drop down.
5. Click on Finish. The Oracle BI server DSN configuration page comes up.
6. Give a suitable name (OracleBIFOodMart) and select local as server.
7. In the next step provide the username and password and select the checkbox that says 'connect to BI server to obtain default settings..'.
8. In the next screen change the password, if required. Also the database for foodmart should be visible. click on finish.

The datasource is now available for use. We need to configure the presentation service to use the new datasource.
open the C:\OracleBIData\web\config\instanceconfig.xml file and change the DSN to
OracleBIFoodMart
Restart the presentation service.

We can now start creating reports and dashboads.

OBIEE - Creating an OBIEE repository and importing a physical schema

2010-06-15T01:52:00.000-07:00

OBIEE stores its physical and logical schema in a repository. The repository is a file with an extension of .rpd In this post we look at creating a new repository and importing the physical schema metadata from the foodmart datasource created in the previous post.
1. Open the Oracle BI server Administration page. Go to Start-> Programs -> Oracle Business Intelligence -> Administration.
2. Create a new repository. In the oracle BI administration tool click on File-> New . Type the name of the repository. Here we use the name 'FoodMart'. save as type .rpd.
3. The administration tool will show three views. The view on the right most end is the physical layer view. The middle view is the logical layer view and the left most view is the presentation layer view.

4. To import the schema from the foodmart database: click on File->Import->from Database. Select the 'FoodMart' datasource
On clicking OK, you will be presented with the following screen

The required table can be selected to import the metadata for that table. Here we will import schema from all the tables. The physical view will show the imported table schemas.

select any table and right click and select 'View Data' to view data for that table.

Importing from a database is not the only way to create a physical schema. The schema can also be created manually, but in most cases that is not required. It is also possible to add more columns or tables to an imported schema.

By default when the schema is imported, the count of rows for each table is not available. Right click on the table and select 'Update Row Count' to find the number of rows in that table at that time.

We will create a star schema for inventory_fact_1997. Create the following foreign keys
product.product_id = inventory_fact_1997.product_id
store.store_id = inventory_fact_1997.store_id
time_by_day.time_id = inventory_fact_1997.time_id
warehouse.warehouse_id = inventory_fact_1997.warehouse_id

Right click on the inventory_fact_1997 table and select Physical Diagram -> Object(s) and All join(s).

The physical schema is now ready for use.

OBIEE - Creating MySql Datasource

2010-06-14T23:55:00.000-07:00

We will be using the sample food mart database for all tutorials. Please look into the introduction blog to obtain the link for this database.
In this post we look at creating a datasource to the foodmart database in MySql. The screenshots are from a windows XP machine.
Steps to create the data source.
1. Goto Start -> Control Panel -> Administrative Tools
2. Click on Data Sources (ODBC). The datasources window will open.

3. Click on system DSN tab (second tab).
4. Click on Add.
5. Select MySql from the list.

If the MySql ODBC driver is not available download and install it from
http://dev.mysql.com/downloads/connector/odbc/5.1.html
6. Provide the connection details

6. Click on OK. The datasource is created and is available for use.

OBIEE - Introduction

2010-06-14T23:24:00.000-07:00

In this series of post we will look at Oracle Business Intelligence Enterprise Edition.
The source database for the examples is the foodmart database which can be downloaded from here . Look at this blog from more details on this database.
The blogs will cover the following:

Creating a repository.

Using Answers

Creating Dashboards

Using BI publisher

Caching

Key Performance Indicators and Balanced Scorecard

2010-06-07T21:48:00.000-07:00

Performance management is critical to an organization's success. The top management spends a lot of time in making sure that the company's day to day activity is aligned with its long term goals. The facets of performance management are :
1 Alignment - The top management needs to align the business processes of a company to its long term goals.
2. The business processes needs to be bound together so that a meaningful BPM system spanning across the organization can be set up.
3. The business processes needs to be monitored in real time basis. This is part of the BAM or Business Activity Monitoring system.
Balanced scorecards and Key Performance Indicators (KPI) are tools that the management uses to monitor the performance of their business processes.

Balanced Scorecard : Balanced scorecard was made popular by Kaplan and Norton. It is a management tool that presents a holistic view of the company measures. It is a reporting tool that shows the financial and non-financial metrics of a company. It can be used for real time monitoring of the company metrics. The balanced scorecard is a single report consisting of mainly four perspectives. The idea is to monitor not only the financial but also the non financial parameters that are critical to a company's success.
The four perspectives are shown in the diagram below.

Key Performance Indicators (KPI) - Key performance indicators are the metrics that may be the part of the balanced scorecard. KPIs are used to present actionable results across the organization. The selection of KPIs is a tricky area and can sometimes be an art. However, any KPI selected should be actionable and should be relevant . Here's one way to select KPIs
1. List down the organizations vision and goals.
2. Prepare a strategy map that is in line with the company goals.
3. Divide the strategy map into different components (financial, non-financial etc).
4. List down business processes for each strategy.
5. List down the critical success factors (CSF) for each business process.
6. Design metrics that monitor these critical success factors on an on-demand basis.

At the end of these exercise we get a list of metrics that need to be monitored. Designing a dashboard using these metrics is the next step. The dashboard is user specific. The top management including the CEO would be interested in only four or five metrics that show the overall health of the strategy. Numbers such as return on capital employed are something that the top management is interested in.

KPIs have the following characteristics:

-> The KPI should be actionable. The management should be able to use the KPI dashboard for decision making. The employees should use the dashboard to align and modify their activities so that the activities are in line with the company goals.
-> The KPI should be mutually exclusive and collectively exhaustive - Two KPIs that give the same kind of information are redundant. Each KPI should be responsible for causing a unique action. Each KPI should try and encompass multiple Critical Factors.

In designing the reports and dashboard for the KPI use the following rules:

1. Make efficient use of graph and tables. Do not use graphs that show only trend but no numbers. Remember that the KPI needs to be actionable and a graph without number is not very useful.
2. The metrics should be on-demand. They should give the latest snapshot. The management needs today's information and not last weeks summary. The decision to be made is 'How to move forward?' and not 'How did the last week look like?'.
3. Weekly and monthly reports can be used to analyze past performance and make corrections in the strategy.
4. Use ALERTS : Each employee should be presented with critical alerts that she subscribes to. The alerts should be related directly to her job role. Let the employee see the number immediately before waiting for the end of week report. For example a sales representative should be able to see the current sales pipeline without waiting for the end of month report, by which it might be too late to get more customers.
5. Use Messages and inferences -The graphs and reports should be accompanies by comments and inferences from the management. These might be done using appropriate graph or table headers also. Each graph should have a comment that explains what action needs to be taken.

Designing an effective dashboard can help the company a lot. Before embarking on the project of designing KPI and dashboard for your company, make sure you understand the following.
1. Top management sponsorship - It is very important that you find atleast one person in the executive team that is committed and passionate about building the KPIs. A lot of departments would be involved and many people may not like the idea of 'scorecard'. Choose a different name to make the dashboard more employee friendly.
2. Employee eduction - All employees should be trained and educated in using the KPIs. The success of the project is dependent on whether the employees take action based on the KPI.
3. Look and feel - Make the dashboard and the reports user friendly by incorporating the company theme into the reports. They should not look like 'just another application' but an extension of their existing tools.
4. Start with in-house tools or Microsoft excel till the dashboard gains popularity.
5. Start with a very small team. preferably 2-3 people. Select a liaison from each department that can spend some time with this team.

KPIs can be of immense help in monitoring and driving the company towards its long term goals. Used effectively, they can do wonders; But be warned, that any halfhearted implementation would only waste company time and resources.

R and Java - JRI using eclipse.

2009-11-25T07:34:00.000-08:00

R is a powerful language for statistical computing and graphics. R has a strong community support and is finding new use in the corporate world. If R can be integrated with Java, it would provide a greap acceptance into new products. A library called rjava exists that helps integrate R with Java. However, setting up the library may be tricky.

This post explores how R can be called from within Java using JRI. We will use the example provided in the rjava package. The example class is called rtest2.java.

Here are the steps to run R from java using eclipse.

1) Create a new project and copy the rtest.java and rtest2.java files from the rjava/jri package. The rjava project folder can be found at the place where R stores the package downloaded during the install step. It should be in a folder called 'win-library'.

2) the rjava/jri folder in 'win-library' should also have the JRI.jar library and jri.dll file. copy R.dll from the bin directory of R into the rjava/jri folder.
Here's the folder hierarchy for JRI

3) add the JRI.jar in the project classpath in eclipse

4) Add the following entries into the run configuration of the product.

THe path variable contains
C:/Users/user/Documents/R/win-library/2.9/rJava/jri/;C:\Program Files\R\R-2.9.1\bin\

That's it. This should work. Run the rtest2 file and you should see R working.

Some of the steps above may not be required. However, i have found this to be working after some research and would not like to experiment further. Please add your comments if it works and also if it does not.

Examples using R - Randomized Block Design

2009-10-03T04:16:00.000-07:00

Problem : We wish to determine whether or not four different tips produce different readings on a hardness testing machine. An experiment such as these might be part of a gauge capability study. The machine operates by pressingthe tip into a metal test coupon, and frm the depth of the resulting depression, the hardness of the coupon can be determined. THe experimenter has decided to obtain four observations for each tip. There is onlyh one factor - tip type - and a completely randomized single factor deisn would consist of randomly assigning each one of the 4x4=16 runs to an experimental unit, that is , a metal coupon , and observing the hardness reading that results. However, if the metal coupons differ slightly in their hardness, as might happen if they are taken from ingots that are produced in different heats, the experimental units will contribute to the variability observed in the hardness data. As a result the experimental error will reflect both random error and variability between coupons. We would like to remove the variability between coupond from the experimental error. A design that would accomplish this requires the experimenter to test each tip once on each of four coupons. This desin is called a randomized complete block design. Each block contains all the treatments. Within a block the order in which the four tips are tested is randomly determined.
The test data is

Let us look at the interaction plot

and the box plot

Let us now run the analysis of variance on the data, we will include the blocking variable in the analysis.
the formula to be used in R is hardness~typeOfTip+testCoupon.

> anova(aov(hardness~factor(typeOfTip)+factor(testCoupon)))
Analysis of Variance Table

Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

A plot of the residuals should not show any pattern since the residuals are assumed to be independently distributed for analysis of variance. Here's the plot of the residuals.

A Quantil-Quantile plot can be used to check the distribution as well. The plot also shows the presence of outliers, if any. The plot is shown below.

Tukeys test can be used for pairwise comparison. Here's the result of the Tukey's test
fit=aov(hardness~factor(typeOfTip)+factor(testCoupon))
TukeyHSD(fit,which='factor(typeOfTip)',ordered="TRUE")

A plot of the residuals should not have a pattern. Here's a plot of the residuals.

We can check for variance. Here's a method to check to equality of variance.
> summary(lm(abs(fit$res)~typeOfTip))

Call:
lm(formula = abs(fit$res) ~ typeOfTip)

Residuals:
Min 1Q Median 3Q Max
-0.050000 -0.032812 -0.003125 0.026562 0.093750

Coefficients:
Estimate Std. Error t value Pr(>t)
(Intercept) 0.075000 0.024719 3.034 0.00893 **
typeOfTip -0.006250 0.009026 -0.692 0.50000
---
Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 0.04037 on 14 degrees of freedom
Multiple R-squared: 0.03311, Adjusted R-squared: -0.03595
F-statistic: 0.4795 on 1 and 14 DF, p-value: 0.5

Since the p-value is large, difference in variance cannot be stated.

The Latin Square Design:
If there are two blocking variables then the latin square design can be used.
problem : Suppose that an experimenter is studying the effects of five different formulations of a rocket propellant used in aircrew escape systems on the observed burning rate. Each formulation is mixed from a batch of raw material that is only large enough for five formulations to be tested. Furthermore, the formulations are prepared by several operators, and there may be substantial differences in the skills and experience of the operators.
Here's the data

The latin square structure is
> matrix(treatments,5,5)
[,1] [,2] [,3] [,4] [,5]
[1,] "A" "B" "C" "D" "E"
[2,] "B" "C" "D" "E" "A"
[3,] "C" "D" "E" "A" "B"
[4,] "D" "E" "A" "B" "C"
[5,] "E" "A" "B" "C" "D"

An analysis of variance gives
> anova(lm(formulations~factor(rawBatches)+factor(treatments)+factor(operators)))

The data shows that the different formulations do produce different burning rate. Also there is a difference between the operators. However, no difference between the raw batches.

Examples using R - Analysis of Variance

2009-09-30T02:05:00.000-07:00

Problem: A product development engineer is interested in investigating the tensile strength of a new synthetic fiber that will be used t omake cloth for men's shirts. The engineer knows from previous experience that the strength is affected by he weight percent of cotton used in the blend of materials for the fiber. Furthermore, she suspects that increasing the cotton content will increase the strength, at least initially. she also knows that cotton content should range between about 10 and 40 percent if the final product is to have other quality characteristics that are desired. The engineer decides to test specimens at five levels of cotton weight percent: 15, 20, 20, 30 and 35 percent. she also decides to test 5 specimens at each level of cotton content.
Here we have a single factor with 5 levels and 5 replicates.The data is

> cotton
$p15
[1] 7 7 15 11 9
$p20
[1] 12 17 12 18 18
$p25
[1] 14 18 18 19 23
$p30
[1] 19 25 22 19 23
$p35
[1] 7 10 11 15 11
A box plot of the data is

A histogram of data looks like

A multiple scatter plot can sometimes be used if corresponding values of the observations need comparison. The scatter plot for this data is as shown.

Analysis of Variance:
Lets use analysis of variance in the above example to find out if all means are equal or if any mean is different.
The data needs to be transformed for aov

> c(cotton_matrix[1,],cotton_matrix[2,],cotton_matrix[3,],cotton_matrix[4,],cotton_matrix[5,])->cotton_data
> cotton_data
p15 p20 p25 p30 p35 p15 p20 p25 p30 p35 p15 p20 p25 p30 p35 p15 p20 p25 p30 p35 p15 p20 p25 p30 p35
7 12 14 19 7 7 17 18 25 10 15 12 18 22 11 11 18 19 19 15 9 18 23 23 11

Analysis of variance yields

> summary(aov(cotton_data~names(cotton_data)))

from the F value we reject the null hypothesis and conclude that the means differ.

Analysis of variance uses certain assumptions and it is important to check the validity of these assumptions. The first method is to analyse the residuals for each observations. There should be no pattern in the residuals. If residuals either spread out or narrow down as time progresses then this could be an experimental error.
Here's a plot of residuals against time (observation)

Another validation is to check the nature of the residuals themselves. One way to do is to plot of curve of residuals versus the fitted values.Here again no pattern should be present

The variance for the five sets can be compared using the Bartlett's test
> bartlett.test(cotton_data~factor(names(cotton_data)))
Bartlett test of homogeneity of variances
data: cotton_data by factor(names(cotton_data))
Bartlett's K-squared = 0.2801, df = 4, p-value = 0.991

The results show that the null hypothesis cannot be rejected and hence the variance of the five sets is indeed same.

We now need to do a pairwise comparison to find out which pair has a difference in mean. we use the Tukey's test to do so.

If the assumption of normality is not met then a test known is Kruskal-Wallis test may be used

> kruskal.test(cotton_data~factor(names(cotton_data)))
Kruskal-Wallis rank sum test
data: cotton_data by factor(names(cotton_data))
Kruskal-Wallis chi-squared = 18.5513, df = 4, p-value = 0.0009626

Examples - Comparing two conditions using R.

2009-09-29T22:50:00.000-07:00

Problem : The tension bond strangth of portland cement mortar is an important characteristic of the product. An engineer in interested in comparing the strength of a modified formulation in which polymer latex emulsions have been added during mixing to the strength of the unmodified mortar. The experimanter has collected 10 observations on strength for the modified forumlation and another 10 observations on strength for the unmodified formulation. The data is

A box plot of the data :

Two sample t-test equal variances, two sided :
>t.test(data2$modified,data2$unmodified,var.equal=TRUE,alternative="two.sided")
Two Sample t-test
data: data2$modified and data2$unmodified
t = -9.1094, df = 18, p-value = 3.678e-08

alternative hypothesis: true difference in means is not equal to 0

95 percent confidence interval:
-1.4250734 -0.8909266

sample estimates:
mean of x mean of y
16.764 17.922

Of course, our assumption has been that the distribution of data for both the samples is normal. We can test this assumption using the quartile-quartile plot. see
here for details on qqnorm plots. A plot for modified sample is

A plot for the unmodified sample is

Paired t-test:
problem: consider a hardness testing machine that presses a rod with a pointed tip into a metal specimen with a known force. By measuring the depth of the depression caused by the tip, the hardness of the specimen is determined. Two different tips are available for this macine, and although the precision of the measurements made by the two tips seems to be the same, it is suspected that one tip produces different hardness reading than the other. AN exmperiment is conducted in which the two tips are used within the same specimen and 10 runs of the experiment are made. The data is as shown below

> hard
$tip1
[1] 7 3 3 4 8 3 2 9 5 4

$tip2
[1] 6 3 5 3 8 2 4 9 4 5

The paired test is
> t.test(hard$tip1,hard$tip2,paired=TRUE,var.equal=TRUE,alternative="two.sided")
Paired t-test
data: hard$tip1 and hard$tip2
t = -0.2641, df = 9, p-value = 0.7976

alternative hypothesis: true difference in means is not equal to 0

95 percent confidence interval:
-0.9564389 0.7564389

sample estimates:
mean of the differences
-0.1

comparing variances:

It is sometimes useful to compare variances of two samples. Consider the mortar example. we can compare the variances using R as follows
> var.test(data2$unmodified,data2$modified)
F test to compare two variances
data: data2$unmodified and data2$modified

F = 0.6138, num df = 9, denom df = 9, p-value = 0.4785
alternative hypothesis: true ratio of variances is not equal to 1

95 percent confidence interval:
0.1524534 2.4710609

sample estimates:
ratio of variances
0.6137766

Design of Experiments - Introduction

2009-09-29T21:50:00.000-07:00

Experiments are performed in various fields to understand the behaviour of a system. In this post we will analyse the common terms used during an experiment design.

Experiment :
An experiment can be defined as a test or a group of test wherein changes are made to the input variables and the effect of that changes observed in the output or the response variable. The aim of the experiment is to find what effects the input variables have on the output variable.

Strategy of Experimentation :
There are various ways of performing the test in an experiment.
One-at-a-time : In this strategy a baseline set of level is selected for each factor. subsequently, one of the factors if varied across its range and the other factors held constant at their baseline level. The test are repeated for other factors. This strategy has an advantage of simplicity, however, it fails to consider any interaction effects between factors. Interaction is defined as the failure of one factor to produce the same effect on the response variable at different levels of another factor.
Factorial: A useful method to deal with multiple input variables or treatments or factors is to use a factorial method of experimentation. In this method the factors are varied together instead of one at a time. However, if there are more than four factors the number of combinations of tests may be too high. It is therefore sometimes unnecessary to run all possible combinations of factor levels. A fractional factorial experiment is used. It is the variation of the factorial experiment where only a subset of the runs are made.

It is useful to understand three basic principles of experimental design before starting with an experimental design. These principles are replication, randomization and blocking.

Replication :
Replication is the term given to repetition of the basic experiment. Replication is used to allow the experimenter to determine the experimental error value. For example, if a sample mean is used to identify the effect of a factor, replication provides a more accurate measure of the sample mean. However, distinction has to be made between replication and repeated measurement. Replication entails performing the test to find out the variability both between the runs and possibly within the runs. A repeated measurement merely takes the value of the output variable multiple times or performs the experiment without changing the factors. Thus is does not capture the variability in the experiment due to the input factor.

Randomization:
The statistical analysis of the experiment is based on the assumption that both the experimental material and the selection of runs is random. The experiments themselves are performed randomly. The observations made should be independently distributed random variables. By choosing the randomization technique we can average out the effects of external factors.

Blocking:
Blocking is used to minimize the variability introduced in the experiment due to nuisance factors. Blocking is generally used when comparison of factors are made. Nuisance factors are the factors that influence the response variable but the experimenter is not interested in them. I.e. they are not part of the study, nevertheless, they do influence the study. Each level of the nuisance factor becomes a block and the experiment is carried out within blocks.

Experimental design - guidelines:

1. Understand and define the problem - Sometimes a single large experiment may not be able to provide the answers or may be difficult to perform. In such cases a series of smaller experiments may be performed.
2. Choice of input variables or factors, their levels and their range : The experimenter considers the design factors and the nuisance factors during this stage. The design factors are those that are changed during the experiment and their effects studied. They may be factors selected for the experiment, variables that are held at constant values during the experiment (hand held factors) and allowed to vary factors. The nuisance factor may be controllable, uncontrollable or noise factors. The experimenter can set the levels of controllable factors. The blocking principle can be used to deal with controllable factors. IF a nuisance factor cannot be controlled but can be measured then a strategy called analysis of covariance can be used to accommodate its effects. For noise factors the strategy is to minimize the variability due to noise factors. This is sometimes referred to as robust design problem.
3. Selection of response variables.
4. Choice of experimental design.
5. The actual experiment.
6. Analysing the data.
7. Conclusion of the experiment.

Dimensional Modeling

2009-09-24T21:49:00.000-07:00

A company that has a huge volume of data builds a data warehouse so that it can generate reports, perform analytics and make informed decisions. The process of building a data warehouse from a transactional or source system is important and the process that a company selects depends on its long term vision and goal of its business intelligence systems. There are two schools of thought on how a data warehouse system should be built. In this article we discuss one of those. The method that we discuss here owe's its existence to Ralph Kimball and can also be called Dimensional modeling.
Before discussing what dimensional modeling is, let us understand what is expected out of a datawarehouse. The requirements of a data warehouse are:
1) It should allow everyone in the organization to effectively access information.
2) It must present a 'single version of truth' of data.
3) It must be able to incorporate changes quickly.
4) It must protect data and manage data access.

Componenets of a Data Warehouse:
Lets discuss the components of the data warehouse topology.

Operational source system : This source systems are the transactional systems that the company uses to capture its operational data. The source systems may range from a relational database to a flat file. The source systems are characterized by high availability and high performance. However, they may not store historical information.

Data Staging Area: The data from the source systems need to be fed to the data warehouse. However, the data may undergo various transformations before it is loaded into the data warehouse. A process called ETL (Extract, Transform and Load) may be carried out during the data staging area. Ralph Kimball advocates that the data in data warehouse should be stored in dimensional form . However, data in the staging area may be in normal form, but should not be avilable to the end user for querying.

Data Presentation: This is the area where the end users submit their query. It consists of a series of data marts. A data mart contains data in a dimensional form. The data in a data mart corresponds to one business process. Note that the data mart does not contain data from a business department but a business process. There may be multiple processes in an organization and hence multiple data marts. However the processes and hence the data marts may contain information that is shared accross the organization. For example, the customer information may be present in all data marts. Therefore all data marts may contain a dimension called customer. This dimension may be shared (physically or logically) accross all the data marts and is called a conformed dimension.
The presentation area is used in the following way
1) Creating predefined reports.
2) Creating adhoc reports.
3) As an input to forecasting.
4) As an input to other analytics tool.
The dimensional schema for a relational database is called a star schema. For a multidimensional system the data may be stored in OLAP cubes.

Besides the above the organization may also contain a metadata repository. The metadata is information about the tables, its indexes, structure Etc. It would be beneficial for the organization to let the users perform query using the metadata rather than the tables themselves. This makes the system flexible since any changes in the underlying physical tables does not effect the query.
The metadata can be looked as
physical table -> physical model -> business model - > business table <- user query
Fact Table: The dimensional schema consists of fact tables and one or more dimensional tables. The fact table is the heart of the schema and contains the transactional information. It stores the numeric information that can be aggregated. The performance metrics and other KPI are generally stored in the fact table. one characteristic of the fact table is that it contains data at the same grain. i.e. all rows in the fact table contain data at the same level of granularity. Also this level of granularity should ideally by as low as possible. This gives the user the ability to perform drill down at any level. The textual values may not be suitable for the fact table and are moved to the dimension table. The fact tables have foreign keys(FK) that correspond to the primary key in the dimensional table. Dimensional Tables: This contain the dimensions upon which the user can perform drill through and use in their queries. The dimensional table mostly contains textual values. A very important dimension table is the Date table. The fact table along with the dimensional table form a star join sch ma. Example : Lets take an example of a retail garment store. The store has its sales information and it wants to set up a data warehouse that allows them to generate adhoc reports. The information that it can put in the fact table is the sales amount, the discount offered , the price after discount etc. The information in its dimensional table could be the size of shirt or trousers sold, the colour of the garment. It could have a data dimension that stores the data of the transaction and also stored other information such as holidays and other special days. The schema looks like below

The process of dimension design is as follows:
1) The first step is selecting the business process. As discussed earlier the business process is distinct from a business department or organization. Typical business process are orders, invoicing, inventory management etc. The business departments may be procurement, store management etc.
2) The second step is identifying the granularity of data that is to be stored in the fact table. Care should be taken at this step since it is difficult to change the grain at a later stage. It is advisable to select as low a grain as possible to allow user to perform adhoc queries. Separate fact tables may be used to store accumulated data at a higher grain.
3) The third step is choosing the dimension. The dimensions may be date, product etc.
The dimension table should contain descriptive values rather than code values. This allows the user to quickly use these columns in their query without performing any lookups. Also the dimension table should contain all derived information as separate columns. For example in a data dimension it is beneficial to store separate columns for day, month, day of year etc. Although this information can be obtained from a 'date' object, the user should not be burdened with performing the transformation for use in the query/report. Creating report from the dimensional schema should be as simple as dragging and dropping the columns in the reports.
4) The fourth step is to identify the numeric information that goes into the fact table. The numeric information forms the part of the KPI and hence the organization dashboard. Once again derived information should be stored as separate columns. For example if the data contains weight and if the user may need weight in either pounds or Kg then it is beneficial to have two columns in the fact table, once containing the weight in pounds and the other the weight in Kg.

Some important concepts that can be helpful while designing a dimensional model are:
Degenerate Dimensions :

Certain values such as the invoice number or the order number can be used in the fact table without a corresponding dimension table. This values are known as degenerate dimension.

Dimensional normalization- snowflaking:
Although normalization is in general not recommended, in certain cases the dimensional tables may be normalized. This is know as snowflaking. This is typically done when the number of attributes is high. The redundant attributes may be put in another table.

Surrogate Key :

The fact table is joined to the dimensional table using a key. This key should not be a natural key but rather a key generated by the system specifically for this purpose. For example a product dimension may be joined to the fact table using the product number, however there are problems to this approach and it as recommended to generate a unique number for each row in the dimensional table and join the fact table to the dimensional table using this unique number.

Data Warehouse Bus Architecture:

Ref:http://intelligent-enterprise.informationweek.com/db_area/archives/1999/990712/webhouse.jhtml
A data warehouse bus architecture can be used to implement an integrated data warehouse in an enterprise. The different data marts can be plugged together using the bus architecture. As seen in the picture, the individual data marts are the rows of the matrix, whereas the columns of the matrix are the conformed dimensions. While designing the data mart, the row is added and the corresponding conformed dimension can be looked up on the columns. It is beneficial to enumerate all conformed dimensions at the start of data warehouse building process. This helps the designer to quickly identify dimensions that can be reused.

Conformed dimension:

Conformed dimensions have been described earlier. Some of the characteristics of conformed dimensions are :
1) They have consistent column names, definition and values.
2) A subset of the entire dimensional table may be used.
3) Separate physical tables may be used. But the physical tables are well synchronized.
4) Dimension tables may be handled by a separate authority, called a dimensional authority.

Slowly Changing Dimensions:
The attributes of the dimensional table may change over time. A suitable technique is needed to incorporate the changed dimensions in the dimensional table without breaking the reports and also making the changed attributes available to be joined to the fact table for reporting.
There are three strategies to handle slowly changing dimensions. The change is described as slow since it is not very often that a dimension attribute changes. If the change is dynamic then the design needs to be rechecked.
Type 1: In the first strategy the new value overrides the old value. So for example, if a product moves from Category A to Category B the category column value for product A is simply changed from category A to category B. This is a simple, fast and easy way to handle the change. The fact table does not change. However, note that the historical information is lost. There is no way to find the performance of product A under category A items. Also historical data of category A may start differing since Product A is not shown under it any more.
Type 2: The second strategy is very widely used. In this strategy an additional row is added to the dimensional table. For example, for product A an additional row is added where the category is now category B. All other values remain same. A new key is generated for the row. This method also highlights the advantage of using a system generated primary key(surrogate key) instead of a natural key as a primary key. The second advantage is that historical reports may still contain the older definition of product A. A date value may be added to show the effective/expiration date.
Type 3: A new column can be added. For product A, two columns can be maintained. one column shows old category and the other shows new category. This strategy can be useful when changes are minor. The historical information is also preserved. However, it may not always be possible to add new columns.

Dimension Role Playing :

Sometimes a single dimension table may be linked to the fact table multiple times (ways). In this case the dimension table may be a single physical table but may be represented in different views. The dimension table therefore plays multiple roles.

Junk Dimension :

A junk dimension is sometimes used to group low cardinality columns such as flags.

Fact table types:
The fact table may be broadly of three types
Transaction fact table: These store data at the transaction grain. Each row may correspond to a single transaction.

Periodic snapshot : These show cumulative data for a particular time frame. Each row may contain aggregated data for the day, week or month. These tables can be used to generate reports for performance metrics (KPI)

Accumulating snapshot: The accumulating snapshot stores data for a transaction or entity that may take an indeterminate time. For example an accumulating snapshot of customer purchases may show all purchases till date. Note that in this kind of tables the values may need to be updated regularly.

Introduction to data Warehouse

2009-09-15T07:25:00.000-07:00

Many companies spend a fortune in organizing and storing their data. The amount of data that is generated daily may vary for different companies but it is not uncommon to hear of scores of GB of data for medium sized companies and terabytes of data for large enterprises. It is indeed challenging to store this size of data in a meaningful way and then to use this data to make decisions. In this article we look at why a company needs a data warehouse and how it should go about building one.

What is a data warehouse anyway?
Lets say that you are a car manufacturing company. You have been making cars for more than 50 years and you have managed to implement IT in all your departments. You have a sales management system that records sales throughout all your network dealers. This sales system not only records the sales history but also records information about potential customers. Then you have the accounting system that generates invoices for the sales made and also keeps track of the company income and expenditure. You may also have a financial system that helps you make your budgetary decisions and provides you a financial road map. Your company would also have a customer relationship management system and a campaign management system. Of course, you would have a state of the art operations systems with advanced inventory management system. There may be various other systems but lets concentrate on what we need out of this system. Different people in the organization may need different kind of information out of this system. The dealer would need the daily or monthly sales figures. She may also need a report of potential customers. At a regional level the manager might need information at aggregate levels. i.e. sales by city by month or a list of potential customers that have a certain fixed budget and fall under a particular age group. As the company matures, it may need deeper analytics such as optimized inventory distribution for its network etc. A data warehouse helps the company in storing data in such a way that such information retrieval becomes faster and simpler. It allows the company to store a large amount of historical system without in any way affecting its transaction systems. A well designed data warehouse system with advanced Business Intelligence tool can do wonders for a company.

How is the database different from a data warehouse?
A database is designed to store the transactional or operational data whereas a data warehouse is designed to store historical information. For example, a database would be used by a transaction system that generates invoice for the car sales. But imagine how fast the data grows in this system. If we add up transactions for a month for all stores across the country and we don't remove this data then the database would become extremely slow. The database is designed to give quick response time for transactions. A data warehouse on the other hand is designed to store hundreds of gigabytes of data and contains query optimizers that return quick results. They may not be good at supporting transactions.

So how does the database and data warehouse coexist? How does the data flow?
A company generally has multiple transaction systems. The systems may be from different vendors and may have different underlying structures. For example the accounting system may be completely different from a procurement system. They may use different vendors and completely different technology. However, we eventually want to generate reports that obtain data from both the systems. We also want to make sure that a 'single version of truth' is maintained in the organization. There have been many cases where two departments cannot come to a conclusion since they have different numbers for the same entity. A data warehouse therefore contains data from multiple source systems. A process known as ETL (Extract, Transform and Load) is used to extract data from the various systems in an organization and feed the data to a single data warehouse system. The data may undergo cleaning, transformation, deduplication etc before it is loaded into the data warehouse.
The diagram below gives a simplified view of the process.

Statistics - Examples

2009-09-07T01:17:00.000-07:00

T-Test:
Problem : A swimming instructor wants to prove that the swimming speed of an athlete increases if the athelete performs some specific exercises before the swim. He undertakes an experiment with 16 participants and randomly assignes 8 participants to each team. For team A he recommends some common exercises and for team B he recommends some specific exercises. The results of the experiment are below.
Team A (Speed) - 10 12 11 16 13 9 15 6
Team B (Speed) - 11 12 13 11 8 14 7 7
Use the t test to find out if the average speed of the two teams is significantly different. Use alpha = 0.05.

Null Hypothesis => A(speed) = B(speed)
Alternate Hypothesis => A(speed) ne B(speed)

We assume unequal variances and therefore use the t-test for unequal variances.
The calculation in R is given below.

We see that the p-value is greater than 0.05 and hence the null hypthesis is not rejected.
What this means is that the special exercises are not so special after all!

We have assumed unequal variances. The other forms of t test are 1) assume equal variances 2) assume a paired test (the data corresponds to one team taken before and after the experiment)

Observe the difference in the degree of freedom and the t-value in all three cases.
look at http://janda.org/c10/Lectures/topic07/pairedt-test.htm for an example of the significance of using paired t-test.

ANOVA - one way:
Problem : An independent magazine wants to find out if the tyre life differs for four different tyre manufacturers. (for the same kind of tyres). It randomly selects 10 participants for each tyre make type and compares the mean of the tyre life.
Make A(tyre life in months) - 12 17 23 20 18 10 30 12 23 20
Make B(tyre life in months) - 10 23 19 30 13 15 18 17 20 22
Make C(tyre life in months) - 11 34 12 20 33 20 18 19 12 17
Make D(tyre life in months) - 10 12 34 23 27 18 15 17 10 15

Null Hypothesis - All means are equal.
Alternate Hypothesis - Atleast one of the means is different.
The results of the analysis are as given below.

The p-value is greater than 0.05 and hence the null hypthesis is not rejected.
What this means is the the shelf life of the tyre is not dependent on the make.

More Detailed examples of ANOVA can be found here:
http://www.personality-project.org/R/r.anova.html

Use of Statistics - Practical Considerations

2009-09-02T22:23:00.000-07:00

It is often confusing to decide on which statistic to use at what point. Also researchers need to be careful that the statistics they present does truly apply in the context of the problem. Statictics can be misleading and probably incorrect if used outside the boundaries set by its assumptions. In this post we analyse different statistics and what care should be taken while using them.

Mean,Median and mode : The mean is probably the most widely used number. A lot of claims are made using the mean but this could be quite misleading. The mean does not include any measure of variability. Also, outliers may distort the mean to a large extent. Median can be reported with the mean to get an idea of how the extreme looks like. For a more detailed analysis the box and whisker plot gives a fair idea of how the data looks like. It is therefore better to provide a measure of variablility along with the measure of central tendency.

Discrete distribution : While using binomail distribution the independence and size assumptions should be met when sampling is done without replacement. In Poisson distribution the size and lambda assumptions should be met. for binomial distributions for large sizes of n the probability at a particular x value should be reported with care. for example in a coin toss with n = 100 and p=0.5 P(x=50)=0.076. This could be counter-intuitive and hence a better way to report this is to use P(x>50). In poisson study the value of lambda may change and hence the researcher should make sure that lambda is valid for their test conditions.

Continuous distribution : Here again, the value of lambda should be chosed with care. The value of lambda used in one study may not be useful in other similar study since the populations may be different or the time interval of the lambda may be different. For normal distribution, care should be taken to verify the distribution since most of the tests are hightly sensitive to the type of distribution of the population.

Sampling - One of the widely misused method is sampling. Many surveys use non random sampling instead of random sampling and the statistic thus obtained may quite off the mark. The sampling data is quite often used to make inferences about the population and if questionable means have been used to sample data then the population inferences may be highly incorrect.

Hypothesis testing for single population: In hypothesis testing it is imperative that the researcher formulate the hypothesis in such a way that what is known is the null hypothesis and what he strives to prove is the alternate hypothesis. Researchers may use null hypothesis as the statement of what they want to prove and this is incorrect since then the theory is assumed to be true and alternate hypothesis only strives to disprove it. While using t-test, the population needs to be normally distributed to some extent. However, the chi square test is extremely sensitive to the assumption that the population is normally distributed and hence the researcher should make sure that the population is indeed normally distributed. Also, the business implication of a statistical significance test needs to be worded carefully. The context needs to be understand and the assumptions while arriving at the 'significance' level should be clarified.

Hypothesis testing for two populations : The assumptions used while using the statistic that compares two populations should be met. 1) For small sample sizes, the x test is valid only if the population is normally distributed and population variances are known. 2) t-test can be used if population is normally distributed and population variances are assumed to be equal.3) For F test the two populations should be normally distribued.

ANOVA: While reporting ANOVA results the researcher needs to consider all variables that may effect the outcome of the experiment. She should at least mention the concomitant variables that have not been considered in the experiment but that have been shown to show some influence to the dependent variable, however small that dependence may be. The treatment levels selected for the study should possibly be random. Certain tests such as two way factorial design or completely randomized design with Tukey's HHSD may require equal smaple sizes. Sometimes researcher arbitrarily make up or delete values to make sample sizes equal and this is incorrect.

Regression Analysis: regression analysis require equal error variance and independence of error terms. Residual and other statistical techniques can be used to verify that. Remember that the regression line is valid only if the assumptions are met. Another problem arises when the regression model is used outside the values used to formulate the regression model. The model is valid only in the domain used to create the model. Data may behave linearly in a certain range but may tend to behave non linearly outside this range.

Multiple Regression: For a small degree of freedom the value of R squared obtained may be inflated. A cause and effect relationship may be assumed to occur between the dependent variable and the predictors. It is however possible that factors not considered in the study may be causing the behaviour. Also for multiple variables with different units the R squared values should not be compared. The coefficients of regression should also not be used to compare the effect of various predictors since the predictors may have different units. Also while building the multiple Regression model it is not necessary that the variables that enters the equation first is the most influential.

Reference;

Use the site http://www.whichtest.info/ to figure out which test to use when.

Also a table is available at http://www.graphpad.com/www/Book/Choose.htm that assists in understanding which test to use when. The table is reproduced below.

Non Parametric Statistics

2009-09-01T21:42:00.000-07:00

The earlier posts on inferential statistics show methods that work on data whose population parameters and distribution are known. However, there are cases where the population parameters are not known. In such cases, no assumption can be made about the population statistics and hence parametric methods cannot be used. In such cases there are certain non-parametric methods that can be used. Some non-parametric statistics can deal with nominal (data where we have unordered categories such as happy, not happy) or ordinal (data where we have ordered categories such as 1-agree 2-disagree , 3-strongly disagree etc).
We will look at the following tests: Runs test, Mann-Whitney U test, Wilcoxon matched-pairs signed ranks test, Kruskal-Wallis test, Friedman test and Spearman's rank correlation coefficient.

Run's Test:
Consider a railway station where the station incharge is keeping a tab of whether the train arrives on time on the station or arrives late given that it has arrived on time on the previous station. He needs to find out whether some problems on the track between the previous station and his station is causing the delay of trains or is it random occurrence not attributable to any specific problem.
15 trains arrive on the station in the day. Let the code for arrival on time be 0 and arrival late be 1. also assume that the trains arrive at constants interval. The arrival can be of the form 000000011111111. i.e the first 7 trains arrive on time and the last 8 arrive late. This kind of data can be suspected, why do the trains arriving in the first half of the day come on time and the others don't. This behaviour coud be non-random. probably, visibility reduces as day progresses and the train driver needs to slow the trains down. Other kind of distribution could be 001011011011000. This kind of distribution could be random. Runs test can be used to establish whether this is indeed random. A run can be defined as an occurrence of a sequence of similar events. For example a 00 in the start of the sequence is one run. The total number of runs are 00-1-0-11-0-11-0-11-000 -> 9.

The Null hypothesis for the run's test is that the observations in the sample are randomly generated. The alternate hypothesis is that they are not randomly generated. The test differs for small samples and large samples.
Small sample Runs test - for small samples let n1 be the number of observations of event 1 and n2 be the number of observations of event 2. if n1 and n2 are less than or equal to 20 this test can be used. This is how the test is carried out.
1) establish that the small sample test can be used by examining n1 and n2 values.
2) assume a value of alpha (0.05).
3) Use the Runs table for small sample and n1 and n2 and alpha and find out the critical value. Note that the Runs table gives two values. An upper tailed table which gives the higher critical value (C2) and a lower tailed table which gives the lover critical value (C1). if the number of runs (N) of the observation fall within C1 and C2 then the decision is to not reject the null hypothesis. I.e. the sample is indeed random.

Large Sample Run Test:
For n1 and n2 greater than 20 the sampling distribution is normal. The z statistic can be used to check the null hypothesis. The z statistic is given by

The z table can be used to find out the critical values for the level of alpha. the z values can then be compared. If the z value from the experiment falls outside the critical value from the table, the null hypothesis is rejected. Or if the p-value is less than alpha the null hypothesis is rejected.

Mann-Whitney U Test :
It is a counterpart of the t-test used when the assumption of normal distribution is not valid, or if the data are at least ordinal. The two tailed hypothesis is : Null hypothesis - the populations are identical.
Let us look at this test using an example. Suppose that a researcher wants to find out if the average age of people entering a movie theater is different in two cities.
The example below shows how the Mann-Whitney U test solves the problem.

The steps are:
combine the data and arrange them in ascending order. Assign rank to the observations and also preserve the information about which group the observation comes from. Calculate W1 as the sum of ranks for group 1 elements and W2 as sum of ranks of group 2 elements. Calculate U1 and U2 using

The test statistic is the smaller of the two U values.also note that U1=n1n2-U2. Use that table to calculate the p-value using U (smaller), n1(no of obs for sample having smaller U) and n2. For a two tailed test double the p-value.

For large samples U is approximately normally distributed. A z statistic can be used to check the null hypothesis. The z statistic is given by

Wilcoxon matched-pairs signed rank test:
The Mann-Whitney test is a t-test counterpart of independent samples. However, if the two sample are related, the wilcoxon matched-pairs signed rank test can be used. This test can be used for situations where the researcher is measuring data before and after studies or data taken from the sample person at two different conditions etc.
The method used for calculation depends on the size of the sample. If the sample is of small size (<15)

here we are comparing the household income for a group of families after a change in government policy. The steps are as follows: first calculate the difference in the income for each pair. Arrange the absolute value of the differences in ascending order and assign ranks to them. For values that have a negative difference change the sign of the rank to negative. Calculate the sum of rank for the negative and positive values. The minimum sum is our test statistic. Compare this value with the value obtained from the T table for given n and alpha. The hypothesis if rejected if the calculated T is less than the T from the table.
Large-Sample Case:
If the sample size is greater than 15 then the T statistic is approximately normally distributed and a z score can be used to check the null hypothesis.
The z formula is given by.

T is calculated in the same way as in small sample size analysis.

Kruskal-Wallis Test:
This test is the non parametric counterpart to one way ANOVA. The parametric ANOVA is based on normally distributed population, independent groups, at least interval level data, and equal population variances. The Kruskal-Wallis test is used to analyse ordinal data and is not based on any assumption of the population distribution. However this test deals with independent populations only and the sample data needs to be selected randomly from the population.
Let the test compare c groups. The null hypothesis is given by
Null Hypothesis - The c population are identical.;Alternate hypothesis : at least one of the c populations is different.
The process is as follows:
1) combine the groups together and arrange the data in ascending order. assign ranks to the data and maintain the group to which the data belongs.
2) In case of a tie between multiple data elements, each tied element is assign the average rank of the tied elements.
3)The K value is calculated as below.

Where c = number of groups
n = total number of items
Tj=total of ranks in a group.
nj=number of items in a group.
4) the K value is compared to the Chi square value with given alpha and df = c-1. If the calculated K value is greater than the chi square value, the null hypothesis is rejected.
5) This is a one tailed test only.

Friedman Test:
This test is the nonparametric counterpart of the randomized block design. Friedman Test can work with populations whose distribution is not known and when data is ranked. The groups need to be independent and the researcher should be able to rank observations in each group .
The null hypothesis is : the populations under treatment are equal.
The alternate hypothesis is : At least on population under the treatment yields larger values than at least one another population under treatment.
Steps for analysis:
1) Rank the data. The ranks are assigned within the group and the data across the group is not combined. see the earlier tests for how data can be ranked. The smallest rank in a group is 1 and the largest is c. c is also the number of treatment levels.
The statistic is calculated using:

where c = number of treatment levels(columns)
b = number of blocks.(rows)
Rj=total of ranks for a particular treatment level (column)
j=particular treatment level (column)
The value obtained from the formula is compared with the chi square value for given alpha and df=c-1.

Spearman's Rank Correlation:
The degree of association of two variables when only ordinal level data is available can be calculated from Spearman's rank correlation given by:

n= number of pairs being correlated.
d = difference in the ranks of each pair.

Multiple Regression model building

2009-08-31T23:53:00.000-07:00

Polynomial Regression:

First order regression models contain predictors that are single powered. Polynomial models have one or more predictors having a power of more than one. A quadratic model has a predictor in the first and second order form.

Since the constants are linear, the variable x1 squared can be recoded to x2 and then the equation becomes linear.
Higher power models can be used but are generally not used for business purposes.

Tukey's ladder of transformation :
A plot of x and y which seems non linear may need transformation for fitting. quadratic polynomial is one such transformation where the data is recoded for better fit. Tukey proposed a series of transformation that can be used to improve the model fit to data. The transformation to be used depends on the shape of the data.(the shape of the scatter plot of the data)
a variable x can be transformed as follows.

when moving towards higher powers of x, we move up the ladder.

Regression model with interaction :
Two variables in a model may not be independent and may interact in a way such that the effect of one depends on the value of another. This interaction effect can be taken care of by using an interaction variable which is the multiplication of the two variables. The linear regression equation for such a model is

Transforming y variable : In certain cases the y variables needs to be transformed.
for example

The variable y is transformed but x is not.

Indicator or Dummy variables: Some variables do not have a quantitative effect on the data but may categorize items. These may be ordinal or nominal variables. These variables are included in regression model as dummy variables. example of indicator variable : a survey to find the satisfaction of a customer may have values such as excellent, good , poor etc. Such a variable is called an indicator variable. The indicator variables may be coded using 0 and 1. for example a gender question can be coded to have 1 for female and 0 for male. If there are more than two values then multiple indicator variables can be taken. for example in the earlier example we can have two indicator variables, namely, good and poor. note that the third variable is not required since if the grading is neither good nor poor then it is bound to be excellent. A value of good is coded as good = 1 and poor = 0, a value of excellent is coded as good = 0 and poor = 0.
Also, as a rule of thumb it is generally necessary to have at least three observations per variable to get meaningful and correct model.

Model-building procedures :
Different predictor variables can be used to form a model. For example the price of a commodity may depend on the following - the cost of raw materials, the demand of the item, the cost of competitor's product etc. The aim of the regression model is to find out the variables which influence the regression model the most, that is the variables that best explain the variance of the dependent model. Also the model needs to be simple. The lesser the number the predictor variables, the better is the model. If a predictor variable adds only marginal variance to the dependent variable, then it is best to ignore that predictor (the more the variables, the tougher it is to collect data and explain to the management).
So how does one select the appropriate model. How does one go about analysing what variables are important.
There are various procedures available to answer these questions. we analyse those procedure below.
All possible regression :

As the name suggests in this procedure we analyse all possible models. for example if there are 5 variables, we examine models by taking one variable at a time, two variables at a time and so on. for k variables this yields 2^k-1 models. For 5 variables this yields 31 models. The advantage of this model is that the researcher can examine all relationships. The disadvantage is that it is too tedious and may not be feasible.

Stepwise regression :
This is the most popular method. It begins with a single variable and adds or deletes variable in each step. In the first step of the procedure all variables are analysed one at a time. The variable yielding the highest value of t is chosen.
In the second step the selected variable is combined with all remaining variables to yield as many models (k variables yield k-1 models in this step). The t value of the added variable is calculated and the variable having the highest t value is selected. However, if while adding another variable the t value of the original variable becomes insignificant then the variable is discarded.
The model continues till all independent variables have been identified.

Forward Selection :

This method is same as stepwise regression, but a variable once added is never removed.

Backward elimination :

This model begins with all predictors. The significance of all predictors is calculated and the one with the least significance is discarded. This process is continued till all variables left are significant.

multicollinearity :

If more than two variables are correlated, we have multicollinearity. the t value obtained may be incorrect since other variables may have influenced it. One way to take care of collinearity is to create a matrix that shows the correlation of one variable with another and chose the variable that has maximum correlation with others and drop the others. Stepwise regression is other method that can eliminate correlated variables.

T-Test, F-Test and P-value

2009-08-31T23:34:00.000-07:00

Two very important tests in statistical analysis are the t-test and the f-test. However, some confusion may arise for a new user as to the difference between the two tests. In this post I will try and present the difference between the two tests and when each should be used.

But before we understand the test, let’s understand what a p-value is. The p-value is the probability of getting results as extreme as the observed values under null hypothesis. For example, after performing a t-test you find out that the p-value is 0.06. Does this mean that the null hypothesis can be rejected? Suppose you decide that due to random errors, even if the null hypothesis is true, 5 out of 100 experiments would inevitable fail the null hypothesis and you can live with that. However, in our experiment the p-value is 0.06, which says that ‘by looking at your data, I think that 6 out of 100 times you would tend to reject the null hypothesis even if it is true’. You are happy with that statement, you were happy if it fails five times, but the data says that six failures are expected to occur due to chance.
For practical purposes, reject a null hypothesis if the p-value is less than alpha (generally 5% or 0.05)

T-test: The t-test is used to find out if the means between two populations is significantly different.
Characteristics of the test are;
1) The test statistic follows a t distribution under null hypothesis.
2) The test can be used to find if the mean of a population is different from a known mean.
3) The test can be used to find out if the means of two samples are significantly different. Note that the two populations need to follow the normal distribution. Also the variances of the two populations need to be equal if sample size is less than 30.
4) The test can be used to find out if the difference between values of a single variable measured at different times is zero.
5) The test can be used to find out if the regression line has a slope different from zero.
6) Paired vs un-paired: A test of type 3 is a paired test. The samples are independent. A test of type 4 is an unpaired test. In many cases of unpaired data, it is the same variable undergoing repeated observations. For example, measurements taken before and after an experiment.
7) The questions that need to be answered before using the t-test are: is it a single population or multiple populations, are the sample sizes equal, are the variances equal, and is it a paired or un-paired test.

F-test: F-test is used to find out if the variances between the two populations are significantly different.
Characteristics of an F-test are:
1) The test statistic has an F distribution under null hypothesis. I.e. the ratio of variances follows an F distribution.
2) F-test can be used to find out if the means of multiple populations having same standard deviation differ significantly from each other. (ANOVA)
3) F-test can be used to find out if the data fits into a regression model obtained using least square analysis. Here we compare is the mean square due to error is significantly different from the mean square due to regression.
4) The test can be a two tailed test or a one tailed test.
5) F-test for ANOVA for two variables is equivalent to performing the t-test. Also the relation is given by F=t squared.
6) For ANOVA the F test is the measure of ratio of variance between groups and variance with the sample groups.

Statistics for Business Intelligence - Multiple Regression

2009-08-25T23:42:00.000-07:00

Simple regression deals with predicting a dependent variable using an independent variable using a linear regression equation. In multiple regression analysis there are more than one independent variables or at least one non linear independent variable.

Multiple Regression Model :
The multiple regression model is of the form.

where y = the value of the dependent variable.
beta0 = the regression constant.
beta1 = partial regression coefficient for independent variable 1.
beta2 = partial regression coefficient for independent variable 2.
k = the number of independent variables.

The partial regression coefficient of the independent variable beta is the change in the dependent variable by a unit change in the independent variable.

Multiple regression model with two independent variables :
In this case we obtain a regression plane that fits the data. The multiple regression equation is given by;

This equations are obtained by minimizing the sum of square of error.

Testing the Multiple Regression Model:
The overall model can be tested by using the hypothesis of the form.
Null Hypothesis: beta1=beta2=betak= is equal to 0.
Alternate Hypothesis: at least one of the beta is not equal to 0.
This test can be used to establish that the data does indeed have a relationship between the independent and the dependent variable(s).
A rejection of null hypothesis indicates that at least one of the independent variables predicts the dependent variable.
The F value test can be used to check the hypothesis.
The F value is given by:

where
MS=mean square;
SS=sum of squares;
df=degrees of freedom=N-k-1;
k=number of independent variables.
N=number of observations.

A significance test can also be undertaken for each regression coefficient to validate whether each of them is significant. A t-test can be used for this purpose.

Residuals:
The residual can be calculated by solving the multiple regression equation and obtaining the dependent variable. The difference between the value obtained from the calculation and the value obtained from observation is the residual value. A plot of residuals can be used to identify the fit of the plot and can also be used to identify outliers.

Standard Error Of Estimation:
The sum of residuals is equal to zero if rounding errors are not considered. Therefore the sum of squares of error can be used to find the error in estimate.
the sum of squares of error or SSE is given by

The standard error of estimate (standard deviation of error for the regression model) can be estimated using:

The assumption in regression is that the errors follow a normal distribution. The standard error of estimate can be used to verify this assumption. (we know that for a normal distribution 68% of values fall with one standard deviation of the mean and 95% fall within two standard deviations.)

Coefficient of multiple Determination (R squared) :
The coefficient of multiple regression accounts for the proportion of the variation of the dependent variable y by the independent variable. a value of 0 indicates no relationship and a value 0f 1 indicates perfect relationship. The coefficient is given by:

SSR = sum of squares of regression, SSE = sum of squares of error and SSyy is the sum of squares total. These values can be obtained by ANOVA analysis.

Adjusted R square :
As variables are added to the regression model the R squared value keeps on increasing. However, sometimes even if the new variable added does not have a significant effect on the y variable, the R squared value increases. To take care of this effect an adjusted R squared value is used. The value is given by:

To summarize, to check the results of multiple regression, the following needs to be checked.
1. the regression model equation.
2. The ANOVA table and the F value of the overall model.
3. SSE values.
4. standard error of estimate.
5. coefficient of multiple determination.
6. adjusted coefficient of multiple determination.

Statistics for Business Intelligence - Simple Regression

2009-08-25T03:12:00.000-07:00

Introduction:

In many situations, a relationship between two variables needs to be analysed. For example, a car manufacturer would like to know whether the number of cars bought in the city is related to the average household income in the city, or a sales manager would like to know if the sales revenue is dependent on the discount percentage offered by the company. Such an analysis can be done by Simple Regression. Simple regression involves building a model that can determine one variable given another variable. The known variable is called the independent variable and the variable to be determined is called dependent variable. To do the analysis, the researcher aims to fit the data in a straight line form. i.e. the data is fit into a line of the form y=mx+c; where y is the dependent variable and x is the independent variable. m is slope of line and c is the y intercept. This is also the deterministic model since it gives the exact value of y for a given value of x. Statisticians also use probabilistic models where y can be determined with a given error range.
y=mx+c+e
where e is the error in determination.
For a sample the regression line is given by

bo and b1 can be obtained for the sample using least square analysis.

Residual Analysis :
Once a regression line is determined, the researcher needs to validate whether the line is a good fit for the data.

To do so, he can use historical information and try to fit this information in the regression line.
For each historical point a residual value is obtained. This is the difference between actual historical value and the value obtained from the regression line. The sum of the squares of this residual values is minimized to find the least squares line. The sum of the residuals is zero for the sample data if there are no rounding errors. A point with high residual value may be an outlier. The residual analysis plot can be used to gauge how effective the regression model is. The residual plot is a plot of the residual value against the independent variable. It checks the following assumptions of simple regression analysis
1) The model is linear
2) The error terms have constant variance, are independent and are normally distributed.
The residual plot can be visually analysed to verify the above assumptions.

Standard Error of estimate:
The standard error of estimate can be used to determine the error that arises out of simple regression. It is the standard deviation of the error terms. the standard error of estimate is given by

Where SSE can be estimated by either of these methods.

The standard error estimate, being the standard deviation value can be used to verify whether the residues are normally distributed. For normal distribution values 68% of values fall within one standard deviation and 95% of values would fall within two standard deviation.

Coefficient of determination :
The coefficient of determination is called r2 (r squared). It is the proportion of variation of the dependent variable(y) explained by the independent variable(x). The value can range from 0 to 1. A value of 0 implies no variation of y w.r.t x and a value of 1 implies all variation in y can be explained by x. From a business point of view, a researcher may chose the value of coefficient to be good or bad depending on the context. A high value is sought by those seeking exact prediction.

The coefficient can be calculated as follows:

the sum of squares of error can be broken into two parts i.e. variance measured by sum of squares of regression(SSR) and sum of squares of error (SSE). The coefficient of determination is given as the proportion of variation explained by regression.

Hypothesis testing for the slope of the regression model:
A test to determine whether the regression model is applicable (significant) is to test if the slope of the regression line is significant. The way to do this is to determine if the population mean is different from 0. (If it is different from 0, the variables are related and hence regression model can be applied). A t-test on the slope can be used to determine a null hypothesis of the form .
Null hypothesis - the hypothesized slope is zero.
Alternate hypothesis- the hypothesized slope is greater than or less than zero.
note that this is a two tailed test.
the t test is given by

where beta1=the hypothesized slopes; df = n-2

Confidence interval for the determination of y : y can be determined from x using the regression model. However a confidence interval can be used to determine the range within which the y value falls for that confidence level or the mean of the y value for that confidence level.
The prediction interval for y can be given by:

Note the regression line is obtained from the sample data. This regression line may be valid only for the range of the sample data. Although the regression line is sometimes extrapolated, the results may be incorrect. Hence care should be taken while using the regression model to predict values.

Statistics for Buiness Intelligence - Chi Square Tests

2009-08-23T05:15:00.000-07:00

Chi square tests, namely chi-square goodness of fit test and chi-square test of independence, are used to analyse data that are a frequency distribution of discrete variables. Consider for example, a wine cellar that has four different categories of wine, a frequency distribution of the four varieties of wine can be analysed by such tests.
Chi-Square Goodness of Fit test :
This test is used on experiments that are an extension of binomial distribution, i.e. on multinomial distribution. This distribution has more than two variables or outcomes in an experiment. The chi square compares the observed distribution of outcomes to the expected distribution of outcomes. In other words it shows how well does the observed distribution fit the expected distribution. For example a retail store chain may claim that their customers follow a certain satisfaction distribution as shown below
Satisfied - 80%
Somewhat satisfied - 10%
Not Satisfied - 10%
The results of a random survey undertaken by a particular store manager can be used to test whether the distribution applies to her store as well.

The formula used for the chi-square test is

The chi square distribution is a series of graphs with different values of degrees of freedom.
This is how the test works - Use the chi-square table and the degree of freedom and a suitable alpha value to find the value of chi-square. Use the above formula to calculate the chi-square value for the experiment. If the experimental value is greater than the value of the table then the null hypothesis can be rejected. Note that this is a single tailed test since we are interested in finding out if the observed distribution follows the assumed distribution or not.

Chi-square test of independence : This test can be used to check the distribution of frequencies when there are two variables having different categories. For example if a tyre company wants of find out if the size of tyre used is dependent of the make of the tyre. The response can be obtained on a two way table, for example the test results can be captured with tyre size on the horizontal and tyre make on the vertical. If we have two sizes of tyres and two makes of tyres, we have a 2X2 matrix of results. Each cell containing the frequency for the make-size combination. In a way the chi-square test of independence tells whether the two variables are dependent.
The null hypothesis of the test is that the variables are dependent.
If the variables are dependent the expected frequency of occurrence can be calculated from the experiment using the formula

Using this expected values and the observed values, the chi square value can be calculated as

statistics for business intelligence - ANOVA

2009-08-12T09:31:00.000-07:00

Design of experiments : An experimental design is used to test a hypothesis by modifying one or more variables. The variables may be dependent or independent. Independent variables may be a treatment variable (can be modified) or classification variable (characteristic of the experimental factors, present prior to the experiment and is not modified during the experiment).
ANOVA - Analysis of variance - This is a methodology in which the researcher studies the variance in a dependent variables due to the other independent variables. This technique attempts to calculate the contribution of variance by each independent variable. Various types of experiments are available; we discuss some of those.

One way Anova - Completely randomized design :
In this experiment, there is only one independent variable. The variable contains more than one classification level. If only two classification levels are present, the design is same as comparing the statistic of two populations. An example of using a one way Anova is a comparison of car music systems where the sound quality is compared. The kinds of music systems are the classifications of the independent variable and the dependent variable is the sound quality. ( can be quantified using a suitable measurement). In general, if k samples are analysed the null hypothesis for one way ANOVA states that the mean of all the samples is same. If any one of the mean is different from the others, the hypothesis is said to be rejected.
ANOVA basically compares the relative size of the treatment variables (independent variables) variation and the error variation (accross treatment groups and within treatment groups). The one way ANOVA can be calculated by ;

Where SS is the sum of squares and MS is the mean square. SSC is the sum of square columns which gives the sum of squares between treatments. SSE is the sum of squares of error. The F value is the ration of treatment variance to the error variance.

The t test can be considered a special case of two way Anova where there are only two treatment variables.

Once the one way ANOVA using F test establishes that a significant difference exist in different treatments, multiple comparisions can be done to find out which pairs are different. t-tests can be used but the error adds up in that case. Other techniques have been developed for multiple comparisons.
Tukey's Honestly Significance Test (HSD) Test - This method is a multiple comparison test where the sample are of equal sizes. The HSD value is given by

The difference in means of pairs are compared to this HSD value and the pair that has the mean difference greater than the HSD value is said to different at that alpha level.

Tukey-Cramer procedure - This method is used for multiple comparisons when the samples are of unequal sizes.
The formula is

Randomized block design : This is the second method of analysis which considers another variable in addition to the treatment variable. This another variable is called the confounding variable. These are variables which are not controlled during the experiment, but have an effect on the outcome of the experiment.
The randomized block design test adds an additional variable that the experimenter cannot control. this variable is also called the blocking variable. In this experiment SSE is broken into SSE and SSR (sum of squares for the block variable).
The formula is

TWO WAY ANOVA : Factorial design - In this procedure two or more variables are explored simultaneously. Every level of each treatment is studied under the condition of every level of all other treatments. For factorial design of two variables, a two way ANOVA can be used. Note that the randomized block design is different from this in that it cannot measure the interaction between the two variables. The null hypothesis for two way anova is
i. Row effects - The row means are all equal.
ii. column effects - the column means are all equal.
iii. the interaction effects are zero.
The two way anova can be calculated by

Interaction effects show that the variation in the column values are dependent on which row is selected. whenever the interaction values are significant the row and column effects should not be considered.

note: The method of analysis for ANOVA is as follows - compare the F values calculated from the formula with the F values obtained from the table using the degrees of freedom specified in the formula and a suitable alpha value. If the F value calculated from the experiment is greater than the F value from the table then the null hypothesis for the F value experiment is said to be rejected.

Links:
1) http://www.southampton.ac.uk/~cpd/anovas/datasets/index.htm
2) http://faculty.chass.ncsu.edu/garson/PA765/anova.htm

statistics for Business intelligence - Inference for 2 populations

2009-08-11T01:16:00.000-07:00

Here we consider comparing the statistic from two samples. we would compare the mean, population proportion and variance. The tests used would be the z test and the t- test. Some of the experiments would use independent samples. (members in both the samples are independent of each other)
Difference in two means using z-statistic : according to the central limit theorem, the difference in two sample means is normally distributed for large sample sizes. The z formula for difference in two sample means for large and independent samples is given by

if the populations are normally distributed and if population variances are not known then the samples variances can be used if sample size is large.

Hypothesis testing can be used in practical scenarios to find out if the mean of a sample differs from the mean of another sample. This would be a two tailed test.

The confidence interval for the difference in the mean is given by

This confidence interval gives a (1-aplha)% confidence level.

Difference in two means - t formula
This methodology can by used if the sample size is small, the samples are independent and the population variance is not known but is assumed to be equal. However, the measurement being studied should be normally distributed.
The t value is given by

For cases where the population variances of the two population are not equal the unpooled formula can be used.

The confidence interval for the difference between the mean of two population for small independent samples and when population variances are unknown is

Inferences for related population : Sometimes sample are taken from two populations that are related. For example, samples taken for calculating illiteracy level before a literacy program is implemented and after it is implemented. Here the population remains essentially same however the measurement that is being taken has changed.
This test is called a matched pair test or t-test for related measures or correlated t test. The t-formula is given by

where df=n-1, n = number of pairs, d = sample difference in pairs, D = mean population difference Sd =SD of sample difference and d(bar) = mean sample difference.

The confidence interval is given by

Comparison of proportions for two populations:
E.g. comparing the market share of a product for two different markets. The formula is given by

The confidence interval for the difference is given by

Two population variances:
The ratio of sample of two variances is called the F value and is the ratio of square of sample variance of sample1 to square of sample variance of sample 2. A distribution for various values of s1 and s2 is called an F distribution. This distribution has degrees of freedom for the numerator and the denominator. Note that the two populations should be normally distributed.

Statistics for Business Intelligence - Hypothesis testing

2009-08-10T21:33:00.000-07:00

Hypothesis is defined in dictionary.com as 'a proposition assumed as a premise in an argument'. This post explores the various kinds of hypothesis in statistics and methods to test them.

Research hypothesis : A statement that is considered the outcome of an experiment or test, before the experiment is undertaken.
Statistical hypothesis : This is used to prove or disprove the research hypothesis by providing more measurable or concrete hypothesis statement. for example, a research hypothesis could be that the stock market index reflects the state of monsoon in the country. A statistical hypothesis might look at the values of the index with the percentage increase or decrease in rainfall during the year compared to previous years.
The statistical hypothesis has two parts. The null hypothesis aims to prove that the old standard is correct and the current situation is in control. The alternate hpothesis aims to prove that the new theory is true, new standards are needed or the system is out of control. The null hypothesis is generally something that the experiment would reject to prove the alternative hypothesis. The aim of the experiment is to find cause to reject or not reject the null hypothesis. The null hypothesis is generally represented as H0 (H subscript 0) and the alternative hypothesis as Ha(H subscript a). For example, the number of literate people in the country is 40% and the government wants to prove that because of its literacy schemes the number has increased from 40. The null hypothesis is
H0: p = 40
The alternate hypothesis is Ha: P >40.
Note that in this case p is less than weight =" 40."

Type 1 error : A type 1 error is commited by rejecting a null hypothesis when it is true. In other words the null hypothesis is true but the experiment prompts the researcher to reject it. The probability of committing a type 1 error is called alpha or level of significance. alpha is the area of the curve under the rejection region lying outside the critical values.

Type 2 error: A type 2 error is committed when a business researcher failes to reject a false null hypothesis. i.e. the null hypothesis is actually false but the experiment prompts the researcher to accept it. The probability of committing a type 2 error is beta. the value of beta varies with the value of probable alternatives and each alternative may have a beta value. note that alpha and beta are inversely proportional. Power = 1- beta is the probability of rejecting a null hypothesis when the hypothesis is wrong. It represents a correct decision.

Using z statistic to test the hypothesis about a population mean :
If the sample size is large i.e. n>= 30 for any population or if x is normally distributed for small population, the z score is given by

The procedure is : use the mean from the null hypothesis and the sigma value to come up with the z score. assume a value of alpha (generally .05) and find out the value of z at the critical value (since alpha is known the area under nonrejected region can be found out. use the table of area under normal distribution and z to find out the z value) if the z from the experiment falls within the z values at critical point the hypothesis is not rejected.
The sample standard deviation can be used if the population SD is not known and n >= 30. If the population is finite i.e. the sample size is a substantial amount of the population then uses the finite correction factor. i.e. if N is the population and n is the sample size, use this formula.

Another method is to convert the z scores at the critical point to the actual critical values and use the critical values to determine the null hypothesis.

The t-statistic (as described in earlier post) can also be used instead of the z statistic of the sample size is small and population is normally distributed.

Hypothesis about the proportion can be tested using the formula

Hypothesis about the variance can be tested using the chi-square method. note again that the chi square method is not robust with respect to normal distribution. i.e. if the distribution is not normal, the chi square method should not be used.

Statistics for Business Intelligence - Inferential Statistics 1

2009-08-09T23:46:00.000-07:00

Inferential statistics is the term given to the branch of statistics that uses the information from the sample to infer the information about the population. For example, given a sample mean , the population mean (also called a parameter) can be determined using inferential statistics.

Estimating population mean - Let us first look at estimating the population mean using the z score.
point estimate - If the population mean is assigned the value of the sample mean, then the estimate is called a point estimate. The point estimate may not be accurate, and different samples may have different point estimates. Also the effectiveness may be dependent on how representative is the sample of the population.
Interval estimate - This gives a confidence interval within which the population parameter is expected to lie. Consider the distribution of z scores below.

alpha is the area under the normal distribution curve and is the area outside the confidence interval. The 100(1-alpha)% confidence interval is given by

x (bar) is the mean of the sample.

mu is the mean of the population.

Z is the z score at alpha/2.

sigma is the population standard deviation.

To calculate the confidence interval for a particular value of alpha, use the z table to arrive at the probability values.
note that sigma is the standard deviation of the population, therefore to calculate confidence interval using this formula, the SD of the population is required. This could be available from previous studies or some other means.

Confidence Interval if population SD is not known : In most cases, the population SD is not known. In these cases the thumb rule is that the sample SD is a good estimation of population SD if n>= 30. To calculate the confidence interval use s (SD of sample) instead of sigma(SD of population) in the equation above.

So far we have seen methods to estimate the population mean and the confidence intervals using the z statistic, let us know look at methods to determine the population mean using t-statistics.
The t-tests can be used to calculate the population mean from the sample mean if the sample size is small. They are also referred to as student's t tests. The t distribution is given by

The difference between t-test and the z formula is that t-test uses t tables instead of z tables. The t distribution approaches the standard normal curve for large values of n. To find out the t value, the degrees of freedom is required. The degrees of freedom or df is given by n-1. i.e. the df is one less than the number of members in the sample. using the t value and df, the probability value can be obtained from the t table.

similar to calculating the confidence interval using the z table, t-table can also be used to calculate the confidence interval. It is given by

Population proportion : The proportion in a population can be determined given the proportion in a sample using the formula

Estimating Population variance :
The population variance can be estimated from the sample variance using the chi-square distribution.

s is the sample variation and sigma is the population variance. The degrees of freedom are given by n-1.the chi-square distribution is not symmetrical and the shape varies with the degrees of freedom.
The confidence interval is given by

Sample Size : The size of the sample to be used for survey can be calculated if the error in estimation E =(sample mean - population mean) is known. The size is given by

The sample size to estimate the proportion p is given by

Statistics for Business Intelligence - Sampling

2009-08-05T22:15:00.000-07:00

It is necessary to understand sampling techniques before data for a sample is gathered for analysis. Some of the terms that are important are
Population - This is the complete set under consideration. For example a survey of food choices for a country might consider all citizens of the country.
Frame - frame is the population where the survey is targeted to. For example, a survey of sports interests among school children considers all schools. A frame considers a list of population such as a school list.

Random Sampling or probabilistic sampling- In this kind of sampling each unit of the population has an equal chance of being selected in the sample.
Non Random Sampling or non probabilistic sampling - In this sampling method units have different probability of being selected for sampling, i.e. the sampling is biased for the selection of the unit.

Types of Random Sampling
Simple Random Sampling - Each unit is assigned a number, and a table of random numbers is used to select the unit. for example if the population has 30 members, each member is assigned a number. Random table is used to generate a number between 1 and 30 and the member corresponding to the random number generated is selected.

Stratified Random Sampling - The population is divided into different strata. These strata are non overlapping. Random methods are used to select members from each stratum. This method helps in selecting a sample that is representative of the population and prevents the researcher from collecting units from a subgroup of the population. The strata can be formed logically, for example in the survey of sports choices, the population can be divided into girls and boys.
In proportionate stratified random sampling the number of units selected from each strata is proportional to the total number of members in the strata. So if a school has 70 boys and 30 girls, and a sample of 10 is required, then 7 boys and 3 girls would be selected.

Systematic Sampling - In this method every kth element of the population is selected. This method is easy to implement but fails if the periodicity of the population coincides with k.

Cluster or Area sampling - In this method the population is broken down into logical clusters or areas. For example a state population can be broken into cities for the purpose of sampling. This technique is mostly used for its convenience. In contrast to stratified sampling, here the internal population is heterogeneous. In stratified sampling each stratum is homogeneous in terms of property influencing the survey.

Nonrandom sampling - Nonrandom sampling is generally not advised when inferential techniques need to be applied . Also the error of sampling calculated may be incorrect for nonrandom sampling.

Convenience Sampling - elements for the sample are selected as per the convenience of the surveyor. The sample may contain less variation than the population. However, the cost for sampling may be reduced since the samples are taken from a convenient location.

Judgement sampling - The elements for sampling are chosen by the judgement of the researcher. Studies show that random sampling gives a better population mean than judgement sampling. This kind of sampling also introduces biases of the researcher.

Quota sampling - Quota sampling divides the population into subgroups or strata as in stratified sampling however, members are selected from the strata using non random techniques. The number of members to be selected from the strata are proportional to the population of the subgroup and is called a quota.

snowball sampling - In this kind of sampling members are selected based on the referral from other members. The advantage is that members for survey can be identified easily. however the technique is non random.

sample mean distribution- A population with a known distribution is chosen. Samples are taken from this population and mean calculated for the samples. The probability distribution of this mean is governed by what is called the Central limit theorem. It is a powerful theorem and states that if samples of size n are taken randomly from a population having a mean of mu and a standard deviation of sigma then the sample means x are normally distributed for large sample sizes (typically n>=30) regardless of the shape of the distribution of the population. However, if the population is normally distributed, the sample means are also normally distributed for all values of n. mathematically mean of the sample means is equal to population mean and SD of sample means is the SD of the population divided by square root of population size.

The power of the theorem lies in the fact that even if the population is not normally distributed, the probability for a particular sample mean can be calculated from a sample of large size since the sample mean distribution would always be normal.

Statistics for Business Intelligence - Distribution

2009-08-04T01:32:00.000-07:00

Discrete variables - Discrete variable take a set of values. for example, type of card drawn from a pack of cards can take any of the four values: hearts, spades, clubs or diamonds.
Continuous values - These can take any values within a specified range. For example height of students in a class can take any value from say 4 feet to 6 feet.

Distribution of discrete variables - lets us first consider the distribution of discrete variables.
Binomial distribution
This is the distribution of results in an experiment where the result can either be a success or a failure. For example a coin toss can be either heads or tails. let p be the probability of success, q be the probability of failure(q=1-p) and n be the number of trials. The probability of x successes is given by

The mean of a binomial distribution is given by (np).
The standard deviation is given by SD=sqrt(npq)
A graph can be plotted by plotting P(x) against x.

Poisson Distribution
This distribution describes the occurrences of rare events. It gives the probability of x occurrences in a specified time interval given that there are lambda expected occurrences in the same time period. for example if it is known that a machine produces 10 defective items in 30 mins, what is the probability that it will produce 4 defective items. The distribution is given by

Binomial problems with large sample sizes and small values of p can be approximated by poisson distribution. Heuristics suggest that if n > 20 and n.p <= 7, then the poisson distribution can be used to approximate binomial distribution.

Distribution for continuous variables
continuous variables take all values within an interval. To calculate the probability between any two points, find the area under the curve. The total area under the curve for this kinds of distribution is 1. To find the probability at a particular point, the thumb rule is to add and subtract a small quantity and take the area under the two values obtained. i.e. probability at x can be found out by finding area under the curve between the points x+dx and x-dx where dx is around half a unit.

Uniform Distribution - This distribution has a constant value throughout. This is also referred to as rectangular distribution.

The distribution is given by

Image and equation Source - mathworld.wolfram.com.

Normal Distribution - This is probably the most frequently encountered distribution and also most widely used. Example of a normal distribution is the error rate of a machining equipment. Physicist refer to it as Gaussian distribution and it is also popularly referred to as a 'bell curve'.

Image source - Wikepedia
The probability density function is given by

Equation source - mathworld.wolfram.com.
The Normal curve depends on the mean and standard deviation. There would be different curves for each combination of mean and standard deviation, therefore a standardized normal distribution curve is used. This curve is obtained by converting the values of x to its corresponding z score. z score is calculated as (x-mean)/SD.
The z distribution has a mean of 0 and standard deviation of 1. To find the probability between two values in the normal curve, find the area under the curve between the two values. To do so, convert the two values to their corresponding z scores and use the standard z score table to arrive at the associated probability. The difference between the probability of the higher value and the lower values gives the probability for the interval.

The normal distribution can be used as an approximation to the binomial distribution. To do so calculate the mean and SD from n and p using mean = n.p and sd = sqrt(n.p.q). A thumb rule is that the approximation can be applied if mean+3SD lies between 0 and n.

Exponential Distribution : This is similar to Poisson distribution but useful for continuous values. It gives the probability distribution for times between random occurrences. The x values range from 0 to infinity and the curve steadily decreases as x increases.

Statistics for Business Intelligence - Shape

2009-07-30T23:51:00.000-07:00

In this post i will discuss the measures of shape used for statistical analysis, specifically skewness and kurtosis. I will also discuss the box and whisker plot.

Skewness - A normal distribution is a bell curve that is perfectly symmetric. perfect symmetry implies that the values are distributed equally around the center. a graph is said to the skewed if it is not symmetrical. The graph may be either skewed towards the right (negatively skewed) or towards the left (positively skewed)

Image Source - Wikepedia
A normal distribution that has the mean, median and mode at the center of the distribution has no skewness.
skewness can be quantified by a measure known as Pearsonian coefficient of skewness.

If the coefficient is positive the plot is positively skewed. Larger the value, greater is the skewness.

Kurtosis :
Kurtosis defines how pointed (tall and thin) the plot is. If the plot if large and thin then it is referred to as leptokurtic. If it is flat and spread out it is called platykurtic. plots in between are called mesokurtic.

Image Source : http://www.uwsp.edu/

Box and Whisker plots : These plots are widely used to understand the data distribution. To understand these plots we need to understand quartile. A quartile breaks the data into four parts. i.e. there are three quartiles in the complete data range. Note that the data needs to be in ascending order to calculate the quartile. The first quartile contains the first 25 percentile of data. Here's an example of the box and whisker plot

The characteristics of the plot are
1) The median or Q2 is the center of the graph.
2) the left end of the box is Q1 and the right is Q3. i.e. 50% of values are inside the box.
3) The line segment outside the box is called a whisker. A length of the whisker is 1.5 IQR (Interquartile range = Q3-Q1). This is also called the inner fence. If data is present outside this inner fence then an outer fence = 3 IQR can be drawn.
4) Values outside the inner fence but inside the outer fence are called mild outliers whereas data outside the outer fence are called extreme outliers.
5) If the median is to the right of the box then the middle 50% of data is skewed to the left.
6) If the longest whisker is to the right of the box then the outer data are skewed to the right.

Statistics for Business Intelligence - Descriptive Statistics

2009-07-29T01:27:00.001-07:00

In this post we look at descriptive statistics as a means for data exploration. Descriptive analysis refers to a group of methods that gives summary information about the data. For example consider the sales figures for a retail clothes outlet. An important figure would be the average of sales for a particular day of the week in a year.

Analysis of a single variable or Univariate analysis -
In most cases we need summary figures for a single variable, say height of students in a class or the maximum selling product during a sale etc. The methods in this analysis take as input various values for a single variable and provides summary statistics for it.

Types of summary statistics - There are mainly three kinds of summary statistics involved in univariate descriptive analysis.

1) Mean - This simly gives the average for all the values of the variable under consideration. for example if the marks scored by five students in a quiz are 6,8,9,5,8 then the mean is given by (sum of values)/(num of values)
or sum = (6+8+9+5+8)/5 = 7.2
The disadvantage of mean value is that an outlier can distort the mean to a very large extent.

2) Median - Median gives the central value in a group of values. In other words around half of the values are greater than the median and the other half are less than the median. consider the same number sequence as above.
6,8,9,5,8
arrange the sequence in ascending order
5,6,8,8,9
The central value is 8 and hence the median is 8. Median gives a number around which the values are distributed. If the series has an even number of observations then divide the middle two numbers by two to arrive at the median.

3) Mode - The mode is the value that is repeated the most number of times. In our series the mode is 8 since it is repeated twice.

The three summary values described above are labeled as measures of central tendency. In a normal distribution the values would be equal.

Another topic in Descriptive Statistics is Distribution. Consider a school that gives a grade to each student. A single variable distribution gives the number of students that have obtained Grade A for each subject.

The same statistic can also be represented graphically

There are cases where the values of the variables are not discreet. consider a distribution of height for the students in the class. A distribution of each value of height vs number of students would probably give only one or two students for each height value. A better approach here would be to use a range of values instead of absolute values. In case of height use a distribution of this type:

Dispersion - Dispersion gives an idea of how the values are distributed around the central value. The measures of dispersion are range, mean absolute deviate, standard deviation and variance.

Range is the difference between the maximum and minimum value in a distribution. for example in the series 5,6,8,8,9 the range is 9-5 = 4.

Mean absolute deviation is an average of the absolute deviation of the numbers around the mean. The formula for mean absolute deviation is

Variance - variance is the average of the squared deviations.

Standard Deviation - It is the square root of variance. It has same units as the data used in analysis. Two methods are used to understand the significance of standard deviation. The first method is widely used and is applicable for all normal distributions (distributions that are symmetrical about the center and have a bell shaped curve). The method states that for normal distributions mean + one standard deviation(sigma) is equal to 68% i.e. 68% of the data are between mean+sigma and mean-sigma. 95% is between mean +- two sigma and 99.7% of the data are between mean+- 3sigma.
The second method is called ChebyChev's theorem. It states that at least
1-1/square(k) of the values fall within +-k deviations from the mean. k > 1. for example 1-1/4 = 75% of values fall within +-2 deviations from the mean. The advantage of this theorem is that it can be applied to all distributions and not only normal distributions.

Note that the formula for variance and standard deviation described above are used for the population. To estimate values for a sample use a divisor of n-1 instead of n.

The last term for this section is coefficient of variation - it is the ratio of standard deviation to the mean expressed as percentage.
COV = standard deviation * 100/ mean

Statistics for Business Intelligence - Introduction

2009-07-28T23:46:00.000-07:00

An understanding of Statistics is imperative before delving into the tools and methods of business intelligence and analysis. We will spend some time on understanding the basics of Statistics for data analysis and in subsequent posts try to give more detailed explanation of the various methods involved.
Statistics for data analysis can be broadly divided into descriptive statistics and inferential statistics.

Descriptive statistics is a group of methods or tools that statisticians use to understand the meaning of data. These methods provide a summary or gist of data and enable the user to comprehend large volumes of data. For example, the average salary for a class of MBA pass out or the median of weight in a group of people are examples of descriptive statistics.

Inferential statistics, on the other hand, provides tools that statisticians use to infer information about the population given the information about the sample of the population. In other words it tries to use the available data to understand how the population would look like. Consider for example a survey that finds out popularity of a TV show. A group of people are selected at random and in a way that represents the population that the survey wants to cover (say a city). Descriptive statistics would give the summary of popularity within the people chosen for the survey but to extrapolate this findings for the complete population of the city, we need inferential statistics.

Business Analytics Road map

2009-07-21T21:46:00.000-07:00

Any company wishing to reap the benefits of business analytics needs to understand the path that it has to take to reach the goal of optimization.

Stage 1 : The first stage is reporting and data analysis. This essentially means that you gather the data from your source systems and run reporting tools on it. Various kinds of reports can be generated depending on the requirement. Data analysis implies using statistical methods to find meaning in numerical data. statistical tools can be very powerful in presenting highly summarized data and are used extensively when huge amount of data is present.

Stage 2: A company that has the basic reporting tools can then move on to OLAP based tools. OLAP stands for Online Analytical processing. Olap tools allow you to build multidimensional data. They also provide with drill down facilities to allow the user to drill down on one of the dimensions. for example, consider an FMCG company that has a location hierarchy(country-> state->city etc) as well as an item hierarchy (Electronics->television etc). people may want various kinds of numbers such as total electronics sales in a country or total television sales in a city etc. All such kinds of data can be made available from a single OLAP system.

Stage 3: reporting and OLAP system gives the user a view of the system, but the user may also want systems that may help him make sense of the information. We need data mining tools that help the user in making decisions. These include data analysis covered in step 1 but here we make use of advanced statistical concepts (inferential statistics in addition to descriptive statistics)

Stage 4: The three stages covered so far are part of what is generally referred to as business intelligence. As a company matures it looks for more capabilities in its decision support systems. It is at this point that the company starts investing into business analytics. The business intelligence answers questions such as : what does my sales records look like? or what has been my maximum selling product. The questions answered by Business analytics are : what would my demand look like within the next three months? how do i optimize my inventory so that my cost is minimum but the service level is achieved? should my supply chain be responsive or efficient?
The answer to this question needs tools such as forecasting and predictive analysis.

Stage 5: The last stage is where the company strives for optimization across its functions. It uses a holistic approach where all its functions such as finance, marketing, operations are optimized together to arrive at a decision.

Business Analytics - The need 2

2009-07-09T08:33:00.000-07:00

Information is required at all levels in the organization. Right from the CEO,CFO or CIO to a technician, everyone in the organization can benefit from access to information. The kind of decision that they make may differ and the granularity of information required may vary but the same system should be able to serve the needs of everyone in the organization. Let us understand how each person in the organization would use this system and what kind of information would they expect out of it.
Lets take the operational guy. We take the example of a network management person in a telecom company. When he sees a problem, what would help him is how a similar problem was solved in the past. If the system can understand the symptom of this problem and suggest possible solution based on past data then the problem could be solved much faster. At a minimum, the system should be able to give him a list of problems faced by a similar component and the solution provided to solve that problem. Such a system would decrease the system downtime and also increase customer satisfaction.

The next person we can consider is someone from middle management. We take an example of a regional sales head of an automobile company. His job role could be achieving sales target for his region. He would need real time information about the sales of each office in his region. He would also need to set targets for each region based on the demand. He would need the forecasted demand for each of the locations and based on this forecast he would launch an effective sales campaign.

However, The person who probably needs information the most is the CEO/CIO/CFO/CTO of the company. He does not care about the numbers at the last level but he does care about a summary into which he can drill down. It is very important the he get a holistic view of the organization but also get the ability to 'look into' the system at a very fine level if the need arises. The system should assist him in arriving at decisions. For example , questions such as starting a new product line or diversifying into a new area, requires not only an understanding of the new product but also the understanding of the capability of one's own company.

To summarize, access to information is necessary for everybody in the organization and an investment into a decision support or reporting system can be justified if implemented smartly. Next, we would look into the details of the implementation.

Business Analytics - The need

2009-07-08T08:36:00.000-07:00

In a series of post i will analyse a road map that a company can follow to implement business analytics. The final aim of business analytics is to allow a company to make informed decisions.

In this first article we will try and understand why a company should invest money in business analytics.

In this modern era, to make profits a company has to differentiate itself from its competitors. A company that can make decisions based on solid information can make better decisions compared to a company that does not have access to information. consider a company that makes and sells ready made garments. Such a company would set up a network of franchisee throughout the country. Its main marketing strategy to attract customers would be to announce a series of discounts on its product. But how does that company differentiate itself from five other companies that has the same strategy? How would access to information help it?

Lets take the first question. We need to find out what strategy would make the company and its product more attractive than others. How should the product mix be distributed. Which location or class of cities buys more jeans than trousers. Which part of the city sells more casual shirts than business shirts. If a company could make these decisions, then it could reduce the quantity of dead stock and bring in more customers. Access to information would help the companies to make this decision. Data from all its offices can be collated and a monthly report generated. This report would give the decision makers an idea of what each location needs.