How to read error message from Lumira

[mrwong05]

Hi,

I am using BI platform 4.2 SP5 with lumira 1.31 add-on.

My Lumira document is Live HANA data from 226 GB HANA 2.0 SPS02 on LAN Network.

When I try to link large datasets (Join 10 million to 180 Million rows) in lumira I get the following error:

Error:[RCI_CE_00042] - [Cannot execute the query. SQL state: HY000, SQL error code: 2, Description: SAP DBTech JDBC: [2]: general error: Allocation failed ; $size$=34930163513; $name$=libhdbsqlptimizer.so; $type$=pool; $inuse_count$=210; $allocated_size$=465737341133]

This seems like a HANA Db error message, am i correct? Is it a lumira error?

Is the only solution to increase the HANA size to avoid this error?

How do i read each part of this error message? 465,737,341,133 is requiring 456 GB of memory?

Thanks,

Matt

[mrwong05 · Accepted solution]

SAP support help me answer:

Open Calculation View> Semantics>View Properties>Advanced>Change 'Execute In:' option to 'SQL Engine" in the drop down.

This sped up my performance of Lumira document loads and FIlter 20x and allowed me to open larger datasets without having to upgrade hardware.

So in the end, after 3 years of searching for an answer, I found what I was looking for. I hope this helps you with your Lumira workflows and your clients are happy.

--Matt

Below is an excerpt from the master KBA for the Lumira performane issues, request you to go through it once and check if any of the steps helps you to improve the performance:

2541042 - SAP Lumira 2.x performance and sizing - resources and troubleshooting ***MASTER KBA**

The HANA Live Data connection properties that helped me may help you:

SAP Lumira SERVER for BI Platform

• HANA Online - HTTP(S) INA connector

Lumira Discovery 2.0 is using a different HANA Online connector than previous versions of Lumira Desktop and Server 1.X - previously OLAP JDBC connector is now in line with Design Studio technology and HANA HTTP(S) INA connector.

In HANA Live,when your application first attempts to connect to the SAP HANA database server, it checks the pool for an existing connection that uses the same connection parameters you specified. If a matching connection is found, then that connection is used. Otherwise, a new connection is used. When you disconnect, the connection is returned to the pool so that it can be reused. In Lumira Server properties, parameter -Dhanaview.connection.pool.size=100 can be adjusted to improve performance when the same HANA View is used across user sessions and/or Lumira stories. Note, however, that it is a throughput parameter - increasing it might consume more resources, and so will only affect environments affected by this very specific connection related issue - most commonly performance degradation as number of concurrent user sessions increases.

Another configuration piece which has been identified to increase performance of HANA Live data connectivity is modifying the Execute in: parameter (Advacnced option of a HANA View in View Properties tab) to Execute in: SQL Engine. It can be something worth testing for some of HANA Live workflows - as always, however, it is important to understand the bottleneck before making such changes

[lbreddemann]

These are indeed HANA error messages and they indeed indicate that the query optimizer component tried to allocate 34930163513 bytes = 32 GB of memory. That allocation failed. The error message also states that the currently already allocated memory size is 465737341133 bytes = 456 GB.

While your query seems to work on a relatively large dataset (10 mio : 180 mio join), we don't know at this point how large this would be in terms of memory. The next best thing here to understand the cause of the error and to find possible solutions is to identify the query that was sent to HANA and see how it should be executed (via EXPLAIN PLAN and PlanViz).

A typical mistake is to try and read back the result set of large data volume operations (like this join of big tables) without appropriate aggregation and filtering. This leads to the materialisation of the result set, which in turn requires a lot of memory and CPU resources.

[mrwong05]

lars.breddemann How can my HANA instance allocate 456 GB when it is only a 200 GB RAM instance?

The problem I face is that I want the users to have access to all the attributes of the data set in Lumira. the data is very deep 180 M rows and 40 columns wide. What are other users doing to handle BIG data in Lumira? How BIG can HANA go in Live-Hana Data connections to Lumira?

