Gestion dynamique des partitions OLAP avec SSIS

Sur des projets de haute volumétrie, le partitionnement des groupes de mesures est indispensable aussi bien :

  • pour optimiser les temps de traitement d’un cube
  • que pour minimiser les temps de requêtes

C’est une bonne pratique reconnue mais pas toujours bien mise en oeuvre.

Tout d’abord, utilisons les seuils indiqués par les différents « white papers ». Si tout le monde les lisait, nos projets ne s’en porteraient que beaucoup mieux. Encore faut-il aimer son job, s’y intéresser et investir un peu de son temps personnel (bon là je dérive …)

La bonne pratique veut que tout groupe de mesures de plus de 20 millions de lignes ou de plus de 250 Mo doit être partitionné. A cela s’ajoute aussi les considérations hardware, nombre de coeurs et de disques, de façon à s’assurer que les opérations de lecture/écriture (I/O) puissent être parallélisées. Bien sur, il ne s’agit que de valeurs indicatives mais cela vous aidera à déterminer s’il faut partitionner ou bien revoir la stratégie de partitionnement déjà en place.

Rentrons désormais dans le vif du sujet. Pour que le partitionnement soit efficace au niveau du temps de process, il faut que les tables SQL Server soient-elles aussi partitionnées et, si possible compressées (uniquement si vous travailler sur une version 2k8 ou ultérieure), le mieux étant d’aligner les partitions SQL Server avec les partitions Analysis Services. Et oui la compression permet aussi de réduire les I/O. J’aborderai ces sujets dans de futurs posts. Juste un petit rappel : en BI, le partitionnement se fait en règle général sur l’axe temps.

Prenons un exemple tout simple, mon client me demande d’intégrer quotidiennement ses données de ventes dont la volumétrie mensuelle est estimée à 20 millions d’enregistrements sur un historique de 3 ans. Mon entrepôt et mes flux SSIS étant bien conçus, je dispose d’une table de vente partitionnée par mois (soit 37 partitions : 3 ans * 12 mois + 1 partition pour gérer les bornes). Sur ce type d’entrepôt, ce que je retrouve en règle générale sur SSAS sur le groupe de mesures des ventes, c’est une gestion glissante des partitions :

  • 1 partition sur le mois en cours
SELECT *
FROM CIAL.FactSale
WHERE IdSaleDate BETWEEN convert(nvarchar(6),getdate(),112) + '01'
AND convert(nvarchar(8),DATEADD(D,-1,convert(nvarchar(6),DATEADD(M,1,getdate()),112) +'01'),112)
  • 1 partition sur l’année en cours

SELECT *
FROM CIAL.FactSale
WHERE IdSaleDate BETWEEN CAST(YEAR(GETDATE()) as nvarchar) + '0101'
AND CONVERT(nvarchar(8),DATEADD(D,-1,convert(nvarchar(6),getdate(),112) + '01'),112)

  • 1 partition sur l’année précédente

SELECT *
FROM CIAL.FactSale
WHERE IdSaleDate BETWEEN CAST(YEAR(GETDATE())-1 as nvarchar) + '0101'
AND CONVERT(nvarchar(8),DATEADD(D,-1,CAST(YEAR(GETDATE()) as nvarchar) + '0101'),112)

  • 1 partition d’archive sur les années antérieures

SELECT *
FROM CIAL.FactSale
WHERE IdSaleDate < CONVERT(nvarchar(8),DATEADD(D,-1,CAST(YEAR(GETDATE())-1 as nvarchar) + '0101'),112)

Cette méthode à les avantages suivants :

  1. sous BIDS le nombre de partitions dans notre projet source est identique à celui de l’UDM en production, ce qui simplifie les déploiements
  2. en nominal, le process et le querying du mois en cours est très rapide.

En revanche les inconvénients sont :

  1. à chaque glissement il faut faire un process full du groupe de mesures (dans notre exemple, tous les mois).
  2. les partitions ne sont pas de la même taille par conséquent, les temps de traitememt et de querying seront différents en fonction de la partition attaquée.

On peut, bien sur, moduler ce principe pour avoir des glissements de partitions plus éloignés dans le temps et des partitions de taille sensiblement équivalentes (ex : faire des partitions de 6 mois) ; mais nous aurons alors des process quotidien plus long (c’est plus long de traiter tous les jours 6 mois de données au lieu d’un), et des temps de réponse en terme de querying dégradés sur le mois en cours par rapport à la précédente solution.

