Informatica – Parent child hierarchy implementation (Unconnected Lookup)

This mapping looks to be simple but a lot of learning for a newbie like me in informatica. Let me share this interesting mapping  in this post.

The transformations needed are,
The source qualifier, two unconnected lookups, expression transformation and target(say a flat file)

Key points:

Here we take a Sequence number ID as SID value which acts as a surrogate key to refer to every row transaction.

Considering this, we make use of two lookup transformation, one to make reference to the parent SID value with an input of Customer SID value and the second lookup for a purpose to make a reference on the respective Customer ID value passing on the Customer SID.

The expression transformation ports(click on image for zoom),

Also to remember a key step on the expression transformation about the Variable settings, which depends on the number of levels or depth of the parent child relationship are to be required.

As example here we go for three levels of parent child hierarchy. So we define the expression on the Variables as “LKP.LKP_get_parentsid(Cust_SID)”, passing the Customer SID value and getting the Parent SID from the lookup.

The lookup ports (click on image for zoom),

The output ports similarly will lookup for the Customer ID –  ”LKP.LKP_get_parent_id (v_Parent_SID)” passing the repective variable ports.

How to obtain size of all tables in SQL server??

There is a stored procedure to retrieve the information on the creation and modification dates and on the size usage of the every table of a database. This takes the input as the table name.

The syntax for this as below,

EXEC sp_spaceused “db_table_name”

But to revieve the meta data information of every table of a single database we can make use of the iteration procedure ‘sp_msForEachTable’.

And so we can give the query as below which gives the metadata for every table of the selected database but as multiple table results and not as a single view.

EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?””
To achieve the result as a single view we can follow the following steps.

First create a temporary table,

CREATE TABLE temp_table
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

Then insert the result of the above defined procedures,

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?””

Now we can view the result of all the tables in a single view as and can drop the table after use to avoid use of disk space,

Select * from temp_table
DROP TABLE temp_table

So we can have the required result of all the meta details of the single database as a single shot view. Thanks for reading this post. Feel free to share your views and comments.

Copy Header with query result in SQL server

How to enable a select copy of headers?

Initially i was struggling to get this option done, and found it hard to type the headers manually where ever i copied my query results. So here i have the solution for you.

Its a simple flow of steps to follow to enable this to work. As usual on your SQL server just follow the below steps and you get your headers from the query result to be copied into your clipboard for a paste to any where.

Select the Tools menu on the top of your window. Then select the Options from tools menu.

Now appears the Options window, where on the left we have the menu Query Results -> SQL Server -> Results to Grid.

Click the ‘Results to Grid’ option and on right pane of the Options window we have the option ‘Include column headers when copying or saving the results’.
Just check this option and we are done, to enable the copying of headers along with the query result datas.

Thanks for reading tis post. Feel free to share your comments and views on the same. Have fun learning

Informatica $$Parameters and their scopes

Important Points:

The parameters are detected by the Informatica IS with a $$ and takes the parameter name from the $$ till the occurrence of the ‘=’ symbol, anything between $$ and = shall be considered as the parameter name.

Similarly any value from the ‘=’ till the end of the line shall be considered as the value to the respective parameter.

The parameter files are saved with an extension .prm

There are different levels of precedence of the parameters,

The mapping level precedence

This is the least precedence level, defined as initial value in the mapping designer itself.

The Global precedence:

These are the parameters defined in the whole workflow’s parameter files, directly as parameters.

Example:

[gloabal]

$$Param_test=2011

The Session level precedence:

The session level parameters are defined in the workflow’s parameter file with the session name, this has the precedence over the global parameters.

Example:

$$Param_test=2011

[s_sample_session_name]

$$Param_test=2012

So here in the session ‘s_sample_session_name’, the ‘’$$Param_test” holds the value 2012 and not the 2011 for the higher precedence by the session level parameter.

The Session Param file precedence:

There can also be a separate parameter file that can be defined for a session, where the parameter value defined in this param file takes the higher precedence than the one defined in the workflow’s param file.

The Path defined parameter precedence:

There can be situations where a reusable session used in two different worklets can need two different param values at two instances. This can be very well achieved by indicating the specific session name through its worklet as in the below example.

Example:

If s_session_test1 is used in WKLT_workletA and WKLT_workletB with two different values, then we can define the parameter value as,

[FLD_folder_name.WF:wf_workflow_name.WT:WKLT_workletA.S:s_session_test1]

$$Param_name=2011

[FLD_folder_name.WF:wf_workflow_name.WT:WKLT_workletB.S:s_session_test1]

$$Param_name=2012

So the same session uses the same parameter, but with different values at different worklets.

Let ur screen freeze in snow!!

