Monday, January 31, 2011

DS - Data Integration and Diff. B/W FACT & DIMENSIONS

What is DATA integration
Data from different sources (like flatfiles CSV oracle db2 teradata mainfraes etc.) are extracted and transformed in to a single specific pattern (unique structure) and is know as integration of data.
The ETL tools like datastage informatica and pentaho (open source) are used to convert the data from different format to unique format.

OR

Data integration involves data combining in different residing in different sources and providing users with a unified view of these data.This process becomes significant in a variety of situations both commercial and scientific (combining research results from different bioinformatics repositories for example). Data integration appears with increasing frequency as the volume and the need to share existing data explodes.It has become the focus of extensive theoretical work and numerous open problems remain unsolved.

***********************************
Differce between FACT AND DIMENSION TABLES

A fact table captures the data that measures the organization's business operations. A fact table might contain business sales events such as cash register transactions or the contributions and expenditures of a nonprofit organization. Fact tables usually contain large numbers of rows, sometimes in the hundreds of millions of records when they contain one or more years of history for a large organization.

Dimension tables contain attributes that describe fact records in the fact table. Some of these attributes provide descriptive information; others are used to specify how fact table data should be summarized to provide useful information to the analyst.

AND

Fact table can stores large amount of the numerical data.so fact table is big in size as compared to Dimension table.

************

DS - Performance Tuning

Performance Tuning for DataStage Jobs

1. Select suitable configurations file (nodes depending on data volume)
2. Select buffer memory correctly
3. Select proper partition
4. Turn off Runtime Column propagation wherever it's not required
5. Taking care about sort
6. Handling null values (use modify instead of transformer)
7. Try to decrease the transformer use (use copy, filter, modify…)
8. Use dataset instead of sequential file in the middle of the vast jobs
9. Take maximum 20 stages for a job
10. Select Join or Lookup or Merge (depending on data volume)
11. Stop propagation of unnecessary metadata between the stages
Don't sort previously sorted and some stages like modify, dataset...etc increases performance

***********************

DataStage Performance Issues
This note explains factors that affect DataStage performance, and recommends performance improvement techniques. Factors Affecting Performance A DataStage job is essentially a batch program. It runs at the speed of its slowest element. It can be disk I/O-bound, communications I/O-bound, or CPU-bound. Even if a job is doing very little actual transformation work, it can still be CPU bound due to the amount of CPU required to extract data from a source database, or load it into a target database. Following are general guidelines about factors that affect DataStage performance:

