So what if we would like to export data to a file via CalcScript DATAEXPORT function, and then use the output file to be feed to FDMEE? How do we do that? Easy: call it from a Maxl script, wrap that into a batch file, and call it from the BefImport, so the file is there at Import step. All this was done using the knowledge Francisco has shared a while ago on the OTN.

But let’s look into the exact steps to take:
1) Create a simple Maxl script (which I encrypted because reasons, and so it’s called mxl.mxls):

2) Create a CalcScript that looks like something similar to this (and named Export, as seen above):

Why like that? ‘Cause I set up in the DLR to use the file output.txt in the inbox:

3) Then make a batch file (mxl.bat), with the following content – & just feeding Essbase back his private key, so it can decrypt the script for itself:

4) As the last step put the following code as BefImport script:

Quick and painless!

I was helping out a colleague of mine so I was investigating why a script that would export data in 11.1.2.2 was not exporting anything in 11.1.2.4. Started by only using

Essbase would respond with:
Received Command [Calculate] from user [admin@Native Directory] using [EXP.csc]
DataExport detects Dynamic Calc member [KittenMbr] in the range. Exporting Dynamic Calc data may slow down performance.
This DataExport operation will export data from existing blocks only. Any FIX on sparse dynamic calc members will be ignored. Use DATAEXPORTNONEXISTINGBLOCKS ON option to export data from all potential blocks.
Data Export Completed. Total blocks: [15]. Elapsed time: [0.023].
Total Number of Non-Missing cells exported: [60].

Now this is a cube with many Sparse members on DynamicCalc – I got no influence on this part -, which was mitigated in the old version by using:
DataExportDynamicCalc ON;
DATAEXPORTNONEXISTINGBLOCKS OFF;

Same result. Hmm interesting.

After 2 minutes on Metalink, we were able to find Document 2123909.1, where Oracle development explains changed the behaviour. Short story: you are supposed to use them as:
DataExportDynamicCalc ON;
DATAEXPORTNONEXISTINGBLOCKS ON;

After using it as above, I get the response from Essbase:
Received Command [Calculate] from user [admin@Native Directory] using [ESC.csc]
DataExport Warning: This DataExport operation will export a total of [3958416] blocks. Exporting dynamic calc members from all blocks has significant performance overhead. Use DATAEXPORTNONEXISTINGBLOCKS OFF option to export data from existing blocks only.
Data Export Completed. Total blocks: [45]. Elapsed time: [508.234].
Total Number of Non-Missing cells exported: [180].

Do I got to add be careful with this? if nothing else is an indication, just look at how much more time it took for export of still such a small amount of data.

Here is a tease of a session I encourage you to join on COLLABORATE 15, as I help with the prep work a bit 😀

Collaborate

I will post some tricks you might need to this on your own, if you want to try it out, after the session tho 😀

As promised the lessons learned:
I) Essbase Application on which we are reporting should be rolling up possibly to top (Gen1).
II) Name Generations in Essbase – for less confusion
Essbase_Named_Gen
III) Don’t flatten account dimension – keep “Fact” – and change setting on Account dimension
Accounts
IV) Tag one dimension as a time dimension
Period
V) (“the other option”): Can be done trough Data Model (BI Publisher) created from SQL statement from reports created in Analysis (copied from under “Advanced” tab), example of SQL statement (which you can already adjust):
GetSQL

SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8, s_9, s_10, s_11, s_12 FROM (
SELECT
0 s_0,
"PRS"."Account"."Accounts" s_1,
"PRS"."Department"."G2: AllDepts and Settings" s_2,
"PRS"."Department"."G3: Department" s_3,
"PRS"."Product"."G2: AllProd and NoProd" s_4,
"PRS"."Product"."G3: Product" s_5,
SORTKEY("PRS"."Account"."Accounts") s_6,
SORTKEY("PRS"."Department"."G2: AllDepts and Settings") s_7,
SORTKEY("PRS"."Department"."G3: Department") s_8,
SORTKEY("PRS"."Product"."G2: AllProd and NoProd") s_9,
SORTKEY("PRS"."Product"."G3: Product") s_10,
"PRS"."IncStmt"."Fact" s_11,
REPORT_AGGREGATE("PRS"."IncStmt"."Fact" BY "PRS"."Department"."G2: AllDepts and Settings","PRS"."Department"."G3: Department","PRS"."Product"."G2: AllProd and NoProd","PRS"."Account"."Accounts") s_12
FROM "PRS"
WHERE
(("Year"."Year - Default" = 'FY16') AND ("Scenario"."Scenario - Default" = 'Budget') AND ("Version"."Version - Default" = 'Working Version') AND ("Period"."G2: Total Year and BegBalance" = 'YearTotal') AND ("Product"."G2: AllProd and NoProd" <> 'NoProd') AND ("Department"."G2: AllDepts and Settings" <> 'Settings') AND ("Account"."Accounts" IN ('Price', 'Volume', 'Revenue', 'COGS', 'Discount', 'Bonus', 'SalesExp', '')))
) djm ORDER BY 1, 10 ASC NULLS LAST, 8 ASC NULLS LAST, 9 ASC NULLS LAST, 7 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

A) Working with Data Model
Creating a Data Set
Create_Data_Model

In the Data tab:
I) Click View
II) Click “Table View”
III)(Optional) Either in SQL or in tab Structure change the column names to make it easier to identify in Mobile designer
Create_Data_Model02

Ready to save and use

Normal by using Subject Areas
Create_Mobile_BISA

Using Bi Publisher Data Model
Create_Mobile_BIPublisher

And this is how it looks like at end:
Mobile