Si vous travaillez sur un entrepôt à haute volumétrie, vous serez certainement confronté à des problèmes de performances pour alimenter et requêter les tables les plus volumineuses. Pour résoudre ces problèmes vous devrez avoir recours au partitionnement (fonctionnalité disponible depuis SQL Server 2005, je le précise car il y a quelques irréductibles qui utilise « encore » du 2000).
La mise en œuvre du partitionnement n’est pas la plus complexe qui soit ; seulement le faire une fois c’est bien, mais réinventer la roue sur chaque projet ça use. Voyons comment nous pouvons industrialiser pour tous nos projets BI la création et la gestion des partitions sous SQL Server.
Le partitionnement en quelques mots
Le partitionnement permet de répartir les données d’une ou plusieurs tables et indexes dans plusieurs groupes de fichiers. Pour ventiler les données dans les différentes partitions, SQL Server va s’appuyer sur une clé de partitionnement que vous devrez définir. En BI, la clé de partitionnement la plus communément utilisée est l’axe temps dans ces différentes granularités (année, mois, semaine …)
Les avantages
- Les données étant stockées dans différents fichiers, les opérations de I/O vont pouvoir être parallélisées
- Vous pouvez requêter la table en interrogeant directement une ou plusieurs partitions. Vous pouvez donc pilotez la restriction des données vous-même.
- Côté SSIS ou TSQL, vous pouvez optimiser l’insertion de données et la gestion des indexes en utilisant si vous faites du « Partition Switching ». J’exposerai cette méthode dans un post ultérieur
- Côté SSAS, si vous partitionner correctement vos groupes de mesures en indiquant la ou les partitions mises en jeux, vous optimiser les temps de process pour les partitions en MOLAP et temps de querying pour les partitions en ROLAP.
Les inconvénients
0, aucun, que dalle, keutchi, nada, bref allez-y foncer, mais qu’est-ce que vous attendez ? Il faut tout de même bien penser et concevoir son plan de partitionnement.
Mise en oeuvre
Vous devez connaître les quelques objets de « base » de SQL Server que vous allez manipuler, à savoir :
- les groupes de fichiers
- les fichiers de données
- les fonctions de partitionnement
- les schémas de partitionnement
Pour plus de détails techniques et de considérations sur les stratégies de partitionnement je vous renvoie à ces quelques liens :
- http://msdn.microsoft.com/fr-fr/library/ms190787.aspx
- http://msdn.microsoft.com/fr-fr/library/ms162136.aspx
- http://msdn.microsoft.com/en-us/library/cc966380.aspx
Voyons désormais comment mettre en place une solution permettant de créer et de maintenir le(s) plan(s) de partitionnement de tous nos projets BI…
La base test utilisée pour ce post est AdventureWorksDW2012.
Définition des règles de nommage
La convention de nommage utilisée dans les exemples de ce post est la suivante :
- pour les groupes de fichiers : <Nom de la base>_FG_<Granularité>_<Valeur du grain> (exemple pour un grain annuel: AdventureWorksDW2012_FG_YEAR_2010)
- les fichiers de données : <Nom de la base>_FN_<Granularité>_<Valeur du grain> (exemple pour un grain annuel: AdventureWorksDW2012_FN_YEAR_2010)
- les fonctions de partitionnement : PartitionFunctionBy<Granularité> (exemple pour un grain annuel: PartitionFunctionByYear)
- les schémas de partitionnement : PartitionSchemeBy<Granularité> (exemple pour un grain annuel: PartitionSchemeByYear)
Les règles de gestion suivies sont les suivantes :
- Le plan de partitionnement ne prend en compte que des granularités temporelles
- La dimension DimDate présente dans AdventureWorks ne me satisfaisant pas (elle ne permet pas de gérer les semaines ISO), j’utilise la dimension DimCalendar décrite dans un précédent post http://wp.me/p2yhHc-4
- Pour les bornes de partitionnement, nous utiliserons le grain jour pour les identifier; ce dernier doit être de type entier au format YYYYMMDD (exemple pour un grain annuel : 20100101,20110101,20120101…)
- Chaque partition est liée à un groupe de fichier lui-même lié à un fichier de données
Création de la table d’administration des partitions
Afin d’avoir un gestion dynamique, il faut avant tout créer une table nous permettant de lister et d’enregistrer le paramétrage de nos partitions à savoir :
- le nom du schéma de partitionnement
- le nom de la fonction de partitionnement
- le type de
- la borne de début
- le nom de la dimension temps utilisée
- le type de granularité temporelle utilisé
- le nom de la colonne relative au grain temporel
- la nom de la colonne de clé primaire de la dimension temps
- le dossier dans lequel doivent être stockés les fichiers de données
- la taille initiale d’un fichier de données
- la taille maximale d’un fichier de données
- l’autoextend d’un fichier de données
- un flag indiquant si le plan de partitionnement est actif ou non.
CREATE TABLE [adm].[PartitionManagement] ( [PartitionManagementID] [tinyint] IDENTITY(1,1) NOT NULL, [SchemePartitionName] [nvarchar](255) NOT NULL, [FunctionPartitionName] [nvarchar](255) NOT NULL, [FunctionRangeType] [tinyint] NOT NULL, --1: LEFT, 2: RIGHT [FunctionRangeStartBound] [int] NOT NULL, [CalendarTable] [nvarchar](255) NOT NULL, [CalendarGranularityType] [nvarchar](20) NOT NULL, [CalendarGranularityColumnName] [nvarchar](255) NOT NULL, [CalendarLeafLevel] [nvarchar](255) NOT NULL, [DbFilePath] [nvarchar](255) NOT NULL, [DbFileSizeIni] [nvarchar](20) NOT NULL, [DbFileSizeMax] [nvarchar](20) NOT NULL, [DbFileGrowth] [nvarchar](20) NOT NULL, [FlagEnable] [bit] NOT NULL, CONSTRAINT [PK_PartitionManagement] PRIMARY KEY CLUSTERED ( [PartitionManagementID] ASC ) ) GO
On paramètre ensuite les plans de partitionnement. Nous allons dans notre exemple en décrire 3 : un annuel, un mensuel et un hebdomadaire.
Mise en place du package SSIS permettant d’administrer les partitions au fil de l’eau
La mise en œuvre du package SSIS est trivial, il suffit d’implémenter une simple boucle, nous permettant de récupérer séquentiellement le paramétrage de chaque plan de partitionnement est d’enchainer quelques scripts SQL :
- un script de création de groupe(s) de fichier(s)
- un script de création de création de fichier(s) de données
- un script de création/modification de la fonction et du schéma de partitionnement
Step 1 : Implémentation de la boucle de lecture de la table d’administration
Déclarons dans un premier temps les variables qui nous seront utiles pour lire notre table d’administration. Il nous faut donc une variable de type objet pour récupérer le contenu de la table et une variable par propriété:
Pour récupérer l’ensemble des plans de partitionnement actifs, il suffit d’utiliser un SQL Task et de stocker le ResultSet dans la variable @partitionManagementList :
Il faut ensuite parcourir le ResultSet, afin de pouvoir gérer les partitions une à une à l’aide d’un For Each Loop Container de type ADO.NET. Ce dernier permet de parcourir les enregistrements contenus dans la variable @partitionManagementList ligne à ligne, et de mapper le contenu de chaque colonne d’une occurrence dans une variable :
Step 2 : Passons désormais à l’implémentation des scripts SQL de gestion des partitions
La première étape consiste à créer les groupes de fichiers spécifiques pour le partitionnement. Le principe est simple, il suffit de lister les bornes temporelles en fonction du grain paramétré et de vérifier pour chaque occurrence si le filegroup existe ou non. Le script SQL qui nous permet d’effectuer cette opération est le suivant :
DECLARE @calendarTable NVARCHAR(255) DECLARE @calendarGranularityType NVARCHAR(255) DECLARE @calendarGranularityColumnName NVARCHAR(255) DECLARE @calendarLeafLevel NVARCHAR(255) DECLARE @functionRangeStartBound INT DECLARE @sqlStmt NVARCHAR(MAX) DECLARE @functionRangeEndBound INT = CAST(CONVERT(NVARCHAR(8),GETDATE()-1,112) AS INT) SET @functionRangeStartBound = ? SET @calendarTable = ? SET @calendarGranularityType = ? SET @calendarGranularityColumnName = ? SET @calendarLeafLevel = ? CREATE TABLE #tmp (granularityValue INT NOT NULL) SET @sqlstmt = ' INSERT INTO #tmp SELECT Calendar.' + @calendarGranularityColumnName + ' FROM ' + @calendarTable + ' AS Calendar WHERE Calendar.' + @calendarLeafLevel + ' BETWEEN ' + CAST(@functionRangeStartBound as nvarchar(8)) + ' AND ' + CAST(@functionRangeEndBound AS NVARCHAR(8)) + ' GROUP BY Calendar.' + @calendarGranularityColumnName EXECUTE sp_executesql @sqlstmt SET @sqlStmt = ( SELECT DISTINCT sqlStmt as 'data()' FROM ( SELECT N'ALTER DATABASE ' + DB_NAME() + ' ADD FILEGROUP ' + REPLACE(DB_NAME(),' ','_') + '_FG_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8)) + N';' as sqlStmt FROM #tmp LEFT OUTER JOIN sys.filegroups ON filegroups.name = REPLACE(DB_NAME(),' ','_') + '_FG_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8)) WHERE filegroups.name is null ) tmp FOR XML PATH('') ) EXECUTE sp_executesql @sqlStmt DROP TABLE #tmp
Le mapping des paramètres du SQL Task de gestion des groupes de fichiers est le suivant :
Une fois les groupes de fichiers créés, il faut s’occuper des fichiers de données. Je vous rappelle que nous sommes parti sur le principe (un groupe de fichier = 1 fichier de données). Pour créer les fichiers, nous utilisons le même principe que pour les groupes de fichiers
DECLARE @calendarTable NVARCHAR(255) DECLARE @calendarGranularityType NVARCHAR(255) DECLARE @calendarGranularityColumnName NVARCHAR(255) DECLARE @calendarLeafLevel NVARCHAR(255) DECLARE @sqlStmt NVARCHAR(MAX); DECLARE @dbFilePath NVARCHAR(255) DECLARE @dbFileSizeIni NVARCHAR(20) DECLARE @dbFileSizeMax NVARCHAR(20) DECLARE @dbFileGrowth NVARCHAR(20) DECLARE @functionRangeStartBound INT DECLARE @functionRangeEndBound INT = CAST(CONVERT(NVARCHAR(8),GETDATE()-1,112) AS INT) SET @functionRangeStartBound = ? SET @calendarTable = ? SET @calendarGranularityType = ? SET @calendarGranularityColumnName = ? SET @calendarLeafLevel = ? SET @dbFilePath = ? SET @dbFileSizeIni = ? SET @dbFileSizeMax = ? SET @dbFileGrowth = ? CREATE TABLE #tmp (granularityValue INT not null) SET @sqlstmt = ' INSERT INTO #tmp SELECT Calendar.' + @calendarGranularityColumnName + ' FROM ' + @calendarTable + ' AS Calendar WHERE Calendar.' + @calendarLeafLevel + ' BETWEEN ' + CAST(@functionRangeStartBound as nvarchar(8)) + ' AND ' + CAST(@functionRangeEndBound AS NVARCHAR(8)) + ' GROUP BY Calendar.' + @calendarGranularityColumnName EXECUTE sp_executesql @sqlstmt SET @sqlStmt = ( SELECT DISTINCT sqlStmt as 'data()' FROM ( SELECT N'ALTER DATABASE ' + DB_NAME() + + ' ADD FILE (NAME=' + REPLACE(DB_NAME(),' ','_') + '_FN_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8)) + ' , FILENAME=''' + @dbFilePath + REPLACE(DB_NAME(),' ','_') + '_FN_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8)) + '.ndf''' + ' , SIZE=' + @dbFileSizeIni +', MAXSIZE=' + @dbFileSizeMax + ', FILEGROWTH=' + @dbFileGrowth + ')' + ' TO FILEGROUP ' + REPLACE(DB_NAME(),' ','_') + '_FG_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8)) + N';' as sqlStmt FROM #tmp left outer join ( SELECT master_files.name filename FROM master.sys.master_files INNER JOIN sys.databases ON databases.database_id = master_files.database_id WHERE databases.name = DB_NAME() ) files ON files.filename = REPLACE(DB_NAME(),' ','_') + '_FN_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8)) WHERE files.filename is null ) tmp FOR XML PATH('') ) EXECUTE sp_executesql @sqlStmt DROP TABLE #tmp
Le mapping des paramètres du SQL Task de gestion des fichiers est le suivant :
Il faut ensuite gérer la fonction et le schéma de partitionnement :
- Pour créer la fonction de partitionnement, il est nécessaire de récupérer l’ensemble de bornes de la partition en fonction du paramétrage spécifié dans la table d’administration. Il suffit donc de prendre le premier jour de chaque occurrence de grain temporel à utiliser. La requête en elle-même est basique, mais elle doit être construite dynamiquement pour respecter le paramétrage de chaque plan de partitionnement. Exemple pour une granularité annuelle :
SELECT Calendar.YearCode,CAST(MIN(Calendar.CalendarID) AS NVARCHAR) AS CalendarID FROM common.DimCalendar AS Calendar WHERE Calendar.CalendarID BETWEEN 2010 AND CONVERT( GROUP BY Calendar.YearCode
Le but du jeu étant de générer un seul ordre SQL pour créer la fonction de partitionnement. Il me faut donc « linéariser » l’ensemble de valeurs afin d’obtenir un liste de bornes séparées par des virgules. Exemple pour une granularité annuelle : 2010,2011,2012. Pour se faire, j’utilise la fonctionnalité SQL « FOR XML PATH ». Il ne reste plus qu’à encapsuler cette liste de valeurs dans une ordre CREATE PARTITION FUNCTION
On effectue le même type d’opération pour créer le schéma de partitionnement.
- Si la fonction de partitionnement existe, je n’ai qu’à la modifier en fonction de la borne correspondant au grain temporel associé à la date courante. Il faut toutefois s’assurer que la borne n’est pas déjà déclarée et modifier au préalable le schéma de partitionnement pour que les données de la nouvelle borne s’insère correctement dans le dernier filegroup
Le script SQL permettant de réaliser ces opérations est le suivant :
</pre> DECLARE @functionPartitionName NVARCHAR(255) DECLARE @functionRangeType INT DECLARE @functionRangeStartBound INT DECLARE @calendarTable NVARCHAR(255) DECLARE @calendarGranularityType NVARCHAR(255) DECLARE @calendarGranularityColumnName NVARCHAR(255) DECLARE @calendarLeafLevel NVARCHAR(255) DECLARE @schemePartitionName NVARCHAR(255) DECLARE @schemeFileGroupLst nvarchar(max) DECLARE @paramDefinition NVARCHAR(MAX) DECLARE @sqlStmt NVARCHAR(MAX); DECLARE @lstPartitionID AS NVARCHAR(MAX) DECLARE @functionRangeEndBound INT = CAST(CONVERT(NVARCHAR(8),GETDATE()-1,112) AS INT) DECLARE @currentPartitionID as INT DECLARE @currentSchemeID as NVARCHAR(max) DECLARE @isBoundExists BIT SET @functionPartitionName = ? SET @functionRangeType = ? SET @functionRangeStartBound = ? SET @calendarTable = ? SET @calendarGranularityType = ? SET @calendarGranularityColumnName = ? SET @calendarLeafLevel = ? SET @schemePartitionName = ? --Teste si la fonction de partition existe IF NOT EXISTS (SELECT * FROM sys.partition_functions WHERE name = @functionPartitionName) BEGIN -- Préparation de la récupération des bornes de partitionnement SET @sqlStmt = 'SELECT @paramLstPartitionID = CAST( STUFF( ( SELECT DISTINCT '',''+' + @calendarLeafLevel + ' AS ''data()'' FROM ( SELECT Calendar.' + @calendarGranularityColumnName + ',CAST(MIN(Calendar.' + @calendarLeafLevel +') AS NVARCHAR) AS ' + @calendarLeafLevel + ' FROM ' + @calendarTable + ' AS Calendar WHERE Calendar.' + @calendarLeafLevel + ' BETWEEN ' + CAST(@functionRangeStartBound as nvarchar(8)) + ' AND ' + CAST(@functionRangeEndBound AS NVARCHAR(8)) + ' GROUP BY Calendar.' + @calendarGranularityColumnName + ' ) tmp ORDER BY 1 FOR XML PATH('''') ) ,1,1,'''' ) AS NVARCHAR(MAX) ) ' -- récupération des bornes de partitionnement SET @paramDefinition = N'@paramLstPartitionID nvarchar(max) OUTPUT' EXECUTE sp_executesql @sqlStmt,@paramDefinition,@paramLstPartitionID=@lstPartitionID OUTPUT -- création de la fonction de partitionnement SET @sqlStmt = 'create partition function ' + @functionPartitionName + ' (int) as range ' + CASE WHEN @functionRangeType=1 THEN 'LEFT' ELSE 'RIGHT' END + ' for values (' + @lstPartitionID +')' EXECUTE sp_executesql @sqlStmt -- récupère la liste des groupe de fichier à associer au schéma de partitionnement SELECT @schemeFileGroupLst = CAST( STUFF( ( SELECT DISTINCT ',' + filegroups.name AS 'data()' FROM sys.filegroups WHERE filegroups.name like REPLACE(DB_NAME(),' ','_') + '_FG_' + UPPER(@CalendarGranularityType) + '_%' ORDER BY 1 FOR XML PATH('') ), 1, 1, '' ) AS NVARCHAR(MAX) ) -- création de schema de partitionnement SET @sqlStmt = N' CREATE PARTITION SCHEME ' + @schemePartitionName + N' AS PARTITION ' + @functionPartitionName + ' TO ' +N'(' + CASE WHEN @functionRangeType=2 THEN N'[PRIMARY],' ELSE '' END + @schemeFileGroupLst + CASE WHEN @functionRangeType=1 THEN N'[PRIMARY]' ELSE '' END + N')' EXECUTE sp_executesql @sqlStmt END ELSE BEGIN -- Préparation de la requête permettant de récupérer la borne courante SET @sqlStmt = ' SELECT @paramPartitionID=min(Calendar.' + @calendarLeafLevel + '),@paramSchemeID=REPLACE(DB_NAME(),'' '',''_'') + ''_FG_' + UPPER(@CalendarGranularityType) + '_'' + cast(min(Calendar.' + @calendarGranularityColumnName + ') as nvarchar(10)) FROM ' + @calendarTable + ' AS Calendar WHERE Calendar.' + @calendarGranularityColumnName + ' = CASE ''' + @calendarGranularityType + ''' WHEN ''year'' THEN LEFT(@functionRangeEndBound,4) WHEN ''month'' THEN LEFT(@functionRangeEndBound,6) WHEN ''week'' THEN common.ufn_ISOWeek(cast(@functionRangeEndBound as nvarchar(8))) ELSE NULL END ' -- récupération de la borne en cours SET @paramDefinition = N'@functionRangeEndBound INT,@paramPartitionID INT OUTPUT,@paramSchemeID NVARCHAR(MAX) OUTPUT' EXECUTE sp_executesql @sqlStmt,@paramDefinition,@functionRangeEndBound,@paramPartitionID=@currentPartitionID OUTPUT,@paramSchemeID=@currentSchemeID OUTPUT -- Vérification de l'existance de la partition pour la borne concernée SELECT @isBoundExists = cast(COUNT(1) as bit) FROM sys.partition_range_values inner join sys.partition_functions on partition_functions.function_id = partition_range_values.function_id WHERE partition_functions.name = @functionPartitionName and partition_range_values.value = @currentPartitionID -- si la partition n'existe pas on la crée IF @isBoundExists=0 BEGIN SET @sqlStmt = 'alter partition scheme ' + @schemePartitionName + ' next used ' + @currentSchemeID EXECUTE sp_executesql @sqlStmt; SET @sqlStmt = N'alter partition function ' + @functionPartitionName + '() split range (' + cast(@currentPartitionID as nvarchar(8)) + N');' EXECUTE sp_executesql @sqlStmt; END END
La création du SQL Task est très simple, il suffit de s’assurer que le bon passage des paramètres :
L’implémentation du package est désormais terminée, et nous avons au final un lot SSIS très simple (comme d’habitude :)) :
Il ne reste plus qu’à partitionner les tables voulues et à planifier l’exécution de votre package.
Retrouvez les sources de cet article sur SkyDrive http://sdrv.ms/Uhhbvl
Tu as oublié Walou dans les inconvénients.
Mince alors….
En tout cas il faudrait que je fasse un complément. Je me suis aperçu que certains ont repris le code sans réfléchir… Il faudrait que j’ajoute une fonctionnalité de merge de partitions histoire de pouvoir merger les partitions d’historique qui ne sont utilisées qu’à la marge.
Quand tu historises des partitions, utilises-tu la compression de données?
En tous cas merci pour l’article qui est vraiment compréhensible pour un néophyte comme moi sur le sujet (plus que d’autres sites).
Peux-tu coller le code de la fonction ufn_ISOWeek?
Merci
Je vais voir si je trouve çà dans mes archives.
Tu peux trouver le code de cette fonction dans l’article http://wp.me/p2yhHc-4
Je te remercie
A ton service 🙂
Bonjour,
Merci beaucoup pour le poste, une fonctionnalité très pratique.
Par contre avez vous eu le temps d’ajouter la fonctionnalité de merge de partitions historique?
Merci pour votre retour.
Bonjour et merci pour votre intervention.
Je n’ai pas encore publié d’article sur le merge de partitions d’historique sur SSAS.
C’est une bonne idée d’article pour la rentrée.
Merci pour votre participation.
Bonjour,
Un topic on ne peut plus, détaillé, une seule question me taraude, comment gérer deux partitions pour deux tables de faits d’un même niveau de granularité: par exemple : Une partition au niveau mensuel pour la table Ventedetails et une autre partition pour la table vente. Merci pour votre réponse
Salut Eric,
Rien ne t’empêche d’utiliser la même fonction de partition pour plusieurs tables, bien au contraire.
C’est lorsque tu vas créer les indexes clustered sur tes tables que tu pourras spécifier le schéma de partitionnement à utiliser.
Il suffit de t’assurer que la clé de partitionnement soit contenue dans l’index clustered.
Si je reprends ton exemple, imaginons que tu as les tables Vente = {venteSKEY, calendrierSKEY, magasinSKEY, montant, numticket} et VenteDetail={venteDetailSKEY, calendrierSKEY, magasinSKEY, produitSKEY, numticket, numligne} et que tu veux les partitionner par année par l’intermédiaire de la fonction de partitionnement partFctAnnee (définie par rapport aux vos valeurs calendrierSKEY) et du schéma associé partSchAnnee.
Il faut alors créer, sur chaque table, un index clustered spécifiant le schéma de partionnement et contenant la clé de partitionnement calendrierSKEY
Ainsi, tu auras par exemple :
– pour la table vente : CREATE [UNIQUE] CLUSTERED INDEX IC_Vente ON Vente(calendrierSKEY, venteSKEY) ON partFctAnnee(calendrierSKEY) ;
– pour la table venteDetail : CREATE [UNIQUE] CLUSTERED INDEX IC_VenteDetail ON VenteDetail(calendrierSKEY, venteDetailSKEY) ON partFctAnnee(calendrierSKEY) ;
la clause UNIQUE n’étant à spécifier que si tu veux gérer une contrainte d’unicité.
N.B: de la même façon tu peux partitionner tes indexes non clustered et columnstore