Les Journées SQL Server 2013

Bonsoir à tous!

Les JSS, les Journées SQL Server reviennent dans une semaine, chez Microsoft!

JSS_2013

N’hésitez pas à vous inscrire si ce n’est encore fait. Voici ci-dessous le planning des différentes et intéressantes sessions de cette année. Vous remarquerez en rose le second jour une session de votre serviteur, avec mon ancien collègue Michel Perfetti. Le sujet: expliquer à des développeurs pourquoi faire du MDX peut résoudre pas mal de problématiques beaucoup plus simplement que du SQL. On vous a concoté une session marrante en mode combat entre ce bon vieux SQL et mon langage favori. Venez faire un saut, vous ne le regretterez pas.

A noter aussi, l’après midi, la session super technique de mes petits poulets de Scop It (Patrice Harel et Charles-Henri Sauget), que je ne manquerai pas de venir encourager!

planningJSS20132 Bonne soirée!

Revert to Parent Security en PowerShell

Bon un petit tip aujourd’hui: je n’ai rien trouvé de tel sur Stack ou autre. Le case study est un serveur SSRS dont toute les sécurités ont été cassées par des Content Manager peu scrupuleux. Comment réparer ce désastre sans avoir à vérifier tous les rapports? La solution: PowerShell et 3 méthodes du WebService de SSRS (ReportServer2010.asmx):

  • ListChildren(path) pour parcourir les descendants
  • GetPolicy(path, out inheritsParentSecurity) qui va donner les policies et surtout un booléen qui renseigne si l’élément adopte la sécurité de son parent ou pas
  • InheritParentSecurity(path) qui va restaurer la sécurité parente sur l’élément

Un bête parcours d’arbre dans une fonction récursive résoud rapidement le problème

Function RevertToParentSecurity ([string]$Path)
{
    $InheritParent = $true
    $CurrentPolicies = $Proxy.GetPolicies("$Path", [ref] $InheritParent)

    #If the current Path is not the root and it has custom policies applied, reset them
    if ( ( ! $InheritParent ) -and ( $Path -ne "/" ) )
    {
        Write-Host $Path -foregroundcolor "red"
        $Proxy.InheritParentSecurity($Path)
    }
    else
    {
        Write-Host $Path -foregroundcolor "green"
    }

    #If the current is a folder, recursive call on its children
    $ItemType = $Proxy.GetItemType("$Path")
    if ( $ItemType -eq "Folder" )
    {
        $Proxy.ListChildren("$Path", $false) | ForEach-Object {
            RevertToParentSecurity $_.path
        }
    }
}

Il suffit alors de l’appeler.
 

$WebServiceVersion = "2005"
$ReportServerUri = "http://localhost/ReportServer/ReportService$WebServiceVersion.asmx"
$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService$WebServiceVersion -UseDefaultCredential;
Write-Host Connected to $Proxy.Url
RevertToParentSecurity "/"

Voilà, si ça peut servir à d’autres et de vous éviter la prise de tête que ça m’a occasionné :)
Bonne soirée!

Je n’ai plus de groupes dans mon Tablix: comment recréer un groupe de détail?

Situation fâcheuse s’il en est, et vous avez probablement comme moi déjà été confronté à ça: un vieux rapport pourri d’expressions, vous en supprimez les lignes, faites une sauvegarde, et boum: plus de groupe de détail. Et contrairement aux versions précédentes, depuis 2008 il n’y a pas moyen d’en créer un facilement.

En fait il suffirait simplement que Microsoft permette de convertir tout groupe statique – en advanced mode – en groupe de détail, mais passons…

Alors comment faire: on va s’amuser avec un peu de RDL tout bête.

On commence par passer en mode code, et aller chercher les lignes sans groupe associé, elles apparaissent dans la balise TablixRowHierachy du Tablix sous forme de TablixMember vides. La première correspond à ma ligne d’entête, la seconde à ce que je veux qui devienne ma ligne de détail.

<Report xmlns="http://schemas.microsoft.com...">
  <Body>
    <ReportItems>
        <Tablix>
            <TablixRowHierarchy>
                <TablixMembers>
                    <TablixMember />
                    <TablixMember />
            </TablixRowHierarchy>

Il suffit donc d’ajouter un groupe sur ce TablixMember, comme ça:

<Report xmlns="http://schemas.microsoft.com...">
  <Body>
    <ReportItems>
        <Tablix>
            <TablixRowHierarchy>
                <TablixMembers>
                    <TablixMember />
                    <TablixMember>
                        <Group Name="detail"/>
                    </TablixMember>
            </TablixRowHierarchy>

Voilà un truc toutbête qui vous permettra d’éviter de jeter votre Tablix en pareil cas :)
Bonne rentrée!
 

MVP pour la quatrième fois consécutive sur SQL Server

Image

Je viens de recevoir le gentil mail de Microsoft, m’annonçant que pour la quatrième fois je suis reconnu MVP (Most Valuable Professional) sur SQL Server! Je tenais à remercier tous ceux qui rendent ce programme possible chez MS, Martine Tiphaine en tête.

Ensuite je voulais dire un mot de tous ceux qui, MVP ou non, par leur expérience, leur gentillesse et leur talent ont été, sont et seront les inspirateurs de ce que j’essaie de produire ici et ailleurs. Je pense à Romu, Patrice, Charly, Tarik, Thomas, David, Fabrice, Florian… Et j’en oublie.

Plus largement un coucou à tous ceux qui font vivre la communauté SQL Server à travers le GUSS, MSDN, Développez.net qui me garde comme modérateur sévère mais juste de MSBI. Un grand merci aussi aux éditions ENI qui ont publié notre bouquin. Mais surtout à vous gentils lecteurs sans qui rien de tout cela n’arriverait !

A très vite pour d’autre aventures :)

PowerView for Multidimensional Models (a.k.a. DaxMD) est sorti!

Ca y est. C’est officiel et on vous l’avait dit aux TechDays, ça n’allait pas trainer: DaxMD, qui apporte le support du DAX à SSAS Multidimensionnel est sorti. Il est inclus dans un CU – ce qui est certes assez inhabituel pour un tel ajout de fonctionnalité – et vous permet d’interroger des cubes en DAX à travers une couche de métas CSDL.

Ce que ça apporte? De meilleures performances au niveau fin, le support de PowerView dans Sharepoint. Pour Excel la réponse est:

Enabling this scenario is a top priority for the team but we do not have any updates on the roadmap or timeframe to share at this time.

Ce que l’on pourrait traduire par:

Cette fonctionnalité est une des principales priorités de l’équipe, mais nous ne pouvons pas encore communiquer sur une roadmap ou une date de sortie.

Wait and see donc. En attendant ça se passe ci-dessous!

download_iconTélécharger SQL Server 2012 SP1 CU4.

Et pour la tester et valider l’intérêt technique, quoi de mieux que d’essayer quelques scénarios de notre session sur le sujet avec Romuald?

A bientôt!

PS: Allez jeter un œil au billet de Chris, dont l’analyse politique de cette sortie est très intéressante.

PPS: Et la documentation MSDN a été mise à jour. Elle est elle aussi très instructive: http://msdn.microsoft.com/en-us/library/dn198310.aspx.

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!