Gestion des partitions d’un cube SSAS avec SSIS

intelligence-analytics

Dans le cadre de projets concernant une volumétrie de données importante le partitionnement des groupes de mesure d’un cube SSAS est indispensable pour optimiser les temps de traitement et minimiser les temps de requêtes.

Cet article présente une manière de créer, supprimer et recharger dynamiquement les partitions d’un cube SSAS en utilisant l’ETL SSIS et le langage XMLA.

Le partitionnement d’un cube est généralement opéré sur l’axe temps. Ici c’est le champ OrderYearMonth qui est utilisé comme clé de partitionnement. Il permet de regrouper les données en fonction de l’année et du mois des commandes (ex : 201601)

Le XMLA est un langage qui exécute des ordres DDL (Data Definition Language) sur SSAS (Analysis services). Il permet de gérer des sources de données, des groupes de mesure, des rôles ou des partitions. Il peut être exécuté dans SSMM (Management Sudio) ou dans un composant spécifique de SSIS (Integration Services). Ce composant est disponible dans la boîte à outils de SSIS sous la section « autres taches »

composant

Etape 1 – Création d’une partition

Le script ci-dessous permet de créer une partition nommée Order200801 dans le cube Adventure Works2012 qui contient le groupe de mesure Sales Order Hearder.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>ProjetDemoSSAS</DatabaseID>
        <CubeID>Adventure Works2012</CubeID>
        <MeasureGroupID>Sales Order Header</MeasureGroupID>
    </ParentObject>
    <ObjectDefinition>
        <Partition 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" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
            <ID> Order200801</ID>
            <Name>Order200801</Name>
            <Source xsi:type="QueryBinding">
                <DataSourceID>Adventure Works2012</DataSourceID>
                <QueryDefinition>SELECT [Sales].[SalesOrderHeader].[SalesOrderID],[Sales].[SalesOrderHeader].[RevisionNumber],[Sales].[SalesOrderHeader].[OrderDate],[Sales].[SalesOrderHeader].[DueDate],[Sales].[SalesOrderHeader].[ShipDate],[Sales].[SalesOrderHeader].[Status],[Sales].[SalesOrderHeader].[OnlineOrderFlag],[Sales].[SalesOrderHeader].[SalesOrderNumber],[Sales].[SalesOrderHeader].[PurchaseOrderNumber],[Sales].[SalesOrderHeader].[AccountNumber],[Sales].[SalesOrderHeader].[CustomerID],[Sales].[SalesOrderHeader].[SalesPersonID],[Sales].[SalesOrderHeader].[TerritoryID],[Sales].[SalesOrderHeader].[BillToAddressID],[Sales].[SalesOrderHeader].[ShipToAddressID],[Sales].[SalesOrderHeader].[ShipMethodID],[Sales].[SalesOrderHeader].[CreditCardID],[Sales].[SalesOrderHeader].[CreditCardApprovalCode],[Sales].[SalesOrderHeader].[CurrencyRateID],[Sales].[SalesOrderHeader].[SubTotal],[Sales].[SalesOrderHeader].[TaxAmt],[Sales].[SalesOrderHeader].[Freight],[Sales].[SalesOrderHeader].[TotalDue],[Sales].[SalesOrderHeader].[Comment],[Sales].[SalesOrderHeader].[rowguid],[Sales].[SalesOrderHeader].[ModifiedDate],[Sales].[SalesOrderHeader].[OrderYearMonth]
		FROM [Sales].[SalesOrderHeader]
		WHERE OrderYearMonth = 200801</QueryDefinition>
            </Source>
            <StorageMode>Molap</StorageMode>
            <ProcessingMode>Regular</ProcessingMode>
            <ProactiveCaching>
                <SilenceInterval>-PT1S</SilenceInterval>
                <Latency>-PT1S</Latency>
                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
                <Source xsi:type="ProactiveCachingInheritedBinding" />
            </ProactiveCaching>
        </Partition>
    </ObjectDefinition>
</Create>

La requête présente entre les balises "QueryDefinition" permet de restreindre le groupe de mesure en fonction de la clé de partitionnement précédemment choisie. Dans ce cas il s’agit du champ OrderYearMonth. La partition Order200801 contiendra les données des commandes passées en janvier 2008.

Etape 2 – Suppression d’une partition

Le script XMLA ci-dessous permet de supprimer la partition Order200801 contenue dans le cube Adventure Works2012.

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>ProjetDemoSSAS</DatabaseID>
    <CubeID>Adventure Works2012</CubeID>
    <MeasureGroupID>Sales Order Header</MeasureGroupID>
    <PartitionID>Order200801</PartitionID>
  </Object>
</Delete>

Etape 3 – Traitement d’une partition

Le script ci-dessous permet de traiter la partition Order200801

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <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" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
      <Object>
        <DatabaseID>ProjetDemoSSAS</DatabaseID>
        <CubeID>Adventure Works2012</CubeID>
        <MeasureGroupID>Sales Order Header</MeasureGroupID>
        <PartitionID>Order200801</PartitionID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

La balise "Type" permet de choisir le type de traitement à appliquer sur la partition (traiter les données, traiter l’index, traiter entièrement…)

Etape 4 – Utilisation dans SSIS

La même méthode est utilisée pour créer, modifier, supprimer ou traiter une partition en XMLA dans SSIS. Une fois la connexion paramétrée, le composant « Tâche DDL d’exécution SQL server Analisys services » permet d’exécuter un script XMLA.

capture1

Pour rendre la création de partitions dynamique il faut :

  • Récupérer et placer dans une variable de type objet la liste des noms des partitions à créer.
  • Ajouter un conteneur de type ForEach qui boucle sur les différentes valeurs de la variable.
  • Dans ce conteneur, construire la requête XMLA en fonction de la valeur courante de la variable et la placer dans une seconde variable.
  • Exécuter la requête XMLA contenue dans cette seconde variable.
  • Procéder au traitement du cube pour que l’ensemble des partitions créées soient traitées.

capture2

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Captcha *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.