none
Problemas de performance usando MDX pra acessar um cubo no SSAS. RRS feed

  • Pergunta

  • Boa tarde pessoal, tudo bem?

    Estava desenvolvendo uma solução de dashboard utilizando o Visual Studio 2008, utilizando um componente chamado Dundas Charts, que serve para criar componentes gráficos para servir como indicadores. Minhas ferramentas são todas microsoft, porém para este projeto, não iríamos utilizar Sharepoint 2010, com suas ferramentas de visualização.

    O meu planejamento consistia em: 1) Puxar meus dados para um DW (OLTP) e desenvolver um cubo OLAP em cima dele, para 2) fazer alguns tipos de calculos, como percentual de valor em relação ao nivel superior (tipo percentual do número de bolsas vendidas no municipio em relação ao todas de vendas de bolsas da região), mais os calculos temporais (YTD, últimos 12 meses e tal). Com os dados prontos, eu queria 3) alimentar via MDX uma tabela OLTP com os dados de sumarização já calculados, de modo que 4) a minha aplicação no Visual Studio comunicasse com o DW (OLTP).

    Dados como Ultimos 12 meses, year to date, e calculos com base em niveis de hierarquias sao mais fáceis de se fazer com membros calculados em OLAP do que fazendo dezenas de joins em OLTP, fora a demora.

    Porém o que percebi foi: Ao completar o cubo, a pivot table se comportava exatamente como eu queria, em um tempo de resposta muito bom (meu DW tem hoje um valor próximo a 800.000 linhas). Porém o problema é que quando eu abria o SQL Management Studio e fazia uma query MDX pra obter a tabela em um formato tabular (o qual ia usar no SSIS pra popular uma tabela OLTP com esses campos já calculados pelo cubo), esta consulta não só demorava como jogava um erro de falta de recursos (memória).

    Se eu usasse o SQL Server 2012, ele mostrava pra mim a consulta MDX pronta, gerada pelo proprio SQL Data Tools, mostrava os resultados em formato tabular ok, mas se eu replicasse esse MDX no SSMS, eu obtinha o erro, assim eu fiquei incapaz de usar o MDX no SSIS pra trazer do OLAP pro OLTP. No final, acabei contornando algo que usando o cubo seria mais facil por milhares de consultas OLTP usando tabelas intermediarias pra gerar os mesmos campos calculados...

    Eu estou errando em algo conceitualmente? Talvez a idéia de alimentar um OLTP por um OLAP seja algo nada comum, sei lá. Não entendendo muito de Partitions, Aggregations, ProActive Caching, talvez parte da minha dúvida esteja em algum destes tópicos... espero que possam me ajudar!

    quinta-feira, 14 de junho de 2012 20:22

