Step 2: Input Metadata in the PredictorDataTables Table

You use the PredictorDataTables table to enter metadata about the new model configuration that you are about to create.

1. From the Tables list, right-click the PredictorDataTables table, point to Open Table, and then click Return all Rows.
2. In the SQL Server Enterprise Manager – [Data in Table ‘PredictorDataTables' in ‘PredictorDemo_dw' on ‘<server name>'] window, click the last row marked by *, and then do the following:

   Use this	To do this
   ModelCfgName	Type Demographics.
   TableName	Type UsersInfo. The TableName indicates the data source for the predictions. You can use one of the tables that you already have in the DemoPredictor_dw database, or create and use a view that can combine data from different tables for a more advanced scenario. You will create a new view based on the RegisteredUser table.
   Type	Type 0. The type can be 0, 1, or 2. Type 0 indicates the table to be a dense table. A dense table contains only one row for each unique identifier (for example, user id). This type is generally the case for a table containing user demographic data. Type 1 indicates the table to be a sparse table. A sparse table always contains three columns: one each for case id, property id, and property value. A sparse table typically contains multiple rows for a single case. Transactional data is often stored in this format, containing entries of the form ( for example, buyer id, product id, and number of products purchased). In this scenario, the same buyer id is likely to appear in more than one row. Type 2 indicates an attributes table, which will be discussed later in the lab.
   CaseColumn	Type UserID. A case column is used to identify the column containing the case id, which is usually the userid.
   PivotColumn	Leave this column blank. PivotColumn is used for sparse (type 1) models containing the property names.
   AggregateColumn	Leave this selection blank PivotColumn is used for sparse (type 1) models containing the property values.
   AggregateOperation	Leave this column blank. PivotColumn is used for sparse (type 1) models. AggregateOperation indicates the type of operation defined for the AggregateColumn. The AggregateOperation can be one of the following values: 0=SUM, 1=MAX, 2=MIN, 3=AVG, 4=COUNT. For example, if your table contains an aggregation column describing the number of a particular product purchased by a particular user, and you are interested in the total number of each product purchased by each user, then you should use the SUM (0) aggregation. If your table contains an aggregation column describing the price of the product purchased by a particular user, and you are interested in the total number of each product purchased by each user, then you should use the COUNT (4) aggregation.

3. Close the SQL Server Enterprise Manager – [Data in Table ‘PredictorDataTables' in ‘PredictorDemo_dw' on ‘<server name>'] window.

Note

• There are several restrictions on how tables can be used in a model configuration:
  • For a given model configuration, there must be at least one dense (type 0) or sparse (type 1) source data table.
  • Only one dense table is allowed, but there can be multiple sparse tables.
  • If there is one dense table, then every item (for example, user) in the table defines a case.
  • Items in sparse tables that do not appear in the dense table are not included in the analysis.
  • If a configuration contains only sparse tables, the table first mentioned is used to define the items in the case. Also, any given property should be referenced in only one table.

Copyright © 2005 Microsoft Corporation.
All rights reserved.