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à!