Please create a deep dive video on the new App Engine Export features in 2021.11

Robert Thomas

Specifically, step by step examples of using OLAPExportDataArea and OLAPExportHierarchy related App Engine APIs.

Find more posts tagged with

Infor EPM Discussions

Comments

martin-reischl

Hi Bob,

I don't have a deep dive but I have some sample code for OLAPExportHierarchy (below). I also tried OLAPExportDataArea but I am getting an error message. Waiting for support.

Dimension export works fine (and fast) with code like this (only added a few attributes for demo):

#define EngineVersion 5.0
#define RuntimeVersion 5.0

bool OLAPExportDcompc(string alias)
@Description: "Export dimension DCOMPC with some attributes to demo the export hierarchy function";
@Category: "Test";
@Returns: "true or false";
@Parameter[alias]: "OLAP connection alias";
{
OLAPConnection con = OLAPCreateNamedConnection(alias);

OLAPDimension olDim = OLAPGetDimension(con, "DCOMPC");

OLAPHierarchyExportConfiguration conf = OLAPCreateHierarchyExport("staging", "DIM_DCOMPC", "ID", "ElementType", "OrderPosition");
OLAPHierarchyExportSetRelationshipExportExternal(conf, "DIM_DCOMPC_Parents", "ID", "ParentID", "Weight", true);

OLAPHierarchyExportAddAttributeMapping(conf, 1, "Name", "ATT_Name");
OLAPHierarchyExportAddAttributeMapping(conf, 1, "Country", "ATT_Country");
OLAPHierarchyExportAddAttributeMapping(conf, 1, "LOC", "ATT_LOC");
OLAPHierarchyExportAddAttributeMapping(conf, 1, "Disabled", "ATT_Disabled");
OLAPHierarchyExportAddAttributeMapping(conf, 1, "DefaultPC", "ATT_DefaultPC");

OLAPExportHierarchy(olDim, "DCOMPC", conf);

return true;
}

John Robson

Hi

Here is a sample I plagiarized for OLAPExportDataArea - with comments below.

#define EngineVersion 5.0
#define RuntimeVersion 5.0

int ExportDataArea()
@Description: "Export a data area to a table in my staging database";
@Category: "Training";
@Returns: "The number of values in the data area or -1 if the process fails.";
{

    //Output SQL table definition - not part of the process
    //The numeric precision seemed fine for this sample but may need tweaking
    /*
    CREATE TABLE [dbo].[AE_Out1]
        (
            [Product] [nvarchar](50) NULL,
            [Region] [nvarchar](50) NULL,
            [Channel] [nvarchar](50) NULL,
            [Measure] [nvarchar](50) NULL,
            [Value] [numeric](18, 4) NULL
        )
     */

    int errorCode = 0;
    string errorMessage = "";
    int counter = 0;  //Counter for records in the data area

    // 1. Create connections to an SQL using V12 syntax.
    string sqlAlias = "Staging"; //Staging is the name of the connection from the Data Connections dashboard
    SQLConnection sqlcon1 = SQLCreateNamedConnection(sqlAlias);

    // 2. Connect to OLAP using a pooled connection, so no need to disconnect.
    string database = "BestPracticesOLAP"; //The connection must exist in the Data Connections dashboard
    OLAPConnection OLAPCon1 = OLAPCreatePooledNamedConnection(database);

    //Export will be from the Analysis Cube which has has 7 dimensions in this order: Period;POS;Product;Region;Channel;Measure;Valtype
    //Create a list of StringLists to specify the elements for our data area.
    StringListList dataAreaElements = CreateStringListList();
    Append(dataAreaElements, ConvertToStringList("2005_Q1_01"));             // Period - one base element
    Append(dataAreaElements, ConvertToStringList("Bodyshops"));                // POS - one base element
    Append(dataAreaElements, ConvertToStringList(OlapDataAreaBCells));        // PRODUCT - ALL base elements
    Append(dataAreaElements, ConvertToStringList(OlapDataAreaCCells));        // REGION - ALL parent elements
    Append(dataAreaElements, ConvertToStringList(OlapDataAreaBCells));        // CHANNEL - ALL base elements
    Append(dataAreaElements, ConvertToStringList(OlapDataAreaAllCells));    // MEASURE - ALL base elements
    Append(dataAreaElements, ConvertToStringList("Budget"));                // VALTYPE - one base element

    //Create data area - with null supression. Calculated elements won't be included.
    OLAPDataArea dataArea = OLAPCreateDataArea(OLAPCon1, "Analysis", OlapDataAreaSuppressNull, OlapDataAreaOperatorNone, 0, OlapDataAreaOperatorNone, 0, dataAreaElements);

    //Define the type of export - here a single value column is user as measures vary by row.
    OLAPDataAreaExportConfiguration singleValueExportConfiguration = OLAPCreateDataAreaExportSingleValue(sqlAlias, "AE_Out1", "Value"); //AE_Out1 is the name of my SQL Table. "Value" is the name of the SQL column which will store numeric values
    //Add output columns. IMHO it makes sense to add a column any time more than one dimension element is selected. For other dimensions it's optional. Names aren't case sensitive.
    OLAPDataAreaExportAddColumnMapping(singleValueExportConfiguration, "PRODUCT", "Product");
    OLAPDataAreaExportAddColumnMapping(singleValueExportConfiguration, "REGION", "Region");
    OLAPDataAreaExportAddColumnMapping(singleValueExportConfiguration, "CHANNEL", "CHANNEL");
    OLAPDataAreaExportAddColumnMapping(singleValueExportConfiguration, "MEASURE", "Measure");

    //Use a try catch wrapper as is if you mistype anything your process won't work.
    try
    {
        NotifyDebug("Export data area.");
        OLAPExportDataArea(dataArea, singleValueExportConfiguration);
        NotifyDebug("Data area exported.");
        //Count records in the data area to check against the number of rows in the SQL output table.
        //The numbers should match and the process returns the record count.
        foreach(OLAPCell cell in dataArea)
        {counter = counter + 1;}
    }
    catch (errorMessage, errorCode)
    {
        counter = -1;
        NotifyError("Data Area export failed: " + errorMessage + " (" + ToString(errorCode) + ")");
    }
    SQLDisconnect(sqlcon1); //Disconnect from the SQL Database
    return counter;
}

