SSIS API #5 : Ajout des Sort Component et liaison aux prédécesseurs

Mai 5th, 2009

Nous avons donc rajouté notre source vers la table FactInternetSales, ainsi qu'un multicast après celle-ci. La boite multicast a pour objet de dupliquer les flux.

Dans un premier temps nous allons lui attacher un tri afin de faire une jointure avec les produits liés.

Et passer de :


Au dataflow suivant :


Ajout de la source de données Produit.

La première étape est logiquement le rajout de la source au produit. Quelques commentaires :

Tout comme la source OLE des faits, nous rajoutons dynamiquement un OLEDB source. Nous le lions au même ConnectionManager que la source de faits attendu qu'il s'agit de la même base de donnée (c'est 'utilité du CM).

La requête de cette source portera sur les produits :

Select * from DimProduct

Je ne reviens pas dans le détail car nous avons dé jà vu comment ajouter une source dans un dataflow.

 

private void addPdtSource()

{

    _pdtDatasource = _mainpipe.ComponentMetaDataCollection.New();

    //_oledbDatasource.ComponentClassID = "DTSAdapter.OleDbSource";

    _pdtDatasource.ComponentClassID = "DTSAdapter.OLEDBSource";

   

   

    // Instanciate an OleDB source Instance for design time based on OleDB Source

    _pdtSourceInstance = _pdtDatasource.Instantiate();

   

   

    // ProvideComponentProperties acts as a kind of constructor and build input, output ...

    _pdtSourceInstance.ProvideComponentProperties();

   

    // Relink runtime oledb component with the package connection manager defined before

    _pdtDatasource.RuntimeConnectionCollection[0].ConnectionManagerID = _sourceCM.ID;

    _pdtDatasource.RuntimeConnectionCollection[0].ConnectionManager

                    = runtime.DtsConvert.GetExtendedInterface(_sourceCM);

   

    /* Each dataflow component has properties :

     * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/065a337e-9a0d-4a5e-aa45-87da58fa37c3.htm

     * 1 - Common Properties :

     * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/51973502-5cc6-4125-9fce-e60fa1b7b796.htm

     * 2 - Custom Properties for oledb source :

     * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/1361a37f-d647-416b-bb96-542b5258f38d.htm#oledb

     */

   

    _pdtSourceInstance.SetComponentProperty("AccessMode", 2);

    _pdtSourceInstance.SetComponentProperty("SqlCommand", "Select * from DimProduct");

   

    _pdtDatasource.Name = "OLE select Product";

    _pdtDatasource.Description = "OLE select Product";

   

   

    _pdtSourceInstance.AcquireConnections(null);

    _pdtSourceInstance.ReinitializeMetaData();

    _pdtSourceInstance.ReleaseConnections();

   

}

 

Ajout des tris :

Petite nouveauté dans notre dataflow, les SORT Component. Nous en ajoutons 2 instances au sein de la méthode AddSort.