This was interesting, today when i was at office my senior just showed me
this funny feature that google has added as one of its creative crisps.

Just like to share it with all of you. Its interesting and really good to
give a try. Just get to the google search page and type this text “Let it
snow” hope you enjoy the aftermath.

Just it gives an awesome search page with the snow coming down and
gradually our monitors freezing down, this is really cool. This desrves
appreciation and definitely something different than the usual google
doodles that google has been doing day out and day in.

Thanks for reading, share it with others

SQL Query to retrieve table size (SQL Server)

Hello all, its been months since i have written a blog. Its never late than never. So let me start it right away with a post on SQL. This is my first post on SQL and hope you all find it useful.

I am today giving a post on getting the size of the tables in your database. Its very simple and we have a system defined procedure that makes your job easier. The syntax to get this is,

exec sp_spaceused ‘TABLE NAME’

You shall get the metdata information on your table as you query the above.

Lets try to get the size information on all the tables of your whole DB. Wondering how? This is even simpler, as usual to make the job easy we have got a time saving procedure to do it for us. Lets make a query to get the size of all tables,

EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

The question mark replaces the table names of our DB. This query returns mutiple tables of the metadata like size of every table in our database. To get a view of the metadata as a single table the following set of query can be run together,

/* CREATE A TABLE TO STORE THE INFORMATION */

CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

/*INSERTING THE INFORMATION RETRIEVED TO THE TABLE*/

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

/*VIEW THE INFORMATION FROM THE TABLE & DROPPING THE TABLE*/

SELECT * FROM #t

DROP TABLE #t /*OPTIONAL TO DROP*/

Hope this post was useful to you all. Thanks and feel free to comment and share your ideas.

Web Portfolio Interface

I have just come out with a simple, clean and elegant design to show case the portfolio of a multimedia studio.

Home Page

Content Page

Portfolio Page

Contact Page

Comments and feed backs are welcome.

India Pakistan Semi Final CWC2011 (A day to remember)

Now this is one of the historic moment for every cricketer and also the fans from both the nations, INDIA and PAKISTAN. The stage was set all at its best. The top most dignitaries from the two neighbouring countries where all set tyo witness a clash of the titans. One team being the top one from its group and highly energetic with the bunch of young players and the other one as the most favorite team of the tournament. Pressure equally mounting on both the teams with the extreme expectations grounding down under the skin of every player.

The day had started well with a clear sky and flying flags. The satdium was jam packed with a cheering crowd always on its feet to support their nation. What more can you expect?? The arch rivals were on a head on clash. A day i must note down is what i felt as a cricket loving fan.

India won the toss and elected to bat. With a great firing start from sehwag who had done all the damage in the first 5 overs and the wind had gone silent as the Sultan of Mulatn departed. Then things were very ordinary with the master going very tentatively. There were lots and lots of mis fields and catches dropped by the team Pakistan, they might have to pay for this and dropping Sachin can be the sign of your poor day. But stilll with some fabulous bowling by the new youngster “Wahab Riaz” taking the % wicket haul and assuring to keep team India at a decent score of 260. Not a mountain hard to climb, looks very much chaseable. A great clash is assured for sure.

The Pakistan too had their great start, with almost giving up no wicket and making sure they have a smooth start. Finally the opening cashes with Kamran perishing in the over 9, and PAK is at 44/1. Then there were no real partnerships were building up, wickets were clearly falling one after the other in the equal intervals of time.

Hold back, here comes the BOOM BOOM Afridi, with PAK 150/6 and a required run rate of about 8 and above things still can go the other way. Afridi was all looking to go down the track, and one bad over was all setting things a bit for Pakistan. And here again team India gets back with a magical delivery of Bhajji to make Afridi play over and get caught. NOw Misbah on the other all alone and pushing hard to play all the 50 overs and make sure he drives his team to the 261 target. This was as said in the commentry, now had become a match between the 11 Indian players and ! man from Pakistan, as they lost all the 9 wickets.

Things were getting worst for Pakistan, as it was getting closer and Required Rate going up up to climb the everest. It was then the need of 30 from 6 balls, a chase that never happened, but this is crciket. Though it did not ahppen today and so we won with the best bowling figures from our bowlers who were all criticized through out the tournament. Hard luck Pakistan, you guys were fabulous. Finally India makes it to the final and its the real clash there against Sri Lanka on the 2nd of April 2011, Saturday.

Was great to witness the whole match. Pakistan team, which was never considered as a favorite and were under rated with all the controvercies surrounding the whiole team but still the young dynamic team under the leadership of the Boom Boom Afridi made to the Semi Final after topping their group in league. Hard luck for the day but lot to cheer about and to go ahead with some real moments to cheer and celebrate.
Lets all cheer for the team Blue and its time we Bleed Blue