* Raw CPU speed. The faster the better. Performance improves roughly linearly with single CPU speed.
* Multiple CPUs. If the jobs to be performed have some inherent parallelism (e.g., processing multiple separate large files), then adding additional CPUs will help. DataStage is not multithreaded (as of release 2.0), so additional CPUs help a single-stream job only if local source and/or target databases can use them productively (e.g., the databases themselves use multiple threads). If the fundamental job is processing one single large file that cannot be easily broken up, then extra CPUs will not help.
* Main memory. The more the better. In-memory aggregation resulting in many output groups can chew a lot of memory storing intermediate values. 40 Mbytes is a pretty standard minimum. 64-80 is better. If you will be using large in-memory lookup files, add memory to accommodate these files to avoid paging overhead.
* Disk I/O. Raw disk I/O speed may affect performance some for local source and/or target databases, but I/O performance to databases is often CPU intensive as well, and money is better spent on a faster CPU.
* Communications I/O. Remote data sources and data targets will tend to slow things way down. If using ODBC to access remote data sources or targets, the best ODBC drivers will support features like array binding and use internal pipelining to provide good performance. Still, most LANs will always be slower than a good local hard disk.
* Other programs running concurrently on the DataStage server machine will tend to slow down the performance of DataStage jobs. Improving Performance This section lists tips and techniques for improving DataStage job performance gleaned from field experience to date. Note, however, that some techniques may improve some jobs and not others. System Configuration
* Upgrade to a faster single CPU and add more cache. This is the cheapest way to improve DataStage performance, and generally improves most jobs.
* Add more main memory. This helps especially if you use the AGGREGATOR stage (figure one rows worth of bytes for each resulting group, and add 20%).
* Move source and target data files/tables to separate disks and enable dual asynchronous I/O paths (e.g., dual SCSI controllers).
* Add additional CPUs if the DataStage job has inherent parallelism that can take advantage of them. A job uses multiple processes if there are multiple paths from a passive stage (sequential file, HASH or ODBC stage) through active stages to another passive stage.
* Use the fastest ODBC driver available for a data source or target. The Intersolv drivers are generally quite fast. Oracles own ODBC driver is very slow. Job Design
* Use column selection at the data source whenever possible. Remove unused columns from an ODBC or HASH input stage definition.
* Use row selection at the data source, whenever possible. For ODBC data source stages, add WHERE clauses to eliminate rows that are not interesting. This saves on the amount of data to be processed, validated and rejected.
* Perform joins, validation, and simple transformations at the source database, whenever possible. Databases are good at these things, and the more you can do during the process of data extraction, the less processing will be needed within the DataStage job. (However, UniVerse SQL is quite slow performing joins against independent tables, so avoid doing that wherever you can.)
* Use a database bulk loader stage rather than an ODBC stage to populate a target warehouse table (where available). The bulk loader stages can often take advantage of database options not available via ODBC, although the functionality is generally limited to INSERTing new rows.
* Optimizing an ODBC stage to load a non-UniVerse target warehouse table: Set the ODBC stage properties as follows:
* Set the "Transaction isolation level" to the lowest level consistent with the goals of the job. This minimizes the target database overhead in journalling.
* Set the "Rows per transaction" to 0 to have all inserts/updates performed in a single transaction, or set it to a number of at least 50-100 to have transaction commits on chunks of rows. This minimizes the overhead associated with transaction commits.
* Set the "Parameter array size" to a number that is at least 1024 divided by the total number of bytes per row to be written (e.g., set it to 10 or greater for 100-byte rows). This tells DataStage to pass that many rows in each ODBC request. This "array-binding" capability requires an ODBC driver that supports the "SQLParamOptions" (ODBC API Extension level 2) capability.
If the target database is remote Oracle, use the ORABULK stage to create the necessary data and control files for running the Oracle SQL*LOAD utility. Use an AFTER routine on the ORABULK stage to automate the copying of the data and control files to the target machine and start SQL*LOAD. To maximize loading performance, use either the structure of the DataStage job or the ORABULK stage to create multiple data files and then use the Oracle SQL*LOAD "PARALLEL" option to perform loading in parallel. Use the SQL*LOAD "DIRECT" option (if available and configured on the Oracle database) to bypass SQL for even greater loading performance.
When using an ODBC stage to populate a warehouse target table, use straight INSERT or UPDATE rather than other update actions. This allows target database array binding which is fast. Other options may require an attempt to UPDATE a row first, followed by an INSERT if the UPDATE fails.
When accessing UniVerse source data, use the HASH stage type rather than the ODBC stage type whenever you can. HASH stage access is somewhat faster than ODBC stage access. If you need the functionality of ODBC (for dynamic normalization/flattening), configure the UniVerse data source in the uvodbc.config file to have "DBMSTYPE = UNIVERSE" rather than "DBMSTYPE = ODBC". Also, if the UniVerse file/table is local, configure it in uvodbc.config with "HOST = localhost" rather than the explicit local host name. This allows UniVerse to perform SQL operations in the same process as the caller instead of spawning an extra server process.
Remote UniVerse operations are quite slow and should generally be avoided. If source data is in a remote UniVerse file, use a BEFORE routine to copy the file (e.g., via FTP) to the DataStage server machine, then run fnuxi if needed, and then access the local file copy from the DataStage job. Similarly, if the data target is a remote UniVerse file, create the file locally on the DataStage server machine and use an AFTER routine to copy the file to the target machine and run fnuxi if needed.
Avoid remote reference lookups on secondary inputs to Transformer stages. If you must perform such lookups on every data row (e.g., for validation and transformation), arrange to copy the reference data into a local HASHed file (e.g., via a "BEFORE" exit routine or another ODBC=>HASH job) first, and then use the local HASHed file for reference lookups. You can request that the file/table being used for lookups be copied into memory. This works best when reference files fit into real memory and dont cause extra paging.).
If multiple CPUs are available on the DataStage server machine, split the work into multiple parallel paths within one job, or into multiple jobs.
(Note: a parallel path travels from a passive stage (SEQ, HASH or ODBC) through independent active stages to another passive stage. If two paths share any active stage, they are not parallel, and are performed in a single process and cannot take advantage of multiple CPUs.)
Perform aggregation as early as possible in a job. This minimizes the amount of data that needs to be processed in later job stages.
Aggregation is faster if the input data is sorted on some or all of the columns to be grouped.
Avoid intermediate files where possible, since this adds considerable disk I/O to the job.
Avoid validation and transformation that is not really needed. Every item of work done on a column value in a row adds per-row overhead. If you have control over the quality of the arriving data, then do not validate things that will always be true.
Move large data files/tables in bulk where possible, instead of using remote database access. Use a "BEFORE" routine to copy source data into a local file or table.
If the warehouse target tables are in UniVerse, try to write them using a HASH stage rather than an ODBC stage. HASH stage writes are somewhat faster than SQL INSERTs. If the ultimate target warehouse file/table is a remote UniVerse file (not table), consider writing to a local UniVerse file (on the DataStage server machine) and then using an "AFTER" routine to copy the file to the remote target warehouse machine and run fnuxi on it there. Transform Functions
* Avoid writing custom transform functions unless you must. If possible, use BASIC expressions and built-in functions as much as possible.
For example, let us say that an input data row contains a separate DATE field in "mm/dd/yy" format and a TIME field in "hh:mm:ss" format. The output row to the target database needs a single TIMESTAMP field, which requires a string of the form "yyyy-mm-dd hh:mm:ss". Rather than define a custom transform function, use the following derivation expression:
OCONV(ICONV(Input.DATE,"D2/"),"D4-YMD[4,2,2]"):" ":Input.TIME
* When writing custom transform functions (e.g., in BASIC), avoid known slow BASIC operations (e.g., concatenation of new values to the beginning or end of a string, pattern matching). Use built-in BASIC functions and expressions as much as possible (because they are implemented efficiently) and avoid calling additional BASIC programs. Perform initialization into private named COMMON once to minimize per-row overhead. Keep in mind that every transform will be executed once per row.
* If multiple custom transforms are needed, do them if possible as separate transform functions instead of one function with switch arguments. This minimizes the code path on individual columns that need only one of the transforms.