private void addSort()

        {

            // First sort

            _firstSort = _mainpipe.ComponentMetaDataCollection.New();

            _firstSort.ComponentClassID = "DTSTransform.Sort";

            wrapper.CManagedComponentWrapper firstSortInstance = _firstSort.Instantiate();

            firstSortInstance.ProvideComponentProperties();

            _firstSort.Name = "SRT First Sort";

   

Tout l'ajout d'une source OLEDB, l'ajout d'un tri se fait par l'ajout d'un ComponentMetadata (par la collection ad hoc).

Ce tri sera alors relatif à la table de fait via le multicast, une fois lié comme suit :

_mainpipe.PathCollection.New().AttachPathAndPropagateNotifications(

   _multicast1.OutputCollection[0], _firstSort.InputCollection[0]);

 

Le fait de rajouter ce lien crée dynamiquement un OutputCollection sur le multicast.

Il faut enfin spécifier sur quelle colonne nous trions notre flux :

wrapper.IDTSVirtualInput100 vInput =  _firstSort.InputCollection[0].GetVirtualInput();

 

wrapper.IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection["ProductKey"];

 

wrapper.IDTSInputColumn100 col = firstSortInstance.SetUsageType(_firstSort.InputCollection[0].ID, vInput, vColumn.LineageID, wrapper.DTSUsageType.UT_READONLY);

 

   

// As sort component has custom properties, Column has also custom properties for sort

// see ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/56f5df6a-56f6-43df-bca9-08476a3bd931.htm#sort

firstSortInstance.SetInputColumnProperty(_firstSort.InputCollection[0].ID, col.ID, "NewSortKeyPosition", 1);

 

Quelques commentaires sur ce qui précède :

Nous ne pouvons pas accéder directement aux listes de colonnes etc.… Nous le ferons via la Notion de VirtualInput qui donne une vue sur la collection. De même, nous retrouvons la clef « Productkey » par la collection VirtualInputcollection[object].

Nous faisons cela car nous souhaitons positionner un tri sur cette colonne. Tout comme nous avons vu que les composants avaient des propriétés communes, des propriétés spécifiques, telles que des propriétés sur les colonnes. Dans notre cas il s'agit de la propriété « NewSortKetPosition », qui va nous permettre d'indiquer que la colonne pointée par col.ID, sera la première clef de tri.

Pour finir, nous ajoutons dans le corps de cette méthode la création du deuxième sort en relation avec la source créées ci-dessus :

    // 2nd sort

    _secondSort = _mainpipe.ComponentMetaDataCollection.New();

    _secondSort.ComponentClassID = "DTSTransform.Sort";

    wrapper.CManagedComponentWrapper secondSortInstance = _secondSort.Instantiate();

    secondSortInstance.ProvideComponentProperties();

    _secondSort.Name = "SRT Second Sort";

   

    _mainpipe.PathCollection.New().AttachPathAndPropagateNotifications(

       _pdtDatasource.OutputCollection[0], _secondSort.InputCollection[0]);

   

    wrapper.IDTSVirtualInput100 vInput2 = _secondSort.InputCollection[0].GetVirtualInput();

    wrapper.IDTSVirtualInputColumn100 vColumn2 = vInput2.VirtualInputColumnCollection["ProductKey"];

    wrapper.IDTSInputColumn100 col2 = secondSortInstance.SetUsageType(_secondSort.InputCollection[0].ID, vInput2, vColumn2.LineageID, wrapper.DTSUsageType.UT_READONLY);

    // As sort component has custom properties, Column has also custom properties for sort

    // see ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/56f5df6a-56f6-43df-bca9-08476a3bd931.htm#sort

    secondSortInstance.SetInputColumnProperty(_secondSort.InputCollection[0].ID, col2.ID, "NewSortKeyPosition", 1);

   

}

 

Le tour est complet. Reste à lier les deux flux sortant des tris sur un « Merge Join », dans le prochain post….

 

 

 

SSIS API #4 : Multicast & IDTSPath

Avril 29th, 2009

Dans notre processus, nous souhaitons exporter des faits, ainsi que les données de références qui y sont rattachées.

Cela suppose donc de gérer deux flux :

  • Un qui pilotera l'export des produits
  • Un qui sera le « dump » des faits

Dans cette optique, un multicast nous permettra de rattacher 2 flux à notre source.

Sa définition est assez triviale, et suit la même logique que la définition de la source de données, modulo, la classe à monter bien entendu.

 

private wrapper.IDTSComponentMetaData100 _multicast1;

private wrapper.CManagedComponentWrapper _multicastInstance1;

private wrapper.IDTSPath100  _path1;    

 

 

private void addMulticast()

{

    /* Instanciate a multicast component */

    _multicast1 =  _mainpipe.ComponentMetaDataCollection.New();

    _multicast1.ComponentClassID = "DTSTransform.Multicast";          

   

    _multicastInstance1 = _multicast1.Instantiate();

    _multicastInstance1.ProvideComponentProperties();

    _multicast1.Name = "MLT FactInternetSales";

   

    /* Add paths and link oledb source to multicast component */

    _path1 = _mainpipe.PathCollection.New();

    _path1.AttachPathAndPropagateNotifications(_oledbDatasource.OutputCollection[0], _multicast1.InputCollection[0]);

}

 

Quelques commentaires sur ce que nous faisons dans cette méthode :

Tout d'abord l'objet multicast n'a pas de propriétés particulières.

Ce qui est important c'est que nous tirons aussi un trait entre la source données (cf. précédent article), et notre nouveau composant.

Pour cela, nous créons un IDTSPath (chemin). Nous lions ensuite un composant, et plus particulièrement son output ( _oledbDatasource.OutputCollection[0] ) vers un autre composant en pointant cette fois-ci vers son input (_multicast1.InputCollection[0]), avec la méthode AttachPathAndPropagateNotifications .

Cette méthode comme son nom l'indique attache ces deux composants en termes de IDTSPath, mais surtout propage les métadonnées (les colonnes et leur type) d'un composant vers l'autre, et notifie les changements de métadonnées.

Il faudra donc bien garder à l'attention ces Path et veiller à alimenter correctement pour propager les flux de données.

 

SSIS API #3 : ConnectionManager & OLE DB Source

Avril 27th, 2009

Dans le précédent article, j'ai créé mon package et lui ajoutais un data flow (MainPipe).

Nous allons maintenant aborder l'aspect accès aux données dans ce billet. Au sein de l'ETL Microsoft, nous définissons des ConnectionManager dont l'objet est centraliser, de manière générique, les définitions et l'accès aux sources de données (et donc réduire la redondance de paramétrage)

