Providers, DSV et XSL Cartridges: connecter SSAS Multidim à une base non supportée

Analysis Services en version multidimensionnelle est pour l’essentiel un produit conçu entre 2000 et 2005, qui possède les prérequis technique de son époque, et le connecter à des bases qui sortent des sentiers battus relève quelque peu de la croisade. Dans le cas où cette aventure vous tenterait, voici quelques pistes à explorer, ainsi que celles à éviter.

SSASProcessing

Il faut savoir que les problèmes de compatibilité sont généralement de deux ordres: la connexion à la base de données source depuis la Data Source, qui doit utiliser un provider  conforme, et la génération de SQL au niveau de la DSV, qui ne forgera pas forcément un SQL compatible avec la base de données ciblée.

Trouver un provider supporté

Ce n’est tout simplement pas facile. En effet, selon MSDN ici les sources de données supportées peuvent se résumer à:

  • Access 🙂
  • SQL Server
  • Oracle
  • Sybase
  • DB2
  • Informix

Toutes ces bases devant êtres accédées avec un provider OLE DB préférentiellement, ADO.NET restant possible mais moins optimal puisque managé. OLE DB, pourtant bientôt déprécié, est et reste le mode d’accès privilégié aux bases sous-jacentes pour SSAS Multidimensionnel. Le moteur lui même supporterait de passer par un provider ODBC, mais ce sont bien les outils de design qui ne permettent pas de l’utiliser, comme précisé.

ODBC data sources are not supported for multidimensional solutions. Although Analysis Services itself will handle the connection, the designers in SQL Server Data Tools used for building data source views, dimensions, and cubes cannot connect to an ODBC data source, even when using MSDASQL driver (.NET driver for ODBC). If your business requirements include an ODBC data source, consider building a tabular solution instead.

Si vous ne possédez ni provider OLE DB ni ADO.NET pour votre base de données, votre calvaire commence donc.

Une approche alternative: les Linked Servers

Les serveurs liés sont des objets de SQL Server relationnel: ils permettent de créer des serveurs au niveau de l’instance SQL Server, accédés par tous les providers supportés (dont un DSN système  et pas user ODBC, et attention à la gestion du x64 si vous vous y mettez: n’oubliez pas le odbcad32 situé dans %WINDIR%\SysWOW64\, petits conseils en passant^^). C’est une solution de contournement intéressante, car ODBC n’est pas supporté, même à travers MSDASQL, dans SSAS, comme expliqué dans le white paper sur la connexion de SSAS à Hive pour Klout:

These tools [ndlr:  BIDS/SSDT] use the .NET Framework Data Provider for OLE DB (System.Data.OleDb), which does not support the OLE DB Provider for ODBC. For this reason, the Connection Manager in Business Intelligence Development Studio and SQL Server Data Tools does not offer the OLE DB Provider for ODBC as an option, as depicted in Figure 4. Hence, the Klout Big Data solution requires a different integration approach.

