Esecuzione di query su un modello di clustering (Analysis Services - Data mining)
Quando si crea una query su un modello di data mining, è possibile recuperare i metadati sul modello oppure creare una query contenuto che fornisca dettagli sui modelli individuati nell'analisi. In alternativa, è possibile creare una query di stima che utilizza i modelli nel modello per eseguire stime per i nuovi dati. Ogni tipo di query fornirà informazioni diverse. Ad esempio, una query contenuto potrebbe fornire dettagli aggiuntivi sui cluster trovati, mentre una query di stima potrebbe indicare a quale cluster è più probabile che appartenga un nuovo punto dati.
In questa sezione viene illustrato come creare query per i modelli basati sull'algoritmo Microsoft Clustering.
Query contenuto
Recupero di metadati del modello tramite DMX
Recupero di metadati del modello dal set di righe dello schema
Restituzione di un cluster o di un elenco di cluster
Restituzione di attributi per un cluster
Restituzione del profilo di un cluster tramite stored procedure di sistema
Individuazione dei fattori discriminanti per un cluster
Restituzione di case appartenenti a un cluster
Query di stima
Stima dei risultati da un modello di clustering
Determinazione dell'appartenenza al cluster
Restituzione di tutti i cluster possibili con probabilità e distanza
Ricerca di informazioni sul modello
Tutti i modelli di data mining espongono il contenuto appreso dall'algoritmo secondo uno schema standard, ovvero il set di righe dello schema del modello di data mining. È possibile creare query sul set di righe dello schema del modello di data mining tramite istruzioni DMX (Data Mining Extension). In SQL Server 2008 è anche possibile eseguire una query direttamente sui set di righe dello schema come tabelle di sistema.
Torna all'inizio
Esempio di query 1: Recupero di metadati del modello tramite DMX
Con la query seguente vengono restituiti i metadati di base sul modello di clustering TM_Clustering creato nell'Esercitazione di base sul data mining. I metadati disponibili nel nodo padre di un modello di clustering includono il nome del modello, il database in cui è archiviato e il numero di nodi figlio del modello. Questa query utilizza una query contenuto DMX per recuperare i metadati dal nodo padre del modello:
SELECT MODEL_CATALOG, MODEL_NAME, NODE_CAPTION,
NODE_SUPPORT, [CHILDREN_CARDINALITY], NODE_DESCRIPTION
FROM TM_Clustering.CONTENT
WHERE NODE_TYPE = 1
Nota
È necessario includere il nome della colonna CHILDREN_CARDINALITY tra parentesi quadre per distinguerlo dalla parola chiave riservata MDX con lo stesso nome.
Risultati dell'esempio:
MODEL_CATALOG |
TM_Clustering |
MODEL_NAME |
Adventure Works DW |
NODE_CAPTION |
Cluster Model |
NODE_SUPPORT |
12939 |
CHILDREN_CARDINALITY |
10 |
NODE_DESCRIPTION |
Tutti |
Per una definizione del significato di queste colonne in un modello di clustering, vedere Contenuto dei modelli di data mining per i modelli di clustering (Analysis Services - Data mining).
Torna all'inizio
Esempio di query 2: Recupero di metadati del modello dal set di righe dello schema
Se si esegue una query sul set di righe dello schema di data mining, è possibile trovare le stesse informazioni restituite in una query contenuto DMX. Tuttavia, il set di righe dello schema contiene alcune colonne aggiuntive, tra cui i parametri utilizzati durante la creazione del modello, la data e l'ora dell'ultima elaborazione del modello e il proprietario del modello.
Nell'esempio seguente vengono restituite le date di creazione, modifica e ultima elaborazione del modello. Vengono inoltre restituiti i parametri di clustering utilizzati per la compilazione del modello e la dimensione del set di training. Queste informazioni possono essere utili per documentare il modello o per determinare quali opzioni di clustering sono state utilizzate per creare un modello esistente.
SELECT MODEL_NAME, DATE_CREATED, LAST_PROCESSED, PREDICTION_ENTITY, MINING_PARAMETERS
from $system.DMSCHEMA_MINING_MODELS
WHERE MODEL_NAME = 'TM_Clustering'
Risultati dell'esempio:
MODEL_NAME |
TM_Clustering |
DATE_CREATED |
12/10/2007 19.42.51 |
LAST_PROCESSED |
12/10/2007 20.09.54 |
PREDICTION_ENTITY |
Bike Buyer |
MINING_PARAMETERS |
CLUSTER_COUNT=10, CLUSTER_SEED=0, CLUSTERING_METHOD=1, MAXIMUM_INPUT_ATTRIBUTES=255, MAXIMUM_STATES=100, MINIMUM_SUPPORT=1, MODELLING_CARDINALITY=10, SAMPLE_SIZE=50000, STOPPING_TOLERANCE=10 |
Torna all'inizio
Ricerca di informazioni sui cluster
Le query contenuto più utili sui modelli di clustering generalmente restituiscono lo stesso tipo di informazioni che è possibile esplorare utilizzando il Visualizzatore cluster, ovvero i profili e le caratteristiche del cluster e l'analisi discriminante tra cluster. In questa sezione vengono forniti esempi di query che recuperano queste informazioni.
Esempio di query 3: Restituzione di un cluster o di un elenco di cluster
Poiché tutti i cluster includono un nodo di tipo 5, è possibile recuperare facilmente un elenco dei cluster eseguendo una query sul contenuto del modello per individuare solo i nodi di tale tipo. È inoltre possibile filtrare i nodi restituiti per probabilità o per supporto, come illustrato in questo esempio.
SELECT NODE_NAME, NODE_CAPTION ,NODE_SUPPORT, NODE_DESCRIPTION
FROM TM_Clustering.CONTENT
WHERE NODE_TYPE = 5 AND NODE_SUPPORT > 1000
Risultati dell'esempio:
NODE_NAME |
002 |
NODE_CAPTION |
Cluster 2 |
NODE_SUPPORT |
1649 |
NODE_DESCRIPTION |
English Education=Graduate Degree , 32 <=Age <=48 , Number Cars Owned=0 , 35964.0771121808 <=Yearly Income <=97407.7163393957 , English Occupation=Professional , Commute Distance=2-5 Miles , Region=North America , Bike Buyer=1 , Number Children At Home=0 , Number Cars Owned=1 , Commute Distance=0-1 Miles , English Education=Bachelors , Total Children=1 , Number Children At Home=2 , English Occupation=Skilled Manual , Marital Status=S , Total Children=0 , House Owner Flag=0 , Gender=F , Total Children=2 , Region=Pacific |
Gli attributi che definiscono il cluster sono disponibili in due colonne del set di righe dello schema di data mining.
La colonna NODE_DESCRIPTION contiene un elenco delimitato da virgole di attributi. Si noti che l'elenco di attributi potrebbe essere abbreviato ai fini della visualizzazione.
La tabella nidificata nella colonna NODE_DISTRIBUTION contiene l'elenco completo di attributi per il cluster. Se il client non supporta set di righe gerarchici, è possibile restituire la tabella nidificata aggiungendo la parola chiave FLATTENED prima dell'elenco di colonne SELECT. Per ulteriori informazioni sull'utilizzo della parola chiave FLATTENED, vedere SELECT FROM <model>.CONTENT (DMX).
Torna all'inizio
Esempio di query 4: Restituzione di attributi per un cluster
Per ogni cluster, nel Visualizzatore cluster viene visualizzato un profilo con l'elenco degli attributi e dei relativi valori. Viene inoltre visualizzato un istogramma che indica la distribuzione dei valori per l'intera popolazione dei case nel modello. Se si esplora il modello nel visualizzatore, è possibile copiare facilmente l'istogramma dalla Legenda data mining e quindi incollarlo in Excel o in un documento di Word. È inoltre possibile utilizzare il riquadro Caratteristiche cluster del visualizzatore per confrontare graficamente gli attributi di cluster diversi.
Se tuttavia è necessario ottenere valori per più di un cluster alla volta, risulta più semplice eseguire una query sul modello. Ad esempio, quando si esplora il modello, si potrebbe notare che i primi due cluster differiscono tra loro in relazione a un attributo, ovvero Number Cars Owned. Pertanto, si desidera estrarre i valori per ogni cluster.
SELECT TOP 2 NODE_NAME,
(SELECT ATTRIBUTE_VALUE, [PROBABILITY] FROM NODE_DISTRIBUTION WHERE ATTRIBUTE_NAME = 'Number Cars Owned')
AS t
FROM [TM_Clustering].CONTENT
WHERE NODE_TYPE = 5
La prima riga del codice specifica che si desiderano solo i primi due cluster.
Nota
Per impostazione predefinita, i cluster sono ordinati per supporto. Pertanto, la colonna NODE_SUPPORT può essere omessa.
La seconda riga del codice aggiunge un'istruzione sub-SELECT che restituisce solo determinate colonne della colonna della tabella nidificata. Limita inoltre le righe della tabella nidificata a quelle correlate all'attributo di destinazione, Number Cars Owned. Per semplificare la visualizzazione, la tabella è associata a un alias.
Nota
La colonna della tabella nidificata, PROBABILITY, deve essere racchiusa tra parentesi quadre perché corrisponde anche al nome di una parola chiave riservata MDX.
Risultati dell'esempio:
NODE_NAME |
T.ATTRIBUTE_VALUE |
T.PROBABILITY |
|---|---|---|
001 |
2 |
0.829207754 |
001 |
1 |
0.109354156 |
001 |
3 |
0.034481552 |
001 |
4 |
0.013503302 |
001 |
0 |
0.013453236 |
001 |
Missing |
0 |
002 |
0 |
0.576980023 |
002 |
1 |
0.406623939 |
002 |
2 |
0.016380082 |
002 |
3 |
1,60E-05 |
002 |
4 |
0 |
002 |
Mancante |
0 |
Torna all'inizio
Esempio di query 5: Restituzione del profilo di un cluster tramite stored procedure di sistema
Come alternativa rapida, anziché scrivere query utilizzando DMX, è anche possibile chiamare le stored procedure di sistema utilizzate da Analysis Services per gestire i cluster. Nell'esempio seguente viene illustrato come utilizzare le stored procedure interne per restituire il profilo per un cluster con ID 002.
CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterProfiles('TM_Clustering", '002',0.0005
Analogamente, è possibile utilizzare una stored procedure di sistema per restituire le caratteristiche di un cluster specifico, come illustrato nell'esempio seguente:
CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterCharacteristics('TM_Clustering", '009',0.0005
Risultati dell'esempio:
Attributi |
Valori |
Frequenza |
Supporto |
|---|---|---|---|
Number Children at Home |
0 |
0.999999829076798 |
899 |
Region |
North America |
0.999852875241508 |
899 |
Total Children |
0 |
0.993860958572323 |
893 |
Nota
Le stored procedure di sistema per il data mining sono per uso interno e Microsoft si riserva il diritto di modificarle, se necessario. Per l'utilizzo in un ambiente di produzione, si consiglia di creare query utilizzando DMX, AMO o XMLA.
Torna all'inizio
Esempio di query 6: Individuazione dei fattori discriminanti per un cluster
La scheda Analisi discriminante tra cluster del Visualizzatore cluster consente di confrontare facilmente un cluster con un altro o con tutti i case rimanenti (il complemento del cluster).
La creazione di query per restituire queste informazioni può tuttavia essere complessa e potrebbe essere necessario eseguire un'elaborazione aggiuntiva sul client per archiviare i risultati temporanei e confrontare i risultati di due o più query. Come alternativa rapida, è possibile utilizzare le stored procedure di sistema.
Con la query seguente viene restituita una singola tabella che indica i principali fattori discriminanti tra i due cluster con ID nodo 009 e 007. Gli attributi con valori positivi prediligono il cluster 009, mentre quelli con valori negativi il cluster 007.
CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterDiscrimination('TM_Clustering','009','007',0.0005,true)
Risultati dell'esempio:
Attributi |
Valori |
Punteggio |
|---|---|---|
Region |
North America |
100 |
English Occupation |
Skilled Manual |
94.9003803898654 |
Region |
Europe |
-72.5041051379789 |
English Occupation |
Manual |
-69.6503163202722 |
Queste informazioni sono identiche a quelle presentate nel grafico del visualizzatore Analisi discriminante tra cluster se si seleziona Cluster 9 dal primo elenco a discesa e Cluster 7 dal secondo elenco a discesa. Per confrontare il cluster 9 con il relativo complemento, utilizzare la stringa vuota nel secondo parametro, come illustrato nell'esempio seguente:
CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterDiscrimination('TM_Clustering','009','',0.0005,true)
Nota
Le stored procedure di sistema per il data mining sono per uso interno e Microsoft si riserva il diritto di modificarle, se necessario. Per l'utilizzo in un ambiente di produzione, si consiglia di creare query utilizzando DMX, AMO o XMLA.
Torna all'inizio
Esempio d query 7: Restituzione di case appartenenti a un cluster
Se per il modello di data mining è stato abilitato il drill-through, è possibile creare query che restituiscono informazioni dettagliate sui case utilizzati nel modello. Inoltre, se il drill-through è stato abilitato per la struttura di data mining, è possibile includere le colonne della struttura sottostante utilizzando la funzione StructureColumn (DMX).
Nell'esempio seguente vengono restituite due colonne utilizzate nel modello, Age e Region, e un'altra colonna, First Name, che non è stata utilizzata nel modello. La query restituisce solo i case classificati in Cluster 1.
SELECT [Age], [Region], StructureColumn('First Name')
FROM [TM_Clustering].CASES
WHERE IsInNode('001')
Per restituire i case che appartengono a un cluster, è necessario conoscere l'ID del cluster. È possibile ottenere questo valore esplorando il modello in uno dei visualizzatori. In alternativa, è possibile rinominare un cluster per farvi riferimento in modo più semplice e in seguito utilizzare il nome al posto di un numero ID. Tenere presente, tuttavia, che i nomi assegnati a un cluster andranno persi se il modello viene rielaborato.
Torna all'inizio
Esecuzione di stime tramite il modello
Anche se il clustering viene solitamente utilizzato per descrivere e comprendere i dati, l'implementazione Microsoft consente anche di eseguire una stima sull'appartenenza al cluster e di restituire le probabilità associate alla stima. In questa sezione vengono forniti alcuni esempi su come creare query di stima sui modelli di clustering. È possibile eseguire stime per più case, specificando un'origine dati tabulare, oppure fornire nuovi valori contemporaneamente creando una query singleton. Per maggiore chiarezza, negli esempi di questa sezione vengono utilizzate tutte query singleton.
Per ulteriori informazioni sulla creazione di query di stima tramite DMX, vedere Creazione di query di stima DMX.
Per informazioni sulla compilazione di query di stima utilizzando gli strumenti grafici di BI Development Studio, vedere Procedure per la scheda Stima modello di data mining.
Torna all'inizio
Esempio di query 8: Stima dei risultati da un modello di clustering
Se il modello di clustering creato contiene un attributo stimabile, è possibile utilizzarlo per eseguire stime sui risultati. Tuttavia, il modello gestisce l'attributo stimabile in modo diverso a seconda che la colonna stimabile venga impostata su Predict o su PredictOnly. Se si imposta l'utilizzo della colonna su Predict, i valori relativi a tale attributo vengono aggiunti al modello di clustering e vengono visualizzati come attributi nel modello finito. Se invece si imposta l'utilizzo della colonna su PredictOnly, i valori non vengono utilizzati per creare cluster. Al contrario, al termine della modalità, l'algoritmo di clustering crea nuovi valori per l'attributo PredictOnly in base ai cluster a cui appartiene ogni case.
Con la query seguente viene fornito un nuovo case singolo al modello, in cui le uniche informazioni sul case sono l'età e il sesso. L'istruzione SELECT specifica la coppia attributo/valore stimabile di interesse, mentre la funzione PredictProbability (DMX) indica la probabilità che un case con tali attributi genererà il risultato di destinazione.
SELECT
[TM_Clustering].[Bike Buyer], PredictProbability([Bike Buyer],1)
FROM
[TM_Clustering]
NATURAL PREDICTION JOIN
(SELECT 40 AS [Age],
'F' AS [Gender]) AS t
Esempio dei risultati quando l'utilizzo è impostato su Predict:
Bike Buyer |
Expression |
|---|---|
1 |
0.592924735740338 |
Esempio dei risultati quando l'utilizzo è impostato su PredictOnly e il modello viene rielaborato:
Bike Buyer |
Expression |
|---|---|
1 |
0.55843544003102 |
In questo esempio la differenza nel modello non è significativa. Tuttavia, talvolta può essere importante individuare le differenze tra la distribuzione effettiva dei valori e le stime del modello. La funzione PredictCaseLikelihood (DMX) è utile in questo scenario, perché indica il livello di probabilità di un case, dato il modello.
Il numero restituito dalla funzione PredictCaseLikelihood è una probabilità e pertanto è sempre compreso tra 0 e 1, con il valore 0,5 che rappresenta il risultato casuale. Pertanto, un punteggio minore di 0,5 significa che il case stimato è improbabile, dato il modello, mentre un punteggio maggiore di 0,5 indica che è molto probabile ottenere un fit tra il case stimato e il modello.
Ad esempio, con la query seguente vengono restituiti due valori che caratterizzano il livello di probabilità di un nuovo case di esempio. Il valore non normalizzato rappresenta la probabilità dato il modello corrente. Quando si utilizza la parola chiave NORMALIZED, il punteggio di probabilità restituito dalla funzione viene adattato dividendo la "probabilità con il modello" per la "probabilità senza il modello".
SELECT
PredictCaseLikelihood(NORMALIZED) AS [NormalizedValue], PredictCaseLikelihood(NONNORMALIZED) AS [NonNormalizedValue]
FROM
[TM_Clustering_PredictOnly]
NATURAL PREDICTION JOIN
(SELECT 40 AS [Age],
'F' AS [Gender]) AS t
Risultati dell'esempio:
NormalizedValue |
NonNormalizedValue |
|---|---|
5,56438372679893E-11 |
8,65459953145182E-68 |
Si noti che i numeri di questi risultati sono espressi tramite la notazione scientifica.
Torna all'inizio
Esempio di query 9: Determinazione dell'appartenenza al cluster
In questo esempio vengono utilizzate la funzione Cluster (DMX) per restituire il cluster a cui è più probabile che appartenga il nuovo case e la funzione ClusterProbability (DMX) per restituire la probabilità di appartenenza a tale cluster.
SELECT Cluster(), ClusterProbability()
FROM
[TM_Clustering]
NATURAL PREDICTION JOIN
(SELECT 40 AS [Age],
'F' AS [Gender],
'S' AS [Marital Status]) AS t
Risultati dell'esempio:
$CLUSTER |
Expression |
|---|---|
Cluster 2 |
0.397918596951617 |
Nota Per impostazione predefinita, la funzione ClusterProbability restituisce la probabilità del cluster più probabile. Tuttavia, è possibile specificare un cluster diverso utilizzando la sintassi ClusterProbability('cluster name'). In questo caso, tenere presente che i risultati di ogni funzione di stima sono indipendenti dagli altri risultati. Pertanto, il punteggio di probabilità nella seconda colonna può fare riferimento a un cluster diverso rispetto a quello specificato nella prima colonna.
Torna all'inizio
Esempio di query 10: Restituzione di tutti i cluster possibili con probabilità e distanza
Nell'esempio precedente il punteggio di probabilità non è molto alto. Per determinare se è disponibile un cluster migliore, è possibile utilizzare la funzione PredictHistogram (DMX) insieme alla funzione Cluster (DMX) per restituire una tabella nidificata che includa tutti i cluster possibili, oltre alla probabilità che il nuovo case appartenga a ogni cluster. La parola chiave FLATTENED viene utilizzata per modificare il set di righe gerarchico in una tabella flat per semplificare la visualizzazione.
SELECT FLATTENED PredictHistogram(Cluster())
From
[TM_Clustering]
NATURAL PREDICTION JOIN
(SELECT 40 AS [Age],
'F' AS [Gender],
'S' AS [Marital Status])
Expression.$CLUSTER |
Expression.$DISTANCE |
Expression.$PROBABILITY |
|---|---|---|
Cluster 2 |
0.602081403048383 |
0.397918596951617 |
Cluster 10 |
0.719691686785675 |
0.280308313214325 |
Cluster 4 |
0.867772590378791 |
0.132227409621209 |
Cluster 5 |
0.931039872200985 |
0.0689601277990149 |
Cluster 3 |
0.942359230072167 |
0.0576407699278328 |
Cluster 6 |
0.958973668972756 |
0.0410263310272437 |
Cluster 7 |
0.979081275926724 |
0.0209187240732763 |
Cluster 1 |
0.999169044818624 |
0.000830955181376364 |
Cluster 9 |
0.999831227795894 |
0.000168772204105754 |
Cluster 8 |
1 |
0 |
Per impostazione predefinita, i risultati sono classificati per probabilità. I risultati indicano che, anche se la probabilità per Cluster 2 è relativamente bassa, Cluster 2 rappresenta comunque il miglior fit per il nuovo punto dati.
Nota La colonna aggiuntiva, $DISTANCE, rappresenta la distanza tra il punto dati e il cluster. Per impostazione predefinita, l'algoritmo Microsoft Clustering utilizza il clustering EM scalabile, che utilizza più cluster a ogni punto dati e classifica i cluster possibili. Tuttavia, se si crea il modello di clustering utilizzando l'algoritmo K-medie, è possibile assegnare un unico cluster a ogni punto dati e questa query restituisce solo una riga. Per interpretare i risultati della funzione PredictCaseLikelihood (DMX), è necessario comprendere queste differenze. Per ulteriori informazioni sulle differenze tra i clustering EM e K-medie, vedere Riferimento tecnico per l'algoritmo Microsoft Clustering.
Torna all'inizio
Elenco di funzioni
Tutti gli algoritmi Microsoft supportano un set comune di funzioni. Tuttavia, i modelli compilati utilizzando l'algoritmo Microsoft Clustering supportano le funzioni aggiuntive elencate nella tabella seguente.
|
Per un elenco delle funzioni comuni a tutti i tipi di modello Microsoft, vedere Guida di riferimento agli algoritmi (Analysis Services - Data mining). Per informazioni sulla sintassi di funzioni specifiche, vedere Guida di riferimento alle funzioni DMX (Data Mining Extensions).