Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Importing dynamic fields to Datawarehouse

Answered Importing dynamic fields to Datawarehouse

  • Monday, January 21, 2013 10:49 PM
     
     
    Sign In to Vote
    0

    Hello,

    I've been tasked to develop a Data warehouse with an ETL process that initially extracts lists from SharePoint into staging tables.

    The issue is that custom fields can be created within SharePoint lists.  These custom fields have to be added to the Data Warehouse.  I'm at a loss how I would add these custom fields to the DW.

    Please can anyone help.  Is there a way to automate this by adding the process to the ETL and create the custom fields within the physical DW schema?

    Any pointers would be great.  For now I'll keep looking.

    Many thanks

     

All Replies

  • Tuesday, January 22, 2013 12:50 AM
     
     
    Sign In to Vote
    0

    I don't understand what you're trying to make dynamic or automate.

    This is not something you would normally automate.   A SharePoint list is just like a table in an external system.  You would create a staging table and ETL process for each one.

    David

    David http://blogs.msdn.com/b/dbrowne/

     
  • Tuesday, January 22, 2013 1:57 PM
     
     
    Sign In to Vote
    0

    Hi David

    I should have clarified.  The custom fields will be created by users on the fly; on any given day there will be new fields and I want to add these custom fields to the data warehouse without any development work.  This is what I mean by dynamic and automate.

    There will be an ID field within SharePoint 2010 in the list that will be used as a reference.  However, the new field should be created as a column within the DW using whatever techniques are out there.

    There is a Staging area that already extracts lists, then the data will be cleaned and added to the Dimensional database.  I have to ensure new fields are added as well without manually adding columns to the DW.

    Can this be done?  if so, is there any advice on where to start.

    Thanks

     
  • Tuesday, January 22, 2013 4:26 PM
     
     
    Sign In to Vote
    0

    Would you like to automate the process of creating columns in the DW, or would you like to store the user-defined fields in some other data structure, like an XML document or Attribtue/Value pairs?

    Do users expect the fields to be visible on reports?

    David

    David http://blogs.msdn.com/b/dbrowne/

     
  • Wednesday, January 23, 2013 10:18 AM
     
     
    Sign In to Vote
    0

    Ideally I'd like the columns to be created in the DW and yes, they would be visible on reports.  In my opinion the latter could be tricky as I'd probably have to manually alter SSRS reports and the Cube(s).  I guess the main starting point is adding the custom fields as columns to the DW

    Paul

     
  • Wednesday, January 23, 2013 12:43 PM
     
     Answered
    Sign In to Vote
    0

    Then you'll be writing some code.  Every SharePoint list has an OData interface, as well as the older "Lists Web Service".  Both return XML that you can examine at runtime to make the schema modifications to your target table.  Don't use the WCF Data Services client, as generates design-time proxy types for the list.  Instead you'll need to work with the XML returned from the list directly.

    David

    David http://blogs.msdn.com/b/dbrowne/