Fureter dans Analysis Services avec MODEL.CONTENT

Il y a des domaines, je le confesse, où Microsoft n’a pas été particulièrement clair sur le fonctionnement de ses produits. Il y en a un en revanche où on sent qu cette volonté de clarté a été particulièrement prioritaire, c’est le Data Mining sous AS. Et cette relative transparence est très utile pour le curieux qui souhaite comprendre le fonctionnement des modèles, voire le développeur qui veut donner plus de sens à ses prédictions.

C’est dans cette optique que se situe le stockage des modèles dans MODEL.CONTENT. Comme son nom l’indique, cette table (enfin le rowset qu’elle interroge pour être plus précis) comprend toutes les données d’un modèle, peu importe son type: prédicteurs d’un réseau bayésien, noeud d’un arbre…
C’est sur celle-ci que sont basés les beaux visuels de Visual Studio (ou de Visio 2007, cf le post précédent). Le gros avantage, c’est que cela va vous permettre en plus de faire de l’aide à la décision quantitative, de motiver vos prédictions en extrayant tout ou partie de cette information.

Comme dit auparavant, MODEL.CONTENT est en fait l’alias DMX du rowset de schéma DMSCHEMA_MINING_MODEL_CONTENT (qui fait partie des différents rowsets de SSAS, voir TechNet à ce sujet), où chaque modèle est décrit sous forme de noeuds (feuilles d’un arbre de décision, neurones d’un réseau de neurones, prédicteurs d’un réseau bayésien…)

Son contenu est le suivant:

  • MODEL_CATALOG: la base Analysis Services
  • MODEL_NAME: le nom du Mining Model
  • ATTRIBUTE_NAME: l’attribut du modèle décrit par la ligne
  • NODE_NAME et NODE_UNIQUE_NAME: le nom du noeud courant
  • NODE_TYPE: un id qui définit le type de node, par exemple 5 pour un Cluster, 8 pour une règle d’association…
  • NODE_CAPTION: son nom affichable
  • CHILDREN_CARDINALITY: son nombre de nodes filles
  • PARENT_UNIQUE_NAME: son nombre de parents
  • NODE_DESCRIPTION: une description
  • NODE_DISTRIBUTION: une table imbriquée qui décrit la distribution de données des cas de la node.
  • ainsi que des champs statistiques propres aux modèles (support, probabilité, règle…)

Le but n’est évidemment pas de tout détailler ici (la doc est faite pour ça) mais de montrer comment on peu s’en servir.

Prenons le cas d’un arbre de décision. Sa visualisation dans BIDS est la suivante (cliquez pour voir en version agrandie):


Et bien on peut parfaitement regénérer par exemple le trajet suivi par une prédiciton dans cet arbre en requêtant le MODEL.CONTENT.

Exécutons la requête DMX suivante:

SELECT
[ArbreEnquete6AvecRayons].[Reponse],
(PredictNodeId(ArbreEnquete6AvecRayons.Reponse)) as [Node]
From
[ArbreEnquete6AvecRayons]
NATURAL PREDICTION JOIN
(SELECT 64 AS [Nb Basket],
‘non’ AS [PRODUITS IMMATERIELS]) AS t

On se retrouve logiquement dans la node en haut à gauche dans le dessin, donc le modèle prédit « oui ». Vous remarquerez que j’ai récupéré le NodeId de la prédiction: on va donc pouvoir savoir le chemin emprunté…

Si depuis une base SQL je réalise une jointure en MODEL.CONTENT et la node récupérée, je peux récupérer l’id de la node courante et sa caption:

SELECT Node, Node_Caption
FROM
OPENROWSET
(
‘MSOLAP’,

‘Data Source=(local)\MONSQL2005;Initial Catalog=XXXX’,
SELECT [ArbreEnquete6AvecRayons].[Reponse],
(PredictNodeId(ArbreEnquete6AvecRayons.Reponse)) as [Node]
FROM [ArbreEnquete6AvecRayons]
NATURAL PREDICTION JOIN
(
SELECT 64 AS [Nb Basket],

 »non » AS [PRODUITS IMMATERIELS]
) AS T’
) AS T

INNER JOIN
OPENROWSET
(
‘MSOLAP’,

‘Data Source=(local)\MONSQL2005;Initial Catalog=XXXX’,
‘SELECT NODE_UNIQUE_NAME,NODE_CAPTION
FROM
[ArbreEnquete6AvecRayons].CONTENT’
) AS U

ON CONVERT(VARCHAR(MAX),T.NODE) = CONVERT(VARCHAR(MAX),U.NODE_UNIQUE_NAME)

Ce qui me donne:


J’ai bien récupéré la node qui m’intéresse. Le but maintenant serait de remonter la chaine via les PARENT_UNIQUE_NAME jusqu’à atteindre la racine de l’arbre.
C’est relativement aisé grâce aux CTE de SQL Server, qui permettent de faire des requêtes récursives, sous réserve de spécifier le point de départ (cas de base) et la condition de récursivité (auto jointure avec la CTE)

Ce qui donne dans notre cas:

WITH MyCTE(NodeId, NodeCaption, NodeParentId)
AS
(

–Prédiction
SELECT NODE_UNIQUE_NAME, NODE_CAPTION ,PARENT_UNIQUE_NAME
FROM
OPENROWSET
(
‘MSOLAP’,

‘Data Source=(local)\MONSQL2005;Initial Catalog=XXXX’,
SELECT [ArbreEnquete6AvecRayons].[Reponse],
(PredictNodeId(ArbreEnquete6AvecRayons.Reponse)) as [Node]
FROM [ArbreEnquete6AvecRayons]
NATURAL PREDICTION JOIN
(
SELECT 64 AS [Nb Basket],

 »non » AS [PRODUITS IMMATERIELS]
) AS T’
)
AS S

INNER JOIN
–Jointée avec la table content (cela constitue le cas de base)
OPENROWSET
(
‘MSOLAP’,

‘Data Source=(local)\MONSQL2005;Initial Catalog=XXXX’,
‘SELECT [NODE_UNIQUE_NAME], [NODE_CAPTION],[PARENT_UNIQUE_NAME]
FROM [ArbreEnquete6AvecRayons].CONTENT’
)
AS T

ON CONVERT(VARCHAR(MAX),S.NODE) = CONVERT(VARCHAR(MAX),T.NODE_UNIQUE_NAME)

UNION ALL

–Auto-Jointure qui réalise la récursivité

SELECT NODE_UNIQUE_NAME, NODE_CAPTION ,PARENT_UNIQUE_NAME
FROM
OPENROWSET
(
‘MSOLAP’,

‘Data Source=(local)\MONSQL2005;Initial Catalog=XXXX’,
‘SELECT [NODE_UNIQUE_NAME], [NODE_CAPTION],[PARENT_UNIQUE_NAME]
FROM [ArbreEnquete6AvecRayons].CONTENT’
)
AS U

INNER JOIN MyCTE V
ON CONVERT(VARCHAR(MAX),U.NODE_UNIQUE_NAME) = CONVERT(VARCHAR(MAX),V.NodeParentId)

)

–Requêtage de la CTE
SELECT NodeCaption FROM MyCTE WHERE NodeParentId IS NOT NULL


A vous de jouer avec une régression logistique (récupérer les nodes), linéaire (reconstituer l’équation…). Les possibilités sont infinies!

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s