Issues with Essbase Connector

[cleblois]

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,

{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

[cleblois]

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

[dyo]

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_feature...

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.

[Anonymous]

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?

[dyo]

Not currently

[rohitk1609]

Essbase Connector not working