Miglioramenti nelle prestazioni per MDX in SQL Server 2008 Analysis Services

Per questa versione di Analysis Services si è posta particolare enfasi sul miglioramento delle prestazioni nell'esecuzione dei calcoli MDX. Molte modifiche importanti sono state apportate all'architettura del motore per realizzare questo miglioramento delle prestazioni. Tuttavia, per sfruttare questi miglioramenti delle prestazioni, è necessario ottimizzare il codice di MDX.

Questo documento consente di capire dove possono verificarsi problemi nel codice MDX esistente che impediscono di usufruire dei miglioramenti delle prestazioni e fornisce consigli su come evitare questi problemi nella nuova codifica MDX. Questo documento include anche un elenco delle funzioni che traggono vantaggio dai miglioramenti delle prestazioni.

Revisione del codice per il massimo miglioramento delle prestazioni in MDX

Nel rivedere il codice, evitare i seguenti scenari o situazioni di codifica poiché potrebbero impedire alle istruzioni MDX di realizzare i miglioramenti prestazionali previsti in SQL Server 2008 Analysis Services (SSAS). Se tuttavia all'atto pratico non è possibile modificare il codice senza incorrere nelle situazioni elencate, il livello prestazionale del codice MDX sarà quello previsto in SQL Server 2005 Analysis Services (SSAS).

Definizioni utili

Spazio

Il set di celle su cui un'espressione viene valutata.

Forma arbitraria

Un spazio che non può essere espresso come cross join di due o più set. Ad esempio, lo spazio {(Drink, USA), (Food, Canada)} rappresenta una forma arbitraria perché è un subset del cross join tra {Drink, Food} * {USA, Canada} = {(Drink, USA), (Drink, Canada), (Food, USA), (Food, Canada)}.

Espressione statica

Un'espressione viene definita statica quando è invariante nello spazio su cui è calcolata.

Ad esempio, nello spazio di CrossJoin(Product.Members, Customer.Members) le espressioni qui di seguito sono invariabili.

  • 1, un'espressione costante

  • Product.Members.Count

Espressione dinamica

Un'espressione è definita dinamica quando viene risolta in un valore diverso per ogni cella nello spazio su cui è calcolata.

Ad esempio, nello spazio di CrossJoin(Product.Members, Customer.Members) le espressioni qui di seguito sono dinamiche.

  • Sales, poiché Vendite è una misura, il suo valore è diverso per ogni cella dello spazio.

Attributo variabile

Un attributo variabile determina in che modo l'espressione viene valutata e fa dipendere da sé l'espressione. Ad esempio, l'espressione Customer.Geography.CurrentMember dipende dagli attributi della gerarchia dei dati geografici.

Generalmente, variando gli attributi si riduce lo spazio su cui vengono valutate le espressioni. Si consideri la seguente espressione:

with member measures.x as Customers.Geography.currentmember.uniquename

Select Customers.Geography.City.members on 0,

Product.members on 1

From sales

Where measures.x

In questa espressione, Customers.Geography è un'espressione statica. La funzione currentmember è un attributo variabile perché introduce una dipendenza dall'attributo City. Uniquename non aggiunge attributi variabili perché è associato a currentmember in una relazione 1:1. Pertanto, uniquename sarà valutato una volta sola per cliente e non ripetuto per ogni Product. Quindi l'intero spazio dell'espressione è stato di fatto ridotto sull'arco dell'attributo variabile.

Utilizzo di espressioni nelle proprietà di non-valore di una cella

Qualsiasi espressione MDX utilizzata per assegnare il valore di una proprietà di non-valore di una cella non trarrà vantaggi dai miglioramenti delle prestazioni. Le prestazioni rimarranno allo stesso livello della SQL Server 2005 Analysis Services (SSAS).

Utilizzo di funzioni non elencate

L'utilizzo di qualsiasi funzione non elencata in questo documento nel codice di MDX non trarrà vantaggi dai miglioramenti delle prestazioni previsti per questa versione del prodotto. Vedere Functions with enhanced performance in questo documento.

