Aprimoramentos de desempenho para MDX no SQL Server 2008 Analysis Services

Para esta versão do Analysis Services, foi dada uma ênfase especial para melhorar o desempenho na execução dos cálculos de linguagem MDX. Várias alterações importantes fizeram com que a arquitetura de mecanismo alcançasse estes ganhos de desempenho. Porém, para tirar proveito dessas melhorias de desempenho, é necessário otimizar seu código MDX.

Este documento ajudará você a entender onde podem ocorrer problemas em seu código MDX existente, os quais impedirão que você experimente as melhorias de desempenho e fornecerá subsídio sobre como evitar estes problemas em sua nova codificação MDX. Este documento também inclui uma lista das funções que se beneficiam das melhorias de desempenho.

Revisando seu código para obter os máximos ganhos de desempenho em MDX

Ao revisar seu código, tente evitar o seguinte conjunto de cenários ou situações de codificação, pois eles poderão impedir suas instruções MDX de atingirem os ganhos de desempenho esperados no SQL Server 2008 Analysis Services (SSAS). Contudo, se não houver nenhuma maneira prática de modificar seu código a fim de evitar as situações referidas, pode-se esperar que o código MDX tenha o mesmo nível de desempenho que tem no SQL Server 2005 Analysis Services (SSAS).

Definições úteis

Espaço

O conjunto de células pelas quais uma expressão é avaliada.

Forma arbitrária

Um espaço que não pode ser expresso como a associação interligada de dois ou mais conjuntos. Por exemplo, o espaço {(Drink, USA), (Food, Canada)} representa uma forma arbitrária, pois é um subconjunto da associação cruzada entre {Drink, Food} * {USA, Canada} = {(Drink, USA), (Drink, Canada), (Food, USA), (Food, Canada)}.

Expressão estática

Diz-se ser uma expressão estática quando é invariável quanto ao espaço no qual é calculada.

Por exemplo, no espaço de CrossJoin(Product.Members, Customer.Members) as seguintes expressões são invariáveis.

• 1, uma expressão constante

• Product.Members.Count

Expressão dinâmica

Diz-se ser uma expressão dinâmica quando resulta em um valor diferente para cada célula ao longo do espaço no qual é calculada.

Por exemplo, no espaço de CrossJoin(Product.Members, Customer.Members) as seguintes expressões são dinâmicas.

• Sales, porque Vendas é uma medida; seu valor é diferente para cada célula no espaço.

Atributo variável

Um atributo variável orienta o modo pelo qual a expressão é avaliada e torna a expressão dependente disso. Por exemplo, a expressão Customer.Geography.CurrentMember depende dos atributos na hierarquia geográfica.

Normalmente, atributos variáveis reduzem o espaço pelo qual expressões são avaliadas. Considere a expressão a seguir.

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

Nesta expressão, Customers.Geography é uma expressão estática. A função currentmember é um atributo variável, pois apresenta uma dependência do atributo Cidade. Uniquename não adiciona nenhum atributo variável, pois está vinculado ao currentmember em uma relação de 1:1. Conseqüentemente, uniquename será avaliado apenas uma vez para cada cliente e não se repetirá para cada Product. Portanto, todo o espaço de expressão foi efetivamente reduzido sobre o atributo variável.

Uso de expressões em propriedades sem valor de uma célula

Qualquer expressão MDX utilizada para atribuir o valor de uma propriedade sem valor de uma célula não se beneficiará das melhorias de desempenho. O desempenho permanecerá no mesmo nível que o SQL Server 2005 Analysis Services (SSAS).

Uso de funções não listadas

O uso de qualquer função não listada neste documento em seu código MDX não se beneficiará do ganho de desempenho desta versão do produto. Consulte Functions with enhanced performance neste documento.

Uso da segurança de célula

Avaliar uma expressão MDX em um espaço com segurança de célula definida impedirá que o desempenho do seu código seja melhorado.

A relação entre segurança de célula e desempenho é apresentada na seguinte tabela.

Segurança de célula	Desempenho esperado
Nenhum	Melhor
Ler	Intermediário
Contingente de leitura	Mais Baixo

Consulte Usando linguagens MDX para definir permissões de dados de célula e Concedendo acesso personalizado aos dados da célula.

