Sparse Table

In a sparse table (or view), a case is represented as multiple rows, with each row representing a property/value pair. For example, product purchase data may have the following schema: CustomerID, ProductID, Quantity. In this type of sparse table, there is one row for each product purchased by the user.

In general, a sparse table contains one row for each transaction, such as one row for each item ordered or each page visited. This table may have several rows for each user who visits your site, because one user may have ordered several items or visited several pages.

All of the rows associated with a single user comprise one case. The case identifier is typically some kind of user ID, such as a unique user or login name. The property is the item you want to predict, and the value is the quantity of that property.

Because the data needed for a sparse data table may be stored in two or more tables in your data source, you may need to use SQL Server Enterprise Manager to create a view that contains the columns required by your analysis model configuration. For example, to predict a purchase that is based on other products purchased, you need the user ID, product SKU, and quantity purchased columns. These columns may be located in two or three different linked tables (such as Customers, Orders, and Order Details, or Requisition and POLineItems) depending on the structure of your database.

The following figure shows the layout of a typical sparse table.

For information about building dense and sparse tables and creating analysis model configurations, see Predictor Schema.

See Also

Dense Table

Predictor Tables in the Data Warehouse

Copyright © 2005 Microsoft Corporation.
All rights reserved.