Business Intelligence and Data Warehousing Notes

Execution orders for Stage Variables, Constraints and Column Derivations in Transformer stage

2011-09-04T18:14:00.000+08:00

For people that work in DataStage ETL tools, using Transformer stage in a Server/Parallel is very common and personally I would say transformer stage will be used at least once most of the time. Have anyone clear about the execution order for Stage variables, Constraints and Column derivations in Transformer?

Today what I wish to share is about the execution order for stage variables, constraints and column derivations.

In transformer stage, execution order for the three components I mentioned above is stage variables, constraints and column derivations. Basically, DataStage executes these from top to bottom. This is clearly shown when you double click on the Transformer stage and you would see stage variable is located the top, followed by constraints and column derivations.

Please always remember the following characteristics for each component:

Stage variables - Executed for every rows that we processed/extracted
Constraints - Can be treated as a filter condition which limits the number of rows/records coming from our input based on the business rules we defined. Stage variable can be used in constraints.
Column derivations - Used to get or modify our input values, i.e. concatenation of two values from inputs, set the column to constant value, etc.

SAP shops reviewing BI strategies in wake of BusinessObjects 4.0

2011-07-12T17:03:00.000+08:00

SAP shops reviewing BI strategies in wake of BusinessObjects 4.0

Data Warehouse Glossary

2011-04-02T12:55:00.000+08:00

I came across this document that contains all the data warehouse related glossary. The document is compiled by Father of Data Warehouse, Bill Inmon. The link to the Data Warehouse Glossary is here.

Hope the document will be beneficial to everyone especially those that currently working in Data Warehouse and Business Intelligence space. :) Feel free to share with me if you guys have good data warehouse and business intelligence related docs/articles.

Default Null in DataStage

2011-02-27T22:10:00.000+08:00

In DataStage, the way to default null in transformer stage for Server job and Parallel job are different.

In transformer stage in Server job, @NULL is used.
In transformer stage in Parallel job, SetNull() is used.

DataStage job aborted due to ulimits - Need to be able to open at least 16 files; please check your ulimits settings for number of file descriptors

2010-07-26T23:29:00.000+08:00

I am running a job on AIX 64-bit with DataStage V8.1 installed. The DataStage job failed with following error messages (refer to screen attached):

Fatal Error: Need to be able to open at least 16 files; please check your ulimits settings for number of file descriptors

Solutions:

1. Check the value for ulimits in dsenv is set to 102400 or higher. If not, change the value of ulimits and do a restart on DataStage engine. Command to restart DataStage engine as below:
          • cd $DSHOME/
          • . ./dsenv
          • bin/uv admin –stop
          • bin/uv admin -start
2. Login using root and check /etc/security/limits. Look for value of nofiles and nofiles_hard in that file. If the value is lower than 102400 then modify it to 102400 for both of the entries. Add the entries if there are not in the limits file.

To resolve the problem, both of the steps must be applied in dsenv and root level.

References
http://www-01.ibm.com/support/docview.wss?uid=swg21322302
http://www-01.ibm.com/support/docview.wss?uid=swg21385849

Enable BASIC transformer in DataStage

2010-06-12T01:17:00.001+08:00

When you are developing a Parallel job in DataStage, we are able to call a server routine in our Parallel job provided that we make use of BASIC transformer.

BASIC transformer is not visible in the Proccesing palette in Parallel job. Hence we would need to bring in the BASIC transformer to our design canvas following the steps stated below:
Go to Repository tree -> Stages Types -> Parallel -> Processing -> BASIC Transformer.
Drag and drop the BASIC transformer stage to our design canvas.

With BASIC transformer, we are able to perform various server type data transformation in parallel job. For example, calling a server routine, link variables, etcs.

Check Services Running in AIX

2010-06-12T00:51:00.002+08:00

In AIX, we can run the following command to verify what are the services that are currently running on the system.
$lssrc -a

Non-Wire protocol drivers VS Wire protocol drivers

2010-05-21T23:16:00.001+08:00

Basically there are two type of ODBC drivers being used in DataStage:

non-wire protocol drivers - require database client software to be installed on the DataStage server (the drivers use the API supplied by the database client)
wire protocol drivers - do not require database client software (they communicate with the database directly)

Technorati Tags: Non-Wire VS Wire protocol drivers

Connection to Teradata database via ODBC stage in DataStage

2010-05-04T23:34:00.002+08:00