Mais après test, cette approche est laborieuse, et antiperformante, surtout si vous décidez de faire du ROLAP (par exemple sur une base très performante en sous jacent type IQ, Teradata ou Vertica, car les tables pour être visibles, doivent être créées soit en tant que vues soit en tant que named queries via des

CREATE VIEW vMyTable
AS
SELECT *
FROM OPENQUERY
(
   [MyLinkedServer],
   'SELECT * FROM MyUnderlyingTable'
)

Ce qui en plus de l’exclamation « Oh mon dieu que c’est dégueulasse! » de votre côté DBA, doit vous faire intuiter plus scientifiquement que la résolution des jointures entre tables sera réalisée par votre moteur SQL. Alors heureux d’avoir acheté une base en colonnes qui coûte cher pour se retrouver à créer un bottleneck dans SQL Server? Donc cette approche vaut pour Hadoop/Hive, ou la récupération de données est assez lente, mais certainement pas pour un scénario ROLAP.

Mais même avec un provider supporté, votre calvaire n’est pas fini…

…et générer un SQL compatible

La génération du SQL est réalisée par la DSV au process du cube (ou à l’exécution des requêtes en mode ROLAP). Cette génération est réalisée par des transformations XSLT des représentations de la DSV vers du SQL, via des cartouches (cartridges) stockées pour un serveur – pour pouvoir lancer un processing -dans

[SSAS]\OLAP\bin\Cartridges

et pour BIDS – si vous voulez pouvoir travailler, créer des named queries et lancer des process depuis BIDS… – dans

[VS]\Common7\IDE\PrivateAssemblies\DataWarehouseDesigner\UIRdmsCartridge

Dans ces dossiers vous trouverez quelques fichiers XSL, correspondant aux moteurs supportés.

cartridges

Lorsque vous processez un cube, les requêtes de processing  vont être générées par la DSV avec un de ces fichiers, selon le type de la data source référencée. Si aucun fichier n’est compatible avec votre provider, comme dit dans l’Unleashed,

If you are using a provider that is not on the list of the supported providers, Analysis Services will try to use the sql2000.xsl cartridge

Ce qui généralement ne marchera pas: il est peu probable qu’un moteur accepte sans broncher des requêtes SQL Server 2000 :). Il est donc indispensable de posséder, en plus d’un provider, une XSLT compatible avec le moteur ciblé, que vous l’ayez téléchargée ou écrite. Si votre curiosité vous titille vous pouvez ouvrir un de ces fichiers, vous allez y trouver des choses intéressantes.

Le ou les providers ciblé par la XSLT:

<mssqlcrt:provider managed="yes" native="yes">Microsoft SQL Server.08.00.0191</mssqlcrt:provider>

Des pelletées de paramètres globaux:

<mssqlcrt:supports-subselect/>
<mssqlcrt:supports-table-alias/>
<mssqlcrt:supports-column-alias/>
<mssqlcrt:supports-cast/>
<mssqlcrt:supports-remote-query/>
<mssqlcrt:supports-top-clause/>
<mssqlcrt:supports-union/>
<mssqlcrt:supports-union-all/>

Et enfin de la XSLT proprement dite: des pelletées de templates qui génèrent du SQL en fonction de requêtes XPATH sur un arbre XML de la DSV représentant la requête source

...
<xsl:template match="Select">
	SELECT <xsl:apply-templates select="./Top"/>
		<xsl:apply-templates select="./Distinct"/>
		<xsl:apply-templates select="./ColumnExpressions"/>
		<xsl:apply-templates select="./Sources"/>
		<xsl:apply-templates select="./Where"/>
		<xsl:apply-templates select="./GroupBy"/>
		<xsl:apply-templates select="./OrderBy"/>
</xsl:template>
<xsl:template match="Where">
	WHERE <xsl:apply-templates select="./*"/>
</xsl:template>
<xsl:template match="Equal|NotEqual|Greater|GreaterOrEqual|Less|LessOrEqual|In|And|Or|Plus|Minus|Divide|Multiply">
	<xsl:choose>
		<xsl:when test="name()='Equal'">			=		</xsl:when>
		<xsl:when test="name()='NotEqual'">			&lt;&gt;	</xsl:when>
		<xsl:when test="name()='Greater'">			&gt;		</xsl:when>
		<xsl:when test="name()='GreaterOrEqual'"><![CDATA[	>=		]]></xsl:when>
		<xsl:when test="name()='Less'">				&lt;	</xsl:when>
		<xsl:when test="name()='LessOrEqual'">		&lt;=	</xsl:when>
		<xsl:when test="name()='In'">				IN		</xsl:when>
		<xsl:when test="name()='And'">				AND		</xsl:when>
		<xsl:when test="name()='Or'">				OR		</xsl:when>
		<xsl:when test="name()='Plus'">				+		</xsl:when>
		<xsl:when test="name()='Minus'">			-		</xsl:when>
		<xsl:when test="name()='Divide'">			/		</xsl:when>
		<xsl:when test="name()='Multiply'">			*		</xsl:when>
	</xsl:choose>