I'll execute the query in HANA sql (rather than Lumira Linking of datasets) and show you the PlanViz.

[TammyPowlas]

Hi Matt - please review these SAP notes to see if they help you:

2442161 - Opening a Lumira via a OpenDocument URL results in ...

https://apps.support.sap.com/sap/support/knowledge/preview/en/2442161 Opening a Lumira Document based on a specific HANA view via a OpenDocument URL and including parameters results in error RCI_CE_00042 Opening the ...

2208172 - SAP Lumira Family Supported Versions Matrix | SAP ...

https://apps.support.sap.com/sap/support/knowledge/preview/en/2208172 Incompatible versions of Lumira and components or applications may result in errors or unanticipated behaviors. Example error: Error: [RCI_CE_00042] ...

[mrwong05]

Hi Lars,

My scenario is: I want to find a very specific person from a large population, lets say the world 7 billion people.

My first step is to look at their name, birth date, birth time, location, and ID. For simplicity's sake, this is one table with 5 columns and 7 billion rows. next i want to find this person's 7th grade teacher from a table that includes the names of their grade-school teachers 1st to 12th grade, the key is their ID. This second table is 13 columns by 7 billion rows.

After I find the person's ID in the first table, it's a simple query of 2 columns to find the 7TH_TEACHER from TEACHERS where ID='<person's id>';

But then, next, I want to find Person 2 and their 8th grade teacher. My attribute columns have changed and my previous subquery also changes.

So to have the flexibility to search amongst all the people and all their teachers, one must load both full tables into Lumira via a Calculation view that has joined both tables. Am I wrong is this architecture?

We thought Lumira would be a simple tool for no-technical management to explore and present the data with on-the-fly relationships. And the problem is that the user determines what attributes are meaningful, not the data-provider. Does it seems I have the wrong tool for what I want to do?

I think are recommending us produce reports to our clients instead of giving them the opportunity to create their own reports through Lumira storyboards?

Thank you for your response, I will take your words of wisdom into consideration in our future roadmap. I agree with your last paragraph completely, optimization seems lost in trying to support all use-cases.

--Matt

[lbreddemann]

Matt,

this is a Q&A site for technical questions. Considerations of solution architecture options are typically too complex to handle them here in a reasonable way.

The function you described (find records in table A and lookup matching records - if any - in table B) would be a database join. For that, there is no need to bring all processed records back into the reporting front-end. Such things can (and should) be handled on the DB/HANA level.

Whether or not SAPBO and Lumira are the best tool for letting users basically work with "raw" tables is something I cannot comment on. In "olden days" such a free-form query requirement would have been supported by, well, free form SQL, graphical query builders or something like the Explorer tool (ok, Explorer requires to model the data structure and how tables are combined upfront).

In any case, I would recommend to talk to a BO expert consultant about the use case and the options to implement those.

cheers,

Lars

[mrwong05]

Thanks for the kind words Lars, I think I will go talk to a BO expert now...

[lbreddemann]

The Q&A platform does not allow for threaded comments or discussions. So I post a new answer now and refer to your questions.

A "larger than licensed" allocation can happen when the server HANA is running on has more memory available and the global allocation limit has not been configured.

Concerning the report design, the approach to this is basically always the same: "overview first, zoom and filter, then details-on-demand", the famous mantra (http://www.cs.umd.edu/~ben/papers/Shneiderman1996eyes.pdf) from Ben Shneiderman .

So, don't try and present a database table filtering tool that returns single records. Instead, provide meaningful aggregations and filter options.

No user, looking for insights, can deal with 180M records. That volume only makes sense when it's fed into some form of automated analysis. And that's what the report, respectively HANA should be doing.

Handling BIG DATA, as you put it, is not about returning millions of records. It's about being able to analyse large volumes of data to produce information and insight.

Based on my experience - and I'm not saying that this is the case here - often problematic "Big Data" models get designed to be everything for everybody and try to have all sorts of analysis in a single model. That approach makes it hard to understand and build the model correctly for the humans and usually does not leave a lot of room for optimisation for the machine.