Exporting Data with Dynamic Calc Sparse Members

What it is folks!

It has been a while since my last post, so sorry for that. I’ve been…you know, busy. But busy is good, right?!

As I type this blog, the song “Thank You (Falettinme Be Mice Elf Agin)” by Sly & The Family Stone is plying in my head. You know the one with the sweet guitar intro? Well, the reason for this is because I must thank Glenn Schwartzberg and his blog on helping me out with a data export issue.

Here’s the situation:

I was working with a client that wanted to extract budget data out of Enterprise Planning Budgeting Cloud (EPBCS), easy enough. However, they have data that rolls up to two sparse, dynamically calculated parent level members, and they would like to export the data with those two members. Ok, now not so easy.

When it comes to extracting data, there are a few ways to go about it in a non-technical way. For instance, you could use the data export functionality within EPBCS, or you could use a business rule. However, because we are in need of two dynamically calc’d sparse members, we need a flexible option that allows us the get the exact data we need. With that said, a business rule is a way to go!

Now I know what you’re saying, “how is a business rule not technical? Isn’t this blog supposed to be for functional users? Are you for real bro?”

I get it, yes, and YES! The beautiful thing about Essbase script language is that it requires no knowledge of query languages or minimal programming experience. You can learn how to write a business rule in less than 10 minutes (I’ll save that for another blog post)!

Here is the script for the data export business rule:

/********************************************************************************

DATE:  September 2018

DESCRIPTION: Export ALL Level 0 data by Years and Scenario

********************************************************************************/

/* Set the export options */

SET DATAEXPORTOPTIONS

{

DataExportLevel All;

DataExportDynamicCalc ON;

DataExportNonExistingBlocks ON;

DataExportDecimal 2;

DataExportPrecision 16;

DataExportColFormat ON;

DataExportColHeader Period;

DataExportDimHeader ON;

DataExportRelationalFile OFF;

DataExportOverwriteFile ON;

DataExportDryRun OFF;

};

 

FIX(@Relative(“OFS_Financials Accounts”, 0),

{ExportData_Scenario},

{ExportData_Year},

  “MED_All_Final”,

@Levmbrs(“Entity”,0),

@Relative(“YearTotal”, 0),

“MED_Total Plan”,

@Levmbrs(“Project”,0))

 

DATAEXPORT “File” ” ” “/u03/lcm/Data_Export_by_Year.txt” “0”;

ENDFIX

A few things. First, the two members highlighted and underlined in red are the dynamic sparse members. Second, when I first created this rule, I had DataExportNonExistingBlocks turned off. The reason why is because Oracle documentation states that “for large outlines with a large number of members in sparse dimensions, the number of potential data blocks can be very high. Exporting Dynamic Calc members from all possible blocks can significantly impact performance.”

Pffbt, don’t need to tell me twice! The client has a lot of members on sparse dimensions and performance is crucial. However, every time I ran the rule, I kept getting the level 0 members of the parents. After about a million tries, I finally turned to the web for help, and lo and behold I found Glenn’s blog.

According to Glenn:

…there is no block for a dynamically calculated member. In this case, the block is calculated upon retrieval. By default, the fix would bypass empty blocks.  I looked at the set statement again and it hit me. There was no statement for emptyblocks.

Bingo! By turning DataExportNonExistingBlocks on (and fixing on a small section of data) I was getting the sparse parent members and not their level 0 children.

The downside with all of this, the rule take an awhile to run. But, the client gets the exact data they need to upload to their ERP so success!

Thanks, Glenn and Thank You (Falettinme Be Mice Elf Agin)!

Till next time!