Calculated measures analysis cube

Tue, 19 Jun 2012 08:00:21 GMT

Hi Everyone,

I have a problem regarding measures in an analysis cube. I made connection to an analysis cube using: Microsoflt OLE DB Provider for OLAP services 8.0 or Microsoft OLE DB Provider for analysis services 10.

Both establish connection to the cube and give the possibility to load the data, which works fine. However the calculated measures in the cube are not shown and can not be loaded by QlikView.

Does anyone know a connector that does see the calculated measures? Or has another answer to this question?

Thank you in advance,

Marcel

Re: Calculated measures analysis cube

ToniKautto — Tue, 03 Jul 2012 13:19:01 GMT

There is no direct support for building or executing MDX-queries in QlikView. The following MDX-query (for the demo-database included with SQL 2005) will not work if you cut-and-paste it into QlikView:

SELECT { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON COLUMNS,

{ [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS

FROM [Adventure Works]

WHERE ( [Sales Territory].[Southwest] )

However, by using openrowset() in SQL, it is possible to encapsulate the MDX-query in a standard SELECT statement. In this example Microsoft OLEDB for Olap Services is used to query Analyis Services:

SQL SELECT * FROM

openrowset('MSOLAP.3','DATA SOURCE=localhost;Initial Catalog=Adventure Works DW',

'SELECT { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON COLUMNS,

{ [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS

FROM [Adventure Works] WHERE ( [Sales Territory].[Southwest] )')

Note: Support for openrowset is disabled by default in SQL 2005 Server.

topic Calculated measures analysis cube in QlikView

Calculated measures analysis cube

Re: Calculated measures analysis cube