</xsl:template>
...

Rigolo non :)? Plus sérieusement, les cartouches sont bien commentées, et pour l’avoir fait, en modifier une pour la rendre compatible avec votre moteur est assez facile.

En conclusion

Pour connecter SSAS à une base il vous faut un provider OLE DB ou ADO.NET et une cartouche XSL. En cas d’absence du provider, vous pouvez utiliser les Linked Servers, tout en sachant que ce ne sera pas optimal d’un point de vue performances. Si seule la cartouche vous manque… pourquoi ne pas l’écrire!

A bientôt!

Localiser un DateName en T-SQL

Un peu de SQL une fois n’est pas coutume. Si vous voulez faire une belle dimension Temps  pour un cube Analysis Services, vous pouvez utiliser le pattern de Jamie/Charles-Henri, mais si vous souhaitez en plus fournir des NameColumn localisées – pour traduire un cube? – vous allez probablement faire un pataquès SSIS monstrueux, ou gérer un référentiel de temps qui fait forcément doublon puisque SQL Server en possède un, certes pas sous la forme la plus agréable qui soit, dans la table syslanguages!

Bon prince, je vous fournis un exemple, avec les mois, à vous de l’étendre à tout (WK, DAY…) si ça vous botte.

CREATE FUNCTION [dbo].[MonthNameLocalized](@MonthNumberOfYear INT,@LCID INT)
RETURNS NVARCHAR(50)
BEGIN
	RETURN
	(
		SELECT
			[MonthName]
		FROM
		(
			SELECT
				alias,
				lcid,
				CAST('<value>'+REPLACE(months,',','</value><value>')+'</value>' AS XML) MonthsXML
			FROM master.dbo.syslanguages
		) Languages
		CROSS APPLY
		(
			SELECT
				ROW_NUMBER() OVER (ORDER BY (SELECT 0)) MonthNumberOfYear,
				[Month].[MonthName].value('.', 'nvarchar(50)') [MonthName]
			FROM Languages.MonthsXML.nodes('value') AS [Month]([MonthName])
			WHERE lcid = @LCID
		) M
		WHERE MonthNumberOfYear = @MonthNumberOfYear
	)
END

Vous pouvez le tester en standalone:

SELECT dbo.MonthNameLocalized(1,1033) --January
SELECT dbo.MonthNameLocalized(4,1036) --Avril
SELECT dbo.MonthNameLocalized(5,1040) --Maggio

Ou dans une dimension temps:

;WITH TimeIterator(CurrentDate) as
(
	SELECT DATEFROMPARTS(2000,1,1)
	UNION ALL
	SELECT DATEADD(DAY,1,CurrentDate)
	FROM TimeIterator
	WHERE DATEADD(DAY,1,CurrentDate) < = DATEFROMPARTS(2020,1,1)
)

SELECT
	CONVERT(INT, CONVERT(VARCHAR(8),CurrentDate,112)) DateID
	, MONTH (CurrentDate) as MonthNumberOfYear
	, [dbo].[MonthNameLocalized](MONTH (CurrentDate),1036) AS [FrenchMonthName]
	, [dbo].[MonthNameLocalized](MONTH (CurrentDate),1033) AS [EnglishMonthName]
FROM TimeIterator
OPTION (MAXRECURSION 0)

Ce qui donne un truc comme ça:

Capture
A bientôt!

[SSRS] Un paramètre pour rechercher… en SQL

Une petite variante sur le même mode de ma recherche dans une dimension en MDX qui commence à dater: la version SQL.
Deux choses à créer
– Un paramètre multivalué (le champ de recherche)
– Un DataSet SQL (le résultat)

