[Jamie Mc Lennan] Comprendre le nommage des colonnes MDX en DMX

Astuce assez connue par les utilisateurs de DMX - inconnue de beaucoup donc! - mais sans laquelle je n'aurais jamais pu écrire sereinement le bout de code ci dessous. Celle-ci répond à la question suivante: comment deviner le nommage - barbare - des colonnes généré par la mise à plat du MDX, cela lorsque l'on prend un cube comme source de prediction join en DMX?

Tout simplement en faisant une requête comme ceci:

SELECT t.* FROM [MyModel] NATURAL PREDICTION JOIN ( [MyMDXQuery] ) AS t

Le moteur renvoie alors le résultat mis à plat de la requête MDX et le nom des colonnes généré :)

Voilà, merci à Jamie et son équipe d'inclure ce genre de trick dans le langage. Vous pouvez (re) lire son papier ici.

A bientôt!

Data Mining et OLAP: Scénario de Cross-Selling

Aux TechDays, il y a bientôt 6 mois, nous avions abordé avec Romuald Coutaud le scénario de Forecasting depuis un cube OLAP, avec insertion des prévisions dans le cube. J'ai eu l'idée d'un scénario alternatif de démonstration de la complémentarité du DataMining et de l'OLAP: l'utilisation d'algorithmes de Cross-Selling en compléments d'un outil d'analyse multidimensionnelle.

L'idée générale est d'ajouter via une action la potentialité pour l'opérateur de voir à la demande, depuis son frontal, pour chaque client une liste d'items à recommander, en passant par le mécanisme de Rowset actions et du DMX.

Pour l'exemple j'ai construit un tout petit cube: c'est un sous-set de celui d'AdventureWorks2008DW: seulement 3 dimensions: Customer, Product et Time (RPD 3 fois) sur la FactInternetSales dont seules les mesures OrderQuantity et SalesAmount on été créées.


Sur ce cube, j'ai construit un MiningModel d'Association Rules, basé sur Customer en Case et Product en Nested - le bridge N-N se faisant implicitement via le groupe de mesures lorsque l'on crée des modèles en source OLAP.


Les deux étapes à suivre sont donc:

1. Création d'une action sur les membres de la dimension Customer
   
2. Exécution d'une requête DMX contextuelle sur cette action, et renvoi en rowset des prédictions.

La requête DMX se décompose elle même en deux parties:

1. Appel de la fonction PredictAssociation qui prend en paramètre la table imbriquée à prédire (ici les futurs produits à acheter)
2. Jointure du modèle avec deux requêtes MDX: une pour récupérer le client (SHAPE) et une autre pour récupérer les produits achetés par celui-ci (APPEND), ces deux requêtes reliées logiquement par l'ID client (RELATE..ON).
   

Pour chaque client on obtient donc prédictions de produits intéressants (5 étant la valeur donnée à PredictAssociation).

SELECT FLATTENED PredictAssociation([Products],5) FROM [CrossSellingByCustomer_MAR] PREDICTION JOIN SHAPE { SELECT {[Measures].[Order Quantity]} ON 0, {[Customer].[Customer].&[28187]} ON 1 FROM [OLAPCrossSellingCube] } APPEND ( { SELECT {[Measures].[Order Quantity]} ON 0, NON EMPTY {[Customer].[Customer].&[28187]*[Product].[Product].[All].Children} ON ROWS FROM [OLAPCrossSellingCube] } RELATE [[Customer]].[Customer]].[Customer]].[MEMBER_CAPTION]]] TO [[Customer]].[Customer]].[Customer]].[MEMBER_CAPTION]]] ) AS n AS t ON [CrossSellingByCustomer_MAR].[Products].[Product] = t.n.[[Product]].[Product]].[Product]].[MEMBER_CAPTION]]]

Il ne reste plus qu'à créer une action de cube, de type Rowset (renvoi de lignes) qui prend en instruction une chaîne de caractères à envoyer au serveur Analysis Services.

Cette action est paramétrée en déclenchement sur les membres de la hiérarchie utilisateur de la dimension Client. Cela signifie que dans un client compatible (Excel, OWC...) tout clic droit sur un membre à ce niveau donnera un menu contextuel permettant de déclencher cette action, et donc d'afficher une boîte modale contenant les prédictions effectuées pour ce membre. Le statement déclenché pour ce faire sera notre instruction DMX, en remplaçant simplement les IDs du client - en dur ci dessus - par celui du membre courant dans la hiérarchie utilisateur, via l'appel à "CurrentMember.UniqueName" (MDX).

