Nested Tables
In Microsoft SQL Server 2005 Analysis Services (SSAS), data must be fed to a data mining algorithm as a series of cases that are contained within a case table. Not all cases can be described by a single row of data. For example, a case may be derived from two tables, one table that contains customer information and another table that contains customer purchases. A single customer in the customer table may have multiple purchases in the purchases table, which makes it difficult to describe the data using a single row. Analysis Services provides a unique method for handling these cases, by using nested tables. The concept of a nested table is demonstrated in the following illustration.
.gif "Two tables combined by using a nested table")
In this diagram, the first table, which is the parent table, contains information about customers, and associates a unique identifier for each customer. The second table, the child table, contains purchases for each customer. The purchases in the child table are related back to the parent table by the unique identifier, the CustomerKey column. The third table in the diagram shows the two tables combined.
A nested table is represented in the case table as a special column that has a data type of TABLE. For any particular case row, this kind of column contains selected rows from the child table that pertain to the parent table.
In order to create a nested table, the two source tables must contain a defined relationship so that the items in one table can be related back to the other table. In Business Intelligence Development Studio, you can define this relationship within the data source view. For more information about how to define a relationship between two tables, see How to: Add, Delete, View, or Modify a Logical Relationship Using Data Source View Designer.
You can create nested tables programmatically by either using Data Mining Extensions (DMX) or Analysis Management Objects (AMO), or you can use the Data Mining Wizard and Data Mining Designer in Business Intelligence Development Studio.
For More Information: Data Mining Designer, Data Mining Wizard, CREATE MINING MODEL (DMX), CREATE MINING STRUCTURE (DMX)
See Also
Concepts
Data Mining Algorithms
Data Mining Concepts
Mining Structures (Analysis Services)
Using the Data Mining Tools
Working with Data Mining