Un de mes besoins client est de pouvoir automatiser la création de partitions ainsi que le process des objets SSAS. Je me suis donc lancé à la recherche d'une solution existante et j'ai trouvé ça => Article SSAS-INFO mais comme d'habitude lorsque l'on veut réutiliser, cela ne convient pas complètement à mon besoin :

• Gestion uniquement des partitions
• Script écrit en VB
• Script écrit en procédurale
• Impossible de spécifier une requête SQL personalisée pour mes partitions
• Fonctionne uniquement sur du mono serveur
• ...

Me voici donc lancé pour la réalisation d'une base SQL + Package SSIS + Script AMO (en C#) pour réaliser les actions suivantes:

• Créer n'importe quel objet SSAS
  • Base
  • Cube
  • Dimension
  • Groupe de mesure
  • Partitions
    • Avec la prise en compte de requêtes SQL Personalisées
    • Avec la prise en compte de DirectSlice MDX
• Supprimer n'importe quel objet SSAS
• Process n'importe quel objet SSAS

Pour ce faire, j'ai créé un modèle de base de données un peu complexe je vous l'accorde, mais heureusement, j'ai aussi fait les procédures stockées qui permettent de remplir simplement les tables.

Ci-dessous, le schéma de l'architecture:

1 - La partie base de données

Ci-dessous le schéma de la base de données :

Afin de remplir toutes ces tables, nous utiliserons la procédure stockée AddSSASObject disponible dans la section telechargement.

EXEC [dbo].[AddSSASObject](@ProjectName NVARCHAR(255)
, @ObjectType NVARCHAR(255)
, @DatabaseName NVARCHAR(255)
, @CubeName NVARCHAR(255)
, @measureGroupName NVARCHAR(255)
, @DataSourceID NVARCHAR(255)
, @PartitionName NVARCHAR(255)
, @SQLQuery NVARCHAR(4000)
, @MDXSlice NVARCHAR(4000)
, @DimensionName NVARCHAR(255)
, @objectID INT = 0 OUTPUT)

Cette procédure prend en paramètres :

• @ProjectName
  Le nom du projet qui doit être créé préalablement dans la table dbo.Project
• @ObjectType
  Le type d'objet, celui-ci peut être:
  • Database
  • Cube
  • MeasureGroup
  • Partition
  • Dimension
     
• @DatabaseName
  Le nom de la dataBase
• @CubeName
  Le nom du cube, celui-ci peut-être null s'il s'agit d'une dimension
• @measureGroupName
  Le nom du measure group, celui-ci peut-être null s'il s'agit d'une dimension ou d'un cube
• @DataSourceID
  A spécifier uniquement s'il s'agit de la création d'une partition, cela correspond à l'id de la datasource sur laquelle s'applique la partition
• @PartitionName
  Le nom de la partition à créer
• @SQLQuery
  La requête SQL qui va servir à alimenter la partition, dans le cas d'un table binding, mettre une requête non filtrée
• @MDXSlice
  La requête MDX pour spécifier un slice sur la partition
• @DimensionName
  Le nom de la dimension que l'on veut créer.

La procédure stockée retourne l'@objectID créé

Exemple :

Ma base contient uniquement le projet "JeanMichProject" et je veux ajouter la partition "JeanNoPartoch" voici l'appel à la procédure à exécuter :

EXEC dbo.AddSSASObject 'JeanMichProject'
	,'Partition'
	,'MaDatabase'
	,'MonCube'
	,'MonMeasureGroup'
	,'MaDataSource'
	,'JeanNoPartoch'
	,'SELECT * FROM MaTable'
	,NULL
	,NULL

La base étant vide, la procédure va me remplir:

• La table database avec la valeur 'MaDatabase'
• La table cube avec la valeur  'MonCube'
• La table measure group avec la valeur  'MonMeasureGroup'
• La table partition avec la valeur 'JeanNoPartoch'

Puis elle va créer l'objet qui lie l'ensemble au projet et retourner son ID

Si par la suite je lance la procédure suivante :

EXEC dbo.AddSSASObject 'JeanMichProject'
	,'Partition'
	,'MaDatabase'
	,'MonCube'
	,'MonMeasureGroup'
	,'MaDataSource'
	,'JeanBOBPartoch' -- Autre partition
	,'SELECT * FROM MaTable'
	,NULL
	,NULL

Deux lignes seront ajoutées en base, la ligne relative à la partition 'JeanBOBPartoch et la ligne relative à l'objet.

Une fois ces informations saisies, il me faut encore spécifier quelles actions effectuer sur ces objets et quand les exécuter c'est là qu'intervient la procédure PlanAction :