Une fois le cube déployé on peut voir que l'action est disponible lorsque l'on browse par la bonne hiérarchie...


Ce qui permet d'afficher les prédictions pour ce membre...


Cela marche évidemment aussi pour Excel:



Quelles sont les applications de ce genre de fonctionnalités? Cela permet de voir le cube non seulement comme outil d'analyse, mais aussi et surtout comme outil non plus passif mais actif pour des interlocuteurs fonctionnels.

Le cube permet d'analyser quelles ont été les ventes (OLAP simple) quelles seront les ventes (OLAP avec données forecastées, tel que montré aux TechDays) mais aussi d'optimiser le processus de vente, en proposant les bonnes offres aux bons prospects, via une méthode éprouvée, le tout de manière intégrée et facile à mettre en oeuvre.

Voilà, en espérant vous donner des idées.

A bientôt!

[SQL] Tester l'existence d'une table temporaire

Astuce connue, mais on me le demande tellement souvent que je me crois obligé de re-citer la méthode: il n'est pas possible d'utiliser EXISTS avec les objets temporaires, on a donc recours à la fonction OBJECT_ID, qui renvoie l'ID d'un object de la base s'il existe, et NULL sinon, on peut donc faire ce genre de script:

IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL
DROP TABLE #tmpTable

Merci encore à Christian Robert qui m'avait il m'a bien longtemps montré cette technique :)

A bientôt!

[SQL] PIVOT en T-SQL une instruction magique, même si pas très standard...

L'instruction PIVOT est l'amie des fans de Tableaux Croisés Dynamiques sous Excel. Elle est en fait assez méconnue - en dehors de nous autres Consultants BI - mais peut servir à faire pas mal de choses.

Le but de l'affaire est de transformer une colonne en ligne, dit autrement en dimension posée sur l'axe COLUMNS et un indicateur numérique en mesure. Par exemple imaginons que l'on possède la liste des ventes par vendeur à destination de chaque pays:

Vendeur	Pays	Nb Ventes
Michel	France	1000
Michel	Allemagne	500
André	Italie	2000
Gilles	France	1500

On peut vouloir exécuter tout un type de rapports, sous forme de TCD, qui ressemblent habituellement à ceux sortis d'un cube OLAP: voir la somme des ventes par vendeur (axe vendeur en ligne) et par géographie:

Vendeur/Géographie	France	Allemagne	Italie
Michel	1000	500	NULL
André	NULL	NULL	2000
Gilles	1500	NULL	NULL

Ou plus simplement la moyenne des volumes de ventes par pays:

Pays	France	Allemagne	Italie
Moy. Ventes	1250	500	2000

Tout cela peut se faire simplement avec l'instruction PIVOT. Celle ci traite les colonnes d'entrée en 3 catégories:

1. La mesure, qui dispose d'un mode d'aggrégation
2. La liste des membres d'une colonne a poser en dimension
3. La liste des colonnes conservées telles-quelles (si on en garde une seule, cela fait une autre dimension en lignes)
   

Pour la première opération on la méthode est donc celle-ci:

1. On récupère d'abord les valeurs nécessaires incluses dans une sous table
   
2. Puis on exécute l'instruction PIVOT qui spécifie une aggrégation pour la mesure choisie ainsi que les membres de la dimension à mettre en colonnes (i.e. à extraire des valeurs d'une colonne)
   
3. Enfin on fait un SELECT sur le tout, en séléctionnant les colonnes conservées comme dimension en ligne
   

SELECT Vendeur, [Allemagne], [France], [Italie] FROM ( SELECT Vendeur,Pays,NbVentes FROM dbo.TestPivot ) AS t PIVOT ( SUM(NbVentes) FOR Pays IN ([Allemagne],[France],[Italie]) ) AS p

Une méthode identique est utilisée pour le deuxième exemple, avec aucune colonne conservée comme dimension en ligne, et un mode d'aggréagation AVG pour la mesure.

