IsAvailableInMdx est une nouvelle propriété de colonne au sein des modèles SSAS Tabulaire, celle-ci est disponible depuis Juin 2018 (SQL 2017 CU7) et permet de contrôler la construction des hiérarchies d’attribut pour une colonne, qui jusqu’ici étaient forcément construites même si jamais utilisées.
Afin de comprendre le gain attendu ainsi que le contexte dans lequel utiliser cette propriété, prenons l’exemple de la solution AdventureWorks.
Il est possible d’obtenir les informations sur le modèle grâce aux DMV (Dynamic Management Views) de SSAS ou alors avec le fichier Excel VertiPaq Analyzer de SQL BI qui prémâche le travail en préparant le modèle adéquat via PowerPivot.
La connexion du modèle PowerPivot modifiée pour pointer sur le serveur cube à analyser, il ne reste qu’à actualiser celui-ci.
Astuce : Si vous utilisez Azure SSAS, il faudra sélectionner connexion Login / Password et indiquer votre Mail / Password AzureAD.
Une première analyse du modèle nous indique que celui-ci fait 54 Mo (en effet c’est très peu …) décomposé de la manière suivante:
- 1 Mb de données
- 52 Mb de dictionnaires
- 1,1 Mb de Hiérarchies
La taille du modèle ne permet pas d’espérer tirer la moindre conclusion sur la propriété, qu’à cela ne tienne, ce n’est pas la première fois qu’on ajoute des données dans AdventureWorks, ci-dessous la requête utilisée afin de doper la dimension produit.
WITH cntCTE AS (SELECT 1 AS i UNION ALL SELECT i+1 FROM cntCTE WHERE i <=100 ) INSERT INTO [dbo].[DimProduct] ([ProductAlternateKey] ,[ProductSubcategoryKey] ,[WeightUnitMeasureCode] ,[SizeUnitMeasureCode] ,[EnglishProductName] ,[SpanishProductName] ,[FrenchProductName] ,[StandardCost] ,[FinishedGoodsFlag] ,[Color] ,[SafetyStockLevel] ,[ReorderPoint] ,[ListPrice] ,[Size] ,[SizeRange] ,[Weight] ,[DaysToManufacture] ,[ProductLine] ,[DealerPrice] ,[Class] ,[Style] ,[ModelName] ,[LargePhoto] ,[EnglishDescription] ,[FrenchDescription] ,[ChineseDescription] ,[ArabicDescription] ,[HebrewDescription] ,[ThaiDescription] ,[GermanDescription] ,[JapaneseDescription] ,[TurkishDescription] ,[StartDate] ,[EndDate] ,[Status]) SELECT LEFT(CONVERT(NVARCHAR(50), NEWID()),25) AS [ProductAlternateKey] ,(SELECT TOP 1 ProductSubcategoryKey FROM dbo.DimProductSubcategory R WHERE T.ProductAlternateKey IS NOT NULL ORDER BY NEWID()) AS ProductSubcategoryKey ,[WeightUnitMeasureCode] ,[SizeUnitMeasureCode] ,CONVERT(NVARCHAR(50), NEWID()) AS [EnglishProductName] ,CONVERT(NVARCHAR(50), NEWID())AS [SpanishProductName] ,CONVERT(NVARCHAR(50), NEWID()) AS [FrenchProductName] ,ABS(CHECKSUM(NewId())) % 1000000 AS [StandardCost] ,0 AS [FinishedGoodsFlag] ,(SELECT TOP 1 Color FROM dbo.DimProduct R WITH(NOLOCK) WHERE T.ProductAlternateKey IS NOT NULL ORDER BY NEWID()) AS [Color] ,ABS(CHECKSUM(NewId())) % 100 AS [SafetyStockLevel] ,ABS(CHECKSUM(NewId())) % 100 AS [ReorderPoint] ,ABS(CHECKSUM(NewId())) % 1000000 AS [ListPrice] ,(SELECT TOP 1 Size FROM dbo.DimProduct R WITH(NOLOCK) WHERE T.ProductAlternateKey IS NOT NULL ORDER BY NEWID()) AS [Size] ,(SELECT TOP 1 Size FROM dbo.DimProduct R WITH(NOLOCK) WHERE T.ProductAlternateKey IS NOT NULL ORDER BY NEWID()) AS [SizeRange] ,ABS(CHECKSUM(NewId())) % 500 AS [Weight] ,ABS(CHECKSUM(NewId())) % 15 AS [DaysToManufacture] ,(SELECT TOP 1 [ProductLine] FROM dbo.DimProduct R WHERE T.ProductAlternateKey IS NOT NULL ORDER BY NEWID()) AS [ProductLine] ,ABS(CHECKSUM(NewId())) % 1000000 AS [DealerPrice] ,(SELECT TOP 1 [Class] FROM dbo.DimProduct R WITH(NOLOCK) WHERE T.ProductAlternateKey IS NOT NULL ORDER BY NEWID()) AS [Class] ,(SELECT TOP 1 [Style] FROM dbo.DimProduct R WITH(NOLOCK) WHERE T.ProductAlternateKey IS NOT NULL ORDER BY NEWID()) AS [Style] ,CONVERT(NVARCHAR(50), NEWID()) AS [ModelName] ,NULL AS [LargePhoto] ,CONVERT(NVARCHAR(50), NEWID()) AS [EnglishDescription] ,CONVERT(NVARCHAR(50), NEWID()) AS [FrenchDescription] ,CONVERT(NVARCHAR(50), NEWID()) AS [ChineseDescription] ,CONVERT(NVARCHAR(50), NEWID()) AS [ArabicDescription] ,CONVERT(NVARCHAR(50), NEWID()) AS [HebrewDescription] ,CONVERT(NVARCHAR(50), NEWID()) AS [ThaiDescription] ,CONVERT(NVARCHAR(50), NEWID()) AS [GermanDescription] ,CONVERT(NVARCHAR(50), NEWID()) AS [JapaneseDescription] ,CONVERT(NVARCHAR(50), NEWID()) AS [TurkishDescription] ,GETDATE() AS [StartDate] ,NULL AS [EndDate] ,'Current' AS [Status] FROM [dbo].[DimProduct] T WITH(NOLOCK) CROSS JOIN cntCTE C OPTION(MAXRECURSION 0)
Après actualisation du VertipaqAnalyser, on constate que la taille de modèle est plus significative.
Rappelons-nous les explications ci-dessus, « IsAvailableInMdx permet de contrôler la construction des hiérarchies d’attribut pour une colonne », le gain que j’espère donc est donc de récupérer de l’espace au sein des « Columns Hierarchies Size ».
Testons le changement de la propriété sur la colonne ProductId de la table Product, pour changer cette propriété, il n’est pour l’heure pas possible d’utiliser l’interface graphique de SSDT, il faudra donc, soit passer par du TMSL, soit par Tabular Editor.
En TMSL :
{
"createOrReplace": {
"object": {
"database": "AdventureWorksIsAvailable",
"table": "Product"
},
"table": {
"name": "Product",
"columns": [
{
"name": "Product Id",
"dataType": "int64",
"sourceColumn": "ProductKey",
"isAvailableInMdx": false,
"sourceProviderType": "Integer"
}, ...
}
Avec Tabular Editor :
Cela fait, un process complet du cube s’impose.
On actualise le fichier VertiPaq Analyzer ce qui nous donne les métriques suivantes :
Cube avec IsAvailableInMdx = True :
Cube avec IsAvailableInMdx = False :
On constate que la colonne passe de 60 Mo à 4,8 Mo et qu’en effet les hiérarchies ne sont plus calculées. Toutefois, un effet de bord inattendu a eu lieu, le moteur a choisi un encodage « Value » à la place de « Hash » ce qui nous a permis d’économiser sur le Dictionnaire par la même occasion.
Mais attention, ces gains ne sont pas sans contrainte, au vue de ces résultats il est tentant de vouloir généraliser le passage de cette propriété sur toutes les colonnes hors :
Avec IsAvailableInMdx = False, il n’est plus possible d’accéder à cette propriété en MDX (Sans blague !)
La requête MDX suivante :
SELECT [Measures].[Internet Total Sales] ON COLUMNS ,[Product].[Product Id].Members ON ROWS FROM [Adventure Works Internet Sales Model]
Retourne :
Exécution de la requête ... Query (2, 2) La fonction MEMBERS attend une expression de hiérarchie pour l'argument . Une expression de membre a été utilisée. Détails techniques : RootActivityId : 3864c5fd-4f82-4bf4-a373-0da63476f306 Date (UTC) : 8/20/2018 12:38:01 PM 0: PFError::SetLastError()+0x25f 1: PFSetLastError()+0x471 2: MDContext::GenerateMDXError()+0x2f 3: MDValue::GetHier()+0x126 4: MDXMembers::Calculate()+0xe5 5: MDFormula::CalculateFormula()+0xe8 6: MDFormula::CalculateSet()+0x3a 7: MDAxis::Calculate()+0xb5 8: MDFormula::CalculateFormula()+0xe8 9: MDAxis::CalculateAxis()+0x43 10: MDSelect::Execute()+0x57b 11: PCDMLSelect::Execute()+0x2e3 12: PCASTDMLRootNode::DispatchExecute()+0x52d4 13: PCDMLStatement::Dispatch()+0x853 14: PCXAExecute::Dispatch()+0x1492 15: PXSession::InternalExecuteCommand()+0x94b 16: PCSession::ExecuteCommand()+0x9d 17: PCClientRequestHolder::ExecuteRequest()+0xd4 18: PCRequestHolder::ExecuteTask()+0x16a 19: PFThreadPool::ExecuteJob()+0x4db 20: PFThreadPool::WorkerMethod()+0x2fe 21: PFCommandThreadPool::ThreadProc()+0x21 22: PFThreadPool::ThreadProcWrapperForPFThreadPool()+0x25 23: beginthreadex()+0x107 24: endthreadex()+0x192 25: BaseThreadInitThunk()+0x22 26: RtlUserThreadStart()+0x34 Exécution terminée
Ce qui veut dire qu’il n’est plus possible de requêter cette colonne avec un TCD Excel (ni au travers de Analyse in Excel depuis PowerBI.com)
La fonction TreatAs en DAX ne fonctionne plus, hors, vous utilisez peut-être celle-ci afin de filtrer vos requêtes :
DEFINE
VAR toto = TREATAS({1},'Product'[Product Id])
EVALUATE
SUMMARIZECOLUMNS('Product'[Color]
,toto
,"titi",[Internet Total Sales]
)
Retourne maintenant l’erreur suivante :
20/08/2018 14:59:17 Impossible d'exécuter une requête interne prenant en charge les structures de la colonne 'Product'[Product Id] car elles n'ont pas été traitées. Actualisez ou recalculez la table 'Product'. Détails techniques : RootActivityId : 7464b6bf-ba3f-4beb-99bb-2fff9af76dce Date (UTC) : 8/20/2018 12:59:17 PM
Malheureusement comme indiqué par Chris Webb, la documentation des fonctions DAX n’indiquent pas si elles ont besoin ou non des hiérarchies.
Les hiérarchies utilisateur ne peuvent être construites sur une colonne avec la propriété à False.
Dans notre exemple, modifier la propriété IsAvailableInMdx sur « Product Category Name » qui fait partie d’une hiérarchie utilisateur « Category » retourne l’erreur suivante au processing.
Le traitement de la hiérarchie « Category » de la table « Product » a démarré. Le traitement de la hiérarchie « Description » de la table « Product » est terminé. La requête DDL JSON a échoué avec l'erreur suivante : Failed to execute XMLA. Error returned: 'An unexpected error occurred (file 'xmuhdatacache.cpp', line 99, function 'XMUserHierarchyLevelAggDataCache::Init'). '.. Détails techniques : RootActivityId : 986d4477-1d1d-4292-bea3-4daf01e0b8d2 Date (UTC) : 8/20/2018 1:10:32 PM 0: PFError::SetLastError() line 2143 + 0x0 (d:\dbs\sh\odqfc\0731_120159\cmd\16\sql\picasso\engine\src\pf\eh\pferror.cpp) 1: PFSetLastError() line 2866 + 0x0 (d:\dbs\sh\odqfc\0731_120159\cmd\16\sql\picasso\engine\src\pf\eh\pferror.cpp) 2: PFSetLastErrorExTag() line 3400 + 0x27 (d:\dbs\sh\odqfc\0731_120159\cmd\16\sql\picasso\engine\src\pf\eh\pferror.cpp) 3: 0x00007FFD4043C091 (symbolic name unavailable) Exécution terminée
En conclusion, il semble cohérent de limiter l’utilisation de IsAvailableInMdx = False dans les cas suivants :
- Sur les clés étrangères des tables de faits (‘Internet Sales'[Product Id], ‘Internet Sales'[Customer Id] …)
- Sur les colonnes uniquement utilisées par des mesures ([Sales Amount],[Margin] …)
- Et pour les autres cas, il faut bien prendre en compte les contraintes liées à la désactivation des hiérarchies.
Le gain devrait déjà être important avec l’optimisation des deux cas ci-dessus.