Todas as Respostas

  • Evaristo boa tarde,

    Apenas para alinharmos os conceitos, OLTP são os dados transacionais originados do DW correto?

    E vc deseja transformá-lo em OLAP, onde futuramente vc irá consumir este dados originado de um cubo?

    A sua dificuldade é processar os dados do DW para o cubo ?

    Se sim, vc poderá utilizar um projeto analisys services para tal feito.

    Não entendi muito bem a parte que vc está utilizando MDX dentro do SSIS. Qual task vc está usando?

    Abs.


    Eduardo Gomes - http://www.h1solucoes.com.br - Twitter: @edugp_sp

    sexta-feira, 15 de junho de 2012 17:53
  • Olá Eduardo,

    O meu desejo é utilizar o Cubo OLAP no Analysis Services somente pra gerar alguns campos caculados, já que criá-los usando funções analíticas é mais fácil. Então eu iria usar um script MDX, tal qual

     SELECT NON EMPTY { [Measures].[Cabecas], [Measures].[Cabecas Abastecidas], [Measures].[Cabecas_Estado] } ON COLUMNS, NON EMPTY { ([Ano].[Ano].[Ano].ALLMEMBERS * [Dim Localidade].[Local].[Municipio].ALLMEMBERS * [Dim Rebanho].[Tipo de Rebanho].[Tipo de Rebanho].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [IBGE] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Esta MDX não fui nem eu que inventei na raça, mas o SQL Server 2012 que compõe de acordo com as dimensões que arrasto pra dentro..

    Mas quando executo ela no SQL Server Management Studio, obtenho este erro:

    Como tá dando esse problema, eu nem coloquei no SSIS, o meu próximo passo seria criar um Data Flow que usasse este MDX pra trazer os dados do OLAP pra uma tabela OLTP.

    E a partir daí, alimentar meus gauges em meu projeto .

    sexta-feira, 15 de junho de 2012 19:18
  • Evaristo, vc criou essa query mdx no wizard do reporting services ok ?

    Tente executar este código no  SQL Server Management Studio;

     SELECT NON EMPTY { 
    			[Measures].[Cabecas], [Measures].[Cabecas Abastecidas], 
    			[Measures].[Cabecas_Estado] } ON COLUMNS, 
    		NON EMPTY { (
    			[Ano].[Ano].[Ano].ALLMEMBERS * 
    			[Dim Localidade].[Local].[Municipio].ALLMEMBERS * 
    			[Dim Rebanho].[Tipo de Rebanho].[Tipo de Rebanho].ALLMEMBERS ) } ON ROWS 
    			FROM [IBGE] 
    			

    Abs.

    Eduardo Gomes - http://www.h1solucoes.com.br - Twitter: @edugp_sp

    sexta-feira, 15 de junho de 2012 19:42
  • E como vc vai exportar um resultado MDX para o SQL novamente? O SSIS não possui componentes para isto.

    Abs.


    Eduardo Gomes - http://www.h1solucoes.com.br - Twitter: @edugp_sp

    sexta-feira, 15 de junho de 2012 19:47
  • A MDX é feita no SQL Server Data Tools (Instalado com o SQL Server 2012), no modo Browse do Projeto do Analysis Services . Quando eu arrasto as dimensoes pra área ele agora executa a query MDX mostrando os resultados como uma tabela ao invés de mostrar a pivot table como no BI Dev Studio (SQL Server 2005/2008/R2), e mostra a MDX Query que rola por trás.

    A MDX que você me sugeriu gera o mesmo erro =/

    É possível sim trazer os dados de volta pra uma tabela OLTP normal pelo SSIS, Eduardo. Usando um Data Flow Component no SSIS, bastaria usar um OLE DB Source, e usar o a Conexão para o Analysis Services (10.0 pois a instância do SQL Server é 2008R2 no meu caso); Escolhendo o modo "SQL Query" ao invés de "Select Table or View", você põe na janela o MDX e ele mapeia. no OLE DB Destination basta mirar pra uma nova tabela OLTP em uma instância de banco de dados SQL Server qualquer.

    Eu vou tentar fazer e rodar o pacote. Vai que é problema do SSMS ao invés de ser a minha query... mas acho que é igual.

    sexta-feira, 15 de junho de 2012 23:04
  • ah ok, entendi. Vc está utilizando o SSIS 2012. Não tive a oportunidade de conhecer o SSIS 2012..rsrsrs...

    Quanto ao erro, muito estranho. O MDX deveria funcionar perfeitamente. Quando ocorre este erro o serviço analysis services para?

    Abs.


    Eduardo Gomes - http://www.h1solucoes.com.br - Twitter: @edugp_sp

    segunda-feira, 18 de junho de 2012 13:17
  • Então! O cubo e o pacote de integração (SSAS e SSIS) foram feitos no 2008, mas se eu importo o projeto pro SSAS 2012 no SQL Server Data Tools (SSDT) eu posso pegar o MDX que o próprio software gera (como na screenshot).

    Então Eduardo, eu rodei aqui no SSIS a busca dos dados em OLAP e me espantei pq funcionou! No final das contas não havia um problema... bastou rodar no SSIS e não houve problema algum na query MDX. No final das contas era um algum problema de configuração no próprio Management Studio, que não sei o porquê, alegou a falta de memória no screenshot. Eu tirei a conclusão precipitada que não funcionaria no SSIS por não ter funcionado no SSMS, achei que fosse culpa da query MDX.

    Aliás você disse que não mexeu muito no SSIS 2012 (no SSDT), quando começar a mexer me responde se vc achou ele demasiadamente lento... estou achando ele mais organizado, mais bonito, e infinitamente mais lento que o 2008 (BIDS). Ele faz validações demoradíssimas ao abrir uma solution, demora demais pra processar uma package... mas a culpa pode ser algo da máquina do trabalho (que apesar da marca não ser grande coisa, tem 8GB de RAM), em casa não fiz muitos testes com o SSIS 2012 pra dizer se ele é eficiente.

    terça-feira, 19 de junho de 2012 13:51
  • Olá Evaristo, tudo bem?

    Tenho desenvolvido algumas coisas com PerformancePoint na parte de gráficos. Minha dúvida é: este componte Dundas Charts que você utiliza não permite uma conexão direta à base OLAP?

    O Reporting Services faz algo parecido: você conecta num cubo OLAP e ele executa uma query MDX, trazendo os dados de um forma tabular para que você use-os em gráficos e tabelas. Se não me engano, o Reporting Services tem todos os gráficos que o Excel tem. E acho que também dá para acoplar esse Dundas ao Reporting Services.

    Enfim, se a solução te atende com rapidez e confiabilidade é o que importa. Mas esse "circuito" OLTP - DW - OLAP - OLTP (ou Transacional - DW - Multidimensional - Relacional) não faz muito sentido, levando-se a teoria em conta.

    Abs,

    Marco


    Analista de Business Intelligence

    quarta-feira, 20 de junho de 2012 14:35
  • Concordo Marco, quando eu vi a minha "necessidade" de popular um OLTP pra alimentar os gráficos na aplicação web é que eu vi que eu estava fazendo algo realmente errado. Poxa, OLAP é pra análise, então por quê não mostrar nele mesmo? E isso se deve ao fato que eu não sei usar o reporting services, e da forma que vocês estão direcionando a resposta, acredito mesmo que eu deveria tê-lo usado.

    Por eu não mexer direito com o reporting services, eu quis criar uma solution no Visual Studio que usasse estes dados, mas daí pra conversar com o SSAS é que eu não sabia como, pois então eu pensei em usar o LINQ pra modelar as entidades que iriam alimentar os gráficos, mas sei lá como fazer o LINQ criar entidades por meio de MDX, achei uns links sobre o SSAS Entity Framework Provider mas como não é algo nativo da Microsoft (além de ser pago) achei que não fosse o caminho certo. Então pensei em fazer esse OLAP -> OLTP no desespero.

    Aí já viraria uma questão de Reporting Services, mas como eu sou muito leigo no assunto, o que eu queria era criar uma Web Solution que tivesse dois links, sendo uma meu Dashboard e outra uma Pivot Table. Qual seria a melhor abordagem? Criar o Dashboard e a Pivot Table com o SSRS em dois rdls diferentes e usar o Visual Studio pra colocá-los um em cada aba?

    No final, eu já terminei fazendo a minha abordagem inicial, mas eu pretendo fazer de um modo mais eficiente nos próximos projetos.

    Sobre a Pivot Table tenho mais dúvidas ainda, pois pelo que eu saiba só posso entregar soluções de Pivot Tables usando o Power Pivot no Excel ou no Sharepoint. Acho que eu não conseguiria fazer um esquema de arrastar e soltar dimensões e measures publicado em ambiente web para os usuários, sem usar um componente como o DundasOLAPCharts e ter um resultado semelhante ao que consegui. Vocês sabem se eu consigo algo assim pelo  SSRS, ou só em um ambiente Sharepoint mesmo?

    Tenho um livro zero bala de Performance Point em casa mas ainda nem abri... =(

    Seguem duas screenshots da solução que eu implementei. Se houver sugestões sobre como melhorar a forma que eu fiz (sugerindo usar SSRS e sem fazer OLAP - OLTP), por favor podem sugerir!

    quinta-feira, 21 de junho de 2012 15:53
  • Evaristo, boa tarde.

    Vc consegue criar esses dashboards no SSRS, mas vc não consegue criar uma pivot table no SSRS. O SSRS é feito para visualização de relatórios estáticos, sem interação do usuário. Para pivot table recomendo o perfomance point que é um serviço do sharepoint. Nesta ferramenta fica mais flexivel o famoso "arrascar e visualizar" o dado que necessita.

    Só por curiosidade estas tabelas printadas é do DundasOlapCharts ? Achei o máximo essas telas! É um componente pro visual studio asp.net?

    Abs.


    Eduardo Gomes - http://www.h1solucoes.com.br - Twitter: @edugp_sp

    quinta-feira, 21 de junho de 2012 16:32
  • Sim! A primeira imagem, de arrastar e soltar é o DundasOlapCharts, já a segunda imagem, do dashboard é feito pelos componentes DundasCharts (sao dois separados) Ambos são pagos mas (não tenho muita certeza, mas) DundasCharts foi adquirido pela Microsoft como MSCharts (Microsoft Chart Controls - http://www.microsoft.com/en-us/download/details.aspx?id=14422), inclusive você pode baixar um projeto piloto pra aguçar a curiosidade em http://archive.msdn.microsoft.com/mschart . Há também um fórum como este só pra MsCharts. 

    A tabela da imagem 2 fiz com GridView mesmo e colori normal com CSS. Por isso que eu estava perguntando também pois queria saber o nível de customização que eu poderia conseguir com o SSRS, e às vezes mostrar as coisas nas cores do cliente faz um agrado na hora de uma pré-venda =)

    quinta-feira, 21 de junho de 2012 17:10