Informatica Scheduler- how to schedule workflow to run daily - weekly- monthly at a specific time?

Informatica Scheduler- how to schedule workflow to run daily at a specific time?

See the screenshot below. You can access the scheduler from workflow manager, open the workflow->edit->schedule tab.Once the schedule is set, right click on the workflow and schedule the workflow. You should now the workflow scheduled in the workflow monitor. 

In the below screenshot the workflow is scheduled to run once daily at 15:15.

Easy steps to set encodings for table compression in Amazon redshift.

Easy steps to set encodings for table compression in Amazon redshift.

If you are loading data from s3 to redshift then you can use the copy command with COMPUPDATE ON option to automatically set the compression on the target based on the data that is getting loaded. Redshift will analyze some 200K and set the compression/encoding that is best for the data that is getting loaded.

If you are loading the data from other tables in redshift and need to explicity set the encodings then following the steps outlined below might help:

a) Load some sample set of records to the target table on redshift that you want to set the encoding/compressions on. Use Analyze compression <tablename> command to check the encodings that is suitable for this target table.

For example: We are loading data to the table employee below.

redshift> Analyze compression employee

table_name      column  Encoding
-----------------------------------
'Employee','employee_key','raw'
'Employee','employee_number','lzo'
'Employee','employee_type','runlength'
'Employee','employee_status','lzo'
'Employee','employee_source_code','lzo'
'Employee','employee_tier','lzo'
'Employee','enterprise_tier','lzo'
'Employee','serving_flag','runlength'
'Employee','employee_flag','lzo'
'Employee','account_update_date','delta32k'
'Employee','tenure_months','bytedict'
'Employee','creation_date','delta32k'
'Employee','category','bytedict'
'Employee','extended_flag','runlength'
'Employee','business_date','runlength'


b) New recreate the table employee on redshift with those encodings:



CREATE TABLE  EMPLOYEE
(
   employee_key                  INTEGER                       NOT NULL encode raw,
    employee_num         CHAR(11)                       NOT NULL encode lzo,
    employee_type   CHAR(1)                       NOT NULL encode runlength,
    employee_status    CHAR(1)                       NOT NULL encode lzo,
    employee_tier    CHAR(1)                       NOT NULL encode lzo,
     serving_flag           char(1)                          NOT NULL encode runlength,
    employee_flag            char(1)                         NOT NULL encode lzo,
    account_update_date           DATE                       NOT NULL  encode delta32k,
    tenure_months     INEGER                       NOT NULL encode bytedict,
    creation_date DATE NOT NULL  encode delta32k,
    category char(5) NOT NULL encode bytedict,
    extended_flag CHAR(1)                       NOT NULL encode runlength,
    business_date DATE                       NOT NULL  encode delta32k
  )
distkey (employee_key);

c) Now reload the data into the table and you should see the encodings applied for this table.


redshift> Analyze compression employee

Amazon Redshift - date manipulation functions

Amazon redshift- date manipulation functions?

--to select the current date and timestamp in redshift use:

select current_date
select current_timestamp

--to add months and convert date to integer use in redshift:

 select to_char(add_months(to_date(current_date, 'YYYY-MM-DD'), -1), 'YYYYMMDD')

Note: Do not use to_date(current_date, 'YYYYMMDD') since redshift has a bug with this function and handles some dates wrong. Atleast this is what I found from my experience.

--to add months and get first of last month:

select to_char(add_months(to_date(current_date, 'YYYY-MM'), -1), 'YYYYMM') ||'01'

--to add months and convert date to integer use:

select to_number(to_char(add_months(current_date, -1),'YYYYMMDD'),'99999999') --to get date id

select to_number(to_char(add_months(current_date, -1),'YYYYMM'),'999999') --to get month id
select date(current_date -  cast('1 month' as interval))

--to extract year part from the current date

 select extract (year from current_date)
 select date_trunc('year', current_date)
 select to_number(to_char(current_date,'YYYY'),'9999')  --integer format

--to extract day and month from the current date

 select extract (day from current_date)

--to get first and last day of the year:

select to_date((extract (year from current_date)) || '0101','YYYYMMDD')
select to_date((extract (year from current_date)) || '1231','YYYYMMDD')

--to get first of the month given a date in redshift use:

 select date_trunc('month', current_timestamp)

--to get last month end date

 select date_trunc('month', current_timestamp)-1

--to add 7 days to current date

select date(current_date +  cast('7 days' as interval))
select date(current_date +  cast('7 days' as interval))

--to get timestamp in a specific format

 select to_timestamp( current_timestamp, 'YYYY-MM-DD HH24:MI:SS')
 select to_timestamp( '2014-12-01 23:12:12', 'YYYY-MM-DD HH24:MI:SS')

--to add hours to a date

  select TO_CHAR(date((current_date +  cast('1 hour' as interval))),'YYYY-MM-DD')

--to converts the date 02 Dec 2011 into the default date format:

select to_date ('02 Dec 2011', 'DD Mon YYYY');

