Task Based UI and its Usage

In all the Applications a particular task requires performing several steps to complete it which leads to common issues. Some issues are as follows:

• Users may not be familiar with the sequence of steps

• Users may inadvertently skip a step

• Users often require additional training to complete the task.

Many clients prefer implementing a user interaction style that assists end users in completing such tasks.
The latest version Siebel 8.0 provides a feature called as Task Based UI, which enables us to use wizard-like interface( in this we can Use a wizard- like interface) to guide users through steps in a task. This helps in completing the tasks in (leads to complete some steps of a task in) a prescribed order. It supports forward and backward navigation through a sequence of views. In addition to this we can use the pause facility.
In TBUI one can implement the Branching conditions based on the users input. This can be achieved with the use of Transient BC (TBC). The mechanism behind TBC is that-data is collected and used during the execution of a task but not saved afterwards i.e. Transient data disappears when the task ends.
I worked upon a scenario i.e. Quote Generation which processes the Quotation, Opportunity and Account Information for each and every Quote. Following is the description
Requirement
The Process starts with the enquiry raised by the customer to the Customer Service Representative (CSR) for the same he/she will

• Create an Opportunity by adding a Customer Account.
• Create the Product for the same Opportunity from Administration – Products.
• Create an Quote for the Opportunity.
• Add the Price list for the Quote; this price should be available for the Line Items (product).
• Generate the Quote information along with the Opportunity and Account.

Finally that will be delivered to the customer (Account Holder) through mail.
Development

Task, Task Group & Task Pane View: I created a new “Task Group” & a “Task”. Then added that “Task” to the “Task Group”. Later added the “Task Group” to the “View Task Group” named “Task Pane View”.

Views & Applets

I used vanilla Applets such as – Opportunity Entry Applet, Product Form Applet, Quote List Applet, Recipient List Applet etc. Made new Objects by copying the above mentioned applets and then used them on the corresponding Task Views. Also I inactivated the fields in the applet which was not supposed to be displayed.

Configuring TBC & Auto Quote functionality:
There is no change in the BC’s. I used Siebel provided “Auto Quote” functionality for the Quote creation. Due to the requirement of giving a choice to add multiple recipients for the Correspondence I created a Transient Business Component.

Adhoc functionality: All the Adhoc functionality which I used here was vanilla provided.

Pulling Data from Quote to Recipient:The biggest challenge was to pull Quote fields in Recipient BC through indirect Joins. First I created Link between Quote and Correspondence then with the help of Link Specification I pulled the Quote Row Id in the Correspondence then to the Recipient BC. Now by configuring a join in Recipient BC and Quote BC using the pulled Quote Row Id, I pulled all the desired fields of Quote BC.The Below Diagram will help you to understand it more clearly,

The Final Version of Task flow was looking like the below figure:

Finally, everything shaped up well and I delivered my first Task Based UI successfully on time. Please put your comments / suggestions.

Inputs from Abhinav A.

Read More about Task Based UI

Missing script for OBIEE Upgrade (7.8.5 to 10.1.3.3.1)

I did OBIEE upgrade with the help of the document Oracle® Business Intelligence Applications Upgrade Guide Version 7.9 February 2007 b31980.pdf
It was configured successfully but I encountered an error in the process of the final testing by running the provided out of box workflow through informatica. The workflows failed showing an ‘INVALID IDENTIFIER’ error indicating that some of the columns in the warehouse tables were missing. Hence I referred the document again.

As per the document the, warehouse Schema has been upgraded with the following mentioned scripts:-
UPGRADE.ctl script.
790_UPGRADE_PRE_CTL_SCRIPT.sql script.
DW.ctl script.
790_UPGRADE_PRE_DIMENSION_SCRIPT.sql script.
Though the above mentioned scripts were successfully executed, the workflows failed with the same error ‘INVALID IDENTIFIER’
Hence I figured out that one more script is required to resolve the above problem.
So after running the script placed at OracleBI\DAC\conf\sqlgen\ctl-file\ oracle_bi_dw.ctl
(DDLimp utility) the issue was resolved.
This script contained all the missing columns.
To run the Script use the following command:-

Here DDLimp is the utility provided by Siebel to run this Script.
Execute the following Command in the CMD Prompt:
—-DDLimp /U USER /P PASSWORD /C <Connect string i.e. system DSN> /G SSE_ROLE /I N /R Y
F <Path for the oracle_bi_dw.ctl Script>/L (location where you want to store log file)

