Analytics Software

Hi,

Anyone have an example of a MDX query that can extract data to a tab delimited text file? We have 11 dimensions but only 4 dimensions will be extracted. For the other 7 dimensions we will take the 'total' value and not have to have it in results. So the format would be the below with the 'Account' and 'Activity' concatenated together with a '_' in between(not sure if this is possible within MDX or not). Top Row is header and 2nd row is fake data. The dimensions below are named: Facility, Account, Activity, Years, Periods.

Facility Account_Activity FY09 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Berlin OffSupp_Travel FY09 0 0 53 43 0 0 0 123 45 0 0 23

I'm not sure MDX can do this or not and have never used it so it's kinda a shot in dark.

Note: We have a DATAEXPORT calc script that we can use to extract data. However we can't figure out how to Concatenate 'Account' and 'Activity' into a single field. We also can't figure out how to make the results 'tab' delimited rather than pipe. Here is that code:


//ESS_LOCALE English_UnitedStates.Latin1@Binary


SET DATAEXPORTOPTIONS
{
DataExportDECIMAL 4;
DataExportRELATIONALFILE ON;
DataExportOVERWRITEFILE ON ;
DataExportDynamicCalc OFF;
DataExportDimHeader ON;
DataExportCOLHEADER "PERIOD";
};



/* Exports zero level data to specified directory */

FIX (
/* Will need to change these to proper subset of data you would like */
@Levmbrs("Account",0),
@Levmbrs("Resource_Location",0),
@Levmbrs("SAU_CAU_Facility",0),
@Levmbrs("SCCC",0),
@Levmbrs("CCCC",0),
@Levmbrs("Activity",0),
@Levmbrs("FY11",0),
@Levmbrs("Working",0),
@Levmbrs("Charge_Type",0),
@Levmbrs("Budget",0))

/* Will need to change this to fix the Unix OS path

This is the path the below DATAEXPORT will export the file to on the Essbase server:
/essbase111/products/essbase/essbaseserver/app/ */

DATAEXPORT "File" "|" "/essbase111/products/essbase/essbaseserver/app/br_test_export.txt" ;

ENDFIX