Running the Profile Data Import DTS Task

You use the Profile Data Import DTS task to import the following data into the Data Warehouse:

  • Profile data collected on your Web site and Commerce Server Business Desk with profile definitions
  • Site terms

When you import profile data into the Data Warehouse, the Profile data import task extends the schema of the Data Warehouse to accommodate any new profile definition properties marked as exported. For information about creating profile definitions and site terms, see Running the Profiles Resource.

To designate which profile properties will be imported into the Data Warehouse, you use the Profile Definition Designer module in Commerce Server Business Desk. For information about specifying profile data for import into the Data Warehouse, see Specifying Data to be Imported into the Data Warehouse.

Ee785075.note(en-US,CS.20).gif Notes

  • Strings that are stored as a global unique identifier (GUID) must begin and end with curly brackets “{” and “}”. This is the case with user_id, which is processed by the Profile data import task. Make sure you insert the beginning and ending curly bracket before writing an event to the Web log file.

    For example, your site must generate the GUID in the Commerce event of the type CEVT={T=ORD,EVT=SUBOR,ORID="fd5823ed-cac9-4b29-aa82-a18fe43b78e8"} as {fd5823ed-cac9-4b29-aa82-a18fe43b78e8}. The CSOLEDB provider does not convert the GUID if the required beginning and ending curly brackets are missing.

    The Retail Solution Site uses the MSCSGenID object to create a GUID with curly brackets, for example:

    Set Application("MSCSGenID") = oGetGenIDObject()
    Function oGetGenIDObject() Set oGetGenIDObject = Server.CreateObject("Commerce.GenID") End Function
    

    The Retail 2002 site uses the SYSTEM.GUID object, and then formats the string to add the GUID, for example:

    Accountmanager.cs string profileID = string.Format("{0}{1}{2}", '{', guidString, '}');
    public static string GenerateProfileID()
            {
                //
                // Create a guid. In .net this does not contain the curly brackets.
                //
                  Guid guid = Guid.NewGuid();
               //
               // The profile service takes ID's as strings (and stores them nvarchar not as uniqueidentifier)
              //
                  string guidString = guid.ToString();
              //
              // Add in the curly bracket.
              //
                string profileID = string.Format("{0}{1}{2}", '{', guidString, '}');
                return profileID;
            }
    
  • You must use the full load option and perform a full import the first time you import profile data into the Data Warehouse. After you have imported profile data, use the incremental load option to import only new data that was created since you last imported profile data.

  • If the Profiles server is clustered and fails over while the Profile data import DTS task is running there might be data loss. You must rerun the Profile data import DTS task in full load for the last increment by changing the import start date to the increment before the failover occurred. Rerunning the Profile data import DTS task in full load will restore any lost data.

  • This DTS task uses the maximum length of the string type when creating tables in the Data Warehouse. Because profiles can have strings that are a maximum of 4000 characters long, the table generated by the DTS task converts each char and varchar into a varchar(4000). It converts char to varchar because SQL Server optimizes on space for the varchar data type.

  • Use the Query Interval property to specify the time intervals in which profile data is imported into the Data Warehouse. This setting prevents locking the data source for the import. For example, if you plan to import data for 10,000 users, you can specify 15-minute intervals for user data import, so that your server is not overloaded.

  • If you have a large number of user profiles, and you are performing a full import, you can set the import start date in the DTS task to import only part of the user profiles. When you import profile data in full load, the start date will be 1/1/2000.

  • Oracle uses a different date format than SQL Server. If user profile data is stored in an Oracle database, you must set the following environment variable before running the Profile import data task:

    Set NLS_DATE_FORMAT = MM/DD/YYYY HH24:MI:SS

  • Use the following performance counters for this DTS task:

    • UPM: DTS Records Exported/sec
    • UPM: DTS Records Exported
    • UPM: Exported Errors

To import profile data for a single site into your Data Warehouse

To import profile data for all associated sites into your Data Warehouse

