Unable to update a cube with Application Engine

maraye-bonner

Hi Can anyone help me with this script.   The script should be updating a cube with new values, but it keeps overwriting the data in the cube.  If the cube has 1000 records and I run the script to add 800,  I end up with 800 records in the cube.    I am very  new to this and need all the help I can get.  

#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 WHERE GLTOT_CalendarPeriod in ('FCIB/2023334','FCIB/2023365','FCIB/2024031','FCIB/2024060','FCIB/2024091','FCIB/2024121','FCIB/2024152','FCIB/2024182','FCIB/2024213','FCIB/2024244');"); //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);

OLAPCellWriteBufferIncrementNumber(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;
}

  

Peter Mayall

You are deleting the existing cube values via "OLAPDeleteDataArea(GLTOTDataArea)" so you are starting from zero.  You may be reading 1000 records, but it would seem you have only 800 unique records.  

If you are expecting to add to the existing cube values, then you should remove the data area define (OLAPDataArea) statements.