EXEC [dbo].[PlanAction](@objectID INT
, @recurrenceType NVARCHAR(50) = 'Weekly'
, @dayOfTheMonth INT
, @dayID INT
, @isActivated BIT
, @ActionType NVARCHAR(255))

Cette procédure prend en paramètres :

• @ObjectID
  L'id retourné par la précédente procédure stockée
• @recurrenceType
  • Weekly
  • Monthly
  • OnDemand
• @dayOfTheMonth
  Si le recurrenceType est Monthly alors il faut remplir ce champs avec une valeur de 1 à 31 l'action sera effectuée tous les mois ce jour
• @dayID
  Si le reccurenceType est Weekly alors il faut remplir ce champs avec une valeur de 1 à 7 ou 1 = dimanche (to do mettre un nom de jour)
• @isActivated 1 = à traiter 0 = ne pas traiter
• @ActionType
  • Create
  • Delete
  • Process Default
  • Process Full
  • Process Data
  • Process Structure
  • Unprocess
  • Process Index

Donc si je veux que ma partition JeanMich soit processée tous les lundi j'exécute la procédure suivante :

EXEC dbo.PlanAction 125
	,'Weekly'
	,NULL
	,2
	,1
	,'Process Full'

Et voilà notre base de données est remplie avec les champs suffisant pour créer/processer ma partition. (Créer car si je met un process sur une partition non existante, le script AMO va créer cette partition)

Il faut maintenant mettre à disposition à notre package SSIS un moyen simple d'acceder à ce merdier  nos données nous allons créer deux vues.

La première (disponible dans la section telechargement) nous permet d'obtenir tous les enregistrements planifiés :

SELECT [ProjectID]
	,[ProjectName]
	,[Serveur]
	,[ProjectPriority]
	,[ObjectID]
	,[ObjectTypeID]
	,[ObjectTypeName]
	,[ObjectTypePriority]
	,[DatabaseID]
	,[DatabaseName]
	,[ActionTypeID]
	,[RecurrenceType]
	,[DayOfTheMonth]
	,[DayID]
	,[isActivated]
	,[ActionGroup]
	,[ActionName]
	,[ActionTypePriority]
	,[CubeID]
	,[CubeName]
	,[DimensionID]
	,[DimensionName]
	,[MeasureGroupID]
	,[MeasureGroupName]
	,[DataSourceID]
	,[MDXSlice]
	,[PartitionID]
	,[PartitionName]
	,[SQLQuery]
FROM [SSAS_Configurations].[dbo].[ObjectListing]

La seconde nous retourne uniquement les enregistrement éligibles pour une action aujourd'hui, elle est basée sur la vue précédente mais à la clause suplémentaire suivante :

ALTER VIEW [dbo].[ObjectListingForToday]
AS
SELECT *
FROM ObjectListing O
WHERE isActivated = 1
	AND (
		(
			RecurrenceType = 'Monthly'
			AND O.DayOfTheMonth = DATEPART(DAY, GETDATE())
			)
		OR (
			(
				RecurrenceType = 'Weekly'
				AND O.DayID = DATEPART(WEEKDAY, GETDATE())
				)
			)
		OR (RecurrenceType = 'OnDemand')
		)

Notre lot SSIS recevra donc toutes les informations nécessaires pour traiter les objets du jour!

2 - La partie SSIS

Sur le lot SSIS je me suis un peu enflamé sur les foreach, mais c'est pour mieux séparer mes différents éléments et pouvoir paralléliser le process des partitions.

Il n'y a en théorie rien à configurer sur ce package pour qu'il s'adapte à votre environnement, il est essentiellement composé d'une script task qui va traiter nos éléments.

3 - La partie Script C#

La script task est composée de 9 classes et 1 interface.

Vous trouverez le détail du code en téléchargement.

4 - To Do List

• La liste des ActionType n'est pas complète, j'ai seulement implementé ceux que j'ai pu tester.
• Finir la partie Rôle

4 - Telechargement

Le zip complet ici => Cube Builder V0.2 <=

Changelog Version 0.2:

• Ajout de commentaires.
• Correction de bugs.

Le zip complet ici => Cube Builder V0.1 <=

Il contient:

• Le dtsx
• Le schéma de la base de donnée
• L'initialisation des données (Type d'actions ...)
• Un fichier excel de suivis des logs (Pensez à changer la connexion)

Encore une fois, il s'agit ici que d'une version non terminée sur laquelle il est possible d'ajouter des milliards de choses :)