locked
Comments Text Analytic RRS feed

  • Question

  • Please suggest a good approach to accomplish below target:
    Environment - SQL Server, SSIS

    Data Source: Multiple Survey comments from (same or different) users for different types of surveys.
    Target: Built text analytic dynamically based on date.

    Eg:
    Comment 1 - lazy brown fox jumps over lazy dog
    Comment 2 - lazy black dog jumps over lazy fox...

    Analytic
    Lazy - 4
    dog - 2
    fox - 2
    black - 1
    brown - 1

    Probably later we will also need to slice data by date, dept person belongs, geographic location of person, survey type etc..


    Friday, May 4, 2012 6:39 PM

Answers

  • You'll probably want to consider using the Term Extraction transform in an SSIS Data Flow in order to pull out the terms in those blocks of text. Then you can report on it. I'm tempted to move this to the SSIS forum, except that reporting on this data may lend itself to an Analysis Services model (this forum) for ad-hoc, slice and dice analysis like you described.

    http://artisconsulting.com/Blogs/GregGalloway

    • Marked as answer by Reddy2125 Monday, May 7, 2012 10:36 PM
    Saturday, May 5, 2012 2:24 AM

All replies

  • You'll probably want to consider using the Term Extraction transform in an SSIS Data Flow in order to pull out the terms in those blocks of text. Then you can report on it. I'm tempted to move this to the SSIS forum, except that reporting on this data may lend itself to an Analysis Services model (this forum) for ad-hoc, slice and dice analysis like you described.

    http://artisconsulting.com/Blogs/GregGalloway

    • Marked as answer by Reddy2125 Monday, May 7, 2012 10:36 PM
    Saturday, May 5, 2012 2:24 AM
  • Thanks a lot furmangg, that was almost half of the task. And term extraction transformation made it very simple and quick. http://www.packtpub.com/article/term-extraction-tasks-in-sql-server-integration-services

    I was preparing to create script task component to split sentence and then debating with the thought of noun, phrases, exclusions etc,

    I guess, reporting and slice&dice should be pretty much obvious now. But upper mgt wants us to build something that can be easily integrated with .net and sharepoint dashboard for reporting. Please share your thoughts for integrating a cube with .net and sharepoint applications. Can PerformancePointServer be an good approach??

    • Marked as answer by Reddy2125 Monday, May 7, 2012 10:36 PM
    • Unmarked as answer by Reddy2125 Monday, May 7, 2012 10:36 PM
    Saturday, May 5, 2012 12:52 PM
  • If you have a version of Analysis Services earlier than SQL 2012, I would build a simple cube off the resulting SQL tables created by the term extraction.
    http://technet.microsoft.com/en-us/magazine/ee677579.aspx

    If you have Analysis Services from SQL 2012, then you could consider a Tabular model which may be a bit easier for a first timer.
    http://www.bidn.com/blogs/DustinRyan/bidn-blog/2622/creating-your-first-tabular-model-part-1

    Once that's done, I would recommend using an Excel PivotTable to slice and dice the Analysis Services model. If you then want to share that report in SharePoint, you can use Excel Services.


    http://artisconsulting.com/Blogs/GregGalloway

    • Proposed as answer by Jerry Nee Monday, May 7, 2012 9:54 AM
    Saturday, May 5, 2012 1:41 PM