App Engine scripts for fast data copy

unknown

Good day

We are creating scripts to use for copying data from one version to another or similar source to destination scenarios. This is on the v12 MT cloud application.

The one using the OLAPCelllWriteNumber function works but copies at a very slow speed. (3 records a second)  Can anyone share 'copy data'  scripts on Application Engine that perform well on the MT cloud. Any other scripts that were useful in their budgeting and planning can also be shared.

Thanks in advance,

Bilal

Ralf Zerfass

Hi Bilal,

using single write cell requests results in poor performance, as you already noticed.

There a two other options for on-prem, but I think should work on MT as well.

Using WriteBuffers, just a code snippet for transfering string values, works also with OLAPCellWriteBufferWriteNumber()

OLAPCellWriteBuffer att_buf = OLAPCreateCellWriteBuffer(oConnection);

    foreach (OLAPCell market_cell in dMarkets) {

        string projekt = OLAPCellGetElement(market_cell,"Project");
        string version = OLAPCellGetElement(market_cell,"Version");
        
        
      
        // Status= 7-Year-Plan und Titel der Versionen aus dem 7-YP setzen
        OLAPCellWriteBufferWriteString(att_buf, "MAR_Attributes", "7-Year-Plan", projekt, version, "~Product", "~Market", "Version_Status");
        OLAPCellWriteBufferWriteString(att_buf, "MAR_Attributes", yp_name+" (HQ)", projekt, version, "~Product", "~Market", "Version_Title");
        
    }

    OLAPCommitCellWriteBuffer(att_buf);
    OLAPClearCellWriteBuffer(att_buf);

Or using CopyDataArea:

#define EngineVersion 5.0
#define RuntimeVersion 5.0

// #include "DB_Connection_Server", "*"

int MAR_Copy_Projects_Connected(OLAPConnection oConnection, string project, string from_cube, string to_cube, string from_version, string to_version, string source)
@Description: "Copy attributes of a project into other version";

@Parameter[oConnection]: "OLAP Connection";
@Parameter[project]: "Project ID";
@Parameter[from_cube]: "From Cube";
@Parameter[to_cube]: "To Cube";
@Parameter[from_version]: "From Version";
@Parameter[to_version]: "To Version";
@Parameter[source]: "Source";


@Category: "Marketing";
@Returns: "OK";

