Compartilhar via


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.

Observação importanteImportante

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çãoObservaçã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çãoObservaçã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çãoObservaçã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çãoObservaçã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