SELECT [Allemagne],[France],[Italie] FROM ( SELECT Pays,NbVentes FROM dbo.TestPivot ) AS t PIVOT ( AVG(NbVentes) FOR Pays IN ([Allemagne],[France],[Italie]) ) AS p

L'inconvénient de tout cela est le côté fastidieux d'énumérer les membres pour la dimension à pivoter: j'ai donc écrit la PS "SmartPivot" qui se sert de curseurs et de DSQLL pour forger le PIVOT. Elle s'utilise comme cela pour les deux exemples, en spécificant juste les 3 types d'usage pour les colonnes concernées, et le mode d'aggrégation de la mesure:

EXEC [dbo].[SmartPivot] @TableName = N'dbo.TestPivot', @LabelColumn = N'Pays', @ValueColumn = N'NbVentes', @FixedColumns = N'Vendeur', @Aggregate = N'SUM' EXEC [dbo].[SmartPivot] @TableName = N'dbo.TestPivot', @LabelColumn = N'Pays', @ValueColumn = N'NbVentes', @FixedColumns = N'', @Aggregate = N'AVG'

La PS SmartPivot est téléchargeable ici sur mon SkyDrive.

A bientôt!

[SSAS] Rowset Action et SQL, une procédure pour les exécuter toutes (les requêtes)!

Il est écrit dans Books Online que la Rowset/DataSet action supporte les requêtes MDX - logique jusque là - mais aussi SQL sur "n'importe quel provider OLE DB". Or je ne connais personne qui soit capable de faire un exemple fonctionnel de requête SQL dans une action de cube.

Je me suis donc demandé s'il ne serait pas possible de passer par un biais différent: MDX permet l'appel à des procédures stockées managées, et les clients (Excel, OWC) peuvent théoriquement afficher des DataTables. Pourquoi ne pas créer une fonction qui prendrait en paramètre une chaine de connexion, une requête et renverrait une DataTable? C'est très bête mais j'ai donc essayé hier soir.

J'ai donc créé cette petite fonction qui marche de la manière suivante:

1. Création d'une connexion OLE DB grâce à la chaine fournie en paramètre.
   
2. Création d'un DataAdapter se basant sur une commande OLE DB sur cette connexion avec la requête fournie en paramètre
3. Remplissage d'une DataTable via le DataAdapter
4. Retour de la DataTable
   

namespace FJSP { public class MyOpenRowsetClass { public DataTable MyOpenRowset(string OleDbConnectionString, string SqlQuery) { DataTable dt = new DataTable("ResultSet"); OleDbConnection conn = null; b { conn = new OleDbConnection(OleDbConnectionString); conn.Open(); OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = new OleDbCommand(SqlQuery, conn); da.Fill(dt); } finally { if (conn != null) conn.Close(); } return dt; } } }

que j'ai ensuite compilé et ajoutée à ma base SSAS en utilisant l'ajout d'assembly.

En exécutant la requête suivante dans Management Studio...

CALL FJSP.MyOpenRowset( "Provider=SQLNCLI10.1;Persist Security Info=False;User ID=sa;Pwd=Pa$$w0rd;Initial Catalog=AdventureWorksDW2008;Data Source=(local);" ,"SELECT * FROM FactInternetSales");

J'obtient exactement ce que je cherchais :)


Ne reste plus ensuite qu'à exécuter un ajout d'action sur le cube SSAS comprenant un appel CALL de ma procédure stockée...


Un deploy plus tard, clic droit, "Action" sur une cellule et... miracle ça marche aussi dans le browser de BIDS!


Et dans Excel? C'est encore mieux! On se connecte en PivotTable, un petit clic sur actions sur une cellule...


Et voilà le résultat!


Imaginez les potentialités offertes par ce petit bout de code de mauvais développeur .NET : n'importe quelle requête, paramétrée de toutes les façons imaginables, sur tous les systèmes possibles supportant OLE DB :).
Je vous fournis la DLL sur mon SkyDrive ici.

J'attends vos retours!

A bientôt!

Edit du 20 Juin 2009: Gurvan Guyader précise justement en commentaire que l'assembly doit être paramétré comme Unrestricted et non pas Safe, cela pour permettre l'accès aux objets de System.Data.OleDb. Edit du 26 Juin 2009: Darren Gosbell a repris ce bout de code pour l'intégrer dans le projet ASSP (Analysis Services Stored Procedures) le lendemain même... J'avoue que j'aurais bien aimé une petite citation :)

