Copying/Splashing values using APE in Version 11

Hi,

I’m looking for the most efficient way to copy cells within a cube in APE in Version 11

For example to copy the values from version Actual to version Budget, a typical splashing issue.

When doing this using the COPY Command in OfficePlus this is a matter of milliseconds.

I wonder how I can execute splashing commands in APE?

Right now the only solution I have, is to create a data area, read all cell values using a foreach-loop and then write the retrieved values one by one to the target area, which works, but is slow (about 20 seconds for 100.000 values - already optimized using a write buffer).

Please find attached the sample code snippet I’m using.

[View:/cfs-file/__key/communityserver-discussions-components-files/545/6278.CopyValues-in-APE.txt:320:240]

Thanks in advance for any tips to improve my solution.

In V12 I’ve found the OLAPCopyDataArea but migration is not possible short-term.

Best regards,

Ralf

Attachment_4485.zip

Find more posts tagged with

Infor EPM Discussions

Comments

paul-randell

Hi Ralf,

Have you tried using an Application Studio report, where you would be able to select the version from and to, and other dimension selections if required, and then using the WBC command you can copy the data.

Here is an extract from the Application Studio Help:

COPY

You can copy values from one area of a cube to another: for example, the actual figures for one year to the budget for another year. In this example, the actual figures are the source and the budget is the target.

You write the copy command in the target cell. In the command you specify only the dimensions and elements that distinguish the source area of the cube from the target area. If the elements belong unambiguously to a specific dimension you need only specify the elements.

COPY - allocation: #C[Tuple]

A tuple is the address of a specific area of the database. In this case, the tuple is the address of the data which you want to copy. It is recommended that you use unique element names to specify the tuple.

Values that you copy from one area of a cube to another replace existing values.

Example

You want to copy the fourth quarter budget for 2006 to the actual results for the same quarter. Select the cell containing the actual figures for the quarter and specify:

#COPY [VALTYPE].[Variance].[Budget];[PERIOD].[All Years].[2006].[2006_Q4]

You want to copy the French fourth quarter budget for 2006 to the German third quarter actuals for 2006.

Select the cell containing the German third quarter actual figures and specify:

#COPY [REGION].[All Regions].[Europe].[France];[VALTYPE].[Variance].[Budget];[PERIOD].[All Years].[2006].[2006_Q4]

Note:COPY uses "External Weighted Basic" as OLAP Server-specific write-back method.

COPYFULL

The COPYFULL command works in the same way as COPY but also copies values which are calculated by rules.
Note: COPYFULL uses "External Weighted" as OLAP Server-specific write-back method.

Ralf Zerfass

Hi Paul,

yes, in AppStudio it is possible to use the splashing commands using WBC and I’ll do this wherever possible.

However, my example was simplified, the whole process is more complex and cannot be handled with just one WBC command, but the copying of the values is only one part of the APE-Process and this part slows down the performance of the whole process.

That’s why I’m looking for a better/faster solution to copy big data areas in APE Version 11.

Ralf

ivan-rebo

Hi,

the 12.0 OLAPCopyDataArea function is just a wrapper for olap xml command data area copy.