DS - Environment Variables

What is an Environment Variable??
Basically Environment variable is predefined variable those we can use while creating DS job.We can set eithere as Project level or Job level.Once we set specific variable that variable will be availabe into the project/job.
We can also define new envrionment variable.For that we can got to DS Admin .

    OR

These are the variables used at the project or job level.We can use them to configure the job i.e., we can associate the configuration file(Wighout this u can not run ur job) increase the sequential or dataset read/ write buffer.

ex: $APT_CONFIG_FILE

Like above we have so many environment variables. go to job properties and click on Paramer tab then click on add environment variable to see most of the environment variables.

***************************
DataStage EE environment variables
The default environment variables settings are provided during the Datastage installation (common for all users).
Users have a few options to override the default settings with Datastage client applications:
# With Datastage Administrator - project-wide defaults for general environment variables, set per project in the Projects tab under Properties -> General Tab -> Environment...
# With Datastage Designer - settings at the job level in Job Properties
# With Datastage Director - settings per run, overrides all other settings and is very useful for testing and debuging.

The Datastage environment variables are grouped and each variable falls into one of categories.
Basically the default values set up during an installation are resonable and in most cases there is no need to modify them.

Environment variables overview

Listed below are only environment variables that are candidates to adjustment in real-life project deployments. Please refer to the datastage help for details on variables not listed here.
General variables
# LD_LIBRARY_PATH - specifies the location of dynamic libraries on Unix
# PATH - Unix shell search path
# TMPDIR - temporary directory
Parallel properties
# APT_CONFIG_FILE - the parallel job configuration file. It points to the active configuration file on the server. Please refer to Datastage EE configuration guide for more details on creating a config file.
# APT_DISABLE_COMBINATION - prevents operators (stages) from being combined into one process. Used mainly for benchmarks.
# APT_ORCHHOME - home path for parallel content.
# APT_STRING_PADCHAR - defines a pad character which is used when a varchar is converted to a fixed length string 

*******************************

Creating project specific environment variables-

Start up DataStage Administrator.- Choose the project and click the "Properties" button.- On the General tab click the "Environment..." button.- Click on the "User Defined" folder to see the list of job specific environment variables.
There are two types of variables - string and encrypted. If you create an encrypted environment variable it will appears as the string "*******" in the Administrator tool and will appears as junk text when saved to the DSParams file or when displayed in a job log. This provides robust security of the value..

Migrating Project Specific Job Parameters. It is possible to set or copy job specific environment variables directly to the DSParams file in the project directory. There is also a DSParams.keep file in this directory and if you make manual changes to the DSParams file you will find Administrator can roll back those changes to DSParams.keep. It is possible to copy project specific parameters between projects by overwriting the DSParams and DSParams.keep files. It may be safer to just replace the User Defined section of these files and not the General and Parallel sections.

Environment Variables as Job Parameters- Open up a job.- Go to Job Properties and move to the parameters tab.- Click on the "Add Environment Variables..." button.- Set the Default value of the new parameter to "$PROJDEF".When the job parameter is first created it has a default value the same as the Value entered in the Administrator. By changing this value to $PROJDEF you instruct DataStage to retrieve the latest Value for this variable at job run time.If you have an encrypted environment variable it should also be an encrypted job parameter. Set the value of these encrypted job parameters to $PROJDEF. You will need to type it in twice to the password entry box.Using Environment Variable Job ParametersThese job parameters are used just like normal parameters by adding them to stages in your job enclosed by the # symbol.
Database=#$DW_DB_NAME#Password=#$DW_DB_PASSWORD#File=#$PROJECT_PATH#/#SOURCE_DIR