[SSIS] XML Destination avec un script

Vous le savez sûrement, il n'existe pas de XML destination en SSIS. Pour une raison imaginable: la gestion du formatage de sortie et de la DTD à utiliser aurait donné un composant probablement trop complexe à coder et à utiliser.

Pour des usages simples, on peut en revanche coder son implémentation de ce type de provider, en utilisant un ScriptDestination et un tout bête StreamWriter - à noter que l'on peut aussi utiliser l'API XML, en particulier son XMLWriter.
J'ai codé celle-ci: elle est très basique et sûrement éminemment perfectible. Mais elle m'a servi plus d'une fois.

On commence déjà par par définir quatre variables globales: le nom du fichier, un StreamWriter .NET et les noms des noeuds racine et tuple.

public class ScriptMain : UserComponent
{
string fichier = @"C:\fichier.xml" ;
StreamWriter xml = null;
string racine = "Employees" ;
string tuple = "Employee" ;

Ensuite ce genre de script comprend 3 méthodes à implémenter:

1. PreExecute, qui s'exécute une fois au début, on va y écrire le header


2. ProcessInputRow qui s'exécute pour chaque ligne: on va écrire chaque ligne


3. PostExecute où on va écrire le footer sur le modèle de PreExecute

PreExecute

Au début, on insère la Node racine sous forme de node XML ouvrante.

public override void PreExecute()
{
xml = new StreamWriter(fichier, false);
xml.WriteLine("<"+racine+">");
}

PostExecute

A la fin, on insère la Node racine sous forme de node XML fermante, et on ferme le handle sur le StreamWriter.

public override void PostExecute()
{
xml.WriteLine("</"+racine+">");
xml.Close();
}


ProcessInputRow

Le plus intéressant est ici: pour chaque ligne on inscrit:

• En ouvrant le nom du tuple
• Puis pour chaque colonne d'entrée dans les métadonnées du composant (interrogé via ComponentMetadata), on récupère son nom que l'on crée en node ouvrante et fermante
• On insère entre les deux la valeur de cette InputColumn pour le paramètre considéré
• On ferme l'identifieur du tuple

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
xml.WriteLine("["+tuple+"]");
foreach(IDTSInputColumn100 c in this.ComponentMetaData.InputCollection[0].InputColumnCollection)
{
string valeur = (string)Row.GetType().GetProperty (c.Name).GetValue(Row, null);
xml.Write("<" +c.Name+ ">"
+ valeur
+ "</"+c.Name+">");
}
xml.WriteLine("</" +tuple+ ">");
}

Voilà, en espérant que cela vous serve dans vos projets. Si vous possédez une meilleure implémentation - je ne suis pas un dieu absolu de C# après tout - n'hésitez pas!

A bientôt!

[Biblio] Un bon livre sur Analysis Services: Analysis Services 2008 Unleashed

On me pose souvent la question "Connaissez vous un bon livre sur XXX?": personnellement pour SSAS j'ai adoré SQL Server Analysis Services 2008 Unleashed chez SAMS d'Irina Gorbach, Alexander Berger et Edward Melomed tous membres de la team SSAS depuis plusieurs années, les deux derniers étant des anciens de Panorama.


Le bouquin est clair, aborde absolument tous les sujets (développement, administration, fonctionnement interne du moteur...) et adresse en tous cas toutes les questions que j'ai pu me poser jusqu'alors sur le produit, de manière concise et illustrée d'exemples. Le seul tout petit bémol est sa relative technicité qui pourrait rebuter ceux d'entre vous qui ne s'intéressent qu'au maniement de BIDS et absolument pas à la technique - je pense qu'il sont rares et auquel cas des bouquins plus "utilisateur final" existent... - mais ils trouveront quand même ce qui les intéresse en sautant certains chapitres. Il contient en outre une multitude de renseignements absolument pas trouvables sur MSDN ou Technet ce qui est un sacré plus par rapport aux autres références existantes.

N'hésitez pas à vous le procurer, vous pouvez le trouver à une trentaine d'euros en ligne.

Voilà n'hésitez pas à me faire vos retours, et merci à Romu qui me l'a initialement conseillé ;)

A bientôt!