Browse Prior Art Database

An Effective Method for Transforming Date and Time Data for Business Intelligence Analysis Disclosure Number: IPCOM000020136D
Original Publication Date: 2003-Oct-28
Included in the Prior Art Database: 2003-Oct-28
Document File: 3 page(s) / 144K

Publishing Venue



An effective method is disclosed for transforming e-Commerce data with date and time data types into the format which will be easily used by data mining, OLAP, and other e-Commerce data analysis. The method consists two major steps, calculating related numerical time and calculating categorized relative time. The method for data categorization associated with calculating categorized relative time is provided for an integer data type, but the method can be used individually to provide data categorization calculation for any other numerical data types. The categories of the data categorization can be easily defined in a definition table and the evaluation can be done by a scalar UDF.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 56% of the total text.

Page 1 of 3

  An Effective Method for Transforming Date and Time Data for Business Intelligence Analysis

1.1 General Algorithm

Let t be the database data with data type of date or timestamp. There exists a database function T (t), the relative numerical time can be defined as:

R (t) = T (t) - T (tref) (2-1)

Where: T is a database function which can convert the date or timestamp format in numerical format. tref is the reference data point.

The relative numerical time can be further cast into the categorized relative time as:

C (R) = Cnull if R is null

= f(R) otherwise (2-2)

Where: Ri (i = 1, 2, ......,n+1) is a set of preselected relative numeric time range, and (R1 < R2
< ...... < Rn+1).

Cnull is the special category value assigned to NULL case.

f(n) is a special function for treating the categories of the converted data. The categories can be assigned in ascending order or descending order. For the categories to be assigned with ascending order, the function has the form of:

f(R) = i i=1,2, ......,n

= n +1 (2-3)

For the categories to be assigned with descending order, the function has the form of:

f(R) = n + 2 - i i=1,2, ......,n

= 1 (2-4)

1.2 Database Implementation

To provide the database implementation of the categorization of the categorized relative time expressed in Eqn. (2-3) or (2-4), a database table is created. The table provides the information about the category ids of the categorized relative time, the minimum and the maximum range, and the description of this category. The database schema for the table can


Page 2 of 3

be expressed as:

Description of the RANGE_INFO table:

   Column Name Column

Column DescriptionType

category_id integer,

              The assigned category ID , Primary keyNOT NULL min_range integer, The minimum range of this category max_range integer, The maximum range of this category description varchar(64) The description of this categoyy

The DDL for this data schema can be expressed as:









WCAMNG is the sample schema used for this exampl...