tag:blogger.com,1999:blog-39602982877340190602024-11-06T02:43:31.138+00:00Data, Data Warehouse, Data Analysis, Data Science and beyond!Unknownnoreply@blogger.comBlogger34125tag:blogger.com,1999:blog-3960298287734019060.post-1186160383792360232020-06-06T10:01:00.004+01:002020-06-06T10:06:43.731+01:00

Hello there, welcome to my blog post. The reason behind this post is to practice R and R markdown for myself and share my work with the audience. I would make references to SQL/relational database commands while working with R. If you are not familiar with SQL then ignore SQL parts, this is just for easy understanding. Prerequisites: Install R R Studio (for ease otherwise R itself is

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-20096061119201204242020-05-10T15:33:00.004+01:002020-05-20T22:42:57.806+01:00

Loading multiple csv files in R which share same column format Scenario - We have number of csv files with identical column format and we would like to import all of this data into one data frames. Solution - following code can help upload multiple identical files into list of data frames in R and then into one big data frame: loadDF <- function(directory){ #set current working

Unknownnoreply@blogger.com0United States37.09024 -95.7128918.9898414388878471 -130.869141 65.190638561112152 -60.556641tag:blogger.com,1999:blog-3960298287734019060.post-28003867169676291272020-05-07T08:04:00.003+01:002020-05-09T09:52:02.670+01:00

If you are just starting with R then here is the list of some commands which you must master. These commands will immensely help you in this journey. Here they are: getwd() - This is the first command that you must learn. This will show you where is all your work going to be saved or a folder on your computer's disk from where you are working. you can execute this command on R console

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-41793745675330748222020-04-26T10:56:00.000+01:002020-04-26T21:06:13.692+01:00

Download R from https://cran.r-project.org/bin/macosx/ Select appropriate version. In this example we will use R-4.0.0.pkg When downloaded, put the package in your working directory or leave it in the download folder Check validity of the downloaded package by verifying checksum using shasum command: Open terminal and issue shasum -a 1 R-4.0.0.pkg, returned checksum code must match

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-40415792453382190632013-03-23T00:51:00.000+00:002020-06-04T07:31:07.924+01:00

IBM Change Data Capture previously known as DataMirror is a Data replication tool and the latest version comes with ETL capabilities as well. Main use of this tool is to move data from one database to another (within a database or between two databases). I would put my thoughts together here on CDC implemented on Windows environment on Oracle database. A mapping or unit created in CDC is

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-80171501451172161372011-06-07T23:06:00.000+01:002013-03-20T23:18:34.288+00:00

Tips explained in part 1 would definitely help one preparing for interview. Now following are few more tips to help friends around preparing for DataStage interviews: 1. Know Designer and Director Client - There could be questions in the interview which might not be relevant to implementation of a job or job design. It could be about various other features of the tool which you use for the

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-33975788283062999402011-06-06T23:07:00.002+01:002013-03-20T23:21:13.619+00:00

There are many friends who are trying to crack down DataStage interviews but somehow because of lack of real world project exposure; not able to make it through. The tips explained below are to help them so that they can be confident while perusing interview and prepare in a way that they are capable of handling any kind of question and answers them tactfully. Tip number one - Always remember

Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-3960298287734019060.post-65163231364528694202011-02-10T23:20:00.002+00:002020-07-30T07:59:59.000+01:00

Incremental loading in the DataStage can be performed by using the Change Data Capture stage. This is a stage which takes sorted input on the key fields and generates a change code which denotes the type of change. Based on change code required DMLs can be performed on target database. Following figure can explain the basic design of a CDC stage: To know more on data analysis, data science.

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-3261508317627091612011-01-31T22:31:00.002+00:002013-03-20T23:28:29.686+00:00

What is the reason behind following error: Fatal Error: File data set, file "{0}".; output of "ImportSP": the record is too big to fit in a block; Give this challenge a shot. Reply.

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-3960298287734019060.post-86022710800708209402011-01-29T17:03:00.001+00:002020-05-03T12:48:22.564+01:00

1. What is "Parallel Processing"? In simple words, parallel processing means executing the jobs on multiple processors. Performing ETL simultaneously. 2. What are the types of parallelism? There are only two types of parallelism pipeline and partition. 3. How does degree of parallelism determined? Degree of parallelism determined by Configuration file. 4. Can different configuration files

Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-3960298287734019060.post-32063066487287504362011-01-26T16:51:00.000+00:002020-06-04T07:32:28.127+01:00

Following is the list of DataStage String Functions Function Usage AlNum Cab be used to check if the given string has alphanumeric characters Alpha TRUE if string is completely alphabetic CompactWhiteSpace all consective whitespace will be reduced to single space Compare Compares two strings for sort ComparNoCase Compare two strings irrespective of Case in-sensitiveness

Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-3960298287734019060.post-1371929687795294992011-01-26T00:07:00.000+00:002020-06-04T07:33:08.356+01:00

Scenario: A column contains both numeric and alpha numeric values in it (see example below): Source column ABC 123 DEF 456 GHI 789 JKL 101 Challenge: Segregate these values into two columns one for numeric only and another one for alpha numeric values (see below) Numeric Only - Alpha Only 123 - ABC 456 - DEF 789 - GHI 101 - JKL Give this

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-3960298287734019060.post-67364158651406433222011-01-24T21:54:00.071+00:002020-07-30T07:58:39.513+01:00

How would you fetch just first and the last record from a sequential file without using operating system commands and where design should be platform independent. So following is one of the approach to address this problem: Following points explain the above figure. However, if you want to know more data analysis, data science then you can follow  1. Read sequential file

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-39485169879853948482011-01-22T08:11:00.000+00:002020-06-04T07:34:43.121+01:00

Sequence stage has a tab called trigger. This tab gives you a place to specify the expressions on the input links or in other words this tab provides a facility to control the behaviour of job activities inside sequences when executed one after another. For example we have two job A and Job B. Jobs B should always run once job A finishes successfully. There should not be any warnings in the

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-81523830491925595602011-01-20T10:22:00.002+00:002011-01-31T22:29:22.131+00:00

What is the use of following special values?1. $PROJDEF 2. $ENV 3. $UNSET Give this challenge a shot. Reply.

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-3960298287734019060.post-26537145297030153652011-01-16T18:11:00.000+00:002011-01-16T18:11:22.843+00:00

Array size is a property of Oracle Enterprise stage which helps to reduce the context switch between DataStage and Oracle database. We should keep this in mind that however it help us reducing the context switch but this can lead the metadata repository database toward having deadlocks (if the meta data repository is Sql Server). Having large array size would improve the performance of the job as

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-25818942029213496132011-01-15T10:38:00.001+00:002011-01-15T10:43:51.445+00:00

&PH& directory in the DataStage contain the files which get created during a job's execution. The two letters in the name of the directory hint to the word "phantom" which is the naming convention in DataStage for background processes. Every time a job runs, it generates these logs which contain the status of the different phantom processes generated during the run. There are two types of the

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-73755770315027418002011-01-13T22:44:00.000+00:002020-06-04T07:35:32.424+01:00

DataStage jobs can be exported into two ways, using GUI and using command prompt. To export jobs from GUI, go to Export --> DataStage Component menu or right click on the particular job to export. A pop up window named "Repository Window" will appear on the screen where all or selected jobs can be exported. This Repository window allows various options to choose from. These options are as

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-56304782967709808042011-01-13T08:59:00.003+00:002011-01-22T07:12:51.870+00:00

Scenario: We want to run a three stage parallel job in a 8 CPU SMP environment having at least two parallel nodes defined in the configuration file.Challange: How many minimum processes would this job make? No combining set on any stages. Give this challenge a shot. Reply.

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-3960298287734019060.post-83300238250643603342011-01-12T22:24:00.037+00:002020-06-04T07:36:13.037+01:00

What is Data Warehouse Data Warehouse is a relational database which is mainly used for analytical and decision making purpose. Good analysis and decision making can happen when there are different sets of data available. When I say different sets, I mean historical data to compare with. Mostly all Data Warehouses holds historical information in them. This historical information gets stored

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-19809490153633469902011-01-10T12:17:00.002+00:002011-01-15T10:42:58.479+00:00

While collecting data, if order of the records matter then which collection method should be used?Give this challenge a shot. Reply.

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-3960298287734019060.post-59288537136059517322011-01-08T10:56:00.000+00:002020-06-04T07:36:56.917+01:00

As the name suggest local container (LC) contains the stages locally in the job and shared container (SC) contains stage in the job as well as allow its usage to other jobs. Differences or qualities between these two types of containers can be listed as follows: Local Containers 1) Mainly used to ease the job design. 2) Piece of logic can be combined together and used inside a job. 3) Can

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-60439257374562634812011-01-06T22:42:00.001+00:002011-01-10T19:23:07.417+00:00

Scenario: Read a dataset and load a Oracle table using Oracle Enterprise Stage with "Load" option. The table to be loaded has a column named "X" and defined as VARCHAR2. Challenge: Column "X" in the dataset has some values in it with leading and trailing spaces and some with pure spaces. How would you make sure that while loading the data these spaces should not be removed or converted into NULL

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-3960298287734019060.post-23667638656291572522011-01-06T22:36:00.000+00:002011-01-06T22:36:16.300+00:00

Data profile is an interesting task. if you love data, you would enjoy doing data profiling. As the name suggest data profiling is to profile or analyse the data. Analyse it's organization, type, amount and all other aspects which data can have. There could be various reason behind the profiling. Following are just to name a few: 1) To check that the data is suitable for other business

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3960298287734019060.post-49811241132980458762011-01-05T14:17:00.004+00:002011-01-08T11:00:46.781+00:00

Scenario: We are reading an Oracle table using Oracle enterprise stage and putting data into a dataset. Challenge: What would happen if the column names of that table contains characters which are not supported by DataStage? Would this job fail or Run? If fail then why? If run then what would be the output? Give this challenge a shot. Reply.

Unknownnoreply@blogger.com1