June 1, 2010 at 3:17 pm
Hi,
I am working on a requirement where I need to extract data from cube and load it into a grid.
But here I am facing an issue while extracting data from cube and hope someone can help me with the same.
I need data in the format as shown below. But when I use MDX for this extract, using cross joins, I receive the error: “System.OutOfMemoryException” is thrown and sometimes it keeps running for more than 30 minutes.
I had never get any result using the MDX below (however tested in the local set-up with very less data and it works fine).
If found some information in the internet about "IIF and NonEmpty" functions, but I don´t know how to use it in this query.
It would be great if I get some kind of inputs in optimizing this query or an optimized query. MDX:
SELECT NON EMPTY
CrossJoin ( Distinct ( {{[Dim Tempo].[Calendario].[Ano].&[2009], [Dim Tempo].[Calendario].[Ano].&[2010]}} ),
Distinct (( {{[Measures].[Val Pago - Valor Recolhido], [Measures].[Val Tributo - Valor Recolhido]}} )))
DIMENSION PROPERTIES MEMBER_TYPE ON COLUMNS, NON EMPTY
CrossJoin ( Distinct ( {[Dim Pessoa].[Orgao Sigla].[All].Children} ) ,
CrossJoin ( Distinct ( {[Dim Competencia].[Competencia].[All].Children} ) ,
CrossJoin ( Distinct ( {[Dim Estabelecimento].[CNPJ Radical].[All].Children} ) ,
CrossJoin ( Distinct ( {[Dim Estabelecimento].[CPF CNPJ].[All].Children} ) ,
CrossJoin ( Distinct ( {[Dim Estabelecimento].[Inscricao Municipal].[All].Children} ) ,
CrossJoin ( Distinct ( {[Dim Estabelecimento].[Razao Social].[All].Children} ) ,
CrossJoin ( Distinct ( {[Dim Estabelecimento].[Status ISS].[All].Children} ) ,
CrossJoin ( Distinct ( {[Dim Pessoa].[Atv Economica Principal].[All].Children} ) ,
Distinct ( {[Dim Opcao Simples].[Opcao Simples].[All].Children} ) ))))))))
ON ROWS FROM [ISS Valor Recolhido]
Looking forward for some help.
Thanks in advance.
Luigggye
June 15, 2010 at 2:58 pm
SELECT
NON EMPTY {
( Distinct ( [Dim Tempo].[Calendario].[Ano].&[2009]*
[Dim Tempo].[Calendario].[Ano].&[2010]*
[Measures].[Val Pago - Valor Recolhido]*
[Measures].[Val Tributo - Valor Recolhido]))}
DIMENSION PROPERTIES MEMBER_TYPE ON COLUMNS,
NON EMPTY
{CrossJoin ( [Dim Pessoa].[Orgao Sigla].[All].Children,
[Dim Competencia].[Competencia].[All].Children,
[Dim Estabelecimento].[CNPJ Radical].[All].Children,
[Dim Estabelecimento].[CPF CNPJ].[All].Children,
[Dim Estabelecimento].[Inscricao Municipal].[All].Children,
[Dim Estabelecimento].[Razao Social].[All].Children ,
[Dim Estabelecimento].[Status ISS].[All].Children ,
[Dim Pessoa].[Atv Economica Principal].[All].Children ,
[Dim Opcao Simples].[Opcao Simples].[All].Children)
} ON ROWS
FROM [ISS Valor Recolhido]
Try the above. Might have some expression error, missing brackets.
But this will be the format. And why dont you use the Query Designer in SSRS (if your are using SSRS) and do learn some basics of MDX before writing out the code. It will help.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply