Error updating a created cube via Application Engine with 1.7 million rows

Hi. I have a script running Application Engine. It should be updating the data in a created cube that exists in the staging area. The script however is overwriting the data in the cube. Can you assist me in updating the script so that it updates the cube and not overwrite the data with the new data. I cannot load the full data set as I get an error " An error has occurred: Remote connection to Application Engine Service has been closed and cannot be used for communication anymore" when I run the Application Engine script with the full data set (1.7 million records). Script is below

#define EngineVersion 5.0
#define RuntimeVersion 5.0

bool FCIB_StagingToGLTOTCube()
@Description: "Import Staging Fact data into GLTOT_Ext cube";
@Category: "FCIB_dEPM_DataIntegration";
@Returns: "True";
{

//TST

//01. Add your BI# code here.
string server = "FSM";
string cube = "GLTOT_Ext";

//02. Connect to SQL Server
SQLConnection Staging = SQLCreateNamedConnection("Staging");
//NotifyInformation("SQL Connection Established");

//03. Query SQL database and retrieve all records from the TB Table
SQLData resultData = SQLExecuteQuery(Staging,"SELECT * FROM GLTOT_Fact;"); //Only use SELECT * if you want all columns
//NotifyInformation("Result Dataset created");

//04. Connect to OLAP Fincon DataBase
OLAPConnection connection = OLAPCreatePooledNamedConnection(server);
OLAPCellWriteBuffer buffer = OLAPCreateCellWriteBuffer(connection, true, 5000);
//NotifyInformation("OLAP Connection established");

//05. Create Target Data area
OLAPDataArea GLTOTDataArea = OLAPCreateDataArea(connection, cube,
OlapDataAreaIncludeB |
OlapDataAreaSuppressNull, OlapDataAreaOperatorNotEqual,
0.0, OlapDataAreaOperatorNone, 0.0,
OlapDataAreaBCells,
OlapDataAreaBCells,OlapDataAreaBCells,OlapDataAreaBCells,OlapDataAreaBCells,
OlapDataAreaBCells,OlapDataAreaBCells,OlapDataAreaBCells,OlapDataAreaBCells,
OlapDataAreaBCells,OlapDataAreaBCells,OlapDataAreaBCells,OlapDataAreaBCells,
OlapDataAreaBCells,OlapDataAreaBCells,OlapDataAreaBCells,OlapDataAreaBCells,
OlapDataAreaBCells,OlapDataAreaBCells,OlapDataAreaBCells);
//NotifyInformation("Data area GLTOTDataArea created");

//06. Delete Target Data area
//OLAPDeleteDataArea(GLTOTDataArea);

//07. Loop through SQL resultSet
int count=0;
int Rowcount=0;
StringList rows = CreateStringList();
foreach (SQLDataRow row in resultData)
{
string GLTOT_AccountingEntity = SQLDataRowGetString(row, "GLTOT_AccountingEntity");
string GLTOT_AccountingUnit = SQLDataRowGetString(row, "GLTOT_AccountingUnit");
string GLTOT_CalendarPeriod = SQLDataRowGetString(row, "GLTOT_CalendarPeriod");
string GLTOT_ChartAccount = SQLDataRowGetString(row, "GLTOT_ChartAccount");
string GLTOT_Currency = SQLDataRowGetString(row, "GLTOT_Currency");
string GLTOT_FinanceDimension1 = SQLDataRowGetString(row, "GLTOT_FinanceDimension1");
string GLTOT_FinanceDimension2 = SQLDataRowGetString(row, "GLTOT_FinanceDimension2");
string GLTOT_FinanceDimension3 = SQLDataRowGetString(row, "GLTOT_FinanceDimension3");
string GLTOT_FinanceDimension4 = SQLDataRowGetString(row, "GLTOT_FinanceDimension4");
string GLTOT_FinanceDimension5 = SQLDataRowGetString(row, "GLTOT_FinanceDimension5");
string GLTOT_FinanceDimension6 = SQLDataRowGetString(row, "GLTOT_FinanceDimension6");
string GLTOT_FinanceDimension7 = SQLDataRowGetString(row, "GLTOT_FinanceDimension7");
string GLTOT_FinanceDimension8 = SQLDataRowGetString(row, "GLTOT_FinanceDimension8");
string GLTOT_FinanceDimension9 = SQLDataRowGetString(row, "GLTOT_FinanceDimension9");
string GLTOT_FinanceDimension10 = SQLDataRowGetString(row, "GLTOT_FinanceDimension10");
string GLTOT_Ledger = SQLDataRowGetString(row, "GLTOT_Ledger");
string GLTOT_Measures = SQLDataRowGetString(row, "GLTOT_Measures");
string GLTOT_Project = SQLDataRowGetString(row, "GLTOT_Project");
string GLTOT_Scenario = SQLDataRowGetString(row, "GLTOT_Scenario");
string GLTOT_System = SQLDataRowGetString(row, "GLTOT_System");
double value = SQLDataRowGetDouble(row, "ValueColumn");

count = count + 1;

//WriteLine("Account: " + account + " Value: " + value);

OLAPCellWriteBufferWriteNumber(buffer, cube, value, GLTOT_AccountingEntity, GLTOT_AccountingUnit, GLTOT_CalendarPeriod,
GLTOT_ChartAccount, GLTOT_Currency, GLTOT_FinanceDimension1, GLTOT_FinanceDimension2,
GLTOT_FinanceDimension3, GLTOT_FinanceDimension4, GLTOT_FinanceDimension5, GLTOT_FinanceDimension6,
GLTOT_FinanceDimension7, GLTOT_FinanceDimension8, GLTOT_FinanceDimension9, GLTOT_FinanceDimension10,
GLTOT_Ledger, GLTOT_Measures, GLTOT_Project, GLTOT_Scenario, GLTOT_System);

//Rowcount = count + Rowcount;
//NotifyInformation("Records Synced : "+ Rowcount);
//OLAPCommitCellWriteBuffer(buffer);
}

OLAPCommitCellWriteBuffer(buffer);
NotifyInformation("Count: "+ count);
OLAPDisconnectPooledConnection(connection);
return true;
}