Where,
U is Database Table owner name.
P is Database Table owner Password.
C is System DSN.
For G, I and R use the Default as given.
F indicates the path of the Script.
L indicates the path were the log file is to be stored.
Finally to my relief the upgrade completed by running the above mentioned script!

Inputs from Alok Chowdhary

Read More about OBIEE Upgrade

AdventureWorks 2000 and SQL Server 2005

Wow !!! It was Lewis Hamilton in India – On hearing the news I almost thought he was in india for the inauguration of our formula one circuit at Delhi or Hyderabad. But later heard that it was for a commercial deal. Ok Friends, Here I am today trying to explain some of the activities that needs to be performed for attaching the AdventureWorks2000 database to SQL Server 2005.

What is AdventureWorks Database? – Microsoft provides a sample database for demonstration of various functional as well as technical aspects of a business need. An excerpt from microsoft website reads ‘The purpose of AdventureWorks Databases is to demonstrate best practices for designing an integrated enterprise-class schema, and provide a sample database for that schema’. This database is not automatically installed with SQL Server Express.

Now, having known little about what AdventureWorks is, let us get into the details of downloading the database and attaching it to the SQL Server 2005.

By accessing the microsoft download center, download Adventure Works Database 2000.msi and install.Once installed, the file AdventureWorks2000_Data.mdf gets loaded into the specific MSSQL folder within the program files whose path could always be found by a windows search. Having performed this activity, one could follow the procedure present in microsoft’s msdn page. The very first issue when executing the command SQLCMD -S (local)\sqlexpress -E in the Visual Studio 2008 command prompt is unable to remote in to the server error. Inorder to enable the remote connectivity, we need to perform the setup in SQL Server 2005 Surface Area Configuration. After the surface area configurations for services and connections are setup with the help of microsoft’s support, we would be able to attach the Adventureworks 2000 database for our testing and learning process. And always the best way to test the above activity is to verify whether the database is attached or not.

For all the people who are new to MSSQL, Hope this gives you an heads up on what AdventureWorks 2000 is ? and how it could be installed in SQL Server 2005 ?.

Read More: SQL Server 2005

Exceptions in Informatica – 2

Let us see few more strange exceptions in Informatica

1. Sometimes the Session fails with the below error message.
“FATAL ERROR : Caught a fatal signal/exception
FATAL ERROR : Aborting the DTM process due to fatal signal/exception.”

There might be several reasons for this. One possible reason could be the way the function SUBSTR is used in the mappings, like the length argument of the SUBSTR function being specified incorrectly.
Example:

IIF(SUBSTR(MOBILE_NUMBER, 1, 1) = ‘9′,
SUBSTR(MOBILE_NUMBER, 2, 24),
MOBILE_NUMBER)

In this example MOBILE_NUMBER is a variable port and is 24 characters long.
When the field itself is 24 char long, the SUBSTR starts at position 2 and go for a length of 24 which is the 25th character.

To solve this, correct the length option so that it does not go beyond the length of the field or avoid using the length option to return the entire string starting with the start value.
Example:

In this example modify the expression as follows:

IIF(SUBSTR(MOBILE_NUMBER, 1, 1) = ‘9′,
SUBSTR(MOBILE_NUMBER, 2, 23),
MOBILE_NUMBER)

OR

IIF(SUBSTR(MOBILE_NUMBER, 1, 1) = ‘9′,
SUBSTR(MOBILE_NUMBER, 2),
MOBILE_NUMBER).

2. The following error can occur at times when a session is run

“TE_11015 Error in xxx: No matching input port found for output port OUTPUT_PORT TM_6006 Error initializing DTM for session…”

Where xxx is a Transformation Name.

This error will occur when there is corruption in the transformation.
To resolve this do one of the following: * Recreate the transformation in the mapping having this error.

3. At times you get the below problems,

1. When opening designer, you get “Exception access violation”, “Unexpected condition detected”.

2. Unable to see the navigator window, output window or the overview window in designer even after toggling it on.

3. Toolbars or checkboxes are not showing up correctly.

These are all indications that the pmdesign.ini file might be corrupted. To solve this, following steps need to be followed.

1. Close Informatica Designer
2. Rename the pmdesign.ini (in c:\winnt\system32 or c:\windows\system).
3. Re-open the designer.

When PowerMart opens the Designer, it will create a new pmdesign.ini if it doesn’t find an existing one. Even reinstalling the PowerMart clients will not create this file if it finds one.

