Gestion dynamique des partitions SSAS – 2ème partie

Nous voilà dans la 2ème partie de cet article où il y a moins de bla bla mais plus de pratique (mettant nos main dans la patte !!)

Dans cette  partie, je vais expliquer les opérations à suivre étape par étape pour créer les partitions de cube d’une façon dynamique.

  • Étape 1 :

Tout d’abord, préparez les données de test, pour cela, on va travailler avec la base de données et le cube AdventureWorksDW, vous pouvez télécharger les sources ainsi les scripts de déploiement à partir des liens ci-dessous :

  • Étape 2 :

Dans un premier temps, nous allons créer une table de paramétrage dans le schéma [par] que nous appellerons Par.PartitionManager.

Nous avons besoin d’un enregistrement pour chaque groupe de mesures que nous souhaitons partitionner.

CREATE TABLE [par].[PartitionManager](
       [IdMeasureGroup] [smallint] IDENTITY(1,1) NOT NULL,
       [CubeName] [varchar](50) NULL,
       [MeasureGroupName] [varchar](50) NULL,
       [DefaultPartitionName] [varchar](50) NULL,
       [SliceQuery] [varchar](500) NULL
) ON [PRIMARY]
GO
  • CubeName : Nom du cube
  • MeasureGroupName : Nom du groupe de mesure
  • DefaultPartitionName : Nom de la partition crée par default dans le projet SSAS pour chaque groupe de mesure
  • SliceQuery : La requête qui permet d’avoir les éléments sur lesquels on va faire le partitionnement du Cube. Pour notre exemple, on a choisi de partitionner chaque groupe de mesure en fonction de l’année, pour cela il faut avoir une liste distincte des années de chaque table de fait concernée. Ex :
SELECT DISTINCT YEAR(fis.OrderDate) AS year FROM [dbo].[FactInternetSales] fis

Voici la requête pour remplir cette table (Les 3 groupes de mesures qu’on va partitionner sont : Internet Sales, Internet Orders et Reseller Sales) :

INSERT INTO par.PartitionManager (CubeName, MeasureGroupName, DefaultPartitionName, SliceQuery)
VALUES
('Adventure Works', 'Internet Sales', 'Internet_Sales','SELECT DISTINCT YEAR(fsi.OrderDate) AS year FROM dbo.FactInternetSales fsi'),
('Adventure Works', 'Internet Orders', 'Internet_Orders','SELECT DISTINCT YEAR(fsi.OrderDate) AS year FROM dbo.FactInternetSales fsi'),
('Adventure Works', 'Reseller Sales', 'Reseller_Sales','SELECT DISTINCT YEAR(frs.OrderDate) AS year FROM dbo.FactResellerSales frs')
  • Étape 3 :

Dans la solution AdventureWorksDW2012Multidimensional-EE, Ouvrez l’onglet Partitions et modifiez la requête source des 3 Partitions Internet_Sales, Internet_Orders et Reseller_Sales en le rajoutant à la fin de chaque requête.

WHERE OrderDateKey BETWEEN -1 AND 0

1

Avec ces modifications, nous somme sûr que nos groupes de mesures seront vides, mais nous ne vous inquiétez pas, nous allons voir dans la prochaine étape le secret derrière ces modifications.

  • Étape 4 :

Maintenant, on va attaquer l’étape la plus importante, c’est la création de notre package SSIS qui va gérer la partition du Cube.

Commençant par créer un nouveau projet SSIS qu’on va appeler AdventureWorksCubePartition, dans ce projet ajoutez un package SSIS DWH_CreatePartitionOLAP avec une connexion vers la base de données AdventureWorksDW2012.

2

On aura besoin des variables suivantes avec leurs valeurs et leurs expressions comme indiqué ci-dessous :

7

Prochaine étape consiste à rajouter un  Execute SQL Task qu’on appellera EST – Get MeasureGroups pour récupérer les données de la table par.PartitionManager  et les passer dans la  variable User:PartitionList.

EST – Get MeasureGroups sera configuré de la façon suivante :

4

5

On va cette fois-ci parcourir User:PartitionList, pour cela on va mettre en place un Foreach Loop Container dont l’objectif  est de parcourir la liste des objets qu’on a dans User:PartitionList et de les mapper avec les variables qu’on a créer avant, bien sûr, à chaque itération les valeurs de ces variables vont être changées, C’est l’objectif non !!! :).

8

Dans l’éditeur Foreach Loop Container, sélectionnez comme type d’énumération  Foreach ADO Enumerator et User: PartitionList dans la variable source de l’objet ADO.

9

Sélectionnez les variables à mapper avec les valeurs de la collection.

10

Rajoutant maintenant un Execute SQL Task (EST – Get Partition Slice) à l’intérieur du Foreach Loop Container.

11

Notre nouveau Execute SQL Task sera configuré de la façon suivante:

12

13

14

l’Execute SQL Task  (EST – Get Partition Slice) va nous permettre d’exécuter la requête User:QuerySlice qu’on a récupéré à chaque itération de la boucle et qui correspond à la colonne SliceQuery de la table par.PartitionManager.

L’exécution de cette requête retourne la liste des années distincte de chaque table de fait et qui font notre élément de partitionnement de chaque groupe de mesures.