0911060553270289.pdf

Find more posts tagged with

Infor EPM Discussions

Comments

martin-reischl

Hi,

first of all, you should use a mapping to load 1.7 million rows, it is much faster than working through row by row via AppEngine.

Create a mapping and use ModelingExecuteFactMapping to run the mapping from AppEngine.

Second, if you don't want to overwrite you values, what is it which you want to do? You can add your new data to existing values by using OLAPCellWriteBufferIncrementNumber instead of OLAPCellWriteBufferWriteNumber.

Third, the error message about losing the connection. There is some limitation on how much you can do from the App Engine Process Editor, and this is what happens when you exceed the limit. Try loading fewer rows from the Process Editor. Then use the Process Executor (or an App Studio action, or whatever) to run the full routine via the service. The service does not have this limitation.

Hope it helps.

Best Regards

Martin

ivan-rebo

Just a note, your call to SQLExecuteQuery returns SQLData which means it would try to load all the rows into app engine memory. If that is 1.7 mil rows that can eat a lot of memory and possibly even cause issues. In the October version that will be released soon we added a way to execute queries using an SQL reader which will provide you the same functionality to run the query and go thru all the rows without having to read all of the rows into memory at once. Maybe that could help with your issue.

maraye-bonner

Hi Martin

Thanks for responding. If I can update (not overwrite ) that would work. To try what you suggest do I have to make any other changes to my code besides OLAPCellWriteBufferIncrementNumber. I am very new to all of this.

Maraye

maraye-bonner

Thanks Ivan

I will look at the update. I am going to try what Martin suggests in the interim

maraye-bonner

Hi Martin

I changed the OLAPCellWriteBufferWriteNumber to OLAPCellWriteBufferIncrementNumber and the data in the cube was overwritten with the data I loaded. Is there something else in the script that needs to be changed ?

Regards

Maraye

martin-reischl

Hi Maraye,

actually there is nothing else that you need to change. The only difference is how it writes the value.

Say, there is a value of 3 in a cell. Then you run the routine, and it comes with a value of 4.

OLAPCellWriteBufferWriteNumber will update the value of 3 to 4, so you'll end up with 4 in the cell. So it is update or overwrite, for me this means the same thing.

OLAPCellWriteBufferIncrementNumber will add the 4 to the existing 3, so you'll end up with 7 in the cell. It is additive logic.

Most of the time, a good practice is to delete the target area first, and then to use an additive load. You have a delete statement in your routine but it is disabled. So running the load with "increment" will duplicate all values if you run the routine twice.

Best Regards

Martin

maraye-bonner

Thanks Martin

I expect that with the update of the cube, however if the cube had 1000 records and I updated with 800 records, I do not expect the the number of records in the cube to decrease to 801 records. This is what is happenning. I would expect to have the same 1000 records or more. So something is not correct with the script.

Maraye

Copyright © 2025 Infor. All rights reserved.