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.
Importante |
---|
È 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 |
|