Monday, February 20, 2012

SSIS: How to Load search result from Search Engines?

This article is in continuation to my last post How to return search result in XML?

In case, you need to pull the search result in XML from the search engines, you can take the approach discussed in the above post.

I wanted to explore the same technique with SSIS. I created two tables– Table SearchXML stores the search key words we are searching for on internet. Table SearchXML_URL will store the searched key word and their respective URL(s) which it has retrieved from the Bing.

I have two search keywords in table SearchXML as shown in below screen.

In SSIS, I created following four variables ApplID, BingSearchURL, SearchKeyword and SourceType. These variables store the application id, URL for the Bing API search, Source type and Search keyword.

On the control flow, I added a Data Flow Task. Under Data Flow Task, I have following three components:

Read SearchXML Table for Search Keywords: This component is an OLE DB Source component and it reads the data from table SearchXML. It will reads the two keyword searches we have in the table (Chrome, Windows 8).

Return Search keyword in XML format: This is a script component. The script component is using the user defined variables. The script component has two output columns. XMLKeyWord and XMLURL. These columns will store the search keyword and the URL it returned from the Bing in XML format.

The code snippet inside the script component is following:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Xml;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
       string searchXMLURL;
        string strXMLURL, strXMLkeyword;

        public override void PreExecute()
        {
            base.PreExecute();
        }

        public override void PostExecute()
        {
            base.PostExecute();
        }

        public override void Input0_ProcessInputRow(Input0Buffer Row)
       {
        searchXMLURL = Variables.BingSearchURL + "?" + Variables.AppID + "&" + Variables.SearchKeyword + Row.SearchKeyword.ToString() + "&" + Variables.SourceType;
       XmlTextReader xmt = new XmlTextReader(searchXMLURL);
        while (xmt.Read())
        {
        if (xmt.Name == "web:DisplayUrl")
        {
        strXMLURL = xmt.ReadString();
        strXMLkeyword =Row.SearchKeyword.ToString();
        CreateNewOutputRows();
        }
        }
        }

        public override void CreateNewOutputRows()
        {
        Output0Buffer.AddRow();
        Output0Buffer.XMLKeyWord = strXMLkeyword;
        Output0Buffer.XMLURL = strXMLURL;
        }

       }

Insert in SearchXML_URL: This is an OLE DB Destination task and it simply stores the output of two columns (XMLKeyWord and XMLURL) into SQL table SearchXML_URL.

After setting up the components in SSIS, I run the SSIS package and it runs successfully and I got the live search results in my destination table.

Thanks for reading this post

Related Article:
Labels:

Saturday, February 18, 2012

What Food Items are included in Food Inflation Index?

Recently it was reported in the media that the Food Inflation has reduced in India. It was reported that it has gone into negative. I am not an economist or an expert but I learn that the people (at least the media, TV anchors etc.) consider it a positive thing.

I actually stopped for a second to think; what does it mean to me? Does it mean that I have to pay less for the food items? Or does it mean something has become cheaper in my sector market? Well, this is winter season in India; and the few things which I know has become cheaper are seasonal vegetables and fruits.

How the Inflation Index is measured?

The inflation is measured in the world by different benchmarks.

Countries like USA, Japan and UK uses CPI i.e. Consumer Price Index to measure the inflation. On the other hand countries like India use WPI i.e. Wholesale Price Index to calculate the inflation.

The second thing which prompted me to find out was what food items are included when the country calculates the Food Inflation?

A check on the Ministry of Commerce and Industry websites shows very interesting facts. The big brother Wholesale Price Index has three sisters, which it calculates.

1. Primary Articles
2. Fuel, Power, Light and Lubricants
3. Manufactured Products

The #1 sister (Primary Article) has three children’s. This sister has around 15 to 20% weightage on the Wholesale Price Index

1. Primary Articles
   a. Food Articles
   b. Non-Food Articles
   c. Minerals

The #2 sister (Fuel, Power, Light and Lubricants) has no children. This sister too has around 15 to 20% weightage on the Wholesale Price Index

The #3 sister (Manufactured Products) has twelve children’s. This is the most powerful sister as it has 60 to 70% weightage on the Wholesale Price Index.

…so coming back to my original interest that what food items are included in the Food Inflation calculation; here is the list of food items which have the privilege to impact our pockets and budgets

The list itself was very amazing; if you buy the “Mother Dairy” brand of milk you are buying something which can impact the country Food Inflation. But if you are buying Amul or Paras or Sudha brand of milk you are not impacting country food inflation. Similarly if you a vegetarian, you are not impacting the country Food Inflation bill but if you like to eat chicken, egg then you are buying something which can impact the Country’s Food Inflation

…Honestly, I do not understand all this but I personally feel that what the consumers can consume is the most powerful tool to measure such things. There are millions who do not have access to any such food item listed in the Food Inflation index.

Thanks for reading this post.

Reference: .

http://eaindustry.nic.in/report/ann4a.htm
Labels: ,

Wednesday, February 15, 2012

How to return search result in XML?