Joiner transformation in Informatica and types of joins supported

Joiner transformation in Informatica and types of joins supported

Informatica joiner transformation are good if you want to join data coming from different databases. If you are reading from the same database it is better to join all the tables using the SQ qualifier instead of joiner transformation for better performance. In the ports tab of the joiner transformation you can set which is the master table and detail table. On the condition tab you can specify the join condition. In the properties tab of the joiner transformation you can specify the type of join.

The Informatica joiner transformation supports the following four types of joins:

• Normal (Inner Join)
• Master Outer (Detail left outer join Master)
• Detail Outer (Master left outer join detail)
• Full Outer (

Normal join: This is equivalent of an inner join and hence selects only the rows from the master and detail tables that match.

SELECT * FROM master_table, detail_table WHERE
master.id = details.id

Master outer join: Does a left outer join of the detail table with the master table hence discards all the unmatched rows from the master table and keeps all the rows from the detail table.


SELECT * FROM detail_table LEFT OUTER JOIN master_table ON
(master_table.id = detail_table.id)


Detail outer join: Does a left outer join of the master table with the detail table hence discards all the unmatched rows from the
details table and keeps all the rows from the master table.

SELECT * FROM master_table LEFT OUTER JOIN details ON
(master_table.id = detail_table.id)


Full outer join: is a full outer join that keeps all rows of data from both the master
and detail sources.

SELECT * FROM maser_table FULL OUTER JOIN details_table ON
(master_table.id = detail_table.id)

How to set up and sync Fitbit Flex Activity Tracker and Review

How to set up and sync Fitbit Flex Activity Tracker? Review at end of this article.

1) Unbox the fitbit package. You should have the below parts in it.

2) Charge the flex chip (the small chip on the left side of the pic above) by inserting into cable that has USB plug at the end (the one in the top of the pic above) and connecting the cable to USB port of your computer. Also, you can connect it to USB charger that plugs into power slots. Once fully charged all the four light indicators on the chip turns on.

Creating fitbit account and the software

3) Now go to http://www.fitbit.com/setup and download the software for the flex model and install the software on your computer. If you have a model other then flex then download the software for that mode. 

4)  Open your Fitbit Connect application. You can set up your device and create a fitbit account by clicking on the Set up a New Fitbit Device link.

5)Click on the New to Fitbit link and then set up your account. You will use this account to login to fitbit.com and see all your activities.

6) After you have set up your account, you need to track your activity with fitbit. Insert the fitbit chip in your fitbit wristband. The device will start tracking your movement in terms of steps taken. Basically every time you move your hand, walk, run etc.

Syncing your fitbit

7) Now to see the activities tracked by your fitbit, you need to sync your fitbit device with your fitbit account. To do this, you insert the fitbit dongle to USB port on your computer.

8)  Start the fit bit connect application and click on the sync button. You need to have your fitbit wristband along with the chip close to the dongle for it to detect and read your activities from the tracker.

9) After sync is complete, click on Go to fitbit.com link or open fitbit.com and login using the account created in one of the steps above. This should take you into account dashboard that will show all the activities tracked in the day by hour.

 10) In your dashboard, you can see the number of steps and calories burnt in the day by hour.

11) That should be it. Sync your activities every now and then. Also, keep your fitbit tracker chip charged once every 2-3 days.

12) If you have android or Iphone, you can install the fitbit app and that app should take care of syncing your activities without having to use the dongle etc.

Review:
a) Easy to set up, sync and check the daily stats. A nice and informative dashboard that gives counts of steps, calories burnt and a graph that plots number of steps by time.
b) LED indicators to indicate the amount of battery power left.
c) A bit hard to wear because of the clip that is used to lock the strip. We have to press it hard for it lock. I would have liked if it was more like a normal watch band. May be it is designed this way so that it hugs the wrist tightly.
d) Comes with two wristbands of different sizes. 
e) Very light weight. Looks stylish.
f) Dongle helps to sync using normal PC. Apps available for Android and Iphone.

Infacmd command to enable, disable, and recyle Informatica service

Infacmd command to enable, disable, and recyle Informatica service 

To disable an Informatica service use the below command:(The variables starting with $ should be defined in your environment or in your script)

$INFA_HOME/server/bin/infacmd.sh isp disableService ­dn $DOMAIN_NAME ­un $DOMAIN_USER ­pd $DOMAIN_PASSWORD ­sn $Service ­mo stop

 To get status of an Informatica service use the below command:

 $INFA_HOME/server/bin/infacmd.sh getServiceStatus ­dn $DOMAIN_NAME ­un $DOMAIN_USER ­pd $DOMAIN_PASSWORD ­sn $Service


To enable an Informatica service use the below command:
 
$INFA_HOME/server/bin/infacmd.sh enableService ­dn $DOMAIN_NAME ­un $DOMAIN_USER ­pd $DOMAIN_PASSWORD ­sn $Service


To recycle Informatica service using Infacmd (command line) then you have disable and enable a service using the commands above.