To import profile data for a single site into your Data Warehouse

  1. Run the Configuration synchronization DTS task to synchronize your site configuration with the Data Warehouse. For instructions, see Running the Configuration Synchronization DTS Task.

  2. Expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

  3. Right-click Data Transformation Services, and then click New Package.

    Alternatively, if you are changing an existing package, right-click Data Transformation Services, click All Tasks, and then select Open Package. In the Select File dialog box, click the package you want to change, and then click Open.

  4. On the Task menu, click Profile data import (Commerce Server).

  5. In the Profile Import Properties dialog box, do the following:

    Use this To do this
    Description Type a description for this import task.
    Operation level Select Site level from the drop-down list.
    Site name Select from the drop-down list the site that contains the data you want to import.
    Query interval (mins.) Type the time interval for the import. User data is imported in time intervals to prevent locking the database table. For example, a Web site with 10,000 users would import user data in 15-minute intervals.

    Setting a smaller value will lock the table for a shorter duration but will increase the number of queries against the table and therefore the time the task takes to run. Setting the value to zero will run a single query, locking the table for the entire duration of import, but might degrade site performance.

    Import start date Select from the drop-down calendar a date on which to begin your import.
    Incremental load Select this option to import only new users (based on a previous import).
    Full load Select this option to import all user data.
  6. In the Retry Properties section, do the following:

    Use this To do this
    Number of retry attempts Type the number of times you want the task to retry connection failures to the Data Warehouse. The default number of retry attempts is 10.
    Time between attempts (seconds) Type the amount of time you want the task to wait between retry attempts. The default time interval is 30 seconds.
  7. Click OK.

    Ee785075.note(en-US,CS.20).gif Note

    • You can add multiple DTS tasks to a package, add sequencing or workflow information, and then run all the tasks in the package.
  8. On the Package menu, click Execute.

    The Executing Package dialog box appears, showing the progress of your import. When the import process is complete, the Package Execution Results dialog box informs you that it completed successfully.

  9. Click OK, and then click Done.

    Profile data and site terms are imported into the Data Warehouse.

  10. Run the Report preparation DTS task to organize the imported data into the designated OLAP cubes. For instructions about running the Report preparation task, see Running the Report Preparation DTS Task.

This data is now available for reports.

To import profile data for all associated sites into your Data Warehouse

  1. Run the Configuration synchronization DTS task to synchronize your site configuration with the Data Warehouse. For instructions, see Running the Configuration Synchronization DTS Task.

  2. Expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

  3. Right-click Data Transformation Services, and then click New Package.

    Alternatively, if you are changing an existing package, right-click Data Transformation Services, click All Tasks, and then select Open Package. In the Select File dialog box, click the package you want to change, and then click Open.

  4. On the Task menu, click Profile data import (Commerce Server).

  5. In the Profile Import Properties dialog box, do the following:

    Use this To do this
    Description Type a description for this import task.
    Operation level Select Data Warehouse level from the drop-down list.
    Site name Select from the drop-down list the Data Warehouse associated with the sites for which you want to import profile data.
    Query interval (mins.) Type the time interval for the import. User data is imported in time intervals to prevent locking the database table. For example, a Web site with 10,000 users would import user data in 15-minute intervals.

    Setting a smaller value will lock the table for a shorter duration but will increase the number of queries against the table and therefore the time the task takes to run. Setting the value to zero will run a single query, locking the table for the entire duration of import but may degrade site performance.

    Import start date Select from the drop-down calendar a date on which to begin your import.
    Incremental load Select this option to import only new users (based on a previous import).
    Full load Select this option to import all user data.
  6. In the Retry Properties section, do the following:

    Use this To do this
    Number of retry attempts Type the number of times you want the task to retry connection failures to the Data Warehouse. The default number of retry attempts is 10.
    Time between attempts (seconds) Type the amount of time you want the task to wait between retry attempts. The default time interval is 30 seconds.
  7. Click OK.

    Ee785075.note(en-US,CS.20).gif Note

    • You can add multiple DTS tasks to a package, add sequencing or workflow information, and then run all the tasks in the package.
  8. On the Package menu, click Execute.

    The Executing Package dialog box appears, showing the progress of your import. When the import process is complete, the Package Execution Results dialog box informs you that it completed successfully.

  9. Click OK, and then click Done.

    Profile data and site terms are imported into the Data Warehouse.

  10. Run the Report preparation DTS task to organize the imported data into the designated OLAP cubes. For instructions about running the Report preparation task, see Running the Report Preparation DTS Task.

This data is now available for reports.

See Also

Running the Profile Data Import DTS Task

ETL Process for the Profile Data Import DTS Task

Scripting for the Profile Data Import DTS Task

Best Practices for Data Warehouse

Specifying Data to be Imported into the Data Warehouse

Workflow for Running the DTS Tasks

Troubleshooting the Data Warehouse Import Process

Copyright © 2005 Microsoft Corporation.
All rights reserved.