On commence par créer un joli DataSet sur une source SQL qui a la structure de ce que l’on veut renvoyer. Pour moi ce sera ça:
SELECT FrenchProductName
FROM AdventureWorksDW2008..DimCustomer
On le valide pour avoir les métadonnées. Maintenant on va créer une expression en lieu et place de cette requête, en cliquant sur le bouton ad-hoc:

L’expression a pour but de générer une requête like %% pour chaque mot recherché, le tout unionné et ordonné par nombre de matches (bon je vous l’accorde ça n’est pas PageRank).

SELECT FrenchProductName
FROM
(
SELECT ProductKey, FrenchProductName
FROM AdventureWorksDW2008..DimProduct
WHERE FrenchProductName LIKE ‘%vélo%’
UNION ALL
SELECT ProductKey,FrenchProductName
FROM AdventureWorksDW2008..DimProduct
WHERE FrenchProductName LIKE ‘%randonnée%’
)
AS T
GROUP BY ProductKey, FrenchProductName
ORDER BY COUNT(*) DESC, FrenchProductName ASC
Pour générer cela on va donc créer une expression qui est d’abord constituée du début et de la fin de la requête.
=
« SELECT FrenchProductName, COUNT(*) Rank « +
« FROM « +
« (« +
« ) »+
« AS T « +
« GROUP BY ProductKey, FrenchProductName « +
« ORDER BY COUNT(*) DESC, FrenchProductName ASC »
Entre chaque élément contenu dans le paramètre Recherche, on va intercaler du SQL suffisant à générer l’ensemble de requête UNION visibles au dessus.
Join
(
Parameters!Recherche.Value,
« %’ « +
« UNION ALL « +
« SELECT ProductKey,FrenchProductName « +
« FROM AdventureWorksDW2008..DimProduct « +
« WHERE FrenchProductName LIKE ‘% »
)
Ne reste plus qu’à rajouter le code SQL suffisant à traiter le premier et le dernier élément de la liste et on a le résultat final:

="SELECT FrenchProductName, COUNT(*) Rank "+
"FROM "+
"("+
"SELECT ProductKey, FrenchProductName "+
"FROM AdventureWorksDW2008..DimProduct "+
"WHERE FrenchProductName LIKE '%"+
Join
(
Parameters!Recherche.Value,
"%' "+
"UNION ALL "+
"SELECT ProductKey,FrenchProductName "+
"FROM AdventureWorksDW2008..DimProduct "+
"WHERE FrenchProductName LIKE '%"
)+
"%') AS T "+
"GROUP BY ProductKey, FrenchProductName "+
"ORDER BY COUNT(*) DESC, FrenchProductName ASC"

Une fois l’expression définie comme requête du DataSet on peut la tester, par exemple en la donnant comme source d’un paramètre:

Voilà, à bientôt!

[SQL] Une Parent Child en lignes/à plat en SQL avec les CTE et l’instruction PIVOT

Je vous l’avoue il y a des fois où j’aime bien céder à la tentation du nerd, celle de se lancer des petits défis inutiles de prime abord mais tellements jouissifs une fois résolus. Un de mes favoris a été la mise à plat de ParentChild, vous l’aurez deviné pour faire plaisir à SSAS.

Bref rappel: beaucoup de SI expriment les hiérarchies sur deux colonnes IdParent, IdEnfant, ce qui est convenable pour le stockage. L’ami Analysis Services éprouve quelques difficultés connues avec cette structure Parent/Enfant ou ParentChild (Basiquement une dimension construite comme cela ne générera d’aggrégats qu’au niveau du membre (All) et jamais en dessous). D’où l’envie de transformer un référentiel ParentChild en table mise à plat, i.e. où chaque niveau est représenté par une colonne.
Jetons d’abord un coup d’oeil à ma table avec la requête suivante:
select Code, CodeRattachement
from TbRef
Une Parent Child bien classique en somme…