Read More about Informatica 

Part II – DBA Refresher for Peoplesoft Database Cloning

Please read the Instant Peoplesoft Cloning Refresher Kit before continuing in this post,

Take care to remove the reuse word in the create control file statement and replace it with SET

Once the Control file is created

alter database open resetlogs

If you encounter problems,

login to command prompt,

sqlplus /nolog
connect sys/manager as sysdba
shutdown immediate
startup
exit

select * from psdbowner;

update PSDBOWNER SET DBNAME = ‘destination db name’

create user sysadm identified by <password> default tablespace <tablespacename> temporary tablespace <tablespacename>

Create user PS identified by <password> default tablespace <tablespacename> temporary tablespace <tablespacename>

Create user PEOPLE identified by <password> default tablespace <tablespacename> temporary tablespace <tablespacename>

Grant connect, resource, dba to sysadm

Grant connect, resource to ps

Grant create session to people

And after revisiting all the above points, you would be able to access application designer after configurations in configuration manager.

Read About: DBA Refresher

Lessons From CMMI (A Software Process Model) For BI Practitioners

Hexaware successfully completed the CMMI Level 5 re-certification recently with KPMG auditing and certifying the company’s software process to be in line with Version 1.2 of the model. This is the highest level in the Capability Maturity Model Integration model developed by Software Engineering Institute in collaboration with Carnegie Mellon. For the uninitiated, Capability Maturity Model Integration (CMMI) is a process improvement approach that provides organizations with essential elements of effective process.

Now, what has CMMI got to do with Business Intelligence?

I participated in the re-certification audit as one of the project managers and I learnt some lessons which I think would be useful for all of us as BI practitioners. The CMMI model has 22 different process areas covering close to 420 odd specific practices. Though the specifics are daunting, the ultimate goal of the model is simple to understand and there-in lies our lesson.

In the CMMI model, Maturity Levels 2 and 3 act as building blocks in creating the process infrastructure to ensure that the higher maturity levels are achievable and sustainable.The high-maturity practices (Levels 4 and 5) of the model focus on:

1) Establish Quantitative Goals in line with the business objectives

2) Measure the performance with respect to the goals using statistical tools

3) Take corrective action to bring the performance in line with the goals

4) Measure again to ensure that the action taken has contributed positively to performance improvement.

Key Lessons for BI Practitioners:

1) Single-minded focus to “close the loop” – CMMI model evaluates every project management action in the context of project goals and measures them quantitatively. Business Intelligence, ideally, should measure all actions in the context of business goals and provide the facility to compare metrics before and after the decision implementation.

2) Strong information infrastructure – Higher levels of maturity in CMMI are sustainable only if the lower maturity levels are strongly established. In the BI context, this translates to a robust architecture that makes measurements possible

3) Accuracy + Precision is the key – Controlling variation (sustainability) is as important as hitting your targets. BI in organizations is weak along the sustainability dimension. For instance, enterprises do have analytics around “How am I doing now” but not much on questions like a) How long will this growth continue? b) When will we get out of this declining trend? etc.

In a way, this post is related to one of my earlier blog on BI and Sig Sigma with the central idea being that, for enterprises to be analytics driven both numbers and processes behind those numbers are equally important. CMMI Model, in its simplest form, also has that as its core theme for achieving high process maturity in an organization.

Thanks for reading and please do share your thoughts. Read More About : CMMI

Haley’s in a Nutshell

INTRODUCTION:
CRM applications are based on various complex business processes that need to be coded in the application.They can change with time and this can be very often.Implementing the changes can have adverse effects on those parts that weren’t changed!!! Implementing Decision tress can be a nightmare through actual programming…with hundreds of If else conditions which is very difficult to maintain and foolish to code in the first place.

Haley Systems, Inc
has been the recognized global leader in rule-based programming. Haley’s Authority is a Rule authoring tool which allows you to write your Business rules in simple English, maintains them, executes them and allows testing them all in no time. Assume it as a programming language which uses simple English to code. The sentence “Set student’s grade to Distinction if he scored at least 70 in all subjects” can One needs to get back to school grammar to construct English sentences.

About Haley Authority: Mind your language (Grammar)
Get familiar with Nouns, Verbs, Adjectives, Adverbs, Prepositions, Conjunctions, Determiners, Phrases, Sentences, Subjects, Predicates and Direct & Indirect Objects.Haley adds few more terms such as Concepts, Entities, Relations, Phrasings, Statements, Conditions, Modules, Knowledge Base. With just an initial brush up on these topics you can get going with the wonderful tool.