Utilizzo della protezione della cella

La valutazione di un'espressione MDX su un spazio per cui è stata definita la protezione della cella impedisce al codice di avere prestazioni migliorate.

La relazione tra la protezione della cella e la prestazione viene presentata nella tabella qui di seguito.

Protezione della cella

Prestazioni previste

Nessuna

Massima

Lettura

Intermedia

Lettura condizionale

Minima

Vedere Utilizzo di espressioni MDX per l'impostazione di autorizzazioni sui dati delle celle e Concessione di accesso personalizzato ai dati delle celle.

Utilizzo della dimensionalità dinamica

L'utilizzo di espressioni di dimensionalità dinamica nel codice MDX impedisce al codice di avere prestazioni migliorate. Ad esempio, espressioni come Sum( IIF( Sales > 10000, h1.Members, h2.Members)) non trarranno vantaggi perché il codice modifica i membri da sommare mentre l'espressione Sales viene valutata. Un altro esempio sarebbe uno scenario dove è necessario utilizzare un membro dell'anno solare o un membro delle gerarchie dell'anno fiscale che dipende da un attributo appartenente al membro corrente dell'attributo Account per eseguire un confronto con il valore equivalente di un periodo parallelo. L'espressione MDX richiesta per questo scenario assomiglierebbe al codice di esempio qui di seguito.

ParallelPeriod(Iif( Account.CurrentMember.Properties("UsesFiscalCalendar")="Y", FiscalTime, CalendarTime).CurrentMember)

Di nuovo, le dimensioni si modificano dinamicamente al modificarsi del membro corrente della dimensione Account.

Utilizzo dei parametri dinamici

L'utilizzo di parametri dinamici nel codice MDX impedisce al codice di avere prestazioni migliorate. Ad esempio, un'espressione come KpiGoal("Sales_" & [Fiscal Year].currentmember.UniqueName) varia nelle celle sulle quali è calcolata; per contro, l'espressione KpiGoal("Sales_" & Cstr(Year(Now))) è invariante.

Nota importanteImportante

È possibile che l'espressione KpiGoal("Sales_" & [Fiscal Year].currentmember.UniqueName) valuti lo stesso valore per lo spazio in cui viene calcolata. Ciò non è tuttavia sufficiente per consentire al motore di fornire i miglioramenti prestazionali previsti.

Riferimenti a membri dinamici

L'utilizzo di qualsiasi riferimento a membri dinamici nel codice MDX impedisce al codice di avere prestazioni migliorate. Ad esempio, nella seguente espressione

(IIF( e, mbr1, mbr2), Sales)

non c'è modo di conoscere la tupla risultante fino a quando l'espressione IIF() non viene valutata in fase di esecuzione. Tuttavia, nell'espressione equivalente seguente

IIF( e, (mbr1, Sales), (mbr1, Sales))

entrambe le tuple risultanti sono note prima di valutare l'espressione e.

Hint di piano

Gli hint di piano sono un'estensione del linguaggio MDX per indicare al motore come valutare le espressioni. In questa versione di Analysis Services gli hint di piano vengono introdotti solo nella funzione IIF(,,).

Gli hint di piano possono essere indicati nell'espressione MDX o configurati a livello globale nelle proprietà di configurazione del server.

Hint di piano in funzioni IIF()

In IIF(,,) gli hint delle espressioni vengono indicati utilizzando la sintassi seguente:

IIF(<cond>, <expr>, <expr>) [HINT <hints>]

<expr> ::= <expr> [HINT <hints>]

<hints> ::= <hint> [<hints>]

<hint> ::= EAGER | STRICT | LAZY

  • EAGER fa sì che l'espressione venga valutata sull'intero sottospazio IIF.

  • STRICT fa sì che l'espressione venga valutata solo nel sottospazio risultante in base ai risultati dell'espressione della condizione.

  • LAZY fa sì che l'espressione venga valutata cella per cella.

  • EAGER e STRICT si escludono a vicenda nell'hint e possono essere utilizzati nella stessa funzione IIF(,,) in diverse espressioni.