What does comes to your mind when you have to search something on Internet – Google, Yahoo, Bing, AOL etc. The contributions of these search giants are tremendous in our personal or professional life. It is easy to go and type your search keywords and find the information you are looking after. There is all kind of information available on internet; it is just how smartly you can search it.

In one of the project recently, I had a requirement to return the search result in XML. I have never tried this with Google, Yahoo, Bing, AOL etc. I tried to search for the internet and I concluded with following information’s, which I think can be beneficial for all of us who ever has such requirement.

Google:

I spend some time to find out with Google. Google does allow you to return search result in XML. Google Web Search services allow you to return search result in XML. It is just that you have to pay some yearly fee. I came across some post where it was stated that you have to pay yearly fee of $100 to get this facility from Google. You can check their Google Custom Search APIs and Tools

Yahoo:

I also spend some time to find out with Yahoo. I could not find any relevant information from Yahoo Developer Network (YDN). If I ever come across anything in this context from Yahoo; I will update this section.

Bing:

My search with Bing returns me the right information, I was looking for. With Bing you can return the search result in XML for free.

Here are the steps you need to take to return search result in XML.

Step 1: To return the search result in XML, you have to first of all identify yourself. You need to create an Application ID for yourself. You need to navigate to following URL to connect with Bing Developer.

http://www.bing.com/toolbox/bingdeveloper/

Step 2: You need to click on Create you AppId link. This will take you to the Bing Application API Dashboard. You need to click on Add button to create your application profile. You basically tell to the Bing that what is your source type(ex: web) and return type format(ex: XML SOAP, JSON etc) .

Once you submit the details; you will get an Application ID. An application Id is an alpha numeric string character. In my case, I got a 40 characters length Application Id.

Once you have the Application ID; you are all set. You simply need to navigate to the following URL in the correct format to return the search result in XML.

http://api.search.live.net/xml.aspx?Appid=&query=&sources=

I tried to search for Google browser Chrome with my Application ID and got the following.

The next thing, I did was to compare the XML result with normal result. I went to Bing and search for Chrome to find out.

The normal search result and XML search results were identical.

I hope you enjoy reading the post. Thanks

Labels:

Friday, February 10, 2012

"Cold Call" Vs "Warm Call"?

Recently I came across these two terms. I was reading a popular news paper and got stuck on these words - Cold calls and Warm Calls.

I tried to find these two words and was amaze to know about them. After knowing these two terms, I realized that on daily basis we get or receive Cold Calls and Warm Calls.

…So what exactly is Cold Call?

Cold calls are sales and marketing tools which are used worldwide to sale a product or service to a potential new customer. The difference between Cold calls and Warm calls are that, Cold calls are uninvited. The customer or the recipient of the Cold call is not aware before he/she faces the cold calls.

Do you remember the numerous calls you receive on daily basis where the person on the other side of the phone try to sale you credit card, insurance policy, Tax savings products etc. or simply try to sell his/here services? Yes, that call is a Cold Call. The basic nature of the Cold Calls are that they are uninvited, unwelcomed.

For some Cold Calls are a mean to buy in customers. Do you remember last time you fall prey to any such call? Cold Calls are totally banned in some countries. Countries like USA and Ireland have “Do not Call Lists”. If your contact # is listed in the Do not Call List, you will not get the Cold Calls. It is like a criminal offence to place a Cold call to the contact number listed in the Do not Call List?

Cold Calls are although not welcomed by the people, but these are very effective way to generate business or pull customer from the competitors.

…So what exactly is Warm Call?

Warm calls are sales and marketing tools which are used worldwide to sale a product or service to a potential customer. The difference between Cold calls and Warm calls are that Warms calls are pre-invited. The customer or the recipient of the Warm call is aware that he or she can get a call.

Just In case you need information on a product or service and you expect someone from the customer care of the companies to call you and provide information; then you are inviting a Warm call.

Do you remember the last time you visited the shopping mall and an executive approaches you with a small form…and you simply fill to…what did you just invited? A Cold call or a Warm Call?

Ok…let us say you got a Cold call and during the interaction you pass on your friends numbers as referrals. What you did? For the caller you gave him a Warm call contacts # and but for your friend you setup a Cold Call.

I hope you know the sales terminology for uninvited calls you face every day.

Thanks for the Reading!

Labels: , ,

Airtel-Sim Card # 8919 … what a hoax?

It was a weekday and as usual I was busy in my professional adventure. In other words I was in office and was busy with with work. I was so busy that I did not notice a miss call on my cell.

When I saw my cell, I was told that I have missed one call. I went to see the details that who the person is who called me? It was Unnamed and I saw this number +923346582857. Immediately I dialed this number without even noticing anything (what can I say my job nature is like this? I can get call from any corner in the world…).

To my surprise, the person on the other side of the call introduces himself as Rahul Verma. He told me that he is calling from Bandra, Mumbai. He told me that he is calling from Airtel Head office and asked me to confirm my Sim has 8919 numbers into it and disconnected it.

Immediately, I check my SIM and was amazed to find that it has number 8919 mentioned on it. Without thinking I called the same number to check what is the matter? The person who introduces himself as Rahul Verma in first call told me that I have won a cash prize of 10,0000/- INR in a lottery. After listening this I disconnected the cell because I was pretty sure this is a fraud.

The only thing which surprised me is that how did he know my SIM Card has 8919 numbers on it? I check it with Google and was shocked to see the complaints of similar nature. I came to know that 8919 is a common number on Airtel SIM cards. Also the phone number which begins with 92 ISD code came from Pakistan.

Well I do not know how this whole scam works but lesson learn from me was

1. 8919 is a common number in Airtel SIM card.

2. Before I dial any number I should at least check the ISD numbers. I have no friends, relatives or business partner in Pakistan.

I was glad that I did not fall prey to the scam but yes I lost some money in this deal.

…so moral of the story is beware of such frauds and do not think the world is so kind to give you something free.

Labels: ,

Friday, February 3, 2012

What is XMR Chart?

What is the one thing you desire most when you are dinning with family in a popular food joint? Quality or simply food quality, right? What is it that you look when you are buying a gazette or dress or anything for which you are paying from your pocket? Is it Quality? Yes, that is right…it is the quality that you look for in everything your buy or pursue.

How do you know or measure quality in the products or services you are delivering to the client? How do you know your processes are working as you expected? There are tools available to measure the qualities. There are Seven popular qualities tool available which are used worldwide to measure or observer the qualities in various industries such as IT, manufacturing, Publishing etc.

These 7 basic tools of quality are following:

1. Ishikawa diagram
2. Check sheet
3. Control chart
4. Histogram
5. Pareto chart
6. Scatter diagram
7. Flow chart

Today I learn about one of the quality tool called XMR chart. XMR stand for X – Individual and MR – Moving Range i.e. Individual and Moving Range Chart.

When XMR chart was invented?

The control chart concept was introduced to the world by Honorable Mr. Walter A. Shewart. He proposes this while working for Bell Labs.

So what exactly is XMR Chart?

XMR or I-MR chart is type of Control chart. This chart is used to observe or monitor one single data item. This single data item can be anything such as individual salesman productivity, an employee attendance or a production support team ticket resolution time etc. The single data item can be observed or monitored on weekly, monthly or yearly basis.

XMR is a type of Control Chart which is used for observing single data point. In other words XMR chart is use to observe one single item in a given time period or in a given data range.

The second important consideration about XMR chart is that data points are independent of each other. By observing one data point you cannot predict what the next data point will be. In other words by observing productivity of one day of an employee you cannot say what the productivity will be next day.

XMR Moving Range observes differences between data points. The range of the data points could be either the two (consecutive data points) or more. The simplest is to use range of two consecutive data points. This is called Moving Range of 2. For example, if you have an employee productivity data like following.

The moving range of 2 data points differences are shown in the above picture.

In case you are using range of more than two data points, then the minimum and maximum data point’s in the range are taken into calculation. For example if we have to calculate the moving range of 3 for the above data then our Moving range values will be like this.

The moving range of 3 took values 30, 28 and 27 and calculated the max (30) and min(27) values and their differences became the moving range values I.e. 3 for the data point.

In order to understand the Moving Range Chart we need to understand few terminologies which you will see in the chart.

Upper Control Limit: This is also called UCL. The UCL is the top or maximum limit set for the data point(s). If all the data points in a Moving range are below UCL, it is considered that the process or monitored item is under control.

To calculate the UCL following formula is used

Upper Control Limit = Average of data points + E * Average of moving range data points

Where E is constant which has a specific value. The value of E is calculated from the below chart. For example, If you are calculating the Moving Range of 2 then the value if E is 2.66.

Lower Control Limit: This is also called LCL. The LCL is the bottom or minimum limit set for the data point(s). If all the data points in a Moving range are above LCL, it is considered that the process or monitored item is under control.

To calculate the LCL following formula is used

Lower Control Limit = Average of data points - E * Average of moving range data points

Where E is constant which has a specific value.

If all the data points in a Moving range fall between UCL and LCL, it is considered that the process or monitored item is under control.

If there are data points in a Moving range which crosses the UCL or LCL boundaries, it is considered that that data points is an outlier and need to be investigated. We need to find out the root cause of what and why the data point is an outlier; what needs to be done to fix this?

Average of Moving Range: The average is calculated for all the individual data points and for all the moving range data points. The below image will clear this; we have calculated the Average of all individual data points and average of all moving range data points.

Once you have the UCL, LCL, Moving Range, Average calculated; you can lay down the Moving Range Chart. The MR charts of above data are following:

As you can see all the data points falls within the UCL and LCL; so we can conclude that the observed item(employee productivity) is under control. In case there are outlier we can review to find out the cause and come up with the resolutions we need to take.

I hope you like the article. Please let me know your opinion via comments.

Labels: , ,
Subscribe to: Posts (Atom)

Popular Posts

Real Time Web Analytics