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!

HAVING en MDX, une clause méconnue et pourtant performante

Le MDX a une clause HAVING. Je sais ça peut paraître une nouveauté pour certains – je ne l’ai pas appris immédiatement loin s’en faut alors qu’elle existe depuis 2005. La seule documentation réellement utilisable sur cette clause est un papier de William Pearson datant de 2007 dans lequel celui-ci détaille une équivalence fonctionnelle entre HAVING et Filter().

Dit autrement, HAVING peut – pour lui – se voir comme un Filter() appliqué sur un Axis, ces deux notations étant alors interchangeables

{
   Filter
   (
      [Dimension].[Level].Members,
      [Measures].[Measure] > 42
   )
} ON 0
{
   [Dimension].[Level].Members
}
HAVING [Measures].[Measure] > 42
ON 0

Si HAVING est effectivement utile pour réaliser des filtrages, elle présente un intérêt évident par rapport à un Filter(). Non qu’elle soit “block-computationable” , ce n’est pas le cas. En revanche elle s’exécute après l’auto-existence des axes et des NON EMPTY. Et ça ça peut être intéressant.

Prenons un exemple bidon: je souhaite afficher la liste des ventes par jour de plus de 10000$. Ecrivons d’abord la requête de base.

SELECT
{
   [Measures].[Internet Sales Amount]
} ON 0
,
NON EMPTY
{
   [Product].[Product].[Product] 
   * 
   [Date].[Date].[Date]
} ON 1
FROM [Adventure Works];

Cette requête MDX, passée au MDX Studio, s’exécute en 200ms et affiche un Cells Calculated de 23797 avec 1 NON EMPTY, et 1 Sonar Subcube. En gros on a appliqué NON EMPTY sur l’Axe, ce qui a renvoyé 23000 cellules, et généré une requête au Storage Engine.

Si on passe l’Axis 1 sous un Filter() pour les fameux 10000$, le Cells Calculated passe à… 869965, et le temps d’exécution explose. Normal, vous connaissez le fonctionnement du Filter: il a itéré sur toutes les combinaisons – produit cartésien oblige – des dates et des produits sans « Exister » avant le Set sur la mesure.

Time              : 2 sec 307 ms
Calc covers       : 0
Cells calculated  : 869965
Sonar subcubes    : 1
NON EMPTYs        : 1

Tout à fait logiquement, en appliquant un NonEmpty() sur le set avant d’appliquer le Filter, on retombe sur 23000 cellules, et on a cette fois ci 2 Sonar Subcubes et 2 NON EMPTY (Celui de la fonction NonEmpty(), et celui du NON EMPTY sur l’axe).

SELECT
{
   [Measures].[Internet Sales Amount]
} ON 0,
NON EMPTY
{
   Filter
   (
      NonEmpty
      (
         [Product].[Product].[Product]
         * [Date].[Date].[Date]
         ,[Measures].[Internet Sales Amount]
      )
      ,[Measures].[Internet Sales Amount]>10000
   )
} ON 1
FROM [Adventure Works]

Time : 135 ms
Calc covers : 0
Cells calculated : 23935
Sonar subcubes : 2
NON EMPTYs : 2

Alors quel est l’intérêt du HAVING? Il vous permet de filtrer l’axe après l’application de l’ordre NON EMPTY sur les Axis, et donc de vous éviter un NonEmpty (Exists) supplémentaire et inutile dans la clause Filter. Pas le truc de l’année, mais si ça permet d’économiser du temps de cerveau disponible…

SELECT
{
   [Measures].[Internet Sales Amount]
} ON 0
,NON EMPTY
{
   [Product].[Product].[Product] 
   * 
   [Date].[Date].[Date]
}
HAVING [Measures].[Internet Sales Amount] > 10000 
ON 1
FROM [Adventure Works];

HAVING peut donc se révéler très utile et rapide pour faire un filtrage (type Filter) post-application des NON EMPTY des axes, dans un rapport RS par exemple, et sans vouloir s’embêter à écrire soi-même la logique d’Exists/NonEmpty spécifique à ceux-ci.

