Querying an Association Model (Analysis Services - Data Mining)

The following query returns basic metadata about the association model, Association, such as the name of the model, the database where the model is stored, and the number of child nodes in the model. This query uses a DMX content query to retrieve the metadata from the parent node of the model:

SELECT MODEL_CATALOG, MODEL_NAME, NODE_CAPTION, 
NODE_SUPPORT, [CHILDREN_CARDINALITY], NODE_DESCRIPTION
FROM Association.CONTENT
WHERE NODE_TYPE = 1

Example results:

For a definition of what these columns mean in an association model, see Mining Model Content for Association Models (Analysis Services - Data Mining).

Back to top

By querying the data mining schema rowset, you can find the same information that is returned in a DMX content query. However, the schema rowset provides some additional columns, such as the date that the model was last processed, the mining structure, and the name of the column used as the predictable attribute.

SELECT MODEL_CATALOG, MODEL_NAME, SERVICE_NAME, PREDICTION_ENTITY, 
MINING_STRUCTURE, LAST_PROCESSED
FROM $system.DMSCHEMA_MINING_MODELS
WHERE MODEL_NAME = 'Association'

Example results:

Back to top

The following query returns a single column that contains details about the parameter settings that were used when the model was created.

SELECT MINING_PARAMETERS 
from $system.DMSCHEMA_MINING_MODELS
WHERE MODEL_NAME = 'Association'

Example results:

MAXIMUM_ITEMSET_COUNT=200000,MAXIMUM_ITEMSET_SIZE=3,MAXIMUM_SUPPORT=1,MINIMUM_SUPPORT=9.40923449156529E-04,MINIMUM_IMPORTANCE=-999999999,MINIMUM_ITEMSET_SIZE=0,MINIMUM_PROBABILITY=0.4

The following query retrieves all of the itemsets, together with a nested table that lists the products included in each itemset. The NODE_NAME column contains the unique ID of the itemset within the model, whereas the NODE_CAPTION provides a text description of the items. In this example, the nested table is flattened, so that an itemset that contains two products generates two rows in the results. You can omit the FLATTENED keyword if your client supports hierarchical data.

SELECT FLATTENED NODE_NAME, NODE_CAPTION,
NODE_PROBABILITY, NODE_SUPPORT,
(SELECT ATTRIBUTE_NAME FROM NODE_DISTRIBUTION) as PurchasedProducts
FROM Association.CONTENT
WHERE NODE_TYPE = 7

Example results:

Back to top

This example demonstrates how to use some of the grouping and ordering functions that DMX provides by default. The query returns the top 10 itemsets when ordered by the support for each node. Note that you do not need to explicitly group the results, as you would in Transact-SQL; however, you can use only one aggregate function in each query.

SELECT TOP 10 (NODE_SUPPORT),NODE_NAME, NODE_CAPTION
FROM Association.CONTENT
WHERE NODE_TYPE = 7

Example results:

This example uses the Association model created in the Basic Data Mining Tutorial. It demonstrates how to create a prediction query that tells you what products to recommend to a customer who has purchased a particular product. This type of query, where you provide values to the model in a SELECT…UNION statement, is called a singleton query. Because the predictable model column that corresponds to the new values is a nested table, you must use one SELECT clause to map the new value to the nested table column, [Model], and another SELECT clause to map the nested table column to the case-level column, [v Assoc Seq Line Items]. Adding the keyword INCLUDE-STATISTICS to the query lets you see the probability and support for the recommendations.

SELECT PredictAssociation([Association].[vAssocSeqLineItems],INCLUDE_STATISTICS, 3)
FROM [Association]
NATURAL PREDICTION JOIN 
(SELECT
(SELECT 'Classic Vest' as [Model])
AS [v Assoc Seq Line Items])
AS t

Example results:

Back to top

Whereas rules are useful for generating recommendations, itemsets are more interesting for deeper analysis of the patterns in the data set. For example, if you were not satisfied with the recommendation that are returned by the previous sample query, you could examine other itemsets that contain Product A, to can get a better idea of whether Product A is an accessory that people tend to buy with all kinds of products, or whether A is strongly correlated with purchases of particular products. The easiest way to explore these relationships is by filtering the itemsets in the Microsoft Association Viewer; however, you can retrieve the same information with a query.

The following sample query returns all itemsets that include the Water Bottle item, including the single item Water Bottle.

SELECT TOP 100 FROM 
(
SELECT FLATTENED NODE_CAPTION, NODE_SUPPORT, 
(SELECT ATTRIBUTE_NAME from NODE_DISTRIBUTION
WHERE ATTRIBUTE_NAME = 'v Assoc Seq Line Items(Water Bottle)') as D
FROM Association.CONTENT
WHERE NODE_TYPE = 7
) AS Items
WHERE [D.ATTRIBUTE_NAME] <> NULL
ORDER BY NODE_SUPPORT DESC

Example results:

When you create query that includes a nested table, the query returns not only the nested table rows that match the criteria, but also all the rows from the outside or case table. Therefore, in this example we added a WHERE clause to eliminate the case table rows that have a null value for the target attribute name.