Integrating with Siebel
Find below the Haley <- -> Siebel Integration Architecture.

Siebel Object Importer
The concepts and relations needed to define rules on Siebel objects are created by importing the selected subset of the Siebel object model into Haley’s.

Siebel Deployment plug-in
Rule modules created in Haley Authority are compiled and stored into runtime tables
Rules Runtime Administration screen
It is used to activate, deactivate, and configure deployed rule modules.

Haley Authority knowledge base
It stores Haley Authority’s metadata, including Object Importer-created Siebel object definitions and rules

Haley inference engine
It executes rules.
Proxy Service

• It is a proxy to the rules engine functionality at runtime.

• It manages Haley’s runtime objects

• It manages deployed rule modules

• Interfaces with Siebel object manager for data

• Interfaces with Haley’s inference engine for rule execution

• Manages the rule execution results from Haley’s inference engine

THE PROS :

• All the business logic can be isolated in a separate database (Knowledge base).

• It can reduce scripting to a great extent.

• Flexible to change the rules, policies on the go

• Allows testing of the Rules created in Haley before actual deployment.

• Easy to understand/write/maintain English Statements than blocks and blocks of code.

THE CONS:

• Need to learn the GRAMMER. Haley Authority cannot write a poem or a play.
• It can not replace all of your code but most of it.Remember everything is built for a purpose.
• There’s no Check in Check out here.Maintaining a development environment where n developers are using Haley’s for constructing rules becomes difficult as only one Knowledge base per environment is supported.

Reader’s Take:

Consider the following scenario wherein an Insurance claim is to be ACCEPTED, PARTIALLY ACCEPTED, REJECTED based on the below decision tree.

I leave the answer to the readers to select between

• A. Coding a mesh of 1000’s of If Else Statements
• B. Using HBR

Comments welcome !

– Inputs from Pinank K.

Read More about Haley’s in a Nutshell

Look before you leap – Is an assessment necessary before a Siebel upgrade ?

In today’s project execution scenario, upgrade assessments have gained much more importance than ever before.

Yet, IT managers face a lot of push back from their customer when they are faced with the cost associated with executing an upgrade assessment

The client wonders, why can’t we directly start with upgrade and move to a newer version. Why spend on an upgrade assessment?

Listed below are some of the arguments in favor of doing an assessment before going in for an upgrade. This applies equally to any package and not only Siebel.

1. Why we require Assessment?

If you do an upgrade assessment with a sample area of the functionality, you will be in a position

• To measure the precise scope of the upgrade exercise: What has to be done, when and how long it will take.

• To get a feel for the technical complexity of the upgrade – The vendor may tout the upgrade as a one click exercise that will be completed in half an hour while the users are out to lunch. The reality may be different.

• To judge which of the new “features” are absolutely essential to get your users back on the road with minimum fuss and low cost

• To assess the technical complexity of the upgrade – How much of your application will still work after the upgrade? Trust me, a lot of things will break.

• To assess the gaps & issues highlighted by a focus group of users – You could let key users preview the result of upgrade exercise and see what they think about it.

• To estimate the time and effort required to do the actual upgrade

• To decide on a GO/NO-GO: The assessment will give you enough information to decide whether the upgrade is a good idea or not after all. It’s a good idea to get the assessment done by an independent party without bias to the software supplier

2. Continuing benefits of Assessment during the upgrade

Once a GO decision has been made, the upgrade assessment will continue to give you returns when executing the actual upgrade in the form of the below artifacts

• Knowledge repository: You will have a good understanding of the pitfalls faced by the assessment team.

• A realistic and re-usable Project Plan : Planning and precursor activities completed during POC will reduce actual upgrade timeline

• Knowledge of Pre-requisites : Identification of upgrade tasks specific to Application/infrastructure

• Compressed critical path: You will know which activities take a long time and fine tune upgrade processes to squeeze the timeline.

• Resource readiness : You will know exactly what kind of resources are required to execute the upgrade and be ready to start upgrade on short notice

In conclusion, an assessment gives client an accurate method of determining if an upgrade is the right route to take, and gives precise estimation of the scope, schedule, resources and cost associated, which in turn lead to speedy decision making.

Inputs from Jagdish

Read More about Siebel Upgrade

Data Integration Challenge – Carrying Id-Date Fields from Source