Comments

The penny dropped for me when I realized that the SQL table got populated with one row for every OLAPCell in my dataarea. For that reasons I include columns for all the dimensions where there isn't a fixed element in my output table.

For it to work you must have data connections to SQL and OLAP in your application and you need to create the output SQL table.

The script is based on the Samples database also known as BestPracticesOLAP.

martin-reischl

Thank you John,

this example is very helpful indeed. I just have a few comments:

1. You don't have to connect with SQL. The OLAP server will handle this internally.

2. I think your data area will not return any data. You request only B level values but have only C level elements from the CHANNEL dimension. This is just an implementation detail

3. I agree with your observations on creating only the columns which have more than one element in the data area result. However, this will be valid only if you have one table per data area. I often have just one table per cube, then it may be helpful to have all dimensions in the columns, so I'll know the full source of the values at any time.

4. Instead of using numeric(18,4) for the value column, you might just use float. Which is exactly what OLAP keeps if it is a numeric cell.

Regards

Martin

John Robson

Thanks Martin.
On 2, I did get Data (700 or so rows) but that's maybe because I tweaked my data.
On 3. For many use cases I agree. You suggestions makes the table idiot proof.
On 4. Thanks. Good point. I am a little rusty on numeric SQL data types but float aligns perfectly with OLAP.

I don't know about you but I was impressed with the speed.

Nick Gladkov

Hi Bob, on top what is already shared, can you check attached archive with more examples? We are going to release sets of examples rather than videos as there are too many use-cases to share all of them. But hopefully you will find these examples useful!

[View:/cfs-file/__key/communityserver-discussions-components-files/548/0083.OLAPExport.zip:93:80]

unknown

Hi Nick,

first of all, thanks for the Examples, I think they are very helpful in terms of working closely to "Best-Practices".

I do have a question, however:
Previously (before v12) we used the VisualBasic DLLs to do some Bulk Export of Cube Data (using Data Area Definition etc) - quite similar to the Export Functions you provide now for v12.

When it comes to larger Cubes (> 10 Mio Base Cells) I have the feeling the Export is rather slow compared to what we are used to before v12.

We used to be able to export a Cube with around 35 Mio Base Cells (8 Dimensions) in about 15-20 Minutes.

Now the same Cube using the Export Functions of your example takes about 1,5 - 2 hours to complete.

If we instead of the Export Functions insert the Cells from the DataArea directly to an In-Memory Table and afterwards do a Bulk Insert to a target table in an SQL Connection this is a little faster but still in most cases over 1 hour.

Do you have experiences on exporting larger cubes as well?
Because to me it seems to scale linear, which if you export only a few thousand values is fine (about a few seconds) but if the performance for larger cubes is about 4 to 5 times (or even up to 10 times) slower than with our previous Bulk Export using the Visual Basic DLLs - we feel this is insufficient for current customer implementations.

Also, do you plan to improve / update these functions in the near future?

Thanks and Br,
Lukas

Nick Gladkov

First, thanks a lot for this detailed answer, this really gives us am impression of how system is being used.

As regards to your question - we have a surprise coming last few months the team was working on the performance improvements and results that I have with the export to SQL is close (so far it looks like it might be faster, but we need to confirm with the example of ~15 mln values export). So definitely worth checking this space and more changes will come very soon.

If you can share model with 35 mln values - this will be even bette, so we can check and compare it now (maybe better to continue it in the mail - nick.gladkov@infor.com)

michael-gies

Hi Nick,
Exporting hierarchies seems to be allowed only into the staging database. This makes sense for the cloud environments. Will there be an option to export in other SQL databases for an on-premise installation as well. I am thinking of a scenario where the customer uses Designer and wants to pre-fill various Designer tables making use of the Reverse Engineering functionalities.
Regards
Michael

Copyright © 2025 Infor. All rights reserved.