A bientôt!

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!

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.

[SSAS] Afficher une Parent Child sur un seul niveau avec MDX

Curieuse demande en apparence, mais finalement assez compréhensible. Lorsque l’on utilise une hiérarchie – ce qui est très pratique pour certains états – il est fréquent de vouloir aussi disposer des informations sous une forme non-hiérarchisée, une sorte de liste, et que cette information soit disponible sous Excel 2007.

C’est particulièrement le cas fonctionnellement dans le domaine du reporting financier et plus techniquement dans tous les domaines où tous les niveaux hiérarchiques peuvent porter des données, donc des dimensions généralement en Parent-Child.

Pour générer une liste de tous les attributs d’une Parent-Child à plat, rien de plus simple: la hiérarchie d’attributs sur laquelle s’appuie la Parent-Child le fait nativement.
Par exemple la hiérarchie Employee (sans S) d’AdventureWorks liste bien tous les Employés… mais lorsqu’on y associe une mesure elle n’est capable d’afficher que les DataMember, autrement dit les mesures associés à ce membre précis et non la valeur aggrégée que la hiérachie rapporte.


Par exemple l’on regarde les valeurs de la Parent-Child (Employees) on constate:
1) Que Ken Sanchez et Brian Welcker n’apparaissaient pas dans la hiérarchie d’attributs: ils n’ont pas de faits associés.
2) Qu’Amy Alberts elle apparaît bien à plat mais avec une valeur fausse: la valeur affichée est en effet celle de son DataMember et pas l’aggrégation de celui ci et de ses enfants.


L’idée est donc de recopier dans la hiérarchie d’attributs la valeur de la Parent-Child.
L’instruction LinkMember permet de récupérer un membre d’une hiérarchie à partir d’un membre d’une autre du moment qu’ils partagent la même clé (ce qui est le cas ici). Certes ce n’est pas le top niveau performance (même si dans la même dimension c’est légèrement mieux), mais de toute manière cela ne concerne pas des dimensions de cardinalité trop élevée vues les applications métier.

On peut réaliser cette affectation via un SCOPE dans le script MDX, de cette manière:

SCOPE
(
[Employee].[Employee].[Employee].Members
);
This = LinkMember([Employee].[Employee].CurrentMember,[Employee].[Employees]);
END SCOPE;

Le résultat est tout bonnement enthousiasmant et l’effet immédiat:


Les noeuds sans données apparaissent (Welcker, Sanchez…) ceux composites sont corrigés (Amy Alberts). Bref tout semble parfait. Sauf qu’il y a un hic.
Si l’on re-regarde la Parent-Child, qui s’appuie sur cette hiérarchie d’attributs, on constate que les chiffres sont complétement faux. En effet, la Parent-Child tire ses données… de la hiérarchie d’attributs… qui elle même tire ses données de la Parent-Child.

Par exemple Amy Alberts est égale à son Data Member plus la somme de ses enfants. Son DataMember vaut 732.078,44 – voir premier screenshot – et la somme de ses enfants 14.803.867,81 – merci calc.exe -, le tout faisant 15.935.546,26, la valeur affichée dans la hiérarchie initialement.
Or ici on a:
15.935.546,26 (la valeur réaffectée dans la hiérarchie d’attributs, que SSAS considère à tort comme le DataMember)
+ 14.803.867,81 (la somme des enfants)
=30.339.814,07


Il faut donc verrouiller la ParentChild avant de faire la modification de la hiérarchie d’attributs, ce que l’on peut faire avec l’instruction Freeze.
Le script précédent devient alors:

FREEZE([Employee].[Employees].Members);
SCOPE
(
[Employee].[Employee].[Employee].Members
);
This = LinkMember([Employee].[Employee].CurrentMember,[Employee].[Employees]);
END SCOPE;

Du coup le résultat devient beaucoup plus acceptable:

La hiérarchie peut donc maintenant faire un rapport Excel à mettre à disposition via Excel Services par exemple, permettant d’analyser des membres de niveaux différents dans la même colonne. C’est l’idée générale qui permet de faire des rapports sur des statistiques, des comptes d’exploitation choisis et mis à plat, pour ne prendre que des exemple communs utilisant les Parent-Child…