Premier besoin: pour mettre chaque enregistrement dans la bonne colonne, nous allons avoir besoin du rang dans la hiérarchie. Ca, une CTE sait très bien le faire.

with
CteRang(Code, Rang)
as
(
select Code, 1
from TbRef
where CodeRattachement is null
union all
select TbRef.Code, CteRang.Rang+1
from TbRef
inner join CteRang
on TbRef.CodeRattachement = CteRang.Code
)
select Code, Rang
from CteRang
On a bien un calcul de rang, comme visible ici:

Bon passons aux choses sérieuses: pour avoir le bon matériel, nous allons générer toutes chaines hiérarchiques dit autrement tous les supérieurs de chaque tuple (le niveau maximal apparaitra donc non plus une seule fois mais n fois, n étant le nombre de noeuds.)

Pour cela nous allons faire une CTE non plus partant de la racine mais de la base: pour chaque noeud (membre d’ancrage avant le UNION ALL), nous allons générer tous ses supérieurs (membre récursif après le UNION ALL).
Le noeud d’ancrage a un rang issu de la CTE précédente, les noeuds récursifs le dérivent en retranchant 1 (il s’agit de supérieurs du noeud d’ancrage. De plus, on prend bien soin de garder l’ID du noeud duquel on est en train de construire la chaîne hiérarchique (Pvt dans mon cas)
with
CteRang(Code, Rang)
as
(
select Code, 1
from TbRef
where CodeRattachement is null
union all
select TbRef.Code, CteRang.Rang+1
from TbRef
inner join CteRang
on TbRef.CodeRattachement = CteRang.Code
),
CteMiseAPlat (Pvt,Rang,Code,Parent)
as
(
select ref.Code,CteRang.Rang,ref.Code,CodeRattachement
from TbRef ref
inner join CteRang
on CteRang.Code = ref.Code
union all
select cte.Pvt, cte.Rang - 1 , ref.Code,ref.CodeRattachement
from TbRef ref
inner join CteMiseAPlat cte
on cte.Parent = ref.Code
)
select *
from CteMiseAPlat
order by Pvt, Rang
Et ça marche:
Vous auriez pu vous en douter, il ne reste plus qu’à… pivoter tout cela, avec une belle instruction PIVOT. On pivote autour de mon champ Pvt, en prenant le Rang comme nom de colonne et en le remplissant avec le Code (mis dans une fonction d’agrégation bidon). On remplace donc le SELECT * précédent par:
select Pvt,[1],[2],[3],[4],[5],[6]
from
(
select Pvt, Rang, Code
from CteMiseAPlat
) as u
pivot
(
Max(Code)
FOR Rang IN ([1],[2],[3],[4],[5],[6])
) as t
Ce qui donne, tadaaaa:
Voilà! La performance n’est pas excellentissime (mieux vaudrait sûrement précalculer les rangs), mais je trouve que c’est un joli couplage de CTE et de PIVOT pour un usage très commun.
A bientôt!

[Releases] SQL Server 2008 R2 en RTM!

Annoncé aujourd’hui sur le site produit US SQL Server 2008 est passé en RTM.
Vous pouvez donc dès à présent l’essayer sur Technet et MSDN.

Ensuite les dates de sortie sont figées: le 3 mai pour les abonnés MSDN et Technet, le 13 pour le reste.Ensuite des précisions sur les éditions sorties: d’abord les deux éditions Core restent:
  • Standard
  • Enterprise
ainsi, bien sûr, que la Web, Workgroup, Developer, Express et Compact. Deux éditions spécialisées (appliances) sont ajoutées:
  • Data Center
  • Parallel Data Warehouse

Enfin les nouvelles features de R2 sont disponibles selon les éditions:

  • Standard intègre la compression des backups , les nouveautés de Reporting Services (Maps…), StreamInsight Standard et peut s’intégrer dans les outils de gestion multi-instance d’une édition supérieure
  • Enterprise ajoute l’intégration PowerPivot dans MOSS, Master Data Services, la gestion multi-instances (moins de 25) ainsi que des nouveaux outils de développement et déploiement T-SQL.
  • Data Center permet de gérer un nombre illimité de processeurs et de mémoire (régression par rapport à 2008, Enterprise semble maintenant limité à 8 sockets et 2 To de RAM), de manager plus de 25 instances, et ajoute StreamInsight Premium (sans limite de nombre d’events)
  • Parallel Data Warehouse est une Data Center utilisant les composants de processing massivement parallèles du projet Madison (issu du rachat de DatAllegro) dont le but avoué est de permettre d’approcher des tailles de DW de l’ordre du PetaOctet… ça laisse rêveur.
Plus de précisions à suivre bientôt, pour plus d’infos n’hésitez pas à vous rendre sur http://www.sqlserverlaunch.com/ (en anglais).

[T-SQL] Déconnecter tous les utilisateurs

Là encore rien de neuf mais cela fait deux fois qu’on me le demande en deux jours donc j’imagine que ce n’est pas évident…

La manière la plus simple de déconnecter tous les utilisateurs d’une base SQL Server c’est de passer la base en mode SingleUser.
Ceci dit SQL n’est pas chien et lorsque l’on lui demande de procéder à ce genre d’éjection sauvage, il se comporte par défaut en gentleman et attend que toutes les transactions actives soient commitées.
Il faut donc le forcer à être un petit peu moins poli et ordonner le rollback de toutes les transactions actives.
ALTER DATABASE [Base] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
–Ici un traitement nécessitant d’être seul sur la base
–…
–Puis retour à la normale
ALTER DATABASE [Base] SET MULTI_USER
GO
Voilà rien de révolutionnaire mais si ça peut aider…

[T-SQL] ROW_NUMBER() OVER sans ORDER BY

Question bizarre et pourtant assez connue: comment numéroter avec ROW_NUMBER() sans effectuer de tri sur le résultat, ce qui peut être utile/souhaitable dans tout un tas de contextes.
MSDN vous dit bien que ROW_NUMBER() OVER attend un ORDER BY…

Bien sûr mais si vous placez une constante dans le ORDER BY, l’optimiseur comprend que vous voulez une simple numérotation des résultats sans tri.

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0) ) AS[Index]
FROM [MaTable]