Before we can connect to the Teradata database using ODBC stage in DataStage, we need to define the Data Source on the DataStage server.

In Windows environment, we can defined the Data Source by going to Control Panel -> Administrative Tools -> Data Sources (ODBC) . Add a system DSN.

Note: DataStage cannot work on user DSN.

In Unix environment, three files need to be edited for ODBC configuration.

1. dsenv file - contains the environment variables to connect to different databases
2. .odbc.ini file - contains the names and the data sources available to the Unix users.
3. uvodbc.config file - contains the DSNs name that connect through ODBC

Example of dsenv file

#Oracle 9i
ORACLE_HOME=/space/oracle9i
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib;export
LD_LIBRARY_PATH
ORACLE_SID=WSMK5
export ORACLE_HOME ORACLE_SID<o:p></o:p></span></p>

Example of an .odbc.ini file

[ODBC]
InstallDir=/usr/odbc
Trace=0
TraceDll=/usr/odbc/lib/odbctrac.so
TraceFile=/usr/odbcusr/joe/trace.log
TraceAutoStop=1

[ODBC Data Sources]
financial=tdata.so

[financial]
Driver=/usr/odbc/drivers/tdata.so
Description=NCR 3600 running Teradata V2R5.1
DBCName=123.45.67.10
DBCName2=123.45.67.11
DBCName3=123.45.67.12
Username=odbcadm
Password=
Database=
DefaultDatabase=sales

Example of uvodbc.config

[ODBC DATA SOURCES]

<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = 127.0.0.1

<TEST_DSN>
DBMSTYPE = ODBC

Once we are done with the configuration, we should do a test to the ODBC driver connectivity.
Step 1. Change to $DSHOME directory. Then type in . ./dsenv
Step 2. Run the DataStage server shell by type in ./bin/dssh
Step 3. Log on to the project using LOGTO your_project_name
Step 4. Use DS_CONNECT to get the list of DSNs available.
Step 5. Type DS_CONNECT dsn_name to test the connectivity.

References
http://publib.boulder.ibm.com/infocenter/iisinfsv/v8r0/index.jsp?topic=/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/tasks/wsisinst_Third_Party_ODBC_Drivers.html

[DataStage 8] IIS-DSEE-TCOS-00036 - Failed to initialize job monitoring

2010-04-03T23:40:00.005+08:00

Situation:
While running a simple DataStage parallel job in Windows Server 2003 to extract from CSV file and load into Data Set, the job completed successfully. The problem is the link color is not in GREEN but in BLACK. I verified the job status in DataStage Director and found out everything is Green with one Warning message. Below is the Warning Message:

main program: Failed to initialize job monitoring. Monitoring information will not be generated.

Cause:

JobAppMon is not running.

Solution:
Run command prompt and enter the following commands:

set APT_ORCHHOME=C:\IBM\InformationServer\Server\PXEngine
cd C:\IBM\InformationServer\Server\PXEngine\java
sh jobmoninit start
JobMonApp has been started.

Re-run the job.

Error in Table Creation

2010-03-25T15:50:00.003+08:00

Situation:
While creating the table in Oracle database, hitting the following error:

Error at Command Line:1 Column:0
Error report:
SQL Error: ORA-01950: no privileges on tablespace 'USERS'
01950. 00000 - "no privileges on tablespace '%s'"
*Cause: User does not have privileges to allocate an extent in the
specified tablespace.
*Action: Grant the user the appropriate system privileges or grant the user
space resource on the tablespace.

Solution:
GRANT RESOURCE to user_name;

SAP BW - Notes #01

2010-03-18T19:18:00.001+08:00

Extractor - a set of ABAP programs, database tables, and other objects that BW uses to extract data from the SAP systems.

BW connects with SAP systems (R/3 or BW) and flat files via ALE (Application Link Enabling) and it connects with non-SAP systems via BAPI (Business Application Programming Interface).

DataStage Error - [General repository interface 'other error']

2008-09-23T10:06:00.001+08:00

Situation
Today,one of my DataStage job that is scheduled to be run everyday suddenly failed and below is the error message that I captured in DataStage Director.

jobControlForEntity..JobControl (@JA_loadETLStreamFromRelationship): Controller problem: Error calling DSRunJob(loadETLStreamFromRelationship), code = -99
[General repository interface 'other error']

When I saw this error message, I really have no clues on why this error message is thrown as the job is running without fail all the time. Anyway, after spending a couple of hours to debug it and I managed to resolve this.