En espérant vous avoir donné des idées, mais attention cependant, il s’agit clairement d’une manip (les parent-child et leur hiérarchie d’attributs sont très liées) dont je n’ai pas eu le temps de tester extensivement toutes les implications. Si vous constatez un comportement étrange n’hésitez pas à le mettre en commentaire.

A bientôt!

PS: avec Excel 2010 ceci serait obsolète: les PivotTables disposent d’un Set Designer, il suffit alors de se créer un jeu contenant les employés souhaités. Mais 2007 est encore là pour quelques années…

[SSAS] Réhabilitation des ParentChild (ou pourquoi elles ne sont pas le mal absolu)

Je discutais l’autre jour avec quelques confrères lorsque j’ai entendu cette phrase au milieu d’un brouhaha technique.

« … les ParentChilds il faut que vous les enleviez: question performance c’est nul, fonctionnellement ça ne sert à rien. Analysis Services n’est pas fait pour ça, il les ont laissées pour s’adapter à certains types d’architectures existantes. Il suffit de lire le Performance Guide. »

Je n’aime pas ce genre d’affirmation pontifiante. D’autant plus quand, comme toute assertion de ce type, elle s’inspire de faits et documents réels en les dénaturant.

Je me propose donc modestement de remettre un petit peu de clarté la dedans.


« Les ParentChild, question performance c’est nul »

Les faits: dans une hiérarchie ParentChild, les aggrégations ne sont créées que pour l’attribut clé et le membre de All de la hiérarchie. Dit autrement des aggrégations ne sont pas calculées sur les niveaux intermédiaires.

Réponse: Le coupable serait donc l’absence de calcul d’aggrégats. Ceux ci sont certes très utiles et jouent un rôle crucial. Mais considérer qu’ils constituent l’unique intérêt de l’OLAP et que leur absence est dans tous les cas catastrophique en terme de performances témoigne d’une certaine méconnaissance de ce genre de systèmes. L’idée de l’OLAP est certes de précalculer mais avant cela de se baser sur un formalisme de stockage spécifique permettant rapidement de récupérer les informations. Un cube même sans aggrégats est bien plus efficace qu’une base relationnelle pour les calculs multidimensionnels habituels.

Une ParentChild n’est donc pas en tous les cas catastrophiques en termes de performances… Tout dépend de la volumétrie!
Plus troublant dans certains cas une utilisation extensive du HideMemberIf dans des hiérarchies naturelles – pour permettre de disposer de hiérarchies irrégulières sur de nombreux niveaux – fait qu’une ParentChild peut être plus performante. (cf ce post d’Ajit Singh pourtant grand pourfendeur des P/C sur SSAS-Info).

« Il suffit de lire un peu la documentation enfin! »

Les faits: le Performance Guide déconseille d’utiliser les ParentChilds et recommande de passer à un design naturel… …mais sur de grosses volumétries et sur beaucoup de dimensions et dans le cas ou fonctionnellement l’alternative est possible, comme visible ci dessous:

Réponse: il s’agit simplement d’un raccourci sur le propos de cet excellent guide. L’extrait est ci-dessous.

If you are in a design scenario with a large parent-child hierarchy (greater than 250,000 members), you may want to consider altering the source schema to re-organize part or all of the hierarchy.

Analysis Services Performance Guide

« Fonctionnellement ça ne sert à rien. »

Les faits: Je crois que c’est l’assertion la plus stupide. Cela ne se base sur rien et vient simplement d’un bouche à oreilles général.

Réponse: Il est facile de trouver quelques cas où la modélisation sans ParentChild est fastidieuse. Même Mosha en convient, au début de ce post.

1) Un bien connu est une hiérarchie opérationnelle récursive: les faits ne se situent pas au niveau le plus bas mais potentiellement partout (au niveau du manager, d’un membre de son équipe…). Les règles de Rollup sont de plus potentiellement compliquées.
En Ragged c’est un enfer à gérer. En ParentChild cela se fait simplement. Et il est rare de disposer de plusieurs centaines de milliers de membres dans ce genre de dimension…

2) Un autre exemple est une hiérarchie de type plan de compte, sur le même modèle ou chaque élément de fait peut référencer un niveau qui n’est pas le plus bas. Dans ce scénario de plus, il est fréquent que les utilisateurs veuillent utiliser les indicateurs de plusieurs niveaux dans la même colonne d’un rapport Excel, et donc disposer de deux représentations: sur un niveau et multi-niveau: le ParentChild est la seule manière d’arriver à cela simplement. De plus là aussi on atteint rarement une volumétrie énorme!

D’ailleurs AdventureWorks contient deux exemples identiques… modélisés en ParentChild -Organization et Account. Pourquoi diable les montrer dans des exemples fonctionnels si comme certains l’affirment « le ParentChild n’est là que pour la compatibilité avec des existants ou des habitudes« .


En conclusion les P/C sont moins performantes que les hiérarchies naturelles sur des grosses volumétries de par leur lacune dans le calcul des aggrégats. Elles sont cependant une nécessité fonctionnelle dans certains cas et il est absurde de s’empêcher de les utiliser sous des prétextes fallacieux.

Dynamic Dimension Security et Parent Child avec UserName() ou CustomData()

La sécurisation dynamique des dimensions est un mécanisme un tantinet plus fin que la sécurité par rôles de SSAS. Son principe est d’aller appliquer la sécurité, c’est à dire les membres d’une dimension qu’un utilisateur peut voir ou ne pas voir non pas en fonction de droits posés sur des rôles de manière fixe et externe aux données, mais de les stocker dans la base.

Teo Lachev en particulier a pas mal écrit sur le sujet, ainsi que Nick Barclay et bien sur Mosha. Le soucis c’est qu’ils ont adressé chacun à leur manière une partie du problème.

J’ai donc essayé de vous expliquer ce concept dans un post unique et en l’illustrant d’un petit exemple avec Adventure Works DW.

Principe

Quand ce genre de sécurité devient-il nécessaire?Lorsque les droits des utilisateurs sont aisément exprimables en termes métiers mais que leur expression en terme de rôles devient fastidieuse.

Une règlerencontrée récemment: « chaque employé du call-center a le droit de voir les données ouvertes par les autres employés ayant le même manager et le même grade. » est très simple à implémenter en SQL, mais infiniement plus fastidieuse avec des rôles SSAS: elle oblige a créer un rôle pour chaque Business Unit, et à changer les utilisateurs de BU à chaque changement RH: ce type de sécurité est par essence dynamique.

Pour faire l’exemple simple énoncé ci-dessus avec AdventureWorks DW, je me suis rajouté moi dans la table Employee d’AWDW. J’ai un manager et un login, WW-VMFJ-01\Administrator.

En SQL je récupère mes « collègues » de cette manière:

SELECT Collegues.*
FROM DimEmployee Emp
INNER JOIN DimEmployee Collegues
ON Emp.ParentEmployeeKey=Collegues.ParentEmployeeKey
WHERE Emp.LastName=‘Jehl’

Application des règles de sécurité: UserName

On le comprend rapidement, les formules de calcul MDX vont avoir pour but de récupérer les informations de l’utilisateur connecté et de les corréler à une hiérarchie Login. C’est ensuite dans l’onglet Dimension Data / Advanced du Role designer que l’on va appliquer cette formule.

Quant à la formule elle-même… La récupération de l’utilisateur courant en MDX est une affaire relativement simple: la fonction UserName vous permet de récupérer son nom au format Domaine\Utilisateur. Ensuite cela se passe de cette façon (cf Adventure Works DW) et c’est cette expression que je vais utiliser pour filtrer les membres auquels j’ai accès:

–3) Je récupère sa position dans la ParentChild grâce à la clé de Dimension
LinkMember
(
2) Je le rattache à la clé de Dimension correspondante
Exists
(
[Employee].[Employee].Members,
— 1) Je recherche le membre ayant le login correspondant à UserName
Filter
(
[Employee].[Employee].[Employee],
[Employee].[Employee].CurrentMember.Properties(« Login ID ») = UserName
)

).Item(0),
Employee.Employees
)
–4) Enfin je récupère ses collègues (membres de même niveau dans la ParentChild)
.Siblings

 

Une fois l’expression collée dans l’onglet advanced je vois immédiatement le résultat en browsant dans l’OWC de BIDS:


Alternative sans domaine: CustomData

Ceci dit cette stratégie ne marche que dans un contexte d’authentification intégrée sur un domaine. Si l’on a mis en place une authentification anonyme avec le Middle Tier msmdpump.dll (cf mes posts précédents sur le sujet) il faut faire autrement.

Dans la plupart des architectures de ce type, la stratégie avancée est d’utiliser la node Role de la ConnectionString pour spécifier les rôles auquel l’utilisateur est mappé (la sécurité est cumulative dans SSAS). Mais on peut aussi faire de la sécurité au niveau utilisateur: en passant par la forge d’une chaine de connexion spécifique qui passe le login à travers le noeud CustomData.

Qu’est ce que le noeud CustomData? C’est un attribut de la chaine de connexion à travers lequel on peut passer ce que l’on souhaite et dont on peut récupérer la valeur via la fonction MDX CustomData().

On va donc créer un Rôle spécifique à l’utilisateur anonyme exécutant l’application pool hostant msmdpump.dll. Dans mon cas l’utilisateur est AnalysisServicesHTTP et le rôle s’appelle… HTTP.
Il faut juste modifier l’expression au dessus pour prendre en compte CustomData, mais aussi en gérant le cas ou cette valeur n’est pas renseignée (Merci à Chris Webb pour cette précision d’ailleurs, sans qui je serais resté bloqué 🙂 )

–1) Si CustomData est vide, je renvoie un Set vide
IIF ( IsEmpty(CustomData()) ,{} ,
–4) Je récupère sa position dans la ParentChild grâce à la clé de Dimension

LinkMember
(
3) Je le rattache à la clé de Dimension correspondante
Exists
(
[Employee].[Employee].Members,
— 2) Sinon je recherche le membre ayant le login correspondant à CustomData
Filter
(
[Employee].[Employee].[Employee],
[Employee].[Employee].CurrentMember.Properties(« Login ID ») = CustomData()
)

).Item(0),
Employee.Employees
)
–5) Enfin je récupère ses collègues (membres de même niveau dans la ParentChild)
.Siblings
)

Dans les frontaux, on peut donc utiliser cette node pour passer le Login de l’utilisateur, en forgeant cette chaîne dans les SSRS. (Bien sûr dans le scénario, SSRS est sur le domaine et joint un cube non présent sur le domaine).

RS possède en effet la possibilité de générer dynamiquement les ConnectionStrings (attention pas sur les DataSources partagées!) et dispose de la propriété User!UserId qui renvoie… l’utilisateur exécutant le rapport sous la forme DOMAIN\User. Pile ce qui nous intéresse.
Il va donc falloir utiliser CustomData pour passer la valeur de UserId comme visible ci dessous (notez au passage l’adresse du middle tier HTTP):


Ensuite il ne reste qu’à faire un rapport simple, et vérifier que l’on ne voit que ses collègues (et les membres supérieurs mais avec la valeur de la mesure pour All pour ceux composés d’au moins un membre sur lequel je n’ai pas les droits).


Voilà, en espérant que cela vous ait donné des idées de scenarii de sécurité alternatifs intéressants, à bientôt!

Data Mining et OLAP: Scénario de Cross-Selling

Aux TechDays, il y a bientôt 6 mois, nous avions abordé avec Romuald Coutaud le scénario de Forecasting depuis un cube OLAP, avec insertion des prévisions dans le cube. J’ai eu l’idée d’un scénario alternatif de démonstration de la complémentarité du DataMining et de l’OLAP: l’utilisation d’algorithmes de Cross-Selling en compléments d’un outil d’analyse multidimensionnelle.