Et voilà!

[SQL] "Performance counter registry hive consistency check" failed.

Vous aurez peut être cette erreur un jour en installant SQL Server 2008 via le gentil SCC (System Configuration Checker). Je l’ai déjà eue plusieurs fois, et une dernière ce matin qui m’a donné envie de rappeller les causes.

Basiquement cette erreur est liée à Perfmon et à la disponibilité des compteurs de performances. La recommandation faite par MS est une réparation fastifieuse de la base de compteurs, alors que le problème vient souvent d’un des deux points suivants.

1) Clé de registres incohérentes

Possiblement généré par pas mal d’installations (ou un développeur aimant s’amuser dans le registre). Dans le registre sont en effet listés l’intégralité des compteurs de perfmon, précisément dans cette clé: HKEY_LOCAL_MACHINE\ SOFTWARE\ Microsoft\ Windows NT\ CurrentVersion\ Perflib\

Et encore plus précisément dans deux valeurs qui sont Counter et Help.
Si vous regardez ces deux valeurs (du texte très long) les compteurs sont numérotés et ordonnés.

Normalement la dernière valeur d’id de compteur de Counter et Help sont respectivement reportées dans les valeurs LastCounter et LastHelp de la clé: HKEY_LOCAL_MACHINE\ SOFTWARE\ Microsoft\ Windows NT\ CurrentVersion\ Perflib\ 009\

Si cela n’est pas le cas, à vous de corriger ces dernières! Si ette erreur ne se présente pas, passez au point suivant.