Uso da dimensionalidade dinâmica

O uso de expressões de dimensionalidade dinâmica em seu código MDX impedirá seu código de ganhar desempenho aprimorado. Por exemplo, expressões como Sum( IIF( Sales > 10000, h1.Members, h2.Members)) não se beneficiarão, pois seu código vai modificar os membros a serem computados à medida que a expressão Sales for avaliada. Outro exemplo seria um cenário onde se necessita utilizar um membro do Ano-Calendário ou um membro das hierarquias do Ano Fiscal que dependam de um atributo pertencente ao membro atual do atributo Conta, a fim de fazer um comparativo com o valor equivalente de um período paralelo. A expressão MDX requerida para este cenário seria algo semelhante ao seguinte código de amostra.

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

Novamente, as dimensões modificam-se dinamicamente, à medida que o membro atual da dimensão Conta é alterado .

Uso de parâmetros dinâmicos

O uso de parâmetros dinâmicos em seu código MDX impedirá seu código de ganhar desempenho aprimorado. Por exemplo, uma expressão como KpiGoal("Sales_" & [Fiscal Year].currentmember.UniqueName) varia ao longo das células sobre as quais está sendo calculada. Ao contrário, a expressão KpiGoal("Sales_" & Cstr(Year(Now))) é invariável.

Importante
Poderá ocorrer o caso em que a expressão KpiGoal("Sales_" & [Fiscal Year].currentmember.UniqueName) seja avaliada como o mesmo valor no espaço em que for calculada. Porém, isso não seria suficiente para o mecanismo proporcionar os ganhos de desempenho esperados.

Referências a membros dinâmicos

O uso de qualquer referência a membro dinâmico em seu código MDX impedirá seu código de ganhar desempenho aprimorado. Por exemplo, na seguinte expressão

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

Não há nenhuma forma de conhecer a tupla resultante até que a expressão IIF() seja avaliada na hora da execução. Porém, na seguinte expressão equivalente

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

uma das tuplas resultantes é conhecida antes de se avaliar a expressão e.

Dicas de plano

Dicas de plano são uma extensão à linguagem MDX para indicar ao mecanismo como avaliar expressões. Nesta versão do Analysis Services, dicas de plano são introduzidas apenas sobre a função IIF(,,).

Dicas de plano podem ser indicadas na MDX expressão ou ser configuradas globalmente nas propriedades de configuração do servidor.

Dicas de plano em funções IIF()

Em expressões IIF(,,), as dicas de expressão são indicadas usando a sintaxe a seguir:

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

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

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

<hint> ::= EAGER | STRICT | LAZY

• EAGER faz com que a expressão seja avaliada sobre o subespaço de IIF inteiro.

• STRICT faz com que a expressão seja avaliada apenas no subespaço resultante de acordo com os resultados da expressão de condição.

• LAZY faz com que a expressão seja avaliada em modo célula por célula.

• EAGER e STRICT são mutuamente exclusivos na dica. Eles podem ser usados no mesmo IIF(,,) sobre expressões diferentes.

Um exemplo da sintaxe:

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

)

Dicas de plano em propriedades de configuração

As propriedades de configuração a seguir são introduzidas para suportar Dicas de Plano, no caminho OLAP\Query:

Nome da propriedade	Valores aceitáveis	Explicação
IIFThenMode	0 | 1 | 2	0, Sem dica (padrão) 1, EAGER 2, STRICT
IIFElseMode	0 | 1 | 2	0, Sem dica (padrão) 1, EAGER 2, STRICT
LazyEnabled	0 | 1	0, Desabilitado (padrão) 1, Habilitado

Procedimentos armazenados definidos pelo usuário (COM ou .NET)

O uso de procedimentos armazenados definidos pelo usuário no código MDX impedirá seu código de ganhar desempenho aprimorado.

Observação
SQL Server 2008 Analysis Services (SSAS) fornece procedimentos armazenados que são otimizados para as melhorias de desempenho.

Uso de conjuntos nomeados ou de aliases definidos em parâmetros

A qualquer momento em que um conjunto nomeado ou um alias definido for utilizado como primeiro parâmetro nas funções Sum, Min, Max, Avg ou Aggregate em seu código MDX, seu código não se beneficiará das melhorias de desempenho.

Por exemplo, a seguinte expressão MDX conta quantos membros têm mais de um filho.

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

Porque o h.members está sendo o alias como S e depois o valor da função Atual é retirado do alias do conjunto, isso impedirá que se obtenha o aprimoramento no desempenho esperado.

Outro exemplo comum dessa situação é ilustrado no seguinte código.

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]

A função SUM na definição de membros não obtém o aprimoramento de desempenho esperado, pois tem como base o conjunto nomeado.

Uso de associações atrasadas em expressões de rollup personalizado

Sempre que uma expressão de rollup personalizado referenciar um membro calculado ou a qualquer outra expressão MDX que seja avaliada no tempo de execução, a expressão de rollup personalizado impedirá o desempenho aprimorado.

Uso de referências antecipadas em scripts

Sempre que você criar referências a definições antecipadas, em instruções separadas, em seu código MDX, seu código não se beneficiará das melhorias de desempenho. Por exemplo, no seguinte trecho de script MDX, uma referência antecipada é criada em Y durante a definição X.

Create Member X as Y * 2;

…

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

Para corrigir esta situação, coloque a definição Y antes da definição X, como no seguinte trecho.

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

Create Member X as Y * 2;

…

Funções com desempenho aprimorado

Funções escalares

A lista seguinte, de funções escalares, inclui as funções nas quais se deve esperar ver desempenho aprimorado. A primeira coluna na lista contém os operadores escalares.

-	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

Observação

Qualquer procedimento armazenado definido pelo usuário, em COM ou em código gerenciado, não resultará em nenhuma melhoria no desempenho quando comparado à SQL Server 2005 Analysis Services (SSAS). Para obter mais informações, consulte Procedimentos armazenados definidos pelo usuário (COM ou .NET) no início deste documento. Expressões constantes, literais ou numéricas, se beneficiarão das melhorias de desempenho.

Funções de membro

A seguinte lista de funções de membro inclui as funções nas quais se deve esperar ver desempenho aprimorado.

.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

Observação
StrToMember(<String Expression>, CONSTRAINED) obtém o melhor desempenho possível quando <String Expression> for uma expressão estática.

Funções definidas

A seguinte lista de funções definidas inclui as funções nas quais se deve esperar ver desempenho aprimorado.

Aggregate	Max	Sum
Avg	Min

Contudo, quando você utilizar quaisquer das funções listadas, o primeiro parâmetro deverá ser uma expressão que utilize qualquer combinação das seguintes funções.

- (operador Except)	.Children	MTD
(<set expression>,(<set expression>, …,(<set expression>) (operador de junção cruzada)	.Members	PeriodsToDate
* (operador de junção cruzada)	.Siblings	QTD
: (operador de intervalo)	AddCalculatedMembers	StrToSet(<String Expression>, CONSTRAINED)
+operador UNION	Crossjoin(<set expression>,(<set expression>, …,(<set expression>)	Tail
	Descendants	Union
	Distinct	Unorder
	Except	WTD
	Hierarchize	YTD
	Intersect

Observação
Conjuntos estáticos, inclusive o conjunto vazio, também se beneficiarão dos ganhos de desempenho esperados.

Funções VBA

A seguinte lista de VBA inclui as funções nas quais se pode esperar ver desempenho aprimorado.

Abs	CLng	Len
CDate	CStr	Agora
CDbl	Int	Direita
CInt	Esquerda	Round

Para as seguintes funções VBA, pode-se esperar ver desempenho aprimorado se a função for avaliada por atributos variáveis.

Asc	Formato	Sgn
AscW	FV	Sin
Atn	Hexa	SLN
Cbool	Hora	Espaço
Cbyte	Ipmt	Sqr
Ccur	Lcase	Str
Cdec	Log	StrComp
Chr	Ltrim	StrConv
ChrW	Minuto	Cadeia de caracteres
Cos	Mês	SYD
CSng	Nper	Tan
Cvar	Out	Timer
Data	Partição	TimeSerial
DateAdd	Pmt	TimeValue
DateDiff	PPmt	Trim
DatePart	PV	TypeName
DateSerial	QBColor	Maiúsculas
DateValue	Taxa	Val
Dia	RBG	Dia da semana
DDB	Rnd	Ano
Exp	Rtrim
Corrigir	Segundo