Questions/Réponses: le futur d’Analysis Services

La question a encore été posée par notre session, et force est de constater que formation après formation, conférence après conférence, on me la pose systématiquement: quel est le futur d’Analysis Services, voire même de l’OLAP en général?

Panneau-AttentionNote importante: Je tiens à rappeler que bien qu’étant quelque peu « identifié » à Microsoft par mon statut de MVP, ce billet ne reflète que ma vision personnelle de la situation, et pas celle de l’éditeur, comme tout le reste de ce blog d’ailleurs.

Le futur: tabular ou multidimensional?

Cette question ne se pose pas que chez Microsoft. Elle est valable chez tous les éditeurs et peut même être étendue à l’alternative Columnar ou MOLAP. Sur ce dernier point j’en suis persuadé, l’avenir pour le moment, en ce mois de Février 2013, est dans le stockage en colonnes.

Hein? Mais vous recommandez de faire du MOLAP dans beaucoup de cas pourtant?

Oui. Pour le moment. Mais je sais aussi que les moteurs MOLAP, apparus au début des années 1990 (PowerPlay, Express, Essbase), en 1998 chez Microsoft (OLAP 7.0) possèdent un certain nombre de faiblesses conceptuelles:

  • Un stockage dimensionnel qui répond bien à la représentation intellectuelle des scénarii d’analyse, mais entraîne un phénomène de matrice creuse (data sparsity) qui donne généralement, et c’est valable pour la plupart des moteurs, de mauvaises performances au niveau fin. D’où le développement du ROLAP, du HOLAP… et au final de schémas d’architecture quelque peu complexes, et difficiles à maintenir.
  • De plus, les volumétries de ces bases sont limitées à quelques TB au plus, ce qui paraît minuscule au vu des exigences de la mouvance Big Data. Sans parler du partitionnement mis en jeu.
  • Des temps de process prohibitifs dus à un changement total de paradigme par rapport au monde relationnel, et ce peu importe les moteurs. Le Proactive Caching ça marche bien mais avec peu de données, ou un schéma de partitionnement très complexe.
  • Un monde « à part » et une courbe d’apprentissage très raide.

Le stockage en colonnes, plus récent, répond à beaucoup de ces problématiques (principalement les problèmes issus du côté sparse du storage, et du monde multidimensionnel). xVelocity (Vertipaq), le moteur de Tabular et de PowerPivot (Excel 2013 Data Model) c’est du in-memory columnar storage.

Génial, on abandonne les cubes et on fait du tabulaire: mais je croyais que vous étiez accroché à votre multidimensionnel?

Non et non. Je ne suis pas « accroché » au moteur OLAP: je dis simplement que fonctionnellement, et à date, il adresse aujourd’hui des scénarios que le mode tabulaire ne permet pas ou difficilement de traiter (volumétries, localisation, modélisations étranges). Le modèle tabulaire aujourd’hui a deux problèmes vus de ma fenêtre:

  • Un mode de stockage uniquement InMemory, qui empêche de traiter de gros projets (Pour rappel un tabulaire c’est 20% de la taille de la BdD relationnelle de chaque base en RAM, et des développeurs qui bossent connectés à un serveur et donc partagent cette RAM sur les infras de dev. A vos calculettes.). On peut certes parler de DirectQuery. Ou pas. Si vous voulez savoir pourquoi « ou pas » lisez ceci
  • Des fonctionnalités limitées liées à la jeunesse du moteur essentiellement autour de l’internationalisation et des relations gérées.

Donc aujourd’hui beaucoup de projets doivent encore être faits en multidimensionnel. Ce n’est pas moi qui le dit, c’est T.K.  Anand.

Que va faire Microsoft demain?

Pas de communication officielle, mais la discrète survey de l’équipe produit envoyée fin d’année dernière donne des billes: pas une seule fois le moteur OLAP n’est mentionné. On s’oriente donc à mon sens vers un rattrapage fonctionnel et technique du moteur OLAP par le moteur Tabulaire. 

D’accord… alors pourquoi DaxMD?

A mon avis, DaxMD (PowerView for Multidimensional Models CTP, disponible ici) est un patch sur le moteur OLAP qui a deux buts:

  • La version officielle: lui permettre de supporter les frontaux mis en place  (ex: PowerView) et à venir, le moteur OLAP n’étant pas déprécié.
  • Ce que je lis derrière les lignes: permettre au moteur OLAP d’adresser une bonne partie des projets – ces fameux projet à haute volumétrie, localisés, ou utilisant des fonctionnalités avancées – le temps que Tabular arrive à sa pleine maturité, quitte à le déprécier à horizon très lointain.

D’accord, donc si on se résume: la solution idéale aujourd’hui (ou plutôt demain matin) ce serait DaxMD? Et demain un tabulaire vNext dans lequel on met quoi?

Sur le même modèle de notre wishlist avec le grand Romu en session, j’isolerais deux priorités:

  1. Urgemment, des fonctionnalités supportées par le moteur OLAP depuis longtemps (SCOPE, Traductions, Actions, Currency Conversion, Calculated Members…)
  2. A plus long terme, un mode de stockage en buffer cache. Il est important que passé une certaine volumétrie on puisse travailler avec le couple HDD (SSD?)+ InMemory. Microsoft pousse autour d’Hadoop, avec HDInsight dans le Cloud et quelques POCs intéressant sur du OnPremises utilisant les technos BI existantes,  il serait bête de ne pas s’engager sur le chemin de bases ad-hoc de nouvelle génération permettant de traiter ces très grosses volumétries.

La faisabilité technique de la dernière étant… comment dire, pas triviale je ne me fais pas trop d’illusions à brève échéance. Sur le premier point en revanche, on a montré en session que presque tout est déjà là techniquement pour que ce soit réalisable dans des délais assez brefs.

Et le langage? DAX ou MDX?

Ce n’est pas le plus important à mon sens même si j’adore le MDX dans sa manière d’exprimer une query de manière géométrique. DAX – sur sa partie requête – n’est à mon sens qu’un genre de SQL imbuvable original aux relations explicitées par un modèle CSDL, et présente donc les mêmes inconvénients que le SQL pour exprimer des requêtes analytiques. (Et là dessus je rejoins pas mal Steven Bolton…)

Mais quel choix va faire Microsoft? Est ce qu’il y en aura même un? No clue, même si le développement uniquement en DAX de PowerView peut donner une idée…

Vous n’êtes pas d’accord? Vous vous posez d’autres questions? N’hésitez pas à commenter!

Techdays 2013, épilogue

TD13

Bonjour à tous, un petit peu crevé après cette préparation éreintante de session qui, d’après les premiers retours, vous a pas mal plu! Je vous rassure on s’est bien amusés avec le grand – autant par les New Rock que par le talent – Romu aussi. Sur un plan plus perso la préparation quelque peu tardive m’a empêché de venir aux sessions qui m’intéressaient mais avec Romuald nous avions des slides à finir à la force du poignet (oui je sais Aurélien ceci est une trèèès mauvaise blague, et sache que toute la salle était avec toi, bon rétablissement mon poulet!).

Merci en tous les cas aux présents, je pense à Pascale, Flo, David, Fab, Rom,  Dominique et j’en oublie, ainsi qu’à tous ceux qui sont venus nous faire leur retours à la sortie, ça fait chaud au coeur, vraiment!

Démos, Director’s Cut

Bon comme on a été un petit peu à la bourre je voulais revenir sur quelques démos histoire que les passages peu clairs le soient un peu moins. On va faire ça dans l’ordre, et ça concerne surtout la partie DaxMD, aussi connu sous le nom de PowerView for Multidimensional Models, CTP publique d’une version du moteur OLAP supportant le DAX, disponible ici. Au passage et ce depuis la session, David Joubert a posté un excellent test de la conversion de modèles tabulaires vers du MD qui fait un peu le même job que certaines de nos démos.

CSDL Metadata

On vous l’a dit, la raison pour laquelle on peut envoyer du DAX à un modèle multidimensionnel – qui permet à PowerView de s’y connecter – dans cette CTP tient en deux raisons

  • Un Formula Engine DAX côté multidimensionnel, capable d’interroger un SE MOLAP
  • Des métadonnées au format CSDL, le format normé de Microsoft introduit avec Entity Framework qui permet de décrire un modèle de données. Pour étendre le CSDL aux données d’analyse, Microsoft a publié une extension au CSDL nommée CSDL-BI.

Pour requêter les métadonnées CSDL, un Rowset XMLA (ou une DMV) existe. Il suffit de passer le nom de la base, du cube (oui car en multidimensionnel on peut avoir plusieurs cubes!) dans le champ… PERSPECTIVE_NAME (Oui bon c’est une CTP hein), et un champ non documenté, VERSION, qui dit que… qui dit 2.0. (Je vous ai déjà dit que c’est une CTP?) Voilà.

<Envelope xmlns=”http://schemas.xmlsoap.org/soap/envelope/”>
       <Body>
              <Discover xmlns=”urn:schemas-microsoft-com:xml-analysis”>
                     <RequestType>DISCOVER_CSDL_METADATA</RequestType>
                     <Restrictions>
                           <RestrictionList>
                                 <CATALOG_NAME>AdventureWorksDW2012Multidimensional-EE<CATALOG_NAME>
                                 <VERSION>2.0</VERSION>
                                 <PERSPECTIVE_NAME>Adventure Works</PERSPECTIVE_NAME>
                           </RestrictionList>
                     </Restrictions>
                     <Properties>
                           <PropertyList>
                                 <FORMAT>Tabular</FORMAT>
                           </PropertyList>
                     </Properties>
              </Discover>
       </Body>

PowerView comprend ça. Pas vous. C’est normal. Mais ça vous montre qu’on a dorénavant une couche de métadonnées dans SSAS (ce qui n’était pas le cas avant Tabular).

Drill-through en MDX, en DRILLTHROUGH ou en DAX?

Pour faire des extractions de détail en MDX, on a basiquement deux solutions. Faire des requêtes MDX bien barbares, ce qui est assez lent, mais pas compliqué à écrire (et au passage ce que génère Excel PivotTable ou SSRS au Designer).

SELECT
{
	[Measures].[Reseller Sales Amount]
}
ON 0
,
NON EMPTY
{
	[Date].[Date].[Date]
	*[Geography].[City].[City]
	*[Product].[Product].[Product]
	*[Reseller].[Reseller].[Reseller]
	*[Sales Territory].[Sales Territory Region].[Sales Territory Region]
}
DIMENSION PROPERTIES [Reseller].[Reseller].[Bank Name]
ON 1
FROM [Adventure Works]

Quand on est bon, on peut faire des requêtes de DRILLTHROUGH sur une cellule. Les vieux de la vieille du RS 2000 faisaient ça avec de l’OLE DB for OLAP dans SSRS (c’est faisable aussi avec le provider AS, mais en mode mining, éditeur coupé). Avantage: lent à la première exécution, mais monte bien en cache.

DRILLTHROUGH
SELECT
{
 [Measures].[Reseller Sales Amount]
}
ON 0
FROM [Adventure Works]
RETURN
 [$Date].[Date]
 ,[$Geography].[City]
 ,[$Product].[Product]
 ,[$Reseller].[Reseller]
 ,[$Sales Territory].[Sales Territory Region]
 ,[Reseller Sales].[Reseller Sales Amount]

Dans la session on vous a montré qu’on peut aussi faire ça en DAX. pour de l’extraction de détail, ça envoie sacrément (les temps de réponse sont en moyenne 10 fois meilleurs sur notre petite base d’exemple).

En plus cela semble constant au vu de nos tests contrairement aux autres modes d’accès.

Evaluate
(
	Summarize
	(
		'Reseller Sales'
		,'Date'[Date.Key0]
		,'Date'[Date]
		,'Geography'[City.Key1]
		,'Geography'[City.Key0]
		,'Product'[Product.Key0]
		,'Product'[Product]
		,'Reseller'[Reseller.Key0]
		,'Reseller'[Reseller]
		,'Reseller'[Bank Name]
		,'Sales Territory'[Sales Territory Region.Key0]
		,'Sales Territory'[Sales Territory Region]
		,"Sales Amount",'Reseller Sales'[Reseller Sales Amount]
	)
)
Order By
	'Date'[Date]
	,'Geography'[City.Key0]
	,'Product'[Product]
	,'Reseller'[Reseller]
	,'Reseller'[Bank Name]
	,'Sales Territory'[Sales Territory Region]

Vous noterez au passage les splendides Key0, Key1 dans la requête. Kézako? Et bien si vous vous y connaissez en member properties vous savez que les Key0…KeyN en MDX sont les KeyColumns d’un attribut. Pour développer DaxMD – de la même manière qu’il a du être difficile de faire rentrer le concept de DefaultMember dans un PowerPivot – cela n’a pas du être une mince affaire pour les équipes de Redmond de faire comprendre l’idée de clés composites et de NameColumn à du DAX . Moyennant quoi dès que la NameColumn est différente de la KeyColumn dans la Dimension, on doit spécifier cette dernière, sans quoi on prend un beau message d’erreur:

Column [Truc] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

Bon à savoir…

Rapport de drill en SSRS

Pour la suite de la démo, Romu a montré l’inclusion de ce code dans un Rapport SSRS  pour lequel il faut utiliser le mode DMX pour taper la requête (comme dans ce vieux post sur le Drillthrough). Les paramètres quant à eux sont évidemment gérés (Syntaxe @Paramètre) et le sous rapport envoyé grâce à un JumpToReport sur le camemb… l’histogramme.

DAX DrillThrough Action dans SSAS

Enfin la cerise sur le gâteau concernait l’exécution de ce code dans une action de type Rowset côté AS. L’idée étant d’avoir un Drill en DAX sur un cube. Une action de Rowset a une expression de type chaîne de caractère qui va être évaluée dans un bloc XMLA <Command>… qui contient normalement du MDX. Mais dans DaxMD, les Command peuvent être en DAX (oui c’est le principe), donc on peut y mettre la requête DAX précédente.

Problème: comme pour le DrillThrough on Calculated Members de Mosha on doit contextualiser la cellule (si je clique sur une cellule, la requête DAX doit être filtrée sur les dimensions autour). Dans son post, Mosha explique que:

In order for drillthrough to address the correct cell, we need to put current cell coordinates into the SELECT clause. One way of doing it would be to write long MDX string concatenation, using <hierarchy>.CurrentMember.UniqueName subexpression for every hierarchy in the cube. However, this is tedious and error-prone approach. There are hundreds of hierarchies, new ones can be added, old ones removed or renamed etc. Instead, we can just call stored procedure which will enumerate all the current coordinates (note that we could’ve done the same with the RETURN clause too).

Procédure que l’on ne possède pas ici (Mosha utilise celle de ASSP qui renvoie du… MDX). Ce que j’ai montré pendant la session c’est le principe de la chose appliqué sur une seule hiérarchie que les plus courageux d’entre vous finiront. (mais si un candidat se présente pour écrire une stored proc… :)). La partie importante de la requête est dans le Filter, qui va désactiver le filtre si on est au niveau All en générant une clause en DAX depuis du MDX. Oui je sais c’est tordu:

"Evaluate
(
	CalculateTable
	(
		Summarize
		(
			'Reseller Sales'
			,'Date'[Date.Key0]
			,...
		)
		,'Sales Territory'[Sales Territory Region.Key0] = "+[Sales Territory].[Sales Territory Region].CurrentMember.Properties('Key0')+"
        ||"""+[Sales Territory].[Sales Territory Region].CurrentMember.Name+"""="""+[Sales Territory].[Sales Territory Region].DefaultMember.Name+"""
    )
)"

Conclusion

Voilà c’était à peu près tout. Merci à tous pour votre présence, à vos commentaires si vous voyez une erreur. Quant au contenu, ce sera avant Vendredi sur mon SkyDrive, j’éditerai le post.

A bientôt!

Master Data Services 2012, toutes les raisons de l’implémenter

Une petite discussion de la semaine dernière avec mon excellent petit collègue Charles-Henri amène ce post sur MDS, l’outil de MDM (Master Data Management, gestion de référentiel) de Microsoft, qui est sorti en mars dernier dans sa seconde version (qui a dit « première version utilisable? » :)). Pour résumer:

  • Je pense que non seulement nous allons être amenés à en mettre de plus en plus en place dans le cadre de projets décisionnels,
  • Mais en plus  que le produit répond à une problématique décorrélée de la BI qui peut elle même être génératrice de projets

Un bref historique

Le terme MDM, vous l’entendez depuis quelques temps – probablement depuis les années 2006-2007 je n’ai pas vérifié mon Décidéo. Ce vocable en vogue à l’époque – et toujours aujourd’hui d’ailleurs – verbalise simplement le fait que les applications d’une entreprise ont potentiellement vocation à partager un certain volume de référentiels.

Diantre la belle affaire.

On parle à cette époque déjà depuis longtemps d’outils de CDI (pas les contrats, la Customer Data Integration, le fait de disposer d’un seul référentiel client), et de PIM (pas l’onomatopée, Product Information Management, pour les produits). Le MDM est simplement la mise sous le même chapeau de ces deux domaines, en considérant que l’intégration des référentiels d’une entreprise se doit d’aller plus loin que le traditionnel diptyque Client-Produit.

Quel rapport avec la BI et le Data Warehousing en particulier?

Je suis moins bon en schémas que le pote Florian Eiden mais je vais m’y coller. Quand on parle d’alimentation d’entrepôt et spécifiquement de dimension, on désigne les opérations réalisées par les environnements de stockage.

  • On procède d’abord à un Staging des données: l’intégration dans un environnement technologique unique de données hétérogènes.
  • Puis on rapproche et on nettoie ces données dans un Operational Data Store non historisé.
  • Très schématiquement l’historisation de l’ODS donne la dimension du Data Warehouse.

<Flagellez moi si vous êtes en désaccord hein>

Si on enlève ces barbarismes technologiques, et sachant qu’un certain nombre de transformations peuvent s’effectuer dans un environnement ou un autre selon le doigt mouillé l’expérience (i.e. le nettoyage des données…), j’ai tendance à plutôt voir le boulot comme ça, indépendamment de l’emplacement où les tâches se font, et ce bien avant l’apparition du terme MDM

Flux

Pour un produit par exemple on va intégrer des « référentiels » hétérogènes (Excel, Access, SGBD loufoques), que l’on va rapprocher au moyen de méthodes floues et de règles métier.  A ce stade, on dispose de Master Data (données de référence). Une dimension sur ces Master Data en est simplement une potentielle historisation (SCD) en plus de modification structurelles liées au frontal ou aux principes de Ralph.

Dit autrement: la BI s’occupe de Master Data depuis un petit bout de temps, mais uniquement pour son petit besoin analytique.

Comment partager le travail réalisé sur un projet BI avec les application métier?

Un ancien chef de produit SQL Server, très bon en MDX, fondateur de l’ex-Winsight et inventeur d’expressions célèbres comme « la première année de souris » décrivait ce genre d’usage par l’anaphore « boucler la boucle analytique ».

C’est joli et cela sied bien à l’usage présent je trouve: en effet pourquoi vouloir tenir à l’écart le reste de l’entreprise des efforts de normalisation effectués en aval dans la BI? On le fait bien parfois en ouvrant les ODS à certaines applis non décisionnelles. Aller plus loin est donc possible technologiquement.

Bon pour être honnête, rien de ne l’a jamais empêché dans l’absolu, sauf à mon humble avis des besoins fonctionnels assez peu conciliables entre la BI et les utilisateurs des référentiels.

– Les utilisateurs ont besoin d’outil intuitifs pour modifier un faible volume de données, réaliser des insertions, avec une restitution compréhensible: en gros ils font de l’Access ou achètent un outil de PIM.

L’IT et la BI en particulier de solution industrialisables, permettant du chargement en bloc de données de référence depuis des systèmes tiers: ils font de l’ETL.

Mettre sous le même chapeau ETL et PIM étendu à tout le spectre des référentiels: c’est ce que fait MDS en 2012.

Principe de Master Data Services 2012

Master Data Services, comme en 2008R2, s’installe avec une base de données dédiée sur laquelle tourne un jeu de services WCF. Un site Web permet la configuration, la création de modèles de données (Client, Produit…), des règles métier associées en utilisant ces services… Il est installé et sa base créée un peu comme RS via un Configuration Manager.

D’un point de vue process, les données sont exposées entité par entité via des vues SQL, donc facilement interrogeables. Le CRUD sur les entités peut se faire directement à travers SQL via des tables de staging SQL Server ou à travers l’API de services WCF.

7840.MDS Architecture

Deux principales choses changent par rapport à 2008R2:

  1. La modification côté utilisateur se base sur Excel. WCF – qui n’est en effet pas le moyen le plus facile d’intéragir de manière graphique avec un client final sans développement tiers – a été utilisé pour proposer un addin Excel en VSTO très choupi. Cet addin, destiné aux clients qui maintenaient leur référentiel local de manière autonome, va leur permettre de modifier le référentiel central que vous mettez en place, tout en respectant de potentielles règles métier (celles-ci étant définies dans le client Web). Cet addin est téléchargeable depuis le site MDS (comme Report Builder depuis Report Manager, tiens tiens). Il est puissant puisqu’il permet de créer des entités et de modifier intégralement leur structure et/ou leur contenu si on possède les droits ad-hoc.  (NB: Pour Office 2013, bien télécharger la toute dernière version sur le site de Microsoft en revanche, sinon vous aurez un bug d’affichage assez rédhibitoire )
  2. L’import des données en bloc se fait désormais par des tables de staging par entité (dit autrement une table pour les Produits, les Couleurs de Produit, les Catégories… avec une colonne par information à renseigner, un truc normal quoi), plutôt qu’avec les inutilisables tables clé/valeur de 2008R2 (vous pouvez regarder le tutorial en 2008R2 de l’équpe produit, c’était à vomir original). En plus, les codes des entités sont maintenant auto-générés (oui pour les fans de SSAS Multidim dans MDS on a un code et un libellé par entité). C’est fini, plus de script dans SSIS pour faire de la surrogate. Une fois ces tables remplies de données source, on peut déclencher le processus de chargement en WCF, via une procédure stockée SQL, ou depuis le frontal Silverlight. En cas de présence de l’entité considérée dans le référentiel, c’est la propriété ImportType de la ligne de l’entité dans la table de staging qui gouverne la mise à jour ou pas des données, avec ou sans erreur levée. En fin de chargement, il est bien sûr possible de consulter le résultat de l’import, comme depuis l’addin Excel (et donc les raisons d’un éventuel échec: infraction vis à vis d’une règle métier… etc…).

Excel Conclusion: l’avenir des projets BI?

Un certain volume d’ETL sur les phases amont des projets BI a, à mon avis, vocation à disparaître tant il est anormal que seule la mise en place de solutions décisionnelles  entraîne un effort d’intégration/standardisation de données.

La qualité de données commence dès sa saisie et l’analyse n’est que le sommet de la pyramide. Donc la prochaine fois que vous constituerez un référentiel juste pour la BI en vous alimentant depuis des bases Access/DBase/MySQL gérées par les métier en local, demandez vous si par hasard votre référentiel ne leur serait pas utile, et testez MDS 🙂

Techdays 2013: une petite dose d’Analysis Services pour démarrer l’année!

Bonjour à tous!

Cette année si vous n’aviez pas assez vu de MDX, de DAX avec David Joubert aux JSS, vous allez pouvoir venir aux Techdays 2013, voir ce que valent nos deux moteurs de Redmond au banc d’essai! Nous animerons en effet avec l’exceptionnel Romuald Coutaud et le non moins exceptionnel Aurélien Koppel de Microsoft une session qui vise à pousser les deux moteurs dans leurs retranchements fonctionnels et techniques afin que vous soyez sûr en repartant:

  1. De faire votre prochain projet avec Analysis Services
  2. De choisir le moteur qui satisfera vos utilisateurs.

Alors on vous attend, préparez des questions qui piquent, potassez votre MDX Cookbook, on se voit le mardi 12 à l’heure du gouter (ou de l’apéro, comme vous préférez!)

Allez vous inscrire, c’est par ici!

Conversion de temps: encore un usage sympa des SCOPE Assignments

Je bondis souvent lorsque l’on s’avise de me dire que “Tabular c’est génial, après tout les SCOPEs et les many-to-many ça ne sert que très rarement”. Effectivement si on voit SSAS comme un couche sémantique bêbête à la Business Objects sur une BdD relationnelle en étoile, que l’on fait en somme de la BI du 20ème siècle pourquoi pas. Mais lorsque l’on souhaite faire quelque chose de plus user-friendly, faire de son cube un vrai hub de données, presque une application à part entière alors je ne vois pas comment faire sans SCOPE (pensez aux Date Tool, aux discrétisations dynamiques comme les tranches d’âge) ou sans Many To Many (Conversion de devises, gestion des SCD…). Mais bon on n’est pas là non plus pour faire du tabular bashing, donc revenons à l’essentiel.

Il y a quelques semaine j’étais de passage chez un client ISV avec une problématique tellement rigolote que je me suis étonné de ne pas l’avoir rencontrée jusqu’alors: leurs données de fait sont datées en GMT mais les utilisateurs du cube sont dans le monde entier et souhaiteraient voir les données de production de leur zone dans… leur fuseau horaire.

Et ça n’est pas compliqué du tout. On a besoin de deux choses

  • Une dimensions Temps à la granularité heure
  • Une dimension technique (i.e. sans relation avec les groupes de mesure) qui schématise les fuseaux horaires.

Pour le premier point je ne vous ferai l’insulte ni de vous expliquer pourquoi on en a besoin, ni comment on le met en place Sourire. Pour le second, un simple requête en mode Table Value Constructor fait très bien l’affaire.

CREATE VIEW [dbo].[_DimUtc]
AS
WITH GmtCte
AS
(
	SELECT -12 UtcKey
	UNION ALL
	SELECT UtcKey + 1
	FROM GmtCte
	WHERE UtcKey <= 13
)
SELECT
	UtcKey,
	'UTC'
	+
	CASE
		WHEN UtcKey > 0 THEN '+'
		ELSE ''
	END
	+
	CASE
		WHEN UtcKey <> 0
		THEN CONVERT(VARCHAR,UtcKey)
		ELSE ''
	END	UtcName
FROM GmtCte
GO

Bon ça ne gère pas les demi fuseaux mais l’idée est là. On dispose bien d’un beau libellé et la clé va servir à générer le pseudo DATEADD. Une fois la dimension montée dans un cube simplifiée on a le Dimension Usage suivant:

DU

Ne reste plus qu’à écrire le SCOPE. Il spécifie que lorsqu’un UTC est sélectionné, ce dernier décale les membres heure de la dimension temps grâce à ParallelPeriod. Les chiffres agrégés sont modifiés grâce à la hiérarchie.

Scope
(
    [Date].[Hour].[Hour],
    [Utc].[Utc].[Utc]
);
    this =
    (
        ParallelPeriod
        (
            [Date].[Calendar].[Hour],
            -[Utc].[Utc].CurrentMember.Member_Key
        ),
        [Utc].[Utc].DefaultMember
    );

End Scope;

Le résultat est alors ce que l’on souhaitait, et plutôt performant:

UTC3

Voilà en espérant que cela vous serve.

A bientôt!

Bonus: Petite friandise: on peut même décider de mettre en place un DefaultMember dynamique: pour cela une petite fonction en .NET à déployer sur le serveur SSAS peut suffire. Il est alors possible de l’appeler dans le script pour mettre à jour le membre par défaut de la dimension UTC. 

public int GetCurrentTimeZoneOffset()
{
   return TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now);
}

Merci aux participants de notre session aux JSS2012!

Merci à tous ceux qui ont eu le courage de venir suivre notre session de DAX et MDX aux Journées SQL Server! Merci surtout au grand David Joubert, Marco Juberto du DAX avec qui je me suis bien marré et vous aussi je l’espère, en plus d’apprendre quelques trucs.

La salle était pas mal remplie pour une 400, avec pas mal de collègues et de copains en guest star (Florian Eiden, Charles-Henri Sauget, Thomas Ricquebourg, Aurélien Koppel, Romuald Coutaud, David Tang…)

Plus globalement sur l’event que je n’ai pas eu le temps de suivre (qui a dit qu’on était en retard dans les répétitions 🙂 ?) le ressenti des gens j’ai pu croiser est bon voire très bon, l’organisation s’affine petit à petit, et tout le monde s’accorde à dire que l’on tient là l’évènement de référence sur SQL Server. Longue vie aux JSS, et félicitations aux nombreux speakers (et les copains de la FC.BI qui dort un peu en particulier).

PS: Comme je suis un type sympa, vous pouvez retrouver les slides et les démos dans ce dossier sur mon SkyDrive.

A bientôt, aux TechDays?

Passez me dire bonjour aux Journées SQL Server!

Bonjour!

Comme dit précédemment, je co-animerai une passionnante session sur DAX et MDX aux Journées SQL Server avec David Joubert, centrée sur l’utilisation de DAX dans Analysis Services Tabular, et plus particulièrement dans des usages précédemment réservés à MDX.

La session aura lieu le mardi 11 décembre à 14h au Centre de Conférences Microsoft à Issy les Moulineaux.

Ce sera l’occasion pour vous de découvrir DAX, de voir comment il peut vous aider à résoudre de manière élégante des problématiques lourdes en MDX (Attribute Overwriting, multi-sélection), sans pour autant négliger les usages intéressants et toujours exclusifs au MDX. Venez avec vos questions, ça va être sympa 🙂

Pour ça rien de plus simple: allez vous inscrire, et n’hésitez pas aussi à venir aux sessions de mes petits camarades d’Infinite Square:

Thomas Ricquebourg le matin même sur MDS, DQS et SSIS

Charles-Henri Sauget sur de la modélisation dimensionnelle avec l’immmmense Florian Eiden le lundi

Patrice Harel la veille aussi sur du processing SSAS

Image

Alors on se retrouve aux Journées SQL Server?

Bonne soirée!

Save the date pour les Journées SQL Server!

La date est officielle ! La seconde édition des Journées SQL Server aura lieu les 10 et 11 décembre 2012 au

Centre de Conférences Microsoft
39 quai du Président Roosevelt
Issy-Les-Moulineaux

Vous pouvez dès à présent bloquer la date dans vos agendas pour participer à la plus grande conférence SQL Server en France.

Les inscriptions seront ouvertes dans quelques semaines, encore un peu de patience. En attendant rendez vous sur la page Facebook des JSS pour plus d’informations!

Parution de Integration Services 2012 chez ENI en Novembre!

Voilà vous savez maintenant à quoi ma capacité rédactionnelle a été occupée ces derniers mois: en effet, le mois prochain si tout se passe bien vous pourrez trouver chez tous les bons libraires techniques – pas la peine d’essayer chez votre vendeur de BD préféré – le livre Integration Services 2012 que j’ai co-écrit avec mes gentils collègues Patrice Harel et Romuald Coutaud.

Tout au long des onze chapitres, vous découvrirez pour ceux qui ne connaissent pas le développement, l’administration et l’extension du produit. Le livre s’adresse aussi bien aux débutants qu’aux utilisateurs du produit souhaitant étendre leurs connaissances, puisque nous avons essayé d’être les plus exhaustifs possibles tout en restant abordables.

Merci à Romuald et Patrice, et à tous ceux qui nous ont aidé durant la longue gestation de cette “oeuvre” majeure à n’en pas douter!

WriteBack et sécurité dynamique de dimension pour gérer les droits sur son cube depuis Excel

La sécurité dynamique de dimension ou Dynamic Dimension Security est un sujet relativement bien couvert dans la littérature anglophone sur Analysis Services. En revanche, beaucoup de scénarii avancés ne sont pas très abordés, surtout dans la langue de Molière, tant il est vrai que nous sommes relativement peu confrontés à des demandes complexes de sécurisation poussées.

Imaginons le cas d’un cube centralisé sans Active Directory. Les utilisateurs se connectent au cube avec un compte unique, ou un nombre restreint de comptes Windows: cette demande est relativement fréquente et peut heureusement être résolue si le frontal utilisé pour se connecter au cube est capable de modifier la chaîne de connexion pour y ajouter un attribut CustomData. Cet attribut peut être interrogé en MDX via la fonction CustomData(), qui renvoie la chaîne de caractère associée.

NB: Vous pouvez aussi faire ça avec un Excel et des ODC: arrangez vous cependant pour mettre dans le CustomData de l’ODC des données incompréhensibles – GUID? – pour l’utilisateur final à qui vous donnez le fichier: celui ci pourrait sinon bypasser la sécurité trop facilement…!

Cette sécurisation doit être dans notre cas appliquée au niveau fin, sur deux dimensions Produit et Magasin, sur lesquelles nous allons vouloir donner des droits membres par membre. Pour ce faire, nous allons créer une dimension Utilisateur, et deux tables factless de sécurisation qui porteront les associations autorisées.

Le schéma est le suivant, il est classique de la sécurisation à la ligne en utilisation des tables de pont:

image

Nous avons donc trois groupes de mesures: un sur la table de faits des ventes par date, produit et magasin, et un sur chacune des factless de sécurisation.

Pour sécuriser cela, un Rôle doit être créé. Ce rôle porte classiquement dans la partie Dimension Data une expression MDX de la forme:

Exists
(
   [Store].[Store].Members
   ,StrToMember("[User].[Custom Data].&["+CustomData()+"]")
   ,"User Store"
)

Qui dit que sont autorisés les membres de la dimension pour lesquels il existe une association avec l’utilisateur connecté. Bien entendu, il faut que l’utilisateur correspondant au CustomData() de la chaîne de connexion existe.

Arrivé à ce stade, c’est terminé, le cube est sécurisé dynamiquement. Il ne reste plus qu’à cacher les objets de sécurité (tables factless, dimension User) et alimenter les tables de sécurité pour gérer les matrices de droits.

Et c’est justement sur ce dernier point que la chose peut devenir intéressante. On m’a demandé récemment si on ne pouvait pas imaginer remplir ces matrices de droits depuis Excel: et évidemment l’idée d’utiliser du WriteBack m’est venue. Mais comme il faut pouvoir voir les éléments pour écrire dedans dans la What-If Analysis de Pivot Tables, il faut créer un second cube, dédié à l’écriture et n’exposant que les tables de sécurité (User, Store, Product et les deux factless) si on ne veut pas du même coup montrer les mesures des factless à l’utilisateur final.

NB: On aurait aussi pu faire de la Cell Security mais euh… non… En fait. NON. Si jamais un jour une envie de Cell Security vous gratouille, rappellez vous que c’est antiperformant, et relisez Chris Webb 🙂

Pour ne pas rendre le tout redondant on peut donc récupérer les deux groupes de mesures via des objets liés. Il est en effet permis dans Analysis Services de récupérer dans un cube les groupes de mesure et partitions d’un autre cube.

WriteBack, Linked MeasureGroups, Dynamic Dimension Security avec CustomData(): que voilà un programme alléchant! Modifions donc la base Analysis Services. Créons deux cubes:

image

Le cube de sécurité ne concerne donc que les tables d’administration, comme le montre son Dimension Usage:

image

Ses deux partitions sont rendues accessibles en écriture dans l’onglet Partitions. Attention, comme pour pouvoir affecter des droits sur des croisements qui n’existent pas encore dans Excel il faut pouvoir afficher le croisement, on crée une mesure bidon.

CREATE MEMBER CURRENTCUBE.[Measures].[Display All]
 AS 1,
VISIBLE = 1;

OK il y a mieux… Mais ça passera. Dans Excel on constate que l’écriture fonctionne:

image

Quant au second cube, il récupère ces deux groupes de mesures via l’assistant d’importation d’objets liés.

image

Maintenant il faut modifier les expressions de sécurité. En effet, Exists() n’est plus utilisable: lorsque l’on utilise le WriteBack on ne peut pas supprimer la ligne, mais juste lui affecter 0 si on veut enlever un droit. Elle “existe” donc encore au sens MDX. On est donc obligés d’utiliser un Filter() (Oui je sais ça désactive la Block Computation mais pour le coup comme l’a dit Mosha il y a bien longtemps les rôles de sécurité sont appliqués avant le script MDX, donc pas moyen de créer un membre bidon basé sur un IIF).

On a donc un truc moche dans ce genre:

Filter
(
   [Product].[Product].Members
   ,(
      StrToMember("[User].[Custom Data].&["+CustomData()+"]")
      , [Measures].[Rights On Product]
   )>0
)

Une fois ceci fait tout marche comme souhaité: la modification du cube de sécurité
dans Excel implique la mise à jour des règles dans le cube de consultation.

Alors cependant tout n’est pas complètement parfait:

  • Utiliser Filter() ne me plait pas des masses, s’il n’y a pas beaucoup d’utilisateurs c’est cependant peu grave
  • Même remarque pour la mesure Dummy qui affiche 1
  • En revanche plus gênants sont les problèmes récurrents de mise à jour du cache lorsqu’un cube implique des groupes de mesures liés activés en écriture, problème censé être résolu depuis 2008 (http://support.microsoft.com/kb/959768) mais toujours présent en 2012 au vu de mes tests.

D’où en conclusion le fait que c’est limité mais assez intéressant, surtout si vous voulez éviter le dev d’une application qui va mettre à jour la table de sécurité (que l’on aura pris soin de mettre en correspondance avec une partition ROLAP) qui est la seule option équivalente.