Vous l’aurez compris, l’objectif est de trouver la bonne adéquation entre temps de process et temps de querying. Nous laisserons donc cette méthode aux filles.

Mais alors, comment garantir des temps de traitement et de requêtage optimum ?

La réponse est simple, tout comme sur votre base de données SQL Server, il va falloir gérer dynamiquement vos partitions SSAS via un package SSIS.

La première étape consiste à mettre en place une convention de nommage de nos partitions sous SSAS et à créer la première partition sur notre groupe de mesure des ventes. Nous utiliserons la convention de nommage suivante : <ID du groupe de mesure>_<AAAAMM> avec notre exemple nous devons donc avoir FactVente_200901. N’oubliez pas de mettre en place le Slice de partition.

une fois la partition créée, déployons notre cube.

N.B : sous BIDS, notre projet ne doit contenir qu’une seule partition : la partition de départ ! Les autres partitions seront créées par notre package SSIS.

La seconde étape et pas la moindre, est la création de notre package SSIS de traitement de cube. Ce dernier se doit d’implémenter les fonctionnalités suivantes :

  • Traiter les dimensions
    • Lister les dimensions présentes sur un cube en générant les ordre XMLA adéquate de process en fonction de l’état de la dimension.
    • Exécuter les ordres XMLA de traitement des dimensions
  • Traiter les groupes de mesures du cube :
    • Lister les groupes de mesures présents sur un cube en générant les ordres XMLA adéquate de process en fonction de l’état de chaque partition et création dynamique des partitions mensuelles.
    • Exécuter les ordres XMLA de traitement des partitions

Afin de rendre mon package générique, je vais avoir besoin des variables suivantes :

  • String ssasServer : nom de l’instance Analysis Services
  • String udmID : id de l’UDM à traiter
  • String cubeID : id du cube à traiter
  • String lstMeasureGroupMonthlyPartitionned : liste des groupes de mesures avec un partitionnement mensuel, les valeurs sont séparées par le caractère ,

N.B : ces variables doivent être initialisées par configuration !

Nous avons aussi besoin de variables pour stocker les ordres XMLA de traitement à exécuter :

  • String xmlaDimensionProcessUpdate : requête XMLA de traitement des dimensions (update ou data)
  • String xmlaDimensionProcessIndex : requête XMLA de traitement des index de dimensions
  • String xmlaPartitionProcessData : requête XMLA de traitement des partitions
  • String xmlaPartitionProcessIndex : requête XMLA de traitement des index de partitions

Passons à l’implémentation de notre première séquence le traitement des dimensions

  • Mise en place d’un Script Task afin de boucler sur les dimensions de l’UDM et en fonction du statut de chacune de définir le traitement adéquate à effectuer. Si la dimension est dans l’état « processed », elle doit être traiter en process update sinon en process data. Pour chaque dimension, on génère aussi un ordre XMLA de traitement d’index.
    • en entrée, le script à besoin des informations de connexion au cube (ssasServer et udmID)
    • en sortie, le script va générer les ordres XMLA de traitement des dimensions et de leurs index (xmlaDimensionProcessUpdate et xmlaDimensionProcessIndex)

  • Voyons désormais l’implémentation du script c#. Le principe est simple, nous devons parcourir la liste des dimensions de l’udm et pour chacune d’elle construire l’ordre de traitement adéquat en fonction de son statut. Pour cela nous utilisons les fonctionnalités AMO (Analysis Management Objects). Ci-dessous le code C# commenté qui vous permettra d’arriver à vos fins :

/// <summary>
/// Tableau de String permettant de stocker les ordre XMLA de process de dimension
/// </summary>
private List<String> lstDimensionProcess = new List<String>();

/// <summary>
/// Main
/// </summary>
public void Main()
{
    Microsoft.AnalysisServices.Server amoUdm = new Microsoft.AnalysisServices.Server();
    String ssasServer, udmId;

    // R�cup�ration des informations n�cessaire pour se connecter � l'UDM
    ssasServer = Dts.Variables["User::ssasServer"].Value.ToString() ;
    udmId = Dts.Variables["User::udmID"].Value.ToString();

    // Connexion � l'UDM
    amoUdm.Connect("Data Source="
        + ssasServer
        + ";Initial Catalog="
        + udmId
        + ";Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;");

    // Boucle sur les dimensions de l'UDM
    foreach (Microsoft.AnalysisServices.Dimension dimension in amoUdm.Databases[udmId].Dimensions)
    {
        // Si l'�tat de la dimension est Processed => ajout d'un ordre de traitement de type ProcessUpdate sur la dimension sinon ajout d'un ordre de traitement ProcessData
        if (dimension.State != AnalysisState.Processed)
        {
            AddDimensionProcessOrder(udmId, dimension.ID, "ProcessData");
        }
        else
        {
            AddDimensionProcessOrder(udmId, dimension.ID, "ProcessUpdate");
        }
    }
    // Construction des requ�tes de traitement des dimensions et de leurs index
    BuildXmlaPartitionProcessScript();
    Dts.TaskResult = (int)ScriptResults.Success;
}