Solutions

Create a new copy of the loadETLStreamFromRelationship by right click on the job and select Create Copy. A new job with the name CopyOfloadETLStreamFromRelationship is created.
Change the job sequence to invoke the new copy of the job which is CopyOfloadETLStreamFromRelationship.
Recompile and re-run the job. Now the job running fine without throwing any errors as stated above.
Delete the previous job loadETLStreamFromRelationship.
Rename the new copy of the job to become loadETLStreamFromRelationship and change back the sequence to invoke the loadETLStreamFromRelationship.

Hope this helps.

Error Encountered in Cognos 8.3 with Oracle XE

2008-09-13T17:40:00.000+08:00

Below is the error message that I encountered when trying to run a report in Cognos.

CNC-MON-0024 Monitor Service paging error: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found The Connection descriptor used by the client was: localhost:1521:XE

I am not too sure what is the actual cause of this error but below is the way that I solved it.

Launch SQL Plus and issue the the command as below:

SQL> connect / as sysdba;
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1287016 bytes
Variable Size 96472216 bytes
Database Buffers 184549376 bytes
Redo Buffers 2904064 bytes
Database mounted.

SQL> alter system set job_queue_process=20;
System altered.

SQL> alter system set PROCESSES=100 scope=SPFILE;
System altered.

SQL> alter system set open_cursors=300 scope=both;
System altered.

SQL> alter database open;
Database altered.

SQL> alter system disable restricted session;
System altered.

Hope this may helps. :)

Install and Configure Cognos 8 Business Intelligence - Part 1

2008-02-27T21:01:00.003+08:00

This post shows the step-by-step from installation and configuration of Cognos 8.

Assumption : MS SQL Server 2005 and IIS already installed in your machine.

Install Cognos 8 BI on Windows 2000

Run the issetup.exe in the win32 folder from your download directory. The following screen will be shown.

Ensure that English is selected as the language and click Next.

Accept the license agreement by choosing the I accept radio button. Click Next.

Accept the default as the installation location. Click Next.

A dialog box will be pop-up to ask for confirmation whether you want to create the directory. Click Yes and next screen will be shown.

Ensure that all the components are selected as depicted in above screen and click Next.

Click Next again at this step.

You are now at Installation Summary screen which summarize list of components to be installed in your machine and in the particular drive. Click Next.

The installation process is now starting. Once completed, click Next and you can see the following screen.

Click on finish to close the screen. You just finish with the installation process of Cognos 8 BI.

You shouldn't have any problem with all the steps indicated here as the entire installation process is pretty straight forward. I will be posted the Part 2 end of this week. :)

Hidden pages within Mozilla Firefox

2008-02-24T01:53:00.003+08:00

The hidden pages of Firefox are configuration items that allow end users to see and make changes to the way Firefox operates. Let's have a try on it by typing the command in the address field.

about: - Shows the about information for the Firefox application. Similar to the usual Help ? About menu option found in most applications (and in Firefox, too).
about:buildconfig - Displays platform configuration information used in the running build of Firefox.
about:cache - Shows the caching information stored for Firefox.
about:plugins - Displays the installed plug-ins running within the current install of Firefox.
about:credits - Shows the brains, and the coding brawn behind the Firefox project.
about:Mozilla - Displays quotations from the Book of Mozilla.
about:config - Modifies configuration parameters used by Firefox

Source:Finding the hidden bells and whistles in Firefox by Derek Schauland

Google Browser Sync

2008-02-22T12:12:00.002+08:00

Google Browser Sync is a Firefox add-ons that can be used to automatically synchronize your bookmarks, history, persistent cookies and saved password across all the computers where you install it. It also remembers which tabs and windows you had open and when you last closed of your browsers. This allows you to restore open tabs and windows in case you accidentally close it.

Once installed, you will find yourself to go right to the sites that you want without to think for half-a-second on what is the address for the web site. To try yourself, go here.

Slowly Changing Dimension (SCD)

2008-02-13T18:08:00.004+08:00

In dimensional data warehouse, most dimensions change over time. When a dimension changes, e.g. when a product get renamed or product get a new category. Therefore we must maintain the dimension history to ensure that old sales orders will still be able to reference after the changes. In order to achieve this, the slowly changing dimension or in short SCD comes into the picture.

