topic Re: Issues with Essbase Connector in QlikView

Issues with Essbase Connector

cleblois — Thu, 11 Dec 2014 15:21:10 GMT

Hi,

I'm struggling with this new Essbase Connector. When running simple queries, I can get results but when going to more complicated, I start getting memory issues.

First example was my first try and I get: "Query result exceeds 2^32 cells" which is more than what is in Essbase cube ...

Second example, I reduced with "NON EMPTY" to avoid empty cells (that are legion) and now I receive "too large memory ".

Any help would be more than welcomed.

Thanks,

Christophe

QVX_UNKNOWN_ERROR: Essbase Error (1200638): Internal error: Query output is too large to be executed. Query result exceeds 2^32 cells. Stack trace written to C:\ProgramData\QlikTech\Custom Data\QvEssbaseConnector\Log\StackTrace.txt

[Data]:

SQL

SELECT {[NetSales]} ON COLUMNS,

{CROSSJOIN({Descendants ([Scenario].[Actload], 1, Leaves)}, CROSSJOIN({[Years].[FY14]}, CROSSJOIN({Descendants ([DataType].[Total ODS], 1, Leaves)}, CROSSJOIN({[Version].[Working]}, CROSSJOIN({Descendants ([Product], 5, Leaves)}, CROSSJOIN({Descendants ([Program], 1, Leaves)}, CROSSJOIN({Descendants ([TO_PML], 1, Leaves)}, CROSSJOIN({Descendants ([FR_PML], 1, Leaves)}, CROSSJOIN({Descendants ([Customer], 7, Leaves)}, CROSSJOIN({Descendants ([Entity].[Global], 4, Leaves)}, CROSSJOIN({Descendants ([AltOrgProd].[DCS PBU], 1, Leaves)}, CROSSJOIN({Descendants ([Currency].[USD Total], 1, Leaves)}, {Descendants ([Period].[YearTotal], 3, Leaves)}))))))))))))} ON ROWS

FROM FPAACTQV.FPAACTQV

QVX_UNKNOWN_ERROR: Essbase Error (1200712): Internal error: Query is allocating too large memory ( > 4GB) and cannot be executed. Query allocation exceeds allocation limits.. Stack trace written to C:\ProgramData\QlikTech\Custom Data\QvEssbaseConnector\Log\StackTrace.txt

[Data]:

SQL

SELECT NON EMPTY {[NetSales]} ON COLUMNS,

FROM FPAACTQV.FPAACTQV

Re: Issues with Essbase Connector

cleblois — Fri, 12 Dec 2014 15:44:02 GMT

Note: Essbase cube is only 64Mb, so why can't the connector absorb it?

Re: Issues with Essbase Connector

dyo — Tue, 20 Jan 2015 12:54:21 GMT

First this is an actual Essbase error not one from the connector.

The first thing to check is to see if you are on 11.1.2 or higher of Essbase, Oracle added support for MDX queries that exceed the 2³² query limit. Essbase sends 64-bit values representing cell offsets to the client. The Java MDX API now handles 64-bit offsets

https://docs.oracle.com/cd/E17236_01/epm.1112/esb_new_features/esb_new_features.html#esb_new_features_292

Also, I have added the following setting in my Essbase admin Services the default limit of rows to be exported is fairly small.

SSPROCROWLIMIT 1000000

Third thing to try, if you still have this error, you can break your query into multiple queries and then concatenate this together in the Qlik script. Most of the time, you will not be doing full loads of Essbase in production, but just adding new data based on the time dimension.

Re: Issues with Essbase Connector

Anonymous — Tue, 27 Jan 2015 22:13:40 GMT

Is there a way to save the server name, port and userid/password in the connection so it doesn't have to be entered every time?

Re: Issues with Essbase Connector

dyo — Tue, 27 Jan 2015 22:17:05 GMT

Not currently

Re: Issues with Essbase Connector

rohitk1609 — Tue, 21 Feb 2017 17:14:38 GMT

Essbase Connector not working