L’idée générale est d’ajouter via une action la potentialité pour l’opérateur de voir à la demande, depuis son frontal, pour chaque client une liste d’items à recommander, en passant par le mécanisme de Rowset actions et du DMX.

Pour l’exemple j’ai construit un tout petit cube: c’est un sous-set de celui d’AdventureWorks2008DW: seulement 3 dimensions: Customer, Product et Time (RPD 3 fois) sur la FactInternetSales dont seules les mesures OrderQuantity et SalesAmount on été créées.

Sur ce cube, j’ai construit un MiningModel d’Association Rules, basé sur Customer en Case et Product en Nested – le bridge N-N se faisant implicitement via le groupe de mesures lorsque l’on crée des modèles en source OLAP.


Les deux étapes à suivre sont donc:

  1. Création d’une action sur les membres de la dimension Customer
  2. Exécution d’une requête DMX contextuelle sur cette action, et renvoi en rowset des prédictions.

La requête DMX se décompose elle même en deux parties:

  1. Appel de la fonction PredictAssociation qui prend en paramètre la table imbriquée à prédire (ici les futurs produits à acheter)
  2. Jointure du modèle avec deux requêtes MDX: une pour récupérer le client (SHAPE) et une autre pour récupérer les produits achetés par celui-ci (APPEND), ces deux requêtes reliées logiquement par l’ID client (RELATE..ON).

Pour chaque client on obtient donc prédictions de produits intéressants (5 étant la valeur donnée à PredictAssociation).

SELECT FLATTENED PredictAssociation([Products],5)
FROM [CrossSellingByCustomer_MAR]
PREDICTION JOIN
SHAPE
{
SELECT {[Measures].[Order Quantity]} ON 0,
{[Customer].[Customer].&[28187]} ON 1
FROM [OLAPCrossSellingCube]
}
APPEND
(
{
SELECT {[Measures].[Order Quantity]} ON 0,
NON EMPTY {[Customer].[Customer].&[28187]*[Product].[Product].[All].Children} ON ROWS
FROM [OLAPCrossSellingCube]
}
RELATE [[Customer]].[Customer]].[Customer]].[MEMBER_CAPTION]]]
TO [[Customer]].[Customer]].[Customer]].[MEMBER_CAPTION]]]
)
AS n
AS t
ON [CrossSellingByCustomer_MAR].[Products].[Product] =
t.n.[[Product]].[Product]].[Product]].[MEMBER_CAPTION]]]

Il ne reste plus qu’à créer une action de cube, de type Rowset (renvoi de lignes) qui prend en instruction une chaîne de caractères à envoyer au serveur Analysis Services.

Cette action est paramétrée en déclenchement sur les membres de la hiérarchie utilisateur de la dimension Client. Cela signifie que dans un client compatible (Excel, OWC…) tout clic droit sur un membre à ce niveau donnera un menu contextuel permettant de déclencher cette action, et donc d’afficher une boîte modale contenant les prédictions effectuées pour ce membre. Le statement déclenché pour ce faire sera notre instruction DMX, en remplaçant simplement les IDs du client – en dur ci dessus – par celui du membre courant dans la hiérarchie utilisateur, via l’appel à « CurrentMember.UniqueName » (MDX).

Une fois le cube déployé on peut voir que l’action est disponible lorsque l’on browse par la bonne hiérarchie…


Ce qui permet d’afficher les prédictions pour ce membre…


Cela marche évidemment aussi pour Excel:



Quelles sont les applications de ce genre de fonctionnalités? Cela permet de voir le cube non seulement comme outil d’analyse, mais aussi et surtout comme outil non plus passif mais actif pour des interlocuteurs fonctionnels.

Le cube permet d’analyser quelles ont été les ventes (OLAP simple) quelles seront les ventes (OLAP avec données forecastées, tel que montré aux TechDays) mais aussi d’optimiser le processus de vente, en proposant les bonnes offres aux bons prospects, via une méthode éprouvée, le tout de manière intégrée et facile à mettre en oeuvre.

Voilà, en espérant vous donner des idées.

A bientôt!