(Tout le code source est disponible sur CodePlex : www.codeplex.com/BILAB</a>, dans l'onglet source, répertoire POC/BILABHistory, solution BIHistory.sln. La classe concernée est MulticastExport.cs dans le sous projet BILAB.Technical.History.PackageBuilder)

Ajout des Gestionnaires de Connexion

Les gestionnaires de connexion (ConnectionManager) font l'objet d'une collection _p.Connections au sein de package. Vous pouvez en ajouter comme suit :

private runtime.ConnectionManager _sourceCM;

 

private void addSourceCM()

{

    _sourceCM = _p.Connections.Add("OLEDB");

    _sourceCM.Name = "AdventureDWSource";

    _sourceCM.ConnectionString = @"Provider=SQLOLEDB.1;Password=myuser;Persist Security Info=True;User ID=myuser;Initial Catalog=AdventureWorksDW;Data Source=localhost";

}


Les sources peuvent être FLATFILE, ADO, ODBC... fonction du besoin.

Ajout de la source au sein du DataFlow

Définir une connexion ne suffit pas en tant que tel à accéder aux données, il faut ajouter une source au sein de notre DataFlow, plus précisément au MainPipe :

private wrapper.IDTSComponentMetaData100 _oledbDatasource;

private wrapper.CManagedComponentWrapper _sourceInstance;

 

private void addSourceConnection()

   {

       _oledbDatasource = _mainpipe.ComponentMetaDataCollection.New();           

       _oledbDatasource.ComponentClassID = "DTSAdapter.OLEDBSource";

<…/>


Le pipeline SSIS est en fait composé d'objets de type ComponentMetadata qui représente chacune des tâches que vous vous pouvez glisser sur le designer SSIS.
Ce type vous permet d'accéder aux propriétés génériques (Commons) des tâches. Nous le "typons" en utilisant à nouveau les CLSID. 

Vous trouverez ici la référence des API Communes à toutes les tâches :

http://msdn.microsoft.com/en-us/library/ms136001.aspx

Plus précisément : http://msdn.microsoft.com/en-us/library/ms135950.aspx

 

Dans le modèle SSIS, nous devons aussi créer une instance de type « source OLE DB » (et non, ce n'est toujours pas fait...) avec la méthode _oleDbSource.Instanciate qui nous renvoie CManagedComponentWrapper qui comme son nom l'indique est un wrapper vers l'objet (COM encore) sous jacent :

<…/>

 

// Instanciate an OleDB source Instance for design time based on OleDB Source

_sourceInstance = _oledbDatasource.Instantiate();

// ProvideComponentProperties acts as a kind of constructor and build input, output ...//

_sourceInstance.ProvideComponentProperties();

 

<…/>

Si je refais un détour sur le développement de composants SSIS, je vous avais proposé il ya quelques temps une destination XML (qui ne passait pas par du DOM, mais par un XMLWriter) :

http://www.techheadbrothers.com/Articles.aspx/developpement-composant-integration-services-ssis

Et c'est en surchargeant la méthode ProvideComponentProperties que j'ajoutais les propriétés dynamiquement à mon composant.

http://www.techheadbrothers.com/Articles.aspx/developpement-composant-integration-services-ssis-page-3

Il faut donc bien comprendre que la méthode ProvideComponentProperties agit comme un véritable constructeur sur l'instance. Dès lors, nous aurons accès aux propriétés propres au PipelineComponent, et notamment lui la possibilité de lier notre source un connectionManager.

<…/>

// Relink runtime oledb component with the package connection manager defined before

            _oledbDatasource.RuntimeConnectionCollection[0].ConnectionManagerID = _sourceCM.ID;

            _oledbDatasource.RuntimeConnectionCollection[0].ConnectionManager

                            = runtime.DtsConvert.GetExtendedInterface(_sourceCM);

<…/>

Nous pouvons aussi spécifier le mode d'accès aux données ainsi que la requête (propriétés spécifiques):

<…/>

_sourceInstance.SetComponentProperty("AccessMode", 2);

_sourceInstance.SetComponentProperty("SqlCommand", "Select * from FactInternetSales");

   

_oledbDatasource.Name = "OLE select from Fact InternetSales";

_oledbDatasource.Description = "OLE select from Fact InternetSales";

<…/>

En complément au lien MSDN sur les propriétés communes, vous trouverez sur le lien suivant les propriétés propres à la source OLE DB :

http://msdn.microsoft.com/en-us/library/ms135923.aspx

Comme dernière étape à notre processus, nous allons faire en sorte que notre composant se connecte à sa source de données pour la valider et récupérer la liste des colonnes à extraire de la source comme suit:

<…/>

   _sourceInstance.AcquireConnections(null);

    _sourceInstance.ReinitializeMetaData();

    _sourceInstance.ReleaseConnections();

   

}

<…>

 

MulticastExport en action …

Enfin si je replace le tout dans l'appel :

public void Export()

        {

            _p = new runtime.Package();

            _p.Name = "MulticastExport";

            _p.CreatorComputerName = System.Environment.MachineName;

            _p.CreatorName = System.Environment.UserName;

   

            this.addDataflow();

            this.addSourceCM();

            this.addSourceConnection();

            //this.addMulticast();

            //this.addPdtSource();

            //this.addSort();

            //this.addMerge();

            //this.addFactDestination();

   

            runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();

            a.SaveToXml("ZBILAB.History.dtsx", _p, null);

        }

 

Nous retrouvons notre package avec une Connexion :


Et une source dans le dataflow :


Les propriétés « populées » avec nos paramètres :


Le vecteur colonne préparé suite à la validation de la source :

La suite …

A partir de cette source nous allons continuer à construire notre flux et ajouter un multicast qui nous permettra de faire un flux vers un fichier texte, et un flux vers une tâche Merge Join.

 

SSIS API #2 : Objets Package & TaskHost

Avril 24th, 2009

Commençons par les basiques. La première étape consiste tout simplement à créer un objet package et à lui ajouter :
- Un data flow (objet pipeline)
- Un connection Manager qui contiendra la définition de notre base
et à le sauvegarder.

Je crée donc une classe Export :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

using runtime = Microsoft.SqlServer.Dts.Runtime;

using runtimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

using wrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;

 

 

namespace BILAB.Technical.History.PackageBuilder

{

 

    public class MulticastExport

    {

        private runtime.Package _p;

        private runtime.TaskHost _taskhost;

        private wrapper.MainPipe _mainpipe;



A noter les 3 imports:
- Un runtime qui représente le moteur SSIS
- RuntimeWrapper "masque" les objets (COM) sous jacents
- et enfin wrapper est le wrapper des composants du dataflow (pipeline)

La methode Export sera celle appelée par notre client :

public void Export()

{

    _p = new runtime.Package();

    _p.Name = "MulticastExport";

    _p.CreatorComputerName = System.Environment.MachineName;

    _p.CreatorName = System.Environment.UserName;

 

    this.addDataflow();

    this.addSourceCM();

    //this.addSourceConnection();

    //this.addMulticast();

    //this.addPdtSource();

    //this.addSort();

    //this.addMerge();

    //this.addFactDestination();

 

    runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();

    a.SaveToXml("ZBILAB.History.dtsx", _p, null);

}


Comme vous pouvez le voir, cette méthode va créer le package _p, ajouter le dataflow, la connection et enfin sauvegarder le package via un objet Application qui représente le runtime (le troisième paramètre à null sera utilisé pour gérer les évènements)

Ajout du DataFlow :

private void addDataflow()

       {

           /*

            * Common properties of taskhost

            * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_1devconc/html/1b725922-ec59-4a47-9d55-e079463058f3.htm

            *

            * Detail :

            * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_7ismrf/html/e39480e5-613f-4bf4-87b1-249942ed9a35.htm

            */

 

            /* Add Method

             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_7ismrf/html/a0d4c1cf-d4a3-4297-8892-7b13f235fe9d.htm

             * 

             */

 

           _taskhost = (runtime.TaskHost)_p.Executables.Add("DTS.Pipeline");

           _taskhost.Name = "DFT History";

           _mainpipe = (wrapper.MainPipe)_taskhost.InnerObject;

 

           // Another example of taskhost creation

           //runtime.Executable e = _p.Executables.Add("Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask, Microsoft.SqlServer.BulkInsertTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");

           //runtime.TaskHost t = (runtime.TaskHost)e;

           //t.Name = "REH";        

       }



Ajouter un DataFlow consiste à ajouter un objet dit Executable. Les Executable sont respectivements :
- les itérateurs for
- les séquences
- les dataflow / TaskHost

Vous trouverez l'architecture des composants sur :
=> http://msdn.microsoft.com/en-us/library/ms137681.aspx
Ces objets ont des propriétés communes
==> http://msdn.microsoft.com/en-us/library/ms137728.aspx

Dans la pratique, nous ajoutons une tâche en faisant référence à son CLSID (vue base de registre) comme dans notre exemple :


mais cela peut être aussi par les assemblies comme l'exemple (en commentaire) du bulk insert .

Pour finir, nous récupérons la classe hébergée et instanciée dans le taskhost via _taskhost.InnerObject

API SSIS #1 : Export Dynamique & analyse des dépendances

Avril 23rd, 2009

Dans la suite des essais sur SMO, mon idée est de générer dynamiquement un package qui exporte les données en analysant les dépendances (fichier plat/ole db etc..). Sur la base de quelques conseils avisés, je me suis orienté vers un sample Microsoft qui détaille comment créer dynamiquement un package SSIS d'export.

Sur codeplex :
http://sqlsrvintegrationsrv.codeplex.com/
et plus précisement :
http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=17647


J'ai trouvé le sample intéressant mais difficile à reprendre. Je vous invite à le consulter. De mon coté, je vais sur cette base, essayer d'illustrer la génération dynamique de package en détaillant les internes, en ayant comme objectif de produire le pipeline suivant :



Tout un programme...

SMO#3 : Analyse récursive des dépendances

Mars 30th, 2009

Sur la base de la proposition, l'ajout de récursivité va permettre d'analyser dynamiquement l'arbre :


____

private

List<TablePath> lookForChilds( TablePath root , TablePath parent)

{

 

List<TablePath> ltp = new List<TablePath>();

 

 

DataTable dt = _database.Tables[parent.TableName,parent.Schema].EnumForeignKeys();

 

foreach (DataRow dr in dt.Rows)

{

 

string tableName = dr[1].ToString();

 

string schemaName = dr[0].ToString();

 

string fkname = dr[2].ToString();

 

if (!(tableName.Equals(parent.TableName) &&

schemaName.Equals(parent.Schema))

)

{


____

Je teste le parent afin de ne pas partir en récursion infinie liée à un éventuel Parent-Chlid ou association :

____

log.Info(

"scanning now " + tableName);

 

TablePath tp = new TablePath();

tp._database = parent._database;

tp._server = parent._server;

tp.TableName = dr[1].ToString();

tp.Schema = schemaName;

tp.ForeignKey = fkname;

tp._childs =

this.lookForChilds(root, tp);

ltp.Add(tp);

}

 

else

{

log.Info(

"preventing infiniteRecursion on " + tableName);

}

 

}

 

return ltp;

}


____


En passant, nous remplissons la ForeignKey impliquée. L'ensemble se restitue toujours avec la méthode AdaptTablePath() :

____

 

private void AdaptTablePath(ref TreeNode node, TablePath tablePath)

{

node.Text = tablePath.TableName +

" via < " +tablePath.ForeignKey +" >";

 

if (tablePath.Childs != null)

{

 

foreach (TablePath tp in tablePath.Childs)

{

 

TreeNode childNode = new TreeNode(tp.TableName );

AdaptTablePath(

ref childNode, tp);

node.Nodes.Add(childNode);

}

}

}


____

En mode restitué :
____

____

SMO Sample #2bis : Recherche des dépendances et restitution

Mars 27th, 2009

Toujour sur le périmètre de la recherche des dépendances de tables, je fait un petit point intermédiaire de manière à les restituer dans mon IHM. Le plus simple est de les afficher dans un treeview.



Pour résumer, lorsque du clic sur le bouton "Analyse" de l'UI,

Je force pour l'analyse sur une base Sample de MS comme suit :


___

private void analyseToolStripMenuItem_Click(object sender, EventArgs e)

        {

            bool state = false;

            #region comments

            //if (UserMenuItem.Text == "" && PasswordMenuItem.Text == "")

            //{

            //    state = _tp.ConnectToDatabase(ServerMenuItem.Text,

            //                                  dbListCombo.SelectedText,

            //                                  ServerAuthMode.Integrated,

            //                                  null, null);

            //}

            //else

            //{

            //    state = _tp.ConnectToDatabase(ServerMenuItem.Text,

            //                                  dbListCombo.SelectedText,

            //                                  ServerAuthMode.Integrated,

            //                                  UserMenuItem.Text, PasswordMenuItem.Text);               

            //}    

            #endregion

 

            state = _tp.ConnectToDatabase(@".\BILAB2008","AdventureWorks",ServerAuthMode.Integrated,null,null);

 

            _tp.TableName = "SalesOrderHeader";

            _tp.Schema = "Sales";

 

            _tp = _tp.AnalyseDatabaseBy("SalesOrderHeader","Sales");

            TreeNode treeNode = new TreeNode();

 

            /* convert TreePath Tree into a TreeNode for display */

            this.AdaptTablePath(ref treeNode, _tp);

            DatabaseTreeView.Nodes.Add(treeNode);

        }



L'élément clef pour alimenter notre Trevview est l'objet treeNode. Nous l'alimentons en récurisif :

____

e="margin: 0px;">private void AdaptTablePath(ref TreeNode node, TablePath tablePath)

{

    node.Text = tablePath.TableName;

    if (tablePath.Childs != null)

    {

        foreach (TablePath tp in tablePath.Childs)

        {

            TreeNode childNode = new TreeNode(tp.TableName);

            AdaptTablePath(ref childNode, tp);

            node.Nodes.Add(childNode);

        }

    }

}


____

 

SMO Sample # 2 : Récupérer les tables dépendantes

Mars 23rd, 2009

Toujours dans la série SMO, je souhaite maintenant créer un arbre objet qui me donnera pour une table, sa  clef (primaire ou composée), les tables qui dépendent de cette pk directement .

j'ai donc rajouté à mon objet TablePath une méthode AnalyseDatabaseBy :

  132 public TablePath AnalyseDatabaseBy(string tableName,string schema)

  133 {  

  134     /* Sets main information */

  135     this.TableName = tableName;

  136     this.Schema = schema;

  137 

  138     /* Build a representation of any PrimaryKey */

  139     ColumnCollection cols = _database.Tables[tableName, schema].Columns;

je fixe en passant le schema et le nom de la table en cours. (Mon idée derrière est de travailler par la suite récursif).

Deuxième étape : j'ai besoin de garder la ou les clefs de la tables via InPrimaryKey
 Cette information ne se retrouve pas directement, il faut itérer dans les colonnes et "noter" les colonnes contributives, en testant InPrimaryKey :

  138 /* Build a representation of any PrimaryKey */

  139             ColumnCollection cols = _database.Tables[tableName, schema].Columns;

  140             StringBuilder sb = new StringBuilder();

  141             foreach (Column col in cols)

  142             {

  143                 if (col.InPrimaryKey)

  144                 {

  145                     sb.Append("[");

  146                     sb.Append(col.Name);

  147                     sb.Append("]");

  148                 }               

  149             }

  150             this._primaryKey = sb.ToString();



Fragment-->


Troisième étape : trouver la liste des tables dépendantes via EnumForeignKeys



J'ai caché toute cette logique sous la forme d'une fonction qui exploite la méthode EnumForeignKeys() d'objet
Table. Ci dessous la fin de la méthode AnalyseDatabaseBy () et l'appel à LookForChilds():

  153     this._childs = this.lookForChilds(this);

  154 

  155     return this;

  156 }

>Examinons le corps de la méthode LookForChilds :

 

e="margin: 0px;">  158 private List<TablePath> lookForChilds(  TablePath parent)

  159 {

  160     List<TablePath> ltp =  new List<TablePath>();

  161 

  162     DataTable dt = _database.Tables[parent.TableName,parent.Schema].EnumForeignKeys();


tyle="font-size: 10pt;">La méthode EnumForeignKeys()  nous renvoie un DataTable contenant la liste des tables liées, leur schema et la foreignkey concernée. Ci dessous un screenshot de l'enum en mode debug :


Nous rajoutons donc les enfants sous la forme d'une liste :

 

  163 foreach (DataRow dr in dt.Rows)

  164            {

  165                TablePath tp = new TablePath();

  166                tp._database = parent._database;

  167                tp._server = parent._server;

  168                tp.TableName = dr[1].ToString();

  169                tp.Schema = dr[0].ToString();

  170                ltp.Add(tp);

  171            }         

  172            return ltp;

  173        }


AnalyseDatabaseBy in action !
Appliqué dans notre IHM à AdventureWorks et à la table SalesOrderDetails :

   54 state = _tp.ConnectToDatabase(@".\BILAB2008","AdventureWorks",ServerAuthMode.Integrated,null,null);

   55 _tp = _tp.AnalyseDatabaseBy("SalesOrderHeader","Sales");



Quickwatch du TablePath _tp :


tyle="font-size: 10pt;">Nous avons 2 enfants , "SalesOrderDetails" tout d'abord :

et ensuite "SalesOrderHeaderSalesReason" :

Code source sur : www.codeplex.com/BILab</a> sous $/POC/BILabHistory

SMO (SQL Management Object) Sample : balade dans les API / connexion et liste des bases

Mars 22nd, 2009

Je vais vous proposer une série de sample afin d'illuster l'utilisation de SMO (SQL Server Management Object) dans le cadre d'un système de sauvegarde de données de base à base, en suivant l'arbre des dépendances (techniques ou fonctionelles) du modèle physique.



J'aurais pu faire un simple package SSIS à cet effet, mais dans la pratique les modèles de donnée changent régulièrement, donc SSIS s'adapate mal car cela supposera de maintenir les métadonnées :(
Il s'agit donc de créer dynamiquement ces packages, tout un programme...
Le code source du projet est sur www.codeplex.com/bilab</a> sous $/POC/BILABHistory/
Dans ce projet, je m'appuie sur une classe clef "TablePath". Elle embarquera toute la logique.

SMO est une API qui permet de se balader dans un SQL Server. Pour se connecter, il faudra passer par la classe Server :

sqlserver =

new Server(sc);


Commençons par du basique : afin de récupérer l'arbre des dépendances, je vous propose déjà de se connecter et de récupérer la liste des bases existantes dans l'instance concernée. C'est que fait la méthode GetDatabaseList :

   27         public List<string> GetDatabaseList(string server, ServerAuthMode mode, string user, string password)

   28         {

   29             List<string> dblist = new List<string>();          

   30             ServerConnection sc = null;

   31             Server sqlserver;

   32 

   33             log.Info("connecting");

   34 

   35 

   36             // Sets appropriate connections proerties accordign db context

   37             if (mode == ServerAuthMode.SqlServer)

   38             {

   39                 sc = new ServerConnection(server, user, password);

   40             }

   41             else if (mode == ServerAuthMode.Integrated)

   42             {

   43                 sc = new ServerConnection(server);

   44             }

   45 

   46 

   47             try

   48             {

   49                 sqlserver = new Server(sc);

   50                 log.Info("Creating db list");

   51                 foreach (Database db in sqlserver.Databases)

   52                 {

   53                     log.Info("adding " + db.Name + " to the list ");

   54                     dblist.Add(db.Name);

   55                 }

   56             }

   57             catch (Exception e)

   58             {

   59                 log.Debug("Error obtaining database list connecting : " + e.Message);

   60             }

   61             finally

   62             {

   63                 sc = null;

   64                 _server = null;

   65             }           

   66 

   67             return dblist;

   68         }

Welcome 2009 & MVP 2008

Janvier 1st, 2009

Meilleurs Voeux à tous pour 2009.

J'ai eu le plaisir de commencer cette année sur une très bonne tonailté en ayant été renouvelé MVP (Most Valuable Professional) pour la quatrième année consécutive. Je dois dire que cela fait toujours autant plaisir d'avoir cette distinction par la communauté MS car la rédaction d'articles (originaux j'espère) sur la BI demande un gros investissement. Merci encore...:D
Alors Welcome 2009 et Meilleurs Voeux à tous !

SSAS & Cubes Asymétriques

Septembre 13th, 2008

Lien: http://9bj4gq.blu.livefilestore.com/y1pOby6EDinv9aE88KNq_KOxqPAZx--q5WWihiniOygBmItDKA1cgNXD05dSAkA5x1rh7kfV_z1DmB6SVRS6Gqkiw/AsymetricPattern_v1.pdf?download

Je vous propose cet article sur les cubes asymétriques sous SSAS.
La notion de cubes asymétriques est pattern projet qui nous permet de mixer au sein d'une même structure, des données détaillées à court terme avec des données agrégées historiques avec un grain différent fonction du temps, autant dire des besoins diamétralement opposés.
Je vous propose donc dans cet article de parcourir ce besoin, de voire quelles options de modélisation vous pourrez prendre et comment l'implémenter dans le Cube Microsoft



Blog sur le décisionnel

Août 24th, 2008

Lien: http://infodecisionnel.free.fr/

Dans la série des blogs en français sur le sujet, je vous renvoie sur http://infodecisionnel.free.fr/

BI Framework et Sample sur codeplex

Mai 21st, 2008

Lien: http://www.codeplex.com/BILAB

FR : après quelques demandes de distributions, je me suis décidé à déposer mon Framework BI r&d sur codeplex ainsi que les jeux d'exemples que j'ai développé au grès de mes articles sur le Stack BI Microsoft.

http://www.codeplex.com/BILAB

EN : after some demands, I decided to delivers my r&d BI Framework on codeplex. This framework and sample was developed for my articles about MS BI Stack.

http://www.codeplex.com/BILAB

GApps, BI, .net et WebServices

Janvier 30th, 2008

FR : Allez et re-blog sur le sujet. La question de l'usage industriel des technologies web 2.0 est posée.
J'aime bien dire que je n'ai pas vendu mon âme au diable à Excel  (aux technoïdes anti-MS), mais on saura désormais que je n'ai pas qu'une seule âme à vendre.
Tout l'art est de ne pas renvoyer les technologies dos à dos, mais d'en faire la synthèse pour inventer de nouveaux usages. Comme je l'écrivait, je meurt (mourrais ) d'envie de "masher" des données métiers dans un Google Spreadsheet. Alors pourquoi ?
Je ne sais pas pour vous mais pour moi, combien de fois je me suis échangé un fichier Excel avec plusieurs collaborateurs avec la perte de "précision" et de "confiance" sur la version de données. Dans le domaine de la BI, il s'agit d'un enjeu clef et je le dis d'autant plus que je suis fan d'Excel Services. Pourquoi ne pas faire pareil avec spreadsheet (le cote full online en plus!)
En route : Ce qui est bien dans Google Apps, c'est que le modèle ouvert by design . Les API et SDK sont dispo sur
Gdata API

 Et comble du bonheur, le tout accessible en .net .

Je voudrais juste vous donner la saveur (2.0) de la modification dynamique du contenu d'une cellule d'une spreadsheet :


service = new SpreadsheetsService("BI-2.0");
service.setUserCredentials("user@gmail.com", "motdepasse");

// Recherche de la feuile nommée BI 2.0
SpreadsheetQuery sq = new SpreadsheetQuery();
sq.Title = "BI 2.0";

// On récupère son URI
SpreadsheetFeed sf = service.Query(sq);
AtomEntry entry = sf.Entries[0];
string path = entry.Links[0].HRef.Content;

// de là on récupère l'URI de la première WorkSheet
WorksheetQuery wq = new WorksheetQuery(path);
WorksheetFeed wf = service.Query(wq);
WorksheetEntry we = (WorksheetEntry)wf.Entries[0];

string worksheetUri = we.Links[1].HRef.Content;

// dans laquelle on joue avec les cellules
CellQuery ce = new CellQuery(worksheetUri);
CellFeed cf = service.Query(ce);

// Informe les users


CellEntry cellToCreate = new CellEntry();
CellEntry.CellElement cellInfo = new CellEntry.CellElement();
cellInfo.InputValue = "Updated by BI T Center !";
cellInfo.Row = 5;
cellInfo.Column = 5;
cellToCreate.Cell = cellInfo;

service.Insert(new Uri(worksheetUri), cellToCreate);

Magique !

 Le résultat en E5 !

EN : I would like to explore the "industrial usage"  of web 2.0 into Enterprise taking as example Google Apps. I'm use to say that I did not sell my soul to Excel, but since a few, you have to know that I have many soul to sell ;)

The (BI) art is to avoid opposition on technologies, but be able to mash them for enterprise intertest and re-invent usages. I don't know for you, but me, I spent a lot of effort in order to share an maintain on a simple speadsheet with others collaborators. This question is central on BI topics, and a road is open with Excel Services. My subjesct is to do the same (being online all the time)

Lets go : Google is web and open by design. SDK is available on Gdata API.

and hopefully available in .net.

I would like to give you the flavour (2.0) of a cell modification using Spreadsheet API.

service = new SpreadsheetsService("BI-2.0");
service.setUserCredentials("user@gmail.com", "motdepasse");

// Recherche de la feuile nommée BI 2.0
SpreadsheetQuery sq = new SpreadsheetQuery();
sq.Title = "BI 2.0";

// On récupère son URI
SpreadsheetFeed sf = service.Query(sq);
AtomEntry entry = sf.Entries[0];
string path = entry.Links[0].HRef.Content;

// de là on récupère l'URI de la première WorkSheet
WorksheetQuery wq = new WorksheetQuery(path);
WorksheetFeed wf = service.Query(wq);
WorksheetEntry we = (WorksheetEntry)wf.Entries[0];

string worksheetUri = we.Links[1].HRef.Content;

// dans laquelle on joue avec les cellules
CellQuery ce = new CellQuery(worksheetUri);
CellFeed cf = service.Query(ce);

// Informe les users


CellEntry cellToCreate = new CellEntry();
CellEntry.CellElement cellInfo = new CellEntry.CellElement();
cellInfo.InputValue = "Updated by BI T Center !";
cellInfo.Row = 5;
cellInfo.Column = 5;
cellToCreate.Cell = cellInfo;

service.Insert(new Uri(worksheetUri), cellToCreate);

Magic !

Look @ E5 cell :

Google, Web et Entreprise 2.0

Janvier 19th, 2008

Lien: http://nauges.typepad.com/my_weblog/

FR : J'ai découvert presqu'un peu par hasard Google Apps (Docs, Spreadsheet et présentation). Il y a vraiment de quoi s'interroger sur les usages. Pour ceux qui s'interrogent, un de mes collègues (Sébastien) m'a cablé sur le blog de Louis Naugès qui pourra vous donner des pistes de reflexions sur les usages et sur les achitectures d'entreprise (2.0 ?)

http://nauges.typepad.com/my_weblog/

Depuis,je meurt d'envie de "masher" une spreadsheet avec des données métier...

EN : I discovered "by mistake" Google Apps(Docs, Spreadsheet and Presentation). Regarding those tools, I (we) think that we could re invent new usages. For those who wants to have a small introduction about that, one of my colleagues (Sébastien) had sent me a link to Louis Naugès Blog. You could find several ideas about "What could be an Entreprise 2.0"

http://nauges.typepad.com/my_weblog/

Since, I would really like to mash my spreadsheet with business data ...