Ids in Source System: Sometimes we would have been in a dilemma to decide whether to carry the identity (id) fields from source system as identity fields into the data warehouse as well. There is couple of situations which would push us to this state1.The business users are familiar with the product ids like 1211 , 1212 than by the product name it self and they need them in the target system

2.Why should we create an additional id field in the target table when I can have a unique identity for each record from the source system

What are Opensource Id fields, they are usually the surrogate keys or unique record keys like product id, customer id etc which the business might be more familiar than with their descriptions, descriptions of these ids are more found on report printouts. In general most of the source id fields would get mapped to the dimension tables.

Here are the reasons why we should carry the source id fields as it is

1. The business is comfortable talking and analyzing in terms of ids than descriptions
2. Having source ids fields which are usually numeric or if not of smaller length is very much lookup friendly, using ids for lookup or filter or join conditions when pulling data from source systems is much better than descriptions
3. Source id fields enables linking of the data from the data warehouse to the source system
4. Just consider the id as another attribute to the dimension and not as a unique identifier

Here are the reasons why we should create additional id (key) field in addition to the source id field

1. Avoiding duplicate keys if the data to be sourced from multiple systems
2. The source ids can merge, split, anything can happen, we would want to avoid the dependency on the source system
3. The id field created in the data warehouse would be index friendly
4. Having a unique id for each record in the data warehouse would help in determining the number of unique records in a much easier way

Dates in Source System: One other field that we usually confuse is the date field in the source systems. The date field present in the source record might provide the information when the record arrived in the source system and a date field generated in the data warehouse system would provide the information when a source record arrived into the data warehouse.

The data warehouse record date and the source record date can be same if the source record gets moved into the data warehouse the same day, certainly both date fields might represent different date values if there is a delay in the source data arrival into the data warehouse.

Why we need to store Source Date in the data warehouse, this need is very clear, we always perform date based analysis based on the arrival of source record in the source systems.

Why we need to generate a Data Warehouse Date, capturing the arrival of the record into the data warehouse answers queries related to audit, data growth and as well to determine what new records arrived into the warehouse which is especially useful for providing incremental extracts for downstream marts or other systems.

Read Brief About: Data Integration Challenge 

Informatica PowerCenter 8x Key Concepts – 1

We shall look at the fundamental components of the Informatica PowerCenter 8.x Suite, the key components are

1. PowerCenter Domain
2. PowerCenter Repository
3. Administration Console
4. PowerCenter Client
5. Repository Service
6. Integration Service

PowerCenter Domain

A domain is the primary unit for management and administration of services in PowerCenter. Node, Service Manager and Application Services are components of a domain.

Node

Node is the logical representation of a machine in a domain. The machine in which the PowerCenter is installed acts as a Domain and also as a primary node. We can add other machines as nodes in the domain and configure the nodes to run application services such as the Integration Service or Repository Service. All service requests from other nodes in the domain go through the primary node also called as ‘master gateway’.

The Service Manager

The Service Manager runs on each node within a domain and is responsible for starting and running the application services. The Service Manager performs the following functions,

• Alerts. Provides notifications of events like shutdowns, restart
• Authentication. Authenticates user requests from the Administration Console, PowerCenter Client, Metadata Manager, and Data Analyzer
• Domain configuration. Manages configuration details of the domain like machine name, port
• Node configuration. Manages configuration details of a node metadata like machine name, port
• Licensing. When an application service connects to the domain for the first time the licensing registration is performed and for subsequent connections the licensing information is verified
• Logging. Manages the event logs from each service, the messages could be ‘Fatal’, ‘Error’, ‘Warning’, ‘Info’
• User management. Manages users, groups, roles, and privileges

Application services

The services that essentially perform data movement, connect to different data sources and manage data are called Application services, they are namely Repository Service, Integration Service, Web Services Hub, SAPBW Service, Reporting Service and Metadata Manager Service. The application services run on each node based on the way we configure the node and the application service

Domain Configuration

Some of the configurations for a domain involves assigning host name, port numbers to the nodes, setting up Resilience Timeout values, providing connection information of metadata Database, SMTP details etc. All the Configuration information for a domain is stored in a set of relational database tables within the repository. Some of the global properties that are applicable for Application Services like ‘Maximum Restart Attempts’, ‘Dispatch Mode’ as ‘Round Robin’/’Metric Based’/’Adaptive’ etc are configured under Domain Configuration

Read More about Informatica PowerCenter 8x 

Part II – Peoplesoft and Microsoft Active Directory Single Sign On Setup

Hip Hip Hurray !!!! 2 Lewis Hamilton Fans !!!!!

Its a dream run on monaco for his team. And it is also time for us to hit the fast track to part 2 of the single sign on between Microsoft and Peoplesoft,

Steps to perform on LDAP:

1. Access ldp.exe from within the Microsoft Active Directory

2. Connect to the LDAP server with the default port 389 (click on Connection->connect)

3. Click connection-> bind and enter the user account name and password

4. Click View->TreeView and enter the Base DN (DC=Helix,DC=Hexaware,DC=com)

5. Select the User Group present with the ldap, CN=Users, DC=Helix, DC=Hexaware, DC=com

6. Select the administrator listed down under the selected user group (e.g CN = Hexaware Admin, CN=Users, DC=Helix, DC=Hexaware, DC=com)

Sample Environment Used: DNS- Helix.Hexaware.com

Steps to do on Peoplesoft:

1. Navigate to PeopleTools > Security > Directory > Configure Directory

2. Create a New Directory ID

3. Select the directory product as ‘Microsoft Active Directory’

4. Enter the default connect DN (The steps are listed on the above section, e.g CN = Hexaware Admin, CN=Users, DC=Helix, DC=Hexaware, DC=com)

5. Enter the administrator password for the administrator user id ‘Hadminis’

6. Enter the LDAP Server Name and the default port 389

7. In the Additional Connect DN’s tab, enter the User DN “Helix\Hadminis” and its password

8. Now test the connectivity to get the success on all the sub sections.

Most Common Errors:
===================

1. User who is setting up the single sign on should be aware of the difference between the Administrator User ID and Its Canaonical Name

2. In Connectivity Failure – Use Network Analyzer Tool ( I will bring in more details in my next post)

Hope the start off steps helps ,.. Await more on Peoplesoft Schema Caching in pitstop’s next post.

Thanks For Give Time To Read This Blog. Read About: Peoplesoft and Microsoft Active Directory

Web Services – The Fuel For Service Oriented Architecture (SOA)

As we are eagerly awaiting the Montreal fest this weekend and with surge in fuel prices, its right time to discuss the driving force behind SOA.

Web Services are the core to a SOA System and they are the driving factor for a lot of present day state of the art systems.Days are gone by where a small piece of change request would require 2 to 4 months of work on the legacy systems with application service provider ASP.
It all depends on the compatibility factor, say for example we need to leverage the functionality of a JCL or a COBOL program to be used in a Visual Basic or AJAX application. It would sound complicated as the solution was designed with a lot of interfaces for the Microsoft or Unix environment; It was really a hard job to perform a couple of years ago. But now It’s web services, a cheat code to a hard to win game. Web Services are a bunch of code within a system which is modular and acts as a plug and play for the users who require a specific service.

By now, web services are far capable, far scalable, far reusable, far accessible and far user friendly to the users. Lets see how Peoplesoft had mastered the Service Oriented Architecture and the various chunks like

• Web Services Creation
• Web Services Publishing, and
• Standards and Technology Behind Web Services

in the following posts. Integration between Peoplesoft and Java Based applications as well as Peoplesoft and Microsoft Sharepoint Services are basically driven by SOA.

Hybrid OLAP – The Future of Information Delivery

As I get to see more Enterprise BI initiatives, it is becoming increasingly clear (atleast to me!) that when it comes to information dissemination, Hybrid Online Analytical Processing (HOLAP) is the way to go. Let me explain my position here.

As you might be aware, Relational (ROLAP), Multi-dimensional (MOLAP) and Hybrid OLAP (HOLAP) are the 3 modes of information delivery for BI systems. In an ROLAP environment, the data is stored in a relational structure and is accessed through a semantic layer (usually!). MOLAP on the other hand stores data in proprietary format providing the notion of a multi-dimensional cube to users. HOLAP combines the power of both ROLAP and MOLAP systems and with the rapid improvements made by BI tool vendors, seems to have finally arrived on the scene.

In my mind, the argument for subscribing to the HOLAP paradigm goes back to the “classic” article

by Ralph Kimball on different types of fact table grains. According to him, there are 3 types of fact tables – Transaction grained, Periodic snapshot, Accumulating snapshot and that atleast 2 of them are required to model a business situation completely. From an analytical standpoint, this means that operational data has to be analyzed along with summarized data (snapshots) for business users to take informed decisions.

Traditionally, the BI world has handled this problem in 2 ways:

1) Build everything on the ROLAP architecture. Handle the summarization either on the fly or thro’ summarized reporting tables at the database level. This is not a very elegant solution as everybody in the organization (even those analysts working with summarized information) gets penalized for the slow performance of SQL queries issued against the relational database through the semantic layer.

2) Profile users and segregate operational analysts from strategic analysts. Operational users are provided ROLAP tools while business users working primarily with summarized information are provided their “own” cubes (MOLAP) for high-performance analytics.

Both solutions are rapidly becoming passé. In many organizations now, business users wants to look at summarized information and based on what they see, needs the facility to drill down to granular level information. A good example is the case of analyzing Ledger information (Income statement & Balance Sheet) and then drilling down to Journal entries as required. All this drilling down has to happen through a common interface – either an independent BI Tool or an enterprise portal with an underlying OLAP engine.

This is the world of HOLAP and it is here to stay. The technology improvement that is making this possible is the relatively new wonder-kid, XMLA (XML for Analysis). More about XMLA in my subsequent posts.

As an example of HOLAP architecture, you can take a look at this link

to understand the integration of Essbase cubes (MOLAP at its best) with OBIEE (Siebel Analytics – ROLAP platform) to provide a common semantic model for end-user analytics.

Information Nugget: If you are interested in Oracle Business Intelligence, please do stop by at http://www.rittmanmead.com/blog/ to read his blogs. The articles are very informative and thoroughly practical.

Thanks for reading. Please do share your thoughts. Read More about Hybrid OLAP

Hybrid OLAP – The Future of Information Delivery

As I get to see more Enterprise BI initiatives, it is becoming increasingly clear (atleast to me!) that when it comes to information dissemination, Hybrid Online Analytical Processing (HOLAP) is the way to go. Let me explain my position here.

As you might be aware, Relational (ROLAP), Multi-dimensional (MOLAP) and Hybrid OLAP (HOLAP) are the 3 modes of information delivery for BI systems. In an ROLAP environment, the data is stored in a relational structure and is accessed through a semantic layer (usually!). MOLAP on the other hand stores data in proprietary format providing the notion of a multi-dimensional cube to users. HOLAP combines the power of both ROLAP and MOLAP systems and with the rapid improvements made by BI tool vendors, seems to have finally arrived on the scene.

In my mind, the argument for subscribing to the HOLAP paradigm goes back to the “classic” article

by Ralph Kimball on different types of fact table grains. According to him, there are 3 types of fact tables – Transaction grained, Periodic snapshot, Accumulating snapshot and that atleast 2 of them are required to model a business situation completely. From an analytical standpoint, this means that operational data has to be analyzed along with summarized data (snapshots) for business users to take informed decisions.

Traditionally, the BI world has handled this problem in 2 ways:

1) Build everything on the ROLAP architecture. Handle the summarization either on the fly or thro’ summarized reporting tables at the database level. This is not a very elegant solution as everybody in the organization (even those analysts working with summarized information) gets penalized for the slow performance of SQL queries issued against the relational database through the semantic layer.

2) Profile users and segregate operational analysts from strategic analysts. Operational users are provided ROLAP tools while business users working primarily with summarized information are provided their “own” cubes (MOLAP) for high-performance analytics.

Both solutions are rapidly becoming passé. In many organizations now, business users wants to look at summarized information and based on what they see, needs the facility to drill down to granular level information. A good example is the case of analyzing Ledger information (Income statement & Balance Sheet) and then drilling down to Journal entries as required. All this drilling down has to happen through a common interface – either an independent BI Tool or an enterprise portal with an underlying OLAP engine.

This is the world of HOLAP and it is here to stay. The technology improvement that is making this possible is the relatively new wonder-kid, XMLA (XML for Analysis). More about XMLA in my subsequent posts.

As an example of HOLAP architecture, you can take a look at this link

to understand the integration of Essbase cubes (MOLAP at its best) with OBIEE (Siebel Analytics – ROLAP platform) to provide a common semantic model for end-user analytics.

Information Nugget: If you are interested in Oracle Business Intelligence, please do stop by at http://www.rittmanmead.com/blog/ to read his blogs. The articles are very informative and thoroughly practical.

Thanks for reading. Please do share your thoughts.

Read More Hybrid OLAP

Kronos Data Collection Manager – DCM

Be it a Software Industry or Hardware Industry, wherever there is a considerable amount of workforce involved, Time and Attendance plays an important role. Especially, the automation of the time and attendance process. Its the Kronos Workforce Timekeeper that tops the list on the day of chequered flag. Lets see how Kronos does it ,..