Maintenant, Il faut boucler sur cette liste pour créer une partition par année. Pour cela on va rajouter un autre composant Foreach Loop Container juste après le EST – Get Partition Slice.

17

18

Le partitionnement du Cube en SSIS se fait par la mise en place d’un Script Task (STC – AMO Partitionning) qui contiendra un Script c#.

15

En entrée, le script à besoin des informations de connexion au cube ainsi que d’autres informations comme indiqué ci-dessous :

16

Maintenant je vais expliquer l’implémentation du script c#. Le principe est simple, pour chaque partition et année passées en entrée du script, on va créer une partition dédiée à l’année, en remplaçant le ‘Between -1 and 0’ qu’on mis à la fin de la requête source de notre partition par le Between entre la clé de la date début et date de fin de l’année en entrée.

Pour cela nous utilisons les fonctionnalités AMO(Analysis Management Objects). Ci-dessous le code C# qui vous permettra d’arriver à vos fins :

NB : Il se peut que visual studio ne reconnaisse pas les fonctions d’AMO, c’est juste un problème de manque de référence, il faut ajouter une référence vers le dll d’AnalysisServices que vous trouverez dans ce répertoire :

C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.AnalysisServices.DLL

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.AnalysisServices;
#endregion

namespace ST_377f706deec74825b03f6cc85452c8e0
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        /// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        public void Main()
        {
            try
            {
                // TODO: Add your code here
                string OlapServerName = Dts.Variables["OlapServerName"].Value.ToString();
                string OlapDbName = Dts.Variables["OlapDbName"].Value.ToString();
                string CubeName = Dts.Variables["CubeName"].Value.ToString();
                string MeasureGroupName = Dts.Variables["MeasureGroupName"].Value.ToString();
                string PartitionName = Dts.Variables["PartitionName"].Value.ToString();
                string Slice = Dts.Variables["Slice"].Value.ToString();
                string DefaultPartitionName = Dts.Variables["DefaultPartitionName"].Value.ToString();
                string SlicePeriode = Dts.Variables["Period"].Value.ToString();

                Server olapServer = new Server();
                olapServer.Connect(OlapServerName);
                if (olapServer.Connected)
                {
                    // connected to server ok, so obtain reference to the OLAP database
                    Database db = olapServer.Databases.FindByName(OlapDbName);
                    if (db != null)
                    {
                        Cube cube = db.Cubes.FindByName(CubeName);
                        MeasureGroup mg = cube.MeasureGroups.FindByName(MeasureGroupName);
                        Partition part;
                        Partition partClone;
                        part = mg.Partitions.FindByName(PartitionName);
                        partClone = mg.Partitions.FindByName(DefaultPartitionName).Clone();
                        if (part != null)
                            part.Drop();
                        part = mg.Partitions.Add(PartitionName);
                        part.StorageMode = StorageMode.Molap;
                        QueryBinding QB = (QueryBinding)partClone.Source.Clone();
                        QB.QueryDefinition = QB.QueryDefinition.Replace("Between -1 and 0", "Between '" + (int.Parse(SlicePeriode) * 10000 + 101).ToString() + "' AND '" + (int.Parse(SlicePeriode) * 10000 + 1231).ToString() + "'");
                        part.Source = QB;
                        part.Slice = Slice;
                        part.Update();
                        mg.Refresh();
                        olapServer.Disconnect();
                    }
                }
            }
            catch (Exception e)
            {
                Dts.Events.FireError(0, "Task Name", e.Message + "\r" + e.StackTrace, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure; 
            }
        }
    }
}

Notre package SSIS est prêt à être exécuté !!!

19

Après l’exécution de notre package SSIS, on voit bien les nouvelles partitions crées pour chaque année d’analyse.

20

L’intégralité du projet est disponible en téléchargement : PartitionManager

 

4 thoughts on “Gestion dynamique des partitions SSAS – 2ème partie

  1. First of all I want to say great blog! I had a quick
    question which I’d like to ask if you don’t mind.
    I was interested to know how you center yourself and
    clear your head prior to writing. I have had difficulty clearing my thoughts
    in getting my thoughts out there. I do enjoy writing however it
    just seems like the first 10 to 15 minutes are wasted just trying to figure out
    how to begin. Any suggestions or hints? Thank you!

    Reply

    1. The only advice I can give you is to master your subject well and write notes before you start writing and of course being focused.

      Reply

  2. Bonjour,

    Lorsqu’on teste, on voit que la partition source reste, cela fait un doublon de données non ?

    Et par ailleurs, le SQL de la partition n’est pas mis à jour, on reste avec le WHERE OrderDateKey BETWEEN -1 AND 0 dans toutes les nouvelles partitions … je ne comprend pas pourquoi car le code à l’air correct

    Reply

    1. Bonjour,
      C’est normal que la partition source reste car comme vous pouvez le voir dans le code c#, elle nous sert pour récupérer la requête source afin de créer une nouvelle partition en remplaçant le BETWEEN -1 AND 0 par les bonnes dates.
      Par contre, ce n’est pas normal que les nouvelles partitions ne changent pas, je pense que vous avez louper quelque chose.

      Reply

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.