This tip shows you how to programmatically fetch the number of clusters discovered by the Microsoft_Clustering algorithm when you request auto-detection.

When you build a mining model using the Microsoft_Clustering algorithm, you can specify the number of clusters via the algorithm parameter CLUSTER_COUNT (set to 10 by default). You can also let the algorithm detect the number of clusters automatically by setting CLUSTER_COUNT to 0. In this case, you need to determine the number of clusters discovered by the model. This tip will demonstrate how to find the number of clusters by querying the mining model content.

As you might already know, the discovered content of a trained model can be retrieved either by fetching the MINING_MODEL_CONTENT schema rowset or by issuing the following DMX query:

SELECT * FROM [model name].CONTENT

If you browse through the tip "Layout of Clustering algorithm content", you will see that the root node in the content graph corresponds to the cluster model and its children are the nodes for the clusters themselves. This means that the cluster count is the CHILDREN_CARDINALITY of the root node, which always has a NODE_TYPE of 1. (Node types can be looked up here: [InstallDrive]:\Program Files\Microsoft SQL Server\90\SDK\Include\oledbdm.h.)

So all we need to do is modify the DMX query above to fetch just the CHILDREN_CARDINALITY column for the root node:

Disclaimer: SQLServerDataMining.com is currently managed by members of the SQL Server Data Mining development team at Microsoft Corporation. It does not represent Microsoft’s official position on its products or technologies. All content is provided “AS-IS” with no warranties, and confers no rights.