/// <summary>
/// G�n�re un sous ordre XMLA de process pour une dimension donn�e
/// </summary>
/// <param name="udmID">ID de l'UDM � traiter</param>
/// <param name="dimensionID">ID de la dimension � traiter</param>
/// <param name="processType">Type de process (ProcessUpdate ou ProcessData)</param>
public void AddDimensionProcessOrder(String udmID, String dimensionID, String processType)
{
    lstDimensionProcess.Add(
    "<Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">"
    + ""
    + "<Type>" + processType + "</Type>"
    + "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>"
    + "</Process>"
    );
}

/// <summary>
/// G�n�ration des requ�tes XMLA de traitement des dimensions et de leurs index � partir
/// des sous requ�tes contenues dans la liste
/// </summary>
public void BuildXmlaPartitionProcessScript()
{
    // Ent�te de la requ�te XMLA xmlaDimensionProcessUpdate
    Dts.Variables["User::xmlaDimensionProcessUpdate"].Value =
        "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">"
        + "<Parallel>";

    // boucle sur chaque occurence de la liste
    foreach (string line in lstDimensionProcess)
    {
        Dts.Variables["User::xmlaDimensionProcessUpdate"].Value += line;
    }

    // Pied de la requ�te XMLA xmlaDimensionProcessUpdate
    Dts.Variables["User::xmlaDimensionProcessUpdate"].Value +=
        "</Parallel>"
        + "</Batch>";

    // La requ�te XMLA de process d'index est obtenue tout simplement � partir de la requete xmlaDimensionProcessUpdate en rempla�ant les occurences
    // ProcessUpdate et ProcessData par ProcessIndexes
    Dts.Variables["User::xmlaDimensionProcessIndex"].Value =
        Dts.Variables["User::xmlaDimensionProcessUpdate"].Value.ToString().Replace("ProcessUpdate","ProcessIndexes").Replace("ProcessData","ProcessIndexes");
}

  • Une fois les requêtes XMLA construites il ne reste plus qu’à les exécuter à l’aide du composant Analysis Services Execute DDL Task :

N.B : veillez à bien configurer votre composant !

  • Votre premier sequence container doit maintenant ressembler à ceci :

Il ne nous reste plus qu’à adapter cette logique pour gérer les partitions de notre cube:

  • Nous avons besoin d’un Script Task afin de lister les groupes de mesures et leurs partitions. Il est primordial de bien identifier les groupes de mesures sur lesquels nous avons une gestion dynamique. Ces derniers sont listés dans la variable lstMeasureGroupMonthlyPartitionned et séparés par des virgules. Je vous rappelle que dans notre solution SSAS, les groupes de mesures sur lesquels nous mettons en place un partitionnement mensuel ne doivent avoir qu’une seule et unique partition, adoptant la convention de nommage <MeasureGroupID>_<AAAAMM> filtrée sur la borne de départ souhaitée. Pour les groupes de mesures avec un partitionnement mensuel, le script doit nous permettre de créer toutes les partitions intermédiaires entre la borne de départ (la première partition) et la date d’exécution et générer pour chacune d’elle les sous-requête XMLA de process. Pour les groupes de mesures n’appartenant pas à cette liste, il faut récupérer chaque partition en générant, pour chacune d’elle, une sous-requête XMLA de type process Data.
    • en entrée, le script à besoin des informations de connexion au cube (ssasServer, udmID et cubeID)
    • en sortie, le script va générer les ordres XMLA de création et de traitement des partitions et de leurs index (xmlaPartitionCreate, xmlaPartitionProcessData et xmlaPartitionProcessIndex) ainsi qu’alimenter deux booléens qui nous permettront de piloter les opérations de process. Les partitions ne sont à créer que si xmlaPartitionCreate > «  » sinon les partition ne sont à processert que si xmlaPartitionProcessData > «  »
    • le bon de code c# qui permettant d’effectuer nos opérations est le suivant :