The ASP.net Master Page – How this works??

What is a master page? – a master page is the aspx file that comprises of the markup that is common throughout the whole website being built with the ASP.net. For instance, most of the websites we might have noticed having a common header, footer and in few cases even the left or right side blocks. In cases like these, we can make use of the concept named Master page.

How it works? – The master page is a aspx template built as a common usage for the other content pages of our website. All we need is a single master page, defining the common areas and its markup, and the other content pages giving the markup of only the content area that varies in each of the page. The below figure might give you a clear picture of how effective your master page concept might work.

How we refer the areas in master page? – all we do in a master page is to add the master page content and its markups, and leave the changing content area with a control called as the ContentPlaceHolder. Each and every content place holder might have a name to it, such taht we can make a rference to these while making up the content aspx pages.

Here is an illustration to make it simple for you-

Step1 (create a master page)

Go for a new web project from the files menu on the top of your Visual Studio. Then as is shown above, select ASp.net Web Site, and your Language to C# (or VB as per your comfort). As the IDE opens, go in the solution explorer window, and right click over the project name to find the option “Add new item..” which opens the below window.

From here select the Master Page, name it Site.master. The site.master comprises of the below markup code as below,

<%@ Master Language=”C#” AutoEventWireup=”true” CodeFile=”Site.master.cs” Inherits=”Site” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>

<head runat=”server”> <title>Untitled Page</title>

<asp:ContentPlaceHolder id=”head” runat=”server”> </asp:ContentPlaceHolder></head>

<body>

<form id=”form1″ runat=”server”>

<div>

<asp:ContentPlaceHolder id=”ContentPlaceHolder1″ runat=”server”>  </asp:ContentPlaceHolder>

</div> </form></body></html>

A ContentPlaceHolder control named ContentPlaceHolder1 – this ContentPlaceHolder control appears within the Web Form and serves as the region for the content page’s user interface.

A ContentPlaceHolder control named head – this ContentPlaceHolder control appears within the <head> server control and can be used to declaratively add content to the <head> element.

This is the basic master page layout. Lets try out an example, i have added the below markup code to my master page,

<form id=”form1″ runat=”server”> <div id=”topContent”> <a href=”Default.aspx”>Master Pages Tutorials</a> </div>  <div id=”mainContent”> <asp:ContentPlaceHolder id=”MainContent” runat=”server”> </asp:ContentPlaceHolder> </div>  <div id=”leftContent”> <h3>Lessons</h3>     <ul> <li>TODO</li> </ul>
<h3>News</h3>     <ul> <li>TODO</li> </ul> </div>  <div id=”footerContent”> <img src=”Images/PoweredByASPNET.gif” alt=”Powered by ASP.NET!” /> </div> </form>

Also have added a css stylesheet as style.css, this is added and shall discuss in another article later, but is simply done through the solution explorer and linked with the page at the header with the <link>, as <link href=”styles.css” rel=”stylesheet” type=”text/css” />.

I have added few css styles,

#topContent {    text-align: right;    background-color: #600;    color: White;    font-size: x-large;    text-decoration: none;    font-weight: bold;    padding: 10px;    height: 50px;}
#leftContent {    width: 230px;        padding: 10px;    font-size: 90%;    text-align: left;    position: absolute;    top: 60px;    margin-top: 10px;    left: 0px;}
#mainContent {    text-align: left;    margin-left: 250px;    padding: 10px;}
#footerContent {    text-align: center;    padding: 5px;}

Just we have got a sample master page ready.

Step2(Creating your content page)

As usual to add a new item, right click on to the explorer and we have the add item window, where we are to slect the Web Form, as is shown below.

Note: Do have a selection on the Select Master page check box.

then we get this window following by,

The default markup in the content page are as below,

<%@ Page Language=”C#” MasterPageFile=”/Site.master” AutoEventWireup=”true” CodeFile=”About.aspx.cs” Inherits=”About” Title=”Untitled Page” %>
<asp:Content ID=”Content1″ ContentPlaceHolderID=”head” Runat=”Server”></asp:Content><asp:Content ID=”Content2″ ContentPlaceHolderID=”MainContent” Runat=”Server”></asp:Content>

which lists down all the ContentPlaceHolder from the master page, such that we can add different content in different content web forms and tie them all to a single master page. This is simple, easier and less error prone method to manage a web alyout. All your feed backs are welcome.

Paper Fold & Overlapping Logo Style (Photoshop)

Its been long since i have got anything from my photoshop over here. So yesterday night felt a bit creative and so i went to try any logo kind of a stuff and therefore i got with this simple paper fold kind of effect and went on to get this logo below in two variants.

and this is the second one of the same concept,

Guys, let me know your comments and feed backs. Thanks.