Having known that Kronos Workforce Timekeeper is used for processing the time and attendance Data of the workforce in an organization, Lets concentrate on the “Data Collection Manager” of kronos. DCM is a set of applications that enables the interaction between data collection devices or clocks and a host application like kronos workforce timekeeper.

DCM constitutes the

• Communication Monitor,
• Device Manager,
• Device Work Rules,
• Event Manager, and
• Sentinel Service applications.

Communication Monitor: When an employee swipes in the access card to enter his/her workplace, the exact time is captured/collected in a collection device such as clocks, biometric devices,.. The collected information needs to reach the host application for further processing, If there is a data flow error or when a collection device is down, then we use the communication monitor to test device communications and its transactions.

Device Manager: is used to check the status of the collection devices and to configure the device firmware. Start and Stop services of the data collection devices is one of the frequently used features of the device manager during the configuration and re-initialization of the device.

Lets see more about pay rule, work rule, their associations and few interesting facts about event manager and sentinel service in the next post.

**DCM – Data Collection Manager
**DCD – Data Collection Devices

To Know More Vistit: Kronos

Exceptions in Informatica

There exists no product/tool without strange exceptions/errors, we will see some of those exceptions.

1. You get the below error when you do “Generate SQL” in Source Qualifier and try to validate it.
“Query should return exactly n field(s) to match field(s) projected from the Source Qualifier”
Where n is the number of fields projected from the Source Qualifier.

Possible reasons for this to occur are:

1. The order of ports may be wrong
2. The number of ports in the transformation may be more/less.
3. Sometimes you will have the correct number of ports and in correct order too but even then you might face this error in that case make sure that Owner name and Schema name are specified correctly for the tables used in the Source Qualifier Query.
E.g., TC_0002.EXP_AUTH@TIMEP

2. The following error occurs at times when an Oracle table is used

“[/export/home/build80/zeusbuild/vobs/powrmart
/common/odl/oracle8/oradriver.cpp] line [xxx]”
Where xxx is some line number mostly 241, 291 or 416.

Possible reasons are

1. Use DataDirect Oracle ODBC driver instead of the driver “Oracle in
2. If the table has been imported using the Oracle drivers which are not supported, then columns with Varchar2 data type are replaced by String data type and Number columns are imported with precision Zero(0).

3. Recently I encountered the below error while trying to save a Mapping.

Unexpected Condition Detected
Warning: Unexpected condition at: statbar.cpp: 268
Contact Informatica Technical Support for assistance

When there is no enough memory in System this happens. To resolve this we can either

1. Increase the Virtual Memory in the system
2. If continue to receive the same error even after increasing the Virtual Memory, in Designer, go to ToolsàOptions, go to General tab and clear the “Save MX Data” option.

Read More about Informatica

Data Integration Challenge – Parent-Child Record Sets, Child Updates

There are certain special set of records like Loan & its Guarantor details in a banking system, each Loan record can have one or more Guarantor record. In a similar way for a services based industry Contracts & its contract Components exist, these sets can be called as parent-child records where in for one parent record like Loan we might have zero to many child records of Guarantor.

During data modeling we would have one table for the parent level record and its attribute, another separate table for the child records and its attributes.

As part of the data load process, have seen situations where a complete refresh (delete & insert) of the Child records is required whenever there is a change in certain attributes of a parent record. This requirement can be implemented in different ways; here we would look at one of the best ways to get this accomplished.

The following steps would be involved in the ETL process

1. Read the parent-child record
2. Determine if a change in the incoming parent record
3. If a change has occurred then issue a delete to the particular set of child records
4. Write corresponding incoming new child records into a flat file
5. Once step 1 to 4 is completed for all parent records have another ETL flow that would bulk load the records from the flat file to the child table

We didn’t issue an insert with a new incoming child record after the delete because the deleted record wouldn’t have got committed and an insert can lock the table. We can issue a commit after every delete and then follow it with an insert but having a commit after each delete would be costlier, writing the inserts to the files handles this situation perfectly.

Also an option to insert first with a different key and then delete the older records would be costlier in terms of locating the records that needs to the deleted.

We could have also looked at the option of updating the records in place of deletion then we would at times end up having dead records in the child tables; the records that have been deleted in the source would still exist in the target child table, also updating a record can disturb contagious memory, deletion and insert would have the pages intact.

Read More about  Data Integration