Text Mining Transformations - csuohio.edu

1 464 Chapter 14nData Mining with SQL server Integration ServicesIn order to use analysis Services as a regular data source, you must performthe following steps:1. Add a new OLE DB data source transformation to your data Ensure that your OLE DB data source uses the Microsoft OLE DBProvider for analysis Services option (located under the .Net Provi-ders for OleDb collection of providers).3. Set the Data Access Mode property of your Data Source to SQLCom-mand, which allows you to type in your Type in an analysis Services query (such as the DMX statement inListing 14-6).5. Click the Parse Query button to ensure the validity of the you can use the new OLE DB data source just like any other datasource and apply any data flow Transformations on top of the results returnedby analysis Mining TransformationsThis section examines two particularly interesting data flow transformationsthat facilitate text Mining : Term Extraction and Term server Data Mining supports theTEXT data type, but that data typeis not enough to perform meaningful text analysis .

2 From the algorithm sperspective, columns having theTEXT data type are treated just like discretecolumns that have theLONG data type as a collection of various distinctstates, without any way to directly access the content of a text perform text Mining with SQL server Data Mining , you must firstbring the text to some form that can be consumed by the algorithms. Thesolution included in the product is to represent each piece of text as acollection of words and phrases, and perform data Mining based on the occur-rence of certain key words and phrases inside a certain document (andpossibly some frequency-related scores).

3 Therefore, a document is modeledvery similarly to a shopping basket that contains (or does not contain) certainitems (which happen to be key words and phrases).After each document is represented as a collection of key phrases, you canperform data Mining using one of the following model types:Classification models that use the key words and phrases nested table asinput to predict the class of a documentClustering models that find similar documents based on commonoccurrencesAssociation models that detect cross-correlations between key wordsand phrasesWorking with SSIS in Data Mining 465 The process of text Mining usually consists of at least the following threephases:1.

4 Build a dictionary of key words and phrases over a collection of repre-sentative documents. This task is usually accomplished using the TermExtraction Based on the dictionary, extract the list of significant key words andphrases for each document to be analyzed. This task is usually accom-plished using the Term Lookup Train Mining models on top of the transformed O T EIf you intend to perform predictions on the Mining models built inphase 3, you must convert any new document to the same representation (that is,run the Term Lookup transformation on new documents using the same dictionaryused in training the Mining model or models).

5 Term Extraction TransformationYou use the Term Extraction transformation to build a glossary of key wordsand phrases for a specific domain. This is usually the first step of a textmining project. The transformation applies to a pipeline that already containsone column with text data of typentextornvarchar. The purpose of thetransformation is to analyze this column and build a dictionary of key termsbased on its content. The output of the transformation is a table with asingle column. Extracting key terms is not a trivial task because it involvessophisticated techniques, such as word stemming and grammar parsing.

6 Thetransformation extracts nouns and noun phrases, such asdata can download the data used for the examples in this section This download containsthe text for 219 State of the Union addresses of Presidents between 1790and user interface of the Term Extraction transformation is quite sim-ple. In the first tab of the Term Extraction Transformation Editor shown inFigure 14-16 (Term Extraction), you must specify the textual column. You canalso name the output columns for key terms and their associated scores. Thescore is based on Term Frequency and Inverse Document Frequency (TFIDF).

7 This is a statistical technique used to evaluate how important a word isto a document. The importance increases proportionally to the number oftimes a word appears in the document, but it s offset by how common theword is in all of the documents in the Exclusion tab shown in Figure 14-17 provides the option to specifyinclusion and exclusion terms. You may already have a list ofpredefinedterms that must be included, as well as a list of terms that youdon t wantextracted. You can specify these two term lists in this Chapter 14nData Mining with SQL server Integration ServicesFigure 14-16 The Term Extraction tab in the Term Extraction Transformation EditorThe Advanced tab of the Term Extraction Transformation Editor (shownin Figure 14-18) also provides options for the terms.

8 For example, you canspecify that terms must be single words or noun phrases. In the case of a nounphrase, you can specify the maximum length. You can also choose the type ofscore to be computed (Frequency or TFIDF), as well as a minimum frequencythreshold and the maximum length (in words) of any single term (phrase). Inaddition, you can choose whether the term extraction shouldbe case-sensitiveor not (the default setting is case-insensitive).After you have configured the term extraction transformation, you shouldbind its output (in the pipeline) to a relational table destination that will storethe dictionary of terms.

9 The table will contain two columns:one contain-ing the terms and the other containing the score associated with each 14-19 shows the full transformation used to extract the terms fromthe State of the Union addresses. The Data Conversion transformation thatappears in the pipeline before the Term Extraction transformation convertsthe text from the original database format (ASCII text) to one of the formatssupported by Term Extraction (UNICODE text).Working with SSIS in Data Mining 467 Figure 14-17 The Exclusion tab in the Term Extraction Transformation EditorTerm Lookup TransformationAfter a dictionary is built using the Term Extraction transformation, eachdocument to be analyzed must be transformed to a collection of terms, basedon that dictionary.

10 The Term Lookup transformation is used to search for keyterms from the input textual column, based on a dictionary. The dictionaryis usually generated by the Term Extraction transformation. Because thedictionary is just a table, you can write SQL queries to modify the list byadding or removing terms when like the Term Extraction transformation, the Term Lookup transforma-tion requires the input text column in the pipeline to have thentextand/ornchardata types. The editor for the Term Lookup transformation isquitesimple. The first tab, Reference Table, is used for specifying the reference table(that is, a dictionary produced by the Term Extraction transformation), asshown in Figure Term Lookup tab shown in Figure 14-21 is used to specify the columnmapping (for mapping the input text column to the Term columndictionary).