Reducera minnesförbrukningen i SSAS Tabular med IsAvailableInMDX

Ämne: Blogg

Vad är IsAvailableInMDX?

IsAvailableInMDX är en kolumnegenskap som är tillgänglig i Azure Analysis Services samt SQL Server Analysis Services 2017. Denna egenskap bestämmer huruvida attributhierarkier ska byggas på en kolumn, vilket kan vara väldigt kostsamt när det kommer till prestanda och minne beroende på antal rader i en tabell.

Om IsAvailableInMDX är satt till false på en kolumn innebär det att det inte längre går att använda denna i ”group by”-frågor i applikationer som genererar MDX-frågor såsom Excel. Kolumner i faktatabeller behöver sällan grupperas på och är ofta de kolumner som tar upp mest minne. Detta gör att vi kan spara mycket prestande och minne genom att använda IsAvailableInMDX till vår fördel.

Varför manipulera IsAvailableInMDX?

Om vi tänker att vi har en faktatabell ”FactSales” med följande kolumner där Sales är en kolumn med försäljningsvärdet och där SalesAmount är ett DAX-mått i SSAS bestående av summan av Sales SalesAmount := SUM(Sales):

 

 

SSAS Tabular beräknar automatiskt attributhierarkier på varje kolumn i varje tabell vilket i många fall inte är nödvändigt. I vårt fall kan vi tänka oss att vi vill bygga en Pivot-tabell som ser ut såhär:

 

Där vi grupperar SalesAmount på Customer och Product.

Förutom detta har SSAS även beräknat attributhierarki på kolumnen Sales då egenskapen IsAvailableInMDX är TRUE som default, vilket möjliggör denna Pivot-tabell:

 

Denna tabell blir i de allra flesta fall helt orimlig och onödig att bygga då vill summera Sales och inte använda det som kolumn eller rad. IsAvailableInMDX blir nu en väldigt användbar egenskap att ändra eftersom den låter oss sluta beräkna attributhierarkier på kolumner som inte behöver det. SSAS låter oss dölja kolumner i klientverktyg såsom Power BI, t.ex. om vi har kolumner som endast används i mått. På dessa kolumner är det absolut användbart att sätta IsAvailableInMDX.

Viktigt att påpeka är att IsAvailableInMDX endast påverkar applikation som genererar MDX och inte applikationer som använder sig av DAX, exempelvis Power BI. Detta betyder att vi fortfarande kan bygga matriser i Power BI med kolumner som vi sätter IsAvailableInMDX = false, om vi nu vill göra det.

Hur?

För tillfället finns inte egenskapen att sätta i SQL Server Data Tools, utan den måste sättas manuellt med hjälp av det JSON-baserade skriptspråket Tabular Model Scripting Language (TMSL) eller Tabular Object Model (TOM), klientbiblioteket som gör att man kan manipulera SSAS-modeller programmatiskt i .NET.

TMSL:

C#:

 

Ett tredje sätt att sätta egenskapen är med verktyget Tabular Editor. I detta verktyg kan vi koppla upp direkt mot ett SSAS instans på servern eller direkt till en bim-fil. Det enda som behövs göras är att klicka på aktuell kolumn och i properties-rutan till höger sätta IsAvailableInMDX till true/false.

 

Användarfall

I ett projekt där vi på BizOne har en SSAS Tabular modell med en faktatabell som innehåller ca 50 miljoner rader testade vi att få ner Process ReCalc-tiden då den var relativt långsam och kördes varje timme. Faktatabellen innehåller en kolumn som heter Receipt_No som tog upp mycket prestanda och minne då den bestod av hela nitton tecken. Kolumnen används i ett flertal mått i SSAS såsom Avg Receipt Value osv, men då kolumnen innehåller så pass granulär information kan vi anta att användarna inte kommer att bygga Pivot-tabeller i Excel med den som rad eller kolumn.

Efter att vi satte IsAvailableInMDX till false fick vi ner ReCalc-processen per timme till ca 30 sekunder från ungefär 8–9 minuter!