{

    NotifyDebug("MAR_Copy_Project:"+"/"+project+"/"+from_cube+"/"+to_cube+"/"+from_version+"/"+to_version+"/"+source);

    // OLAPConnection oConnection = DB_Connection_Server("marketingadmin");

    StringList slSource = ConvertToStringList(source);

    if (source == "") {
        slSource = ConvertToStringList(OlapDataAreaBCells);
    }


    // 2. Create string list for target data area
    StringListList targetElements = CreateStringListList();
    StringList slMonth = ConvertToStringList(OlapDataAreaBCells);
    StringList slProject = ConvertToStringList(project);
    StringList slVersion = ConvertToStringList(to_version);
    StringList slUnit = ConvertToStringList(OlapDataAreaBCells);
    StringList slProduct = ConvertToStringList(OlapDataAreaBCells);
    StringList slMarket = ConvertToStringList(OlapDataAreaBCells);
    StringList slMeasure = ConvertToStringList(OlapDataAreaBCells);

    Append(targetElements, slMonth);
    Append(targetElements, slProject);
    Append(targetElements, slVersion);
    Append(targetElements, slSource);
    Append(targetElements, slUnit);
    Append(targetElements, slProduct);
    Append(targetElements, slMarket);
    Append(targetElements, slMeasure);


    // 3. Create string list for source data area
    StringListList sourceElements = CreateStringListList();
    StringList src_Project = ConvertToStringList(project);
    StringList src_Version = ConvertToStringList(from_version);
    
    Append(sourceElements, slMonth);
    Append(sourceElements, src_Project);
    Append(sourceElements, src_Version);
    Append(sourceElements, slSource);
    Append(sourceElements, slUnit);
    Append(sourceElements, slProduct);
    Append(sourceElements, slMarket);
    Append(sourceElements, slMeasure);


    // 4. Delete all existing values in the target area.
    // Filter data area to values that are not null (suppress null values). Use base elements only
    OLAPDataArea deleteValues = OLAPCreateDataArea(oConnection, to_cube,
                                                   OlapDataAreaIncludeB | OlapDataAreaSuppressNull, OlapDataAreaOperatorNone,
                                                   0.0, OlapDataAreaOperatorNone, 0, targetElements);


    OLAPDeleteDataArea(deleteValues);
    
    // 5. Filter data area to values that are not null (suppress null values). Use the base elements only
    OLAPDataArea localValues = OLAPCreateDataArea(oConnection, from_cube,
                                                  OlapDataAreaIncludeB | OlapDataAreaSuppressNull, OlapDataAreaOperatorNone,
                                                  0.0, OlapDataAreaOperatorNone, 0, sourceElements);
    

    // 6. Copy Data area
    OLAPCopyDataArea(localValues, to_cube, "Relative", 1.0, targetElements, sourceElements, true, true);
    
    // Da die Text-Elemente teilweise auch auf C-Elementen stehen können, diese nochmals extra mit anderen Flags kopieren

    NotifyDebug("MAR_Copy_Project - Copy Unit text for C-elements");

    StringListList targetElements_text = CreateStringListList();
    StringList slMonth_text = ConvertToStringList(OlapDataAreaCCells);
    StringList slProject_text = ConvertToStringList(project);
    StringList slVersion_text = ConvertToStringList(to_version);
    StringList slUnit_text = ConvertToStringList("text");
    StringList slProduct_text = ConvertToStringList(OlapDataAreaCCells);
    StringList slMarket_text = ConvertToStringList(OlapDataAreaCCells);
    StringList slMeasure_text = ConvertToStringList(OlapDataAreaCCells);

    Append(targetElements_text, slMonth_text);
    Append(targetElements_text, slProject_text);
    Append(targetElements_text, slVersion_text);
    Append(targetElements_text, slSource);
    Append(targetElements_text, slUnit_text);
    Append(targetElements_text, slProduct_text);
    Append(targetElements_text, slMarket_text);
    Append(targetElements_text, slMeasure_text);


    // 3. Create string list for source data area
    StringListList sourceElements_text = CreateStringListList();
    StringList src_Project_text = ConvertToStringList(project);
    StringList src_Version_text = ConvertToStringList(from_version);
    
    Append(sourceElements_text, slMonth_text);
    Append(sourceElements_text, src_Project_text);
    Append(sourceElements_text, src_Version_text);
    Append(sourceElements_text, slSource);
    Append(sourceElements_text, slUnit_text);
    Append(sourceElements_text, slProduct_text);
    Append(sourceElements_text, slMarket_text);
    Append(sourceElements_text, slMeasure_text);


    // 4. Delete all existing values in the target area.
    // Filter data area to values that are not null (suppress null values). Use C elements only
    OLAPDataArea deleteValues_text = OLAPCreateDataArea(oConnection, to_cube,
                                                        OlapDataAreaIncludeC | OlapDataAreaSuppressNull, OlapDataAreaOperatorNone,
                                                        0.0, OlapDataAreaOperatorNone, 0, targetElements_text);


    OLAPDeleteDataArea(deleteValues_text);
    
    // 5. Filter data area to values that are not null (suppress null values), only C-elements
    OLAPDataArea localValues_text = OLAPCreateDataArea(oConnection, from_cube,
                                                       OlapDataAreaIncludeC | OlapDataAreaSuppressNull, OlapDataAreaOperatorNone,
                                                       0.0, OlapDataAreaOperatorNone, 0, sourceElements_text);
    

    foreach (OLAPCell testcell in localValues_text) {

        NotifyDebug(OLAPCellGetElement(testcell,"Product"));
        NotifyDebug(OLAPCellGetElement(testcell,"Market"));
        NotifyDebug(OLAPCellGetElement(testcell,"Measure"));
        NotifyDebug(testcell);
        
    }

    // 6. Copy Data area
    OLAPCopyDataArea(localValues_text, to_cube, "Relative", 1.0, targetElements_text, sourceElements_text, true, false);
    
    return (0);
}

For OLAPCopyDataArea the documentation also shows a good example, that's where I copied mine from ...

Hope that helps,

Ralf