2) Nom de clé non adapté à la locale

La clé précédente se termine par 009. Cela correspond à une installation anglaise (en-us) de Windows. Et si ce n’est pas votre cas? Sur une installation française par exemple, ce dossier se nomme 00C. A quoi cela correspond? Tout simplement à la conversion en héxa de la Culture sur 3 caractères. Il faut impérativement que ces deux valeurs correspondent.
Avec ce petit bout de code C# vous pouvez le récupérer facilement.

string culture = string.Format(« {0,3} », CultureInfo.InstalledUICulture.Parent.LCID.ToString(« X »)).Replace( » « , « 0 »);

Il ne vous reste plus qu’à réaliser un backup de la clé 009 et de la restaurer sous un nom approprié!
En espérant vous avoir aidés.

A bientôt!

[SQL] Pourquoi le Change Data Capture va changer notre vie

Rien que ça… Mais je parierais que ça n’est pas immérité. Avec les latences dans les adoptions de nouvelles technos ma « prédiction » ne s’avèrera peut être vraie que dans 2 ou 3 ans (oui on bosse encore beaucoup et majoritairement en 2005!).
Mais bon, reprenons depuis le début…

« C’est quoi le CDC ou Change Data Capture?« 

C’est un mécanisme de tracking de modification de contenu de tables. Basiquement le CDC crée des tables de suivi des modifications DML (INSERT, UPDATE, DELETE) effectués dans une table de données. Ces tables sont nourries par des jobs qui scannent le log de la base, comme visible sur cette belle slide de formation ci-dessous.


« Change Data Capture » n’est pas le nom d’un produit. C’est une désignation de ce genre de feature. Ce processus existe par exemple chez Oracle (depuis 9i si mes souvenirs Oracliens sont bons, mais n’hésitez pas à me démentir), sous Infosphere, Sybase…. et généralement suivant le même principe (Un RDBMS maintient un Tran Log, pourquoi ne pas s’en servir après tout?)
Il a été introduit en environnement Microsoft sous SQL Server 2008.

« Mais ça va servir à quoi en Data Warehousing? On a déjà SSIS! »

Le CDC n’a pas pour but de remplacer SSIS mais de faciliter les alimentations de Data Warehouses, en ce qui concerne les changements dans les sources. Soyons clair: SSIS est un fantastique outil qui permet de gérer des workflows complexes, d’implémenter des alimentations massivement multi-sources, des règles métier tordues et des SCD bizarroïdes, mais on génère un énorme overhead dans la gestion de la recherche des modifications sur les sources.

Par exemple on ne peut pas toujours compter sur une datation des lignes modifiées en production. On passe très – trop – souvent par une remontée intégrale sur le serveur ETL des référentiels, et des comparaisons avec le DW pour tracker les modifications.

Dans des cas plus sympathiques les modifications dans cette base de production sont trackées dans un champ de type DateTime qui stocke le dernier Update par exemple, et il suffit de récupérer les lignes déjà modifiées.
Soit ça marche déjà mieux. Mais c’est limité!

Et si vous voulez récupérer des modifications intermédiaires? Vous implémentez une gestion par trigger lentissime, à vidanger soit même…
Le CDC résout ces problèmes sans vous demander une once d’implémentation.

« Oui… enfin techniquement on doit bien faire quelque chose non? »

Je vous l’accorde. Il faut quand même faire quelques petites choses .
1) Activer CDC sur la base concernée

EXEC sp_cdc_enable_db
GO

2) Activer CDC pour une table à suivre

EXEC sys.sp_cdc_enable_table
@source_schema = ‘MonSchéma’
, @source_name = ‘MaTableASuivre’
, @role_name = NULL /*Rôle pour restreindre l’accès aux tables de tracking, Null si pas de restriction*/
, @capture_instance = ‘NomDeCaptureInstanceDeMaTableASuivre’
, @supports_net_changes = 1 /*Suivi des modifications intermédiaires, 1 pour oui*/
, @captured_column_list = ‘MaColonne1, MaColonne2’
, @filegroup_name = ‘FileGroupPourStockerMaTableDeTracking

Et c’est terminé. Le CDC est en route.

« C’est stocké où? On récupère ça comment? »

Vous avez vu ci-dessus: on spécifie un FileGroup. Tout simplement parce que c’est stocké dans une table par table tracée, et que cette table de tracking, nommée _CT est stockée sur ce FileGroup dans un schéma dédié au cdc. Il est plus que conseillé de ne pas utiliser le FileGroup de données pour le CDC !
Après on peut soit récupérer les infos directement… mais ça n’est pas la manière la plus aisée 🙂
Soit utiliser deux fonctions de table:
fn_cdc_get_all_changes_MaCaptureInstance qui récupère tous les changements (U,I,D) entre deux LSN
fn_cdc_get_net_changes_MaCaptureInstance qui récupère le dernier état de chaque tuple entre deux LSN

« LSN? Quel rapport avec le log? »

On l’a dit quelques lignes au dessus: le CDC marche sur le log donc les modifications sont tracées par LSN. Le LSN, ou Log Sequence Number est la valeur unique associée à chaque enregistrement dans le log.
Mais rassurez vous! On peut facilement faire correspondre une date à un LSN, en demandant à la fonction sys.fn_cdc_map_time_to_lsn de renvoyer le LSN le plus proche d’un DateTime.


SELECT @lsn = sys.fn_cdc_map_time_to_lsn (
‘smallest greater than or equal’
, ‘2009-08-21 00:00:00.000’
);

« Ca m’inquiète ce lien avec le Log… alors on est obligés d’être en Full Recovery? »

Non. Mais c’est une bonne question. Cette forte intrication entre le Log et CDC implique quelques avertissements. Lorsque l’on est en Simple ou en Bulk, un mode de log plus verbeux est employé pour les opérations concernées par le CDC.
De plus le CDC empêchent de vider le Log tant que des informations concernées par la capture n’ont pas été lues. Ce qui désactive le SHRINK et donc l’AutoShrink et peut faire grossir des Logs en Simple.

« Et les performances dans tout ça? »

Alors forcément il y a un impact. Le nier dénoterait un tempérament marchand de tapis que je récuse 🙂 Il faut absolument passer par le paramétrage de certains aspects du CDC

Choisir les bons attributs: pas toutes les colonnes!
Tuner les jobs de capture: Jouer avec le paramètre maxtrans pour déterminer le nombre de tran examinées par scan, ainsi qu’avec le nombre de scans du log avant une pause avec maxscans et la durée de ces pauses avec pollinginterval.

et beaucoup d’autres conseils et arguments exposés dans cet excellent document publié sur MSDN, le guide du tuning du Change Data Capture du MTE allemand Steffen Krause.

Conclusion?

Potentiellement, le CDC révolutionne le DataWarehousing en économisant du temps d’ETL (généralement 70-80% du projet BI). Ce temps gagné ne l’est pas en vain: toute économie sur des tâches techniques et redondantes permet de se consacrer plus profondément à la problématique métier, et c’est ce qui prime dans cette activité, plus encore qu’ailleurs, non?

A bientôt!

[SQL] Tester l’existence d’une table temporaire

Astuce connue, mais on me le demande tellement souvent que je me crois obligé de re-citer la méthode: il n’est pas possible d’utiliser EXISTS avec les objets temporaires, on a donc recours à la fonction OBJECT_ID, qui renvoie l’ID d’un object de la base s’il existe, et NULL sinon, on peut donc faire ce genre de script:

IF OBJECT_ID(‘tempdb..#tmpTable’) IS NOT NULL
DROP TABLE #tmpTable

Merci encore à Christian Robert qui m’avait il m’a bien longtemps montré cette technique 🙂

A bientôt!