SCD is a technique for implementing history maintenance in dimensional data warehouse. In general, there are three types of SCD : SCD Type 1, SCD Type 2 and SCD Type 3. Let's define the types of SCD.

a) SCD Type 1

Updates dimension records by overwriting the existing data.
Used when no history of the records is maintained in the table like situation where we directly overwrite the incorrect data.

b) SCD Type 2

Doesn't delete or modify any existing data.
Whenever there is a change in source, a newer version of records will be created. This means that it will keep a full history of dimension data in the table.

c) SCD Type 3

Maintains just previous and current data.
It keep history by adding more than one column to records to maintain its history. For example, in customer dimension table, we will have two extra columns: Current_Address and Previous_Address.
Used to maintain a limited history where we have a database space constraint.

Disable substitution variable prompting in SQL Plus (Oracle)

2008-01-27T22:47:00.004+08:00

If we run a script or SQL that contains "&" symbols, for example when we try to insert some data into database, what actually happen is SQL Plus will prompt the user for a value.

In that case, we can use SET DEFINE OFF to stop SQL*Plus performing any variable substitution. This makes SQL*Plus treat all ampersands (&) as literal characters.

Eraser - Free secure data removal tool

2007-12-21T10:50:00.004+08:00

Eraser completely removes data from your system by overwriting it several times (sometimes referred to as shredding), which sanitizes the data, making it unrecoverable. Eraser permanently removes data from your PC, ensuring confidentiality. Similar to shredding a paper document, when you shred a file, it cannot be recovered.

Normally, when you delete a file, the operating system does not really remove the file from the disk; it only removes the reference of the file from the file system table. The file remains on the disk until another file is created over it, and even after that, it might be possible to recover data by studying the magnetic fields on the disk platter surface. Eraser works by overwriting it several times with carefully selected patterns. Follow the link to download Eraser.

Teradata - Table Type Specifications of Set VS Multiset

2007-12-19T16:40:00.004+08:00

There are two different type tables in Teradata. They are SET and MULTISET. If we do not specify the table type when we creating a table in Teradata, SET is used as the default.

SET table - does not allow duplicate rows and Teradata will check to ensure that no two rows in a table is exactly the same
MULTISET table - allow duplicate rows

IBM acquired Cognos at $5 billion USD

2007-11-14T17:10:00.001+08:00

SAP bought BusinessObjects last month and now IBM acquired Cognos, last of large independent business intelligence providers. In just a month, two leading business intelligence players have been acquired.

http://www.cognos.com/news/releases/2007/1112.html

Star Wars in DOS Version

2007-10-21T01:18:00.000+08:00

It had been almost two years after my last blog. Today when surfing the net and came across this DOS version of Star Wars make me suddenly had the feel to post in my own blog and share it. This is the reason that make me blogging again after two years. Now let us watch the Star Wars.

Open the command prompt by typing the cmd from Run.
Type telnet towel.blinkenlights.nl in the command prompt and enjoy :)

Below is the screen shot of the Star Wars that captured by me.

Hope you all enjoy it ;)

Removing unused device drivers from Windows XP machines

2006-01-17T11:53:00.001+08:00

When you install a device driver on a Windows XP machine, the operating system loads that driver each time the computer boots regardless of whether the device is present—unless you specifically uninstall the driver. This means that drivers from devices that you have long since removed from your system may be wasting valuable system resources.

Follow these steps to view and remove these unnecessary device drivers:

Press [Windows]+[Break] to bring up the System Properties dialog box or go to Control Panel System.

Select the Advanced tab and click the Environment Variables button.

Click the New button below the System Variables panel.

In the New System Variable dialog box, type devmgr_show_nonpresent_devices in the Variable Name text box and 1 in the Variable Value text box.

Click OK to return to the System Properties dialog box and then click OK again.

Select the Hardware tab and click the Device Manager button.

In Device Manager, go to View Show Hidden Devices.

Expand the various branches in the device tree and look for the washed out icons, which indicate unused device drivers.

To remove an unused device driver, right-click the icon and select Uninstall.

Display Chinese on Mozilla Firefox in Linux

2005-10-20T01:40:00.000+08:00

If the Firefox failed to display chinese word for a web page, make sure that the character encoding is correct.(under View --> Character encoding).

If the chinese web page still cannot display properly, then you need to install the 2 extension which are mozilla-firefox-locale-zh-tw and mozilla-firefox-locale-zh-cn.

Then restart Firefox and all the problem should be solved.