/// <summary>
/// liste des sous-requête XMLA de process data
/// </summary>
private List<String> lstPartitionProcessData = new List<String>();

/// <summary>
/// liste des sous requête XMLA de création de partition
/// </summary>
private List<String> lstPartitionCreate = new List<String>();

private enum MeasureGroupPartitionningType : int
{
    Montly = 1,
    Weekly = 2
}

/// <summary>
/// Main
/// </summary>
public void Main()
{
    Microsoft.AnalysisServices.Server amoUdm = new Microsoft.AnalysisServices.Server();
    Microsoft.AnalysisServices.Cube amoCube;
    String ssasServer, udmID, cubeID, lstMeasureGroupMonthlyPartitionned,lstMeasureGroupWeeklyPartitionned;

    ssasServer = Dts.Variables["User::ssasServer"].Value.ToString() ;
    udmID = Dts.Variables["User::udmID"].Value.ToString();
    cubeID = Dts.Variables["User::cubeID"].Value.ToString();
    lstMeasureGroupMonthlyPartitionned = Dts.Variables["User::lstMeasureGroupMonthlyPartitionned"].Value.ToString();
    lstMeasureGroupWeeklyPartitionned = Dts.Variables["User::lstMeasureGroupWeeklyPartitionned"].Value.ToString();

    amoUdm.Connect("Data Source="
        + ssasServer
        + ";Initial Catalog="
        + udmID
        + ";Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;");

    amoCube = amoUdm.Databases[udmID].Cubes[cubeID];

    foreach (Microsoft.AnalysisServices.MeasureGroup measureGroup in amoCube.MeasureGroups)
    {
        // Boolean afin de déterminer si le groupe de mesures a été testé
        Boolean checkMeasureGroup = false;

        // vérifie si le groupe de mesures dispose d'une gestion de partitions mensuelles.
        foreach (String mgmp in lstMeasureGroupMonthlyPartitionned.Split(','))
        {
            if (mgmp.Trim().ToLower() == measureGroup.Name.Trim().ToLower())
            {
                // Le cas échéant, on lance la procédure permettant de gérer les partitions mensuelles
                ManageMeasureGroupPartitions(udmID, cubeID, measureGroup, (int) MeasureGroupPartitionningType.Montly);
                checkMeasureGroup = true;
                break;
            }
        }

        if (!checkMeasureGroup)
        {
            // vérifie si le groupe de mesures dispose d'une gestion de partitions hebdomadaires.
            foreach (String mgmp in lstMeasureGroupWeeklyPartitionned.Split(','))
            {
                if (mgmp.Trim().ToLower() == measureGroup.Name.Trim().ToLower())
                {
                    // Le cas échéant, on lance la procédure permettant de gérer les partitions hebdomadaires
                    ManageMeasureGroupPartitions(udmID, cubeID, measureGroup, (int) MeasureGroupPartitionningType.Weekly);
                    checkMeasureGroup = true;
                    break;
                }
            }
        }

        // si le groupe de mesures n'a pas été testé, il s'agit d'un groupe de mesures sans partitionnement => il faut le processer
        if (!checkMeasureGroup)
        {
            AddMeasureGroupProcessDataOrder(udmID, cubeID, measureGroup.ID);
        }

    }

    // construction des ordres XMLA de création des partitions
    if (lstPartitionCreate.Count > 0)
    {
        BuildXmlaScript(lstPartitionCreate, "User::xmlaPartitionCreate",false);
        Dts.Variables["User::execPartitionCreate"].Value = true;
    }

    // construction des ordres XMLA de process des partitions
    if (lstPartitionProcessData.Count > 0)
    {
        BuildXmlaScript(lstPartitionProcessData, "User::xmlaPartitionProcessData",true);
        // La requête XMLA de process index est obtenue tout simplement à partir de la requête xmlaPartitionProcessData en remplaçant les occurences ProcessData par ProcessIndexes
        Dts.Variables["User::xmlaPartitionProcessIndex"].Value = Dts.Variables["User::xmlaPartitionProcessData"].Value.ToString().Replace("ProcessData","ProcessIndexes");
        Dts.Variables["User::execPartitionProcess"].Value = true;
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

/// <summary>
/// Procédure de gestion des partitions d'un groupe de mesures
/// </summary>
/// <param name="udmID">ID de l'udm à processer</param>
/// <param name="cubeID">Id du cube à processer</param>
/// <param name="measureGroup">Groupe de mesures à traiter</param>
/// <param name="type">Type de partitionnement</param>
private void ManageMeasureGroupPartitions(String udmID, String cubeID, Microsoft.AnalysisServices.MeasureGroup measureGroup, int type)
{
    switch (type)
    {
        case (int) MeasureGroupPartitionningType.Montly:
            // Récupère le mois de la première partition au format AAAAMM
            String refMonthPartition = measureGroup.Partitions[0].ID.Substring(measureGroup.Partitions[0].ID.Length - 6, 6);

            // Construction du mois de début au format DateTime
            DateTime startMonthDate = new DateTime(
                Convert.ToInt32(refMonthPartition.Substring(0, 4))
                , Convert.ToInt32(refMonthPartition.Substring(4, 2))
                , 1);

            // Construction du mois courant au format DateTime à partir de (J-1)
            DateTime endMonthDate = DateTime.Today.AddDays(-1);
            endMonthDate = endMonthDate.AddDays(1 - endMonthDate.Day);

            // Boucle sur chaque mois entre le mois de début et le mois courant
            do
            {
                // Construction de l'ID de partition à tester (attention aux conventions de nommages)
                String monthID = startMonthDate.Year.ToString() + (startMonthDate.Month < 10 ? "0" + startMonthDate.Month.ToString() : startMonthDate.Month.ToString());
                String partitionID = measureGroup.ID + "_" + monthID;

                // Si la partition n'existe pas, nous devons la créer et la traiter
                if (!PartitionExists(measureGroup, partitionID))
                {
                    AddXmlaPartitionCreateScript
                    (
                        udmID,
                        cubeID,
                        measureGroup.ID,
                        partitionID, measureGroup.Partitions[0].DataSource.ID,
                        ((Microsoft.AnalysisServices.QueryBinding)(measureGroup.Partitions[0].Source)).QueryDefinition.Replace(refMonthPartition, monthID),
                        measureGroup.Partitions[0].StorageMode.ToString(),
                        measureGroup.Partitions[0].ProcessingMode.ToString(),
                        System.Security.SecurityElement.Escape(measureGroup.Partitions[0].Slice.ToString().Replace(refMonthPartition, monthID))

                    );
                    AddPartitionProcessDataOrder(udmID, cubeID, measureGroup.ID, partitionID);
                }
                // Sinon nous devons traiter : toutes les partitions dont le statut est différent de Processes ou
                // bien la dernière partition
                else if (measureGroup.Partitions[partitionID].State != AnalysisState.Processed || startMonthDate == endMonthDate)
                {
                    AddPartitionProcessDataOrder(udmID, cubeID, measureGroup.ID, partitionID);
                }

                startMonthDate = startMonthDate.AddMonths(1);
            }
            while (startMonthDate <= endMonthDate);
            break;
        case (int) MeasureGroupPartitionningType.Weekly:
            // Récupère la première de la première partition au format AAAAMM
            String refWeekPartition = measureGroup.Partitions[0].ID.Substring(measureGroup.Partitions[0].ID.Length - 6, 6);

            // Construction de la première semaine de début au format DateTime
            DateTime startWeekDate = FirstDateOfWeek( Convert.ToInt32(refWeekPartition.Substring(0, 4)),Convert.ToInt32(refWeekPartition.Substring(4, 2)) );

            // Construction de la semaine courante au format DateTime à partir de (J-1)
            int endWeekYear, endWeekNumber;
            GetWeekNumber(DateTime.Today.AddDays(-1), out endWeekYear, out endWeekNumber);
            DateTime endWeekDate = FirstDateOfWeek(endWeekYear, endWeekNumber);

            // Boucle sur chaque semaine entre la semaine de début et la semaine courante
            do
            {
                // Construction de l'ID de partition à tester (attention aux conventions de nommages)
                int startWeekYear, startWeekNumber;
                GetWeekNumber(startWeekDate, out startWeekYear, out startWeekNumber);
                String weekID = startWeekYear.ToString() + (startWeekNumber < 10 ? "0" + startWeekNumber.ToString() : startWeekNumber.ToString());
                String partitionID = measureGroup.ID + "_" + weekID;

                // Si la partition n'existe pas, nous devons la créer et la traiter
                if (!PartitionExists(measureGroup, partitionID))
                {
                    AddXmlaPartitionCreateScript
                    (
                        udmID,
                        cubeID,
                        measureGroup.ID,
                        partitionID, measureGroup.Partitions[0].DataSource.ID,
                        ((Microsoft.AnalysisServices.QueryBinding)(measureGroup.Partitions[0].Source)).QueryDefinition.Replace(refWeekPartition,weekID),
                        measureGroup.Partitions[0].StorageMode.ToString(),
                        measureGroup.Partitions[0].ProcessingMode.ToString(),
                        System.Security.SecurityElement.Escape(measureGroup.Partitions[0].Slice.ToString().Replace(refWeekPartition, weekID))
                    );
                    AddPartitionProcessDataOrder(udmID, cubeID, measureGroup.ID, partitionID);
                }
                // Sinon nous devons traiter : toutes les partitions dont le statut est différent de Processes ou
                // bien la dernière partition
                else if (measureGroup.Partitions[partitionID].State != AnalysisState.Processed || startWeekDate == endWeekDate)
                {
                    AddPartitionProcessDataOrder(udmID, cubeID, measureGroup.ID, partitionID);
                }

                startWeekDate = startWeekDate.AddDays(7);
            }
            while (startWeekDate <= endWeekDate);
            break;
        default:
            break;
    }

}

/// <summary>
/// Boucle sur les partitions d'un groupe de mesures pour déterminer s'il contient partitionID
/// </summary>
/// <param name="measureGroup">groupe de mesure</param>
/// <param name="partitionID">ID de la partition à tester</param>
/// <returns>vrai si la partition existe déjà, sinon faux</returns>
private Boolean PartitionExists(Microsoft.AnalysisServices.MeasureGroup measureGroup, String partitionID)
{
    foreach (Microsoft.AnalysisServices.Partition partition in measureGroup.Partitions)
    {
        if (partition.ID == partitionID)
        {
            return true;
        }
    }
    return false;
}

/// <summary>
/// Procédure de création d'uns sous-requête XMLA de création de partition
/// </summary>
/// <param name="udmID">ID de l'udm à traiter</param>
/// <param name="cubeID">ID du cube à traiter</param>
/// <param name="measureGroupID">ID du groupe de mesure à traiter</param>
/// <param name="partitionID">ID de la partition à traiter</param>
/// <param name="dataSourceID">ID de la Data Source</param>
/// <param name="queryDefinition">Requête de la partition</param>
/// <param name="storageMode">Mode de stockage</param>
/// <param name="processingMode">Type de process</param>
/// <param name="slice">Slice de partition</param>
private void AddXmlaPartitionCreateScript(String udmID, String cubeID, String measureGroupID, String partitionID, String dataSourceID, String queryDefinition,String storageMode,String processingMode, String slice)
{
    lstPartitionCreate.Add(
        "<Create xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">"
        + "<ParentObject>"
        + "<DatabaseID>" + udmID + "</DatabaseID>"
        + "<CubeID>" + cubeID + "</CubeID>"
        + "<MeasureGroupID>" + measureGroupID + "</MeasureGroupID>"
        + "</ParentObject>"
        + ""
    + "<Type>ProcessData</Type>"
    + "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>"
    + "</Process>"
    );
}

/// <summary>
/// Ajoute une sous-requête de type process data à la liste lstPartitionProcessData
/// </summary>
/// <param name="udmID">ID de l'udm à traiter</param>
/// <param name="cubeID">ID du cube à traiter</param>
/// <param name="measureGroupID">ID du groupe de mesure à traiter</param>
private void AddMeasureGroupProcessDataOrder(String udmID, String cubeID, String measureGroupID)
{
    lstPartitionProcessData.Add(
    "<Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">"
    + ""
    + "<Type>ProcessData</Type>"
    + "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>"
    + "</Process>"
    );
}

/// <summary>
/// Génération des requêtes XMLA à partir des sous requêtes contenues dans une liste
/// </summary>
/// <param name="lstSource">Liste contenant les sous requêtes XMLA</param>
/// <param name="packageVarDest">variable de destination à charger</param>
private void BuildXmlaScript(List<String> lstSource, String packageVarDest, Boolean batchParallel)
{
    // Entête de la requête XMLA xmlaPartitionProcessData
    Dts.Variables[packageVarDest].Value =
        "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">"
        + (batchParallel ? "<Parallel>" : "" ) ;

    // Ajoute chaque sous-requête XMLA de process de partition à partir de la liste xmlaPartitionProcessData
    foreach (string line in lstSource)
    {
        Dts.Variables[packageVarDest].Value += line;
    }

    // Pied de la requête XMLA xmlaPartitionProcessData
    Dts.Variables[packageVarDest].Value +=
        (batchParallel ? "</Parallel>" : "")
        + "</Batch>";
}

/// <summary>
/// Fonction retournant le numero de la semaine ISO
/// </summary>
/// <param name="fromDate">Date</param>
/// <returns>N° de la semaine ISO</returns>
private int WeekNumber(DateTime fromDate)
{
    // Récupère le premier jour de l'année
    DateTime startOfYear = fromDate.AddDays(-fromDate.Day + 1).AddMonths(-fromDate.Month + 1);
    // Récupère le dernier jour de l'année
    DateTime endOfYear = startOfYear.AddYears(1).AddDays(-1);
    // ISO 8601 : la première semaine de l'année est celle qui contient le premier jeudi
    // DayOfWeek retourne 0-6 respectivement du dimanche au samedi
    int[] iso8601Correction = { 6, 7, 8, 9, 10, 4, 5 };
    int nds = fromDate.Subtract(startOfYear).Days + iso8601Correction[(int)startOfYear.DayOfWeek];
    int wk = nds / 7;
    switch (wk)
    {
        case 0:
            // appel récursif de la fonction pour avoir le n° de la semaine du 31/12 de l'année précédente
            return WeekNumber(startOfYear.AddDays(-1));
        case 53:
            // si le 31/12 tombe avant le premier jeudi de l'année suivante
            if (endOfYear.DayOfWeek < DayOfWeek.Thursday)
                return 1;
            else
                return wk;
        default: return wk;
    }
}

/// <summary>
/// Procédure retournant le numero et l'année de la semaine ISO
/// </summary>
/// <param name="date">Date</param>
/// <param name="year">Année de la semaine ISO</param>
/// <param name="wk">N° de la semaine ISO</param>
private void GetWeekNumber(DateTime date, out int year, out int wk)
{
    year = date.Year;
    // Récupère le premier jour de l'année
    DateTime startOfYear = new DateTime(year, 1, 1);
    // Récupère le dernier jour de l'année
    DateTime endOfYear = new DateTime(year, 12, 31);
    // ISO 8601 : la première semaine de l'année est celle qui contient le premier jeudi
    // DayOfWeek retourne 0-6 respectivement du dimanche au samedi
    int[] iso8601Correction = { 6, 7, 8, 9, 10, 4, 5 };
    int nds = date.Subtract(startOfYear).Days + iso8601Correction[(int)startOfYear.DayOfWeek];
    wk = nds / 7;
    switch (wk)
    {
        case 0:
            // appel récursif de la fonction pour avoir le n° de la semaine du 31/12 de l'année précédente
            GetWeekNumber(startOfYear.AddDays(-1), out year, out wk);
            break;
        case 53:
            // si le 31/12 tombe avant le premier jeudi de l'année suivante
            if (endOfYear.DayOfWeek < DayOfWeek.Thursday)
            {
                wk = 1; year += 1;
            }
            break;
        default:
            break;
    }
}

/// <summary>
/// Fonction retournant le premier jour de la semaine
/// </summary>
/// <param name="year">week year</param>
/// <param name="weekOfYear">week number</param>
/// <returns>date</returns>
private static DateTime FirstDateOfWeek(int year, int weekOfYear)
{
    DateTime jan1 = new DateTime(year, 1, 1);
    int daysOffset = (int)CultureInfo.CurrentCulture.DateTimeFormat.FirstDayOfWeek - (int)jan1.DayOfWeek;
    DateTime firstMonday = jan1.AddDays(daysOffset);
    int firstWeek = CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(jan1, CultureInfo.InvariantCulture.DateTimeFormat.CalendarWeekRule, CultureInfo.CurrentCulture.DateTimeFormat.FirstDayOfWeek);

    if (firstWeek <= 1)
    {
        weekOfYear -= 1;
    }

    return firstMonday.AddDays(weekOfYear * 7);
}

  • Comme pour les dimensions, un fois vos scripts XMLA construits, il ne vous reste plus qu’à les exécuter.

Au final l’allure de votre package est la suivante :

N.B : s’il vous plait par pitié faites des packages simples, qui ne ressemblent pas à des sapins de noël.

Voyons voir désormais son comportement, avant l’execution de celui-ci, mon groupe de mesure des ventes ne contient qu’une seule partition :

après l’execution, nous constatons qu’il a bien fait le boulot :

Les avantages d’un tel partitionnement sont les suivants :

  • Partitions alignées sur celles de sqlserver => temps de process et de requêtage optimum
  • Généricité du package SSIS pouvant être réutilisé sur l’ensemble de vos solutions SSAS
  • Souplesse du package SSIS ne nécessitant aucune modification en cas de modification de structure de votre solution SSAS car il suffit de mettre à jour le paramétrage par l’intermédiaire des configurations SSIS.

Les inconvénients :

  • Le code source de votre projet SSAS sera différent de l’UDM en production, toutes les partitions créées dynamiquement seront absentes de votre solution BIDS. Pour cela vous avez deux options :
    • Avant toute modification, récupération du projet à partir de votre UDM. Cela permettra de récupérer l’ensemble des partitions. Cette solution n’est viable que si vos développements avec leur passage en production ne prennent pas plus d’un mois.
    • Laisser votre projet sous BIDS tel quel, et faire vos MEP sur un serveur d’intégration. Une fois votre UDM processée, synchronisez la directement sur votre serveur de production (ma privilégiée). Je reviendrai dessus lors d’un post archi

Il ne vous reste plus qu’à enrichir cette base de travail si vous avez à faire d’autre type de partitionnement …

Les sources sont disponibles sur SkyDrive http://sdrv.ms/T3ReRd

6 réflexions sur “Gestion dynamique des partitions OLAP avec SSIS

  1. Suite à quelques tests réalisés sur des UDM conséquentes, je me suis rendu compte que la création des partitions, une à une, via les fonctions AMO était beaucoup trop longue. L’article a donc été modifié en conséquence; la création des partitions se fait désormais par script XMLA (création de 200 paritions en 6 secs contre 20 mins auparavant).
    Petit bonus, j’en ai profité pour gérer aussi le type de partitionnement hebdomadaire.

  2. Bonjour,

    Beau boulot. Par contre il faut bien préciser que lors de la création de la partition, si le slice n’est pas positionné, le partitionnement ne sert pas à grand chose d’un point de vue performance des requêtes.
    De plus, attention à la grnaularité de partitionnement, SSAS et Windows Server s’en sortent mal quand le nombre de partition est trop important (de mémoire +5000 en 2008 R2).

    A+

  3. Bonjour Partice,
    Tu fais bien de le préciser. En effet, la solution telle quelle ne permet d’avoir des gains qu’en terme de processing mais pas de queyring, d’où l’importance des slices de partition.
    Je ne parle pas non plus de design d’agregation qui peut être aussi une solution pour des gains de performances en terme de querying (sur de très gros cubes).
    Je ferai certainement un post sur le merge de partition pour les données d’histo/d’archives pour lesquelles le business est bien moins critique. Ce qui permettra de répondre à la problématique d’un trop grand nombre de partitions.
    Merci pour ta contribution.
    @+

  4. Suite à la remarque de Patrice, j’ai modifié le code pour prendre en compte le slice de partition.
    Vous l’aurez surement remarqué, j’ai eu quelques déboires avec les copier/coller des sources C#. Malheureusement, les parties de codes XMLA mettent un peu le bazard et wordpress a du mal à s’y retrouver, finissant même par me supprimer quelques lignes de codes.
    J’ai fini par mettre le package en pièce jointe à la fin de l’article (pensez à renommer l’extension en .dtsx)
    N.B : Si quelqu’un à la solution pour mes problèmes de copier/coller je suis preneur.😉

  5. L’autre inconvénient, c’est que le système source ne doit pas renvoyer de lignes sur les mois précédents, car tu ne reprocesses pas une partition déjà traitée hors mois en cours si j’ai bien compris. Ceci dit, je ne vois pas de manière simple de gérer ce cas hormis créer une base de configuration qui pointerait les partitions à processer.

    • Salut David,
      En effet, la solution proposée ne processe que la derniere partition et les partitions dont le statut est unpocessed. Dans le cas de données antidatées, comme tu l’as indiqué, il est nécessaire de mettre en place une table tierce listant les partitions à reprocesser exceptionnellement. Cette dernière doit être alimentée lors du chargement des données de faits (avec de bonnes conventions de nommage ça se fait très facilement). Il ne reste ensuite plus qu’à modifier le package proposé pour exploiter cette table. Et là je vois deux solutions :
      – ajouter à la fin du package un simple boucle permettant de processer séquentiellement les dites partitions (solution la plus simple mais la moins sexy)
      – lineariser, dans une variable, la liste de partitions à reprocesser et passer cette variable en paramètre au script task de gestion des partitions. Il suffit ensuite de tester l’ existence de chaque partition dans notre variable et de les ajouter à la liste des partitions à traiter. Définitivement ma solution préférée (très peu de modifications à apporter et conservation de la logique de process existante).

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