You should be able to find it in the 11.0 olap sdk (the 12.0 docu is https://docs.infor.com/depm/12.0.x/en-us/depmolh_cloud/default.html?helpcontent=olap_cloud/zbg1510174519656.html).

You can construct such xml in app engine and send it to olap using OlapXMLRequest app engine function.

(string OLAPXMLRequest(OLAPConnection connection, string request))

regards

Ivan

Mike Potashnick

Ralf,

We have developed a Copy widget for use in many situations. The widget will read any cube structure and allow you to select elements from multiple dimensions and apply it to multiple cubes. As long as the cubes share the same Year, Period, Version dimensions it works really well and quickly. We deploy these solutions across most of our clients. I will try to loop in our main developer, who can share some tips and show you what we have developed. If you, or any of the community are interested, we can discuss how we can share the tool with you.

unknown

Hi Ralf,

You have been going at this the correct way, though i would recommend further optimising your code.

There are multiple built in functions that allow the use of dynamic features, therefore i would recommend making full use of these function via the following method;

Within the foreach statement:

StringArray Elements = OLAPCellGetElements(cell);
double value = cell;

//version is the 5th dimension in the cube (as an example)
Elements[4] = "Budget";

OLAPCellWriteBufferWriteNumberDynamic(BufferCon, Cube, value, Elements);

This will help reduce the processing time currently required, also set an auto commit onto your 'OLAPCreateCellWriteBuffer' and experiment with the best number to write automatically.

Remove the OLAPCellGetElement you currently have within the foreach when using the above code.

Hope this helps.

Ibrahim

CFMS

Ralf Zerfass

Ivan, Mike and Ibrahim,

thanks a lot for your hints and tips.

Ivan: I never dealt with XML-Requests yet. If you could provide a short sample code that would save me (and maybe others) a lot of time of investigation.

Mike, if you could provide any code or widgets that can easily be re-used, I'm sure the whole community will appreciate.

Ibrahim: sounds good, will try it.

Best wishes to everybody,

Ralf

MikeLewis

Here is come code we use to copy a budget. This calls a couple of custom functions, but they are pretty simple. If you need help with them, let me know.

#define EngineVersion 4.0
#define RuntimeVersion 4.0

#include "RHR_OLAPPooledConnection","*"
#include "RHR_GetCycleFromPTime","*"
#include "RHR_GetBaseElementsFromElement","*"

bool RHR_CopyBudget(string SourceVersionUN, string TargetVersionUN, string SourceYearUN)

@Description: "Copy Budget";
@Category: "RHR Data Copy Functions";
@Returns: "True, if successful.";

@Parameter[SourceVersionUN]: "The Source Version element to be used.";
@Parameter[TargetVersionUN]: "The Target Version element to be used.";
@Parameter[SourceYearUN]: "The Source Year element to be used.";
{
    OLAPConnection OLAPPooledConn = RHR_OLAPPooledConnection();

    // Values used for testing
    if(SourceVersionUN=="") { SourceVersionUN="[PSOURCE].[Mid-Month Forecast].[1]";}
    if(TargetVersionUN=="") { TargetVersionUN="[PSOURCE].[Published Forecast].[1]";}
    if(SourceYearUN=="") { SourceYearUN="[PTIME].[2019].[1]";}

    // Get the Dimension Elements we need
    string SourceVersion = ResolveUniqueName(OLAPPooledConn, SourceVersionUN);
    string TargetVersion = ResolveUniqueName(OLAPPooledConn, TargetVersionUN);
    string SourceYear    = ResolveUniqueName(OLAPPooledConn, SourceYearUN);

    OLAPElementList CompanyList = RHR_GetBaseElementsFromElement("PCOMPANY","Budgeted Companies");
    OLAPElementList CurrencyList = OLAPGetChildElementList(OLAPPooledConn, "PCURRENCY", "TOTAL_LC", true);
    string PLevel="INPUT_COSTCENTER";

    OLAPElementList SourceYearList = OLAPGetChildElementList(OLAPPooledConn, "PTIME", SourceYear, true);
    if(Count(SourceYearList) == 0)
    {
        OLAPElement element = OLAPGetDimensionElement(OLAPPooledConn, "PTIME", SourceYear);
        SourceYearList = OLAPGetElementList(element);
    }

    string Cycle = RHR_GetCycleFromPTime(SourceYear);

    // Start Copy Process

    // Delete Current Values to:
    //     Delete any values that are not in the Elements to be copied
    //     Make sure there are no zeros to save space in the cube
    OLAPDataArea TargetData = OLAPCreateDataArea(OLAPPooledConn,
                                                 "PMAIN",
                                                 OlapDataAreaIncludeB | OlapDataAreaSuppressNull,
                                                 OlapDataAreaOperatorNotEqual, 0.0,
                                                 OlapDataAreaOperatorNone, 0.0,
                                                 SourceYearList,
                                                 Cycle,
                                                 TargetVersion,
                                                 CompanyList,
                                                 OlapDataAreaBCells,
                                                 OlapDataAreaBCells,
                                                 CurrencyList,
                                                 OlapDataAreaBCells,
                                                 PLevel,
                                                 OlapDataAreaBCells,
                                                 OlapDataAreaBCells,
                                                 OlapDataAreaBCells,
                                                 OlapDataAreaBCells);
    OLAPDeleteDataArea(TargetData);

    // Get the Data to be copied
    OLAPDataArea SourceData = OLAPCreateDataArea(OLAPPooledConn,
                                                 "PMAIN",
                                                 OlapDataAreaIncludeB | OlapDataAreaSuppressNull,
                                                 OlapDataAreaOperatorNotEqual, 0.0,
                                                 OlapDataAreaOperatorNone, 0.0,
                                                 SourceYearList,
                                                 Cycle,
                                                 SourceVersion,
                                                 CompanyList,
                                                 OlapDataAreaBCells,    // InterCompany
                                                 OlapDataAreaBCells,     //CostCenter
                                                 CurrencyList,
                                                 OlapDataAreaBCells,     // Account
                                                 PLevel, // PLevel
                                                 OlapDataAreaBCells,    //PPSegment
                                                 OlapDataAreaBCells,    //PSSegment
                                                 OlapDataAreaBCells,    //PTSegment
                                                 OlapDataAreaBCells        //PDetail
                                                );

    // Loop through each value to be copied and write it
    foreach (OLAPCell cell in SourceData)
    {
        double Value = cell;
        string TargetPTIME = OLAPCellGetElement(cell, "PTIME");
        string PSource = OLAPCellGetElement(cell, "PSOURCE");
        string PCompany = OLAPCellGetElement(cell, "PCOMPANY");
        string PInterCompany = OLAPCellGetElement(cell, "PINTERCOMPANY");
        string PCostCenter = OLAPCellGetElement(cell, "PCOSTCENTER");
        string PCurrency = OLAPCellGetElement(cell, "PCURRENCY");
        string PAccount = OLAPCellGetElement(cell, "PAccount");
        string PLEVEL = OLAPCellGetElement(cell, "PLEVEL");
        string PPSegment = OLAPCellGetElement(cell, "PPSEGMENT");
        string PSSegment = OLAPCellGetElement(cell, "PSSEGMENT");
        string PTSegment = OLAPCellGetElement(cell, "PTSEGMENT");
        string PDetail = OLAPCellGetElement(cell, "PDETAIL");

        // Don't write zeros to save space in the cube
        if(Value!=0)
        {
            OLAPCellWriteNumber(OLAPPooledConn,
                                "PMAIN",
                                Value,
                                TargetPTIME,
                                Cycle,
                                TargetVersion,
                                PCompany,
                                PInterCompany,
                                PCostCenter,
                                PCurrency,
                                PAccount,
                                PLEVEL,
                                PPSegment,
                                PSSegment,
                                PTSegment,
                                PDetail);
        }
    }

    return true;
}

Ralf Zerfass

Hi Mike and Ibrahim,

using the dynamic functions and/or autocommit nearly had no performance effect, Runtime stays with about 20 seconds for 100.000 cells.

But I've found my big performance killer examining Mike's sample:

Before I read the value of the cell of the data area in the foreach loop again, using the OLAPCellReadNumber[Dynamic].

But this is completely **** and unnecessary, as the value is already there in the data area and can be easily retrieved with Mike's:

double value = cell;

That boost performance from 20 seconds up to 5 seconds!

Still not as good as the COPY command in O+, but a big step forward!

Great! Thank you.

Maybe there is some more potential using the XML-request?

Ralf

Copyright © 2025 Infor. All rights reserved.