Di seguito viene fornito un esempio della sintassi:

IIF([Measures].[Internet Sales Amount]=0

, {([Date].[Calendar Year].CURRENTMEMBER, [Customer].[Country].[All Customers])} HINT EAGER

, {{[Date].[Calendar Year].CURRENTMEMBER} * [Customer].[Country].[Country].MEMBERS} STRICT LAZY

)

Hint di piano nelle proprietà di configurazione

Le proprietà di configurazione seguenti vengono introdotte per supportare gli hint di piano, nel percorso OLAP\Query:

Nome proprietà

Valori possibili

Spiegazione

IIFThenMode

0 | 1 | 2

0 = nessun hint (valore predefinito).

1 = EAGER

2 = STRICT

IIFElseMode

0 | 1 | 2

0 = nessun hint (valore predefinito).

1 = EAGER

2 = STRICT

LazyEnabled

0 | 1

0 = disabilitato (valore predefinito)

1 = abilitato

Stored procedure definite dall'utente (COM o .NET)

L'utilizzo di stored procedure definite dall'utente nel codice MDX impedisce prestazioni migliorate al codice.

[!NOTA]

SQL Server 2008 Analysis Services (SSAS) fornisce stored procedure ottimizzate per i miglioramenti delle prestazioni.

Utilizzo di set denominati o di alias impostati nei parametri

Ogni volta che un set denominato o un alias impostato viene utilizzato come primo parametro nelle funzioni Sum, Min, Max, Avg o Aggregate del codice MDX, il codice non trarrà vantaggio dai miglioramenti delle prestazioni.

Ad esempio, l'espressione MDX qui di seguito conta quanti membri hanno più di uno figlio.

Sum(h.members as S, Iif(S.Current.Children.Count > 1, 1, 0))

Il fatto che a h.members venga assegnato l'alias S e in seguito il valore della funzione Current venga preso dal set a cui è stato assegnato l'alias, impedisce il previsto miglioramento delle prestazioni.

Un altro esempio comune di questa situazione è illustrato nel codice qui di seguito.

WITH

SET [Core Products] AS '{[Product].[Category].[Bikes]}'

MEMBER [Measures].[Core Products Sales] AS SUM([Core Products], [Measures].[Internet Average Unit Price] * [Measures].[Internet Order Quantity])

Select [Measures].[Core Products Sales] on 0

From [Adventure Works]

La funzione SUM della definizione del membro non ottiene il previsto miglioramento delle prestazioni perché si basa su un set denominato.

Utilizzo di associazioni tardive in espressioni di rollup personalizzate

Ogni volta che un'espressione di rollup personalizzata fa riferimento a un membro calcolato o a qualsiasi altra espressione MDX valutata in fase di esecuzione, l'espressione di rollup personalizzata impedisce la prestazione migliorata.

Utilizzo di riferimenti in avanti negli script

Ogni volta che nel codice MDX si creano riferimenti di definizione in avanti, in istruzioni separate, il codice non trarrà vantaggi dai miglioramenti delle prestazioni. Ad esempio, nel frammento di script MDX qui di seguito, un riferimento in avanti viene creato su Y durante la definizione di X.

Create Member X as Y * 2;

Create Member Y as ( Sales, [Date].[Calendar].[Month].PreviousMember);

Per correggere questa situazione, inserire la definizione di Y prima della definizione di X, come mostrato nel frammento seguente.

Create Member Y as ( Sales, [Date].[Calendar].[Month].PreviousMember);

Create Member X as Y * 2;

Funzioni con prestazioni migliorate

Funzioni scalari

L'elenco seguente di funzioni scalari include le funzioni nelle quali sono prevedibili prestazioni migliorate. La prima colonna dell'elenco contiene gli operatori scalari.

-

OR

KEY

*

XOR

LEVELS.COUNT

/

CALCULATIONPASSVALUE

MEMBERTOSTR

+

CASE

MEMBERVALUE

<

COALESCEEMPTY

NAME

<=

HIERARCHIES.COUNT

ORDINAL

<>

ID

PROPERTIES

=

IIF

UNIQUENAME

>

IS

USERNAME

>=

ISANCESTOR

VALIDMEASURE

unary minus

ISEMPTY

VALUE

NOT

ISLEAF

 

AND

ISSIBLING

 

[!NOTA]

Qualsiasi stored procedure definita dall'utente, sia in COM che in codice gestito, non presenterà alcun miglioramento delle prestazioni se confrontata a SQL Server 2005 Analysis Services (SSAS). Per ulteriori informazioni, vedere Stored procedure definite dall'utente (COM o .NET) nelle sezioni precedenti di questo documento. Espressioni costanti, sia letterali che numeriche, trarranno vantaggi dai miglioramenti delle prestazioni.

Funzioni di membro

L'elenco seguente di funzioni di membro include le funzioni nelle quali sono prevedibili prestazioni migliorate.

.CurrentMember

.FirstSibling

.LastSibling

.DataMember

.Item

.Lead

.DefaultMember

.Lag

.Parent

.FirstChild

.LastChild

.UnknownMember

Ancestor

KPIStatus

NextMember

Ancestors

KPITrend

OpeningPeriod

Ascendants

KPIValue

ParallelPeriod

ClosingPeriod

KPIWeight

PrevMember

Cousin

LastPeriods

StrToMember(<String Expression>, CONSTRAINED)

KPIGoal

LinkMember

 

[!NOTA]

StrToMember(<String Expression>, CONSTRAINED) ottiene la migliore prestazione possibile quando <String Expression> è un'espressione statica.

Funzioni set

L'elenco seguente di funzioni set include le funzioni nelle quali sono prevedibili prestazioni migliorate.

Aggregate

Max

Sum

Avg

Min

 

Tuttavia, quando si utilizza una qualunque delle funzioni elencate, il primo parametro deve essere un'espressione che utilizza una qualsiasi combinazione delle seguenti funzioni.

- (operatore Except)

.Children

MTD

(<set expression>,(<set expression>, …,(<set expression>) (operatore Cross join)

.Members

PeriodsToDate

* (operatore Cross join)

.Siblings

QTD

: (operatore Range)

AddCalculatedMembers

StrToSet(<String Expression>, CONSTRAINED)

+ (operatore Union)

Crossjoin(<set expression>,(<set expression>, …,(<set expression>)

Tail

 

Descendants

Union

 

Distinct

Unorder

 

Except

WTD

 

Hierarchize

YTD

 

Intersect

 

[!NOTA]

I set statici, ivi compreso il set vuoto trarranno anch'essi vantaggi dai previsti miglioramenti delle prestazioni.

Funzioni VBA

L'elenco seguente di funzioni VBA include le funzioni nelle quali è lecito prevedere prestazioni migliorate.

Abs

CLng

Len

CDate

CStr

Now

CDbl

Int

Destro

CInt

Left

Round

Per le seguenti funzioni VBA, è lecito prevedere prestazioni migliorate se la funzione viene valutata su attributi variabili.

Asc

Format

Sgn

AscW

FV

Sin

Atn

Hex

SLN

Cbool

Hour

Space

Cbyte

Ipmt

Sqr

Ccur

Lcase

Str

Cdec

Log

StrComp

Chr

Ltrim

StrConv

ChrW

Minute

String

Cos

Month

SYD

CSng

Nper

Tan

Cvar

Oct

Timer

Date

Partition

TimeSerial

DateAdd

Pmt

TimeValue

DateDiff

PPmt

Trim

DatePart

PV

TypeName

DateSerial

QBColor

Ucase

DateValue

Rate

Val

Day

RBG

Weekday

DDB

Rnd

Year

Exp

Rtrim

 

Fix

Second