Showing posts with label SSIS. Show all posts
Showing posts with label SSIS. Show all posts
Wednesday, September 15, 2010
Tuesday, September 14, 2010
Friday, August 27, 2010
TERM LOOKUP TRANSFORMATION
reference is taken from:-http://msdn.microsoft.com/en-us/library/ms137850(v=SQL.90).aspx
If a plural form of the noun or noun phrase exists in the reference table, the lookup matches only the plural form of the noun or noun phrase. For example, all instances of students would be counted separately from the instances of student.
If only the singular form of the word is found in the reference table, both the singular and the plural forms of the word or phrase are matched to the singular form. For example, if the lookup table contains student, and the transformation finds the words student andstudents, both words would be counted as a match for the lookup term student.
If the text in the input column is a lemmatized noun phrase, only the last word in the noun phrase is affected by normalization. For example, the lemmatized version of doctors appointments is doctors appointment.
When a lookup item contains terms that overlap in the reference set—that is, a sub-term is found in more than one reference record—the Term Lookup transformation returns only one lookup result. The following example shows the result when a lookup item contains an overlapping sub-term. The overlapping sub-term in this case is Windows, which is found within two reference terms. However, the transformation does not return two results, but returns only a single reference term, Microsoft Windows. The second reference term, Windows XP Home Edition SP1, is not returned.
The Term Lookup transformation can match nouns and noun phrases that contain special characters, and the data in the reference table may include these characters. The special characters are as follows: %, @, &, $, #, *, :, ;, ., , , !, ?, <, >, +, =, ^, ~, |, \, /, (, ), [, ], {, }, “, and ‘.
The Term Lookup transformation can only use a column that has either the DT_WSTR or the DT_NTEXT data type. If a column contains text, but does not have one of these data types, the Data Conversion transformation can add a column with the DT_WSTR or DT_NTEXT data type to the data flow and copy the column values to the new column. The output from the Data Conversion transformation can then be used as the input to the Term Lookup transformation.
The Term Lookup transformation input columns includes the InputColumnType property, which indicates the use of the column.InputColumnType can contain the following values:
The Term Lookup transformation adds two columns to the transformation output, named by default Term and Frequency. Term contains a term from the lookup table and Frequency contains the number of times the term in the reference table occurs in the input data set. These columns do not include the CustomLineageID property.
The lookup table must be a table in a SQL Server 2000, a SQL Server 2005, or an Access database. If the output of the Term Extraction transformation is saved to a table, this table can be used as the reference table, but other tables can also be used. Text in flat files, Excel workbooks or other sources must be imported to a SQL Server database or an Access database before you can use the Term Lookup transformation.
The Term Lookup transformation uses a separate OLE DB connection to connect to the reference table.
The Term Lookup transformation works in a fully precached mode. At run time, the Term Lookup transformation reads the terms from the reference table and stores them in its private memory before it processes any transformation input rows.
Because the terms in an input column row may repeat, the output of the Term Lookup transformation typically has more rows than the transformation input.
The transformation has one input and one output. It does not support error outputs.
Term Lookup Transformation
The Term Lookup transformation matches terms extracted from text in a transformation input column with terms in a reference table. It then counts the number of times a term in the lookup table occurs in the input data set, and writes the count together with the term from the reference table to columns in the transformation output. This transformation is useful for creating a custom word list based on the input text, complete with word frequency statistics.
Before the Term Lookup transformation performs a lookup, it extracts words from the text in an input column using the same method as the Term Extraction transformation:
The Term Lookup performs a lookup and returns a value using the following rules:
- Text is broken into sentences.
- Sentences are broken into words.
- Words are normalized.
The Term Lookup performs a lookup and returns a value using the following rules:
- If the transformation is configured to perform case-sensitive matches, matches that fail a case-sensitive comparison are discarded. For example, student and STUDENT are treated as separate words.
Note:
A non-capitalized word can be matched with a word that is capitalized at the beginning of a sentence. For example, the match between student and Student succeeds when Student is the first word in a sentence.
When a lookup item contains terms that overlap in the reference set—that is, a sub-term is found in more than one reference record—the Term Lookup transformation returns only one lookup result. The following example shows the result when a lookup item contains an overlapping sub-term. The overlapping sub-term in this case is Windows, which is found within two reference terms. However, the transformation does not return two results, but returns only a single reference term, Microsoft Windows. The second reference term, Windows XP Home Edition SP1, is not returned.
| Item | Value |
|---|---|
| Input term | Microsoft Windows XP Home Edition SP |
| Reference terms | Microsoft Windows, Windows XP Home Edition SP1 |
| Output | Microsoft Windows |
The Term Lookup transformation can only use a column that has either the DT_WSTR or the DT_NTEXT data type. If a column contains text, but does not have one of these data types, the Data Conversion transformation can add a column with the DT_WSTR or DT_NTEXT data type to the data flow and copy the column values to the new column. The output from the Data Conversion transformation can then be used as the input to the Term Lookup transformation.
The Term Lookup transformation input columns includes the InputColumnType property, which indicates the use of the column.InputColumnType can contain the following values:
- The value 0 indicates the column is passed through to the output only and is not used in the lookup.
- The value 1 indicates the column is used in the lookup only.
- The value 2 indicates the column is passed through to the output, and is also used in the lookup.
The Term Lookup transformation adds two columns to the transformation output, named by default Term and Frequency. Term contains a term from the lookup table and Frequency contains the number of times the term in the reference table occurs in the input data set. These columns do not include the CustomLineageID property.
The lookup table must be a table in a SQL Server 2000, a SQL Server 2005, or an Access database. If the output of the Term Extraction transformation is saved to a table, this table can be used as the reference table, but other tables can also be used. Text in flat files, Excel workbooks or other sources must be imported to a SQL Server database or an Access database before you can use the Term Lookup transformation.
The Term Lookup transformation uses a separate OLE DB connection to connect to the reference table.
The Term Lookup transformation works in a fully precached mode. At run time, the Term Lookup transformation reads the terms from the reference table and stores them in its private memory before it processes any transformation input rows.
Because the terms in an input column row may repeat, the output of the Term Lookup transformation typically has more rows than the transformation input.
The transformation has one input and one output. It does not support error outputs.
Thursday, August 26, 2010
TERM EXTRACTION
What does the Term Extraction Transformation do?
In simplest terms it can extract individual nouns and collections of nouns and adjectives from text (these are the “Terms”) and returns them with a frequency count or score.
Because it uses an internal dictionary to simplify terms to identify repeated elements – such as removing plurals – it only works for English text. The dictionary is not exposed and cannot be edited, nor can the component be pointed at a custom dictionary of your choosing, so like the Fuzzy Lookup it is a bit of a black box in terms of your ability to tweak its operation – the algorithms and dictionary are fixed – i’ll pick up some flaws with this later. The only real control you can have over the content of the output is the use of an Exclusion List, which allows you to feed a list of terms to ignore into the component.
he first thing to configure is the input column on the “Term Extraction” tab – this transformation accepts a single input column which must be either a Unicode Text Stream or Unicode String. In the example package i’ve simply used a Data Conversion task to convert my Non-Unicode input stream prior to the Term Extraction. You can also assign custom names to the Term and Score columns as well.
Next up is to specify your Exclusion list, if you are using one – this must be in the form of a single column in a table in either a SQL Server or Access Database.
The final page is the most important in terms of affecting the output. Term Type controls whether the component returns Nouns, Noun Phrases – or both. Score type controls whether the score returned is a simple count or the TFIDF – the Inverse Document Frequency – TFIDF of a Term T = (frequency of T) * log( (#rows in Input) / (#rows having T) ). I’m sure that’s a useful number to someone. Parameters sets the minimum frequency a term has to have before it will be output – obviously a setting of 1 would return every siingle noun and /or noun phrase found. Maximum length of term sets the maximum number of words in a term. Finally Options sets the case sensitivity of the search.
The Term Extraction Transformations’ dictionary limits
The problem with this component stems from its black box dictionary which limits how well it can handle data. As an example, despite it claiming to remove plurals, if you look in the results of the example package, both Commandment and Commandments appear as distinct terms. If you extend this to the real world – say, mining emails or web pages – misspellings are common, product names are often nonsensical from a dictionary point of view – and a custom dictionary would allow you to work around that. As it is you would end up having to fix it after extracting it.By adding a custom dictionary, or allowing it to be extended in the reverse of an exclusion table, this component would become more useful
When would you use the Term Extraction Transformation?
It should be applied to situations where you need to trawl through large amounts of (English) text data to pull out common terms. One use I attempted when learning SSIS was to try and emulate the Quackometer, a web based tool that tries to analyse web pages and determine if their content is valid science or junk science. I did this by pulling down the web pages as text, running them through the Term Extraction and then trying to detect common valid and junk science terms (and using an Exclusion list to remove common HTML terms). I never finished it but it remains a lurking project which may yet reappear on these pages.
In simplest terms it can extract individual nouns and collections of nouns and adjectives from text (these are the “Terms”) and returns them with a frequency count or score.
Because it uses an internal dictionary to simplify terms to identify repeated elements – such as removing plurals – it only works for English text. The dictionary is not exposed and cannot be edited, nor can the component be pointed at a custom dictionary of your choosing, so like the Fuzzy Lookup it is a bit of a black box in terms of your ability to tweak its operation – the algorithms and dictionary are fixed – i’ll pick up some flaws with this later. The only real control you can have over the content of the output is the use of an Exclusion List, which allows you to feed a list of terms to ignore into the component.
he first thing to configure is the input column on the “Term Extraction” tab – this transformation accepts a single input column which must be either a Unicode Text Stream or Unicode String. In the example package i’ve simply used a Data Conversion task to convert my Non-Unicode input stream prior to the Term Extraction. You can also assign custom names to the Term and Score columns as well.
Next up is to specify your Exclusion list, if you are using one – this must be in the form of a single column in a table in either a SQL Server or Access Database.
The final page is the most important in terms of affecting the output. Term Type controls whether the component returns Nouns, Noun Phrases – or both. Score type controls whether the score returned is a simple count or the TFIDF – the Inverse Document Frequency – TFIDF of a Term T = (frequency of T) * log( (#rows in Input) / (#rows having T) ). I’m sure that’s a useful number to someone. Parameters sets the minimum frequency a term has to have before it will be output – obviously a setting of 1 would return every siingle noun and /or noun phrase found. Maximum length of term sets the maximum number of words in a term. Finally Options sets the case sensitivity of the search.
The Term Extraction Transformations’ dictionary limits
The problem with this component stems from its black box dictionary which limits how well it can handle data. As an example, despite it claiming to remove plurals, if you look in the results of the example package, both Commandment and Commandments appear as distinct terms. If you extend this to the real world – say, mining emails or web pages – misspellings are common, product names are often nonsensical from a dictionary point of view – and a custom dictionary would allow you to work around that. As it is you would end up having to fix it after extracting it.By adding a custom dictionary, or allowing it to be extended in the reverse of an exclusion table, this component would become more useful
When would you use the Term Extraction Transformation?
It should be applied to situations where you need to trawl through large amounts of (English) text data to pull out common terms. One use I attempted when learning SSIS was to try and emulate the Quackometer, a web based tool that tries to analyse web pages and determine if their content is valid science or junk science. I did this by pulling down the web pages as text, running them through the Term Extraction and then trying to detect common valid and junk science terms (and using an Exclusion list to remove common HTML terms). I never finished it but it remains a lurking project which may yet reappear on these pages.
