All Dimensions in DataExport rows, Is it possible?

Since I’m getting ready for KScope, I thought I would throw a quick bit your way about the Dataexport calc script function. This question is asked a lot in various forms. Today the question on OTN was:

“I am doing a data export in a calc script. I have 12 dimensions in the cube. The output file is coming out fine except for one issue. The file does not write the name of the period member for which the data is being extracted. It writes the members of 11 dimensions but does not write the period.

If I fix on 1 month it will write a total of 12 columns 11 dimensions + 1 data column.
What I want is 13 columns (12 dimensions + 1 data column)

Am I missing something or is this how it is supposed to be?

Period is a dense dimension, The following are my export options:

SET DATAEXPORTOPTIONS

{DataExportDynamicCalc On;

DataExportDecimal 12;

DataExportRelationalFile On;

DataExportOverwriteFile ON;

};”

 If you want to see the thread on OTN, here it is:

My answer is the way DataExport works is to talk all of the dimensions except one dense dimension and make them row members, the final dimension, just like a grid retrieval is a column dimension. In the case of a DataExport the columns do not have a header.

In the Poster’s case, Periods was being set as the columns, but that is not necessarily always going to be the case. As one response suggested the poster could add:

DataExportColHeader Period;

That would insure that periods is being used as the columns, but would not give the poster what he wanted. In a 12 dimension cube, we will only get 11 row dimensions no matter what. The only way I know to get what the poster wants is to add a dense dimension to the cube. This dimension could be a single member dimension (just the dimension name). Since it is a single member it would not take any additional space or increase calculation or retrieval times. We could then set that dimension to be the column dimension and we would get all of our “real” dimensions as row members. If we can’t alter the existing cube, we could clone it add the dimension to the cloned cube, export (hopefully) only level 0 data, load it into the new cube and then use DataExport there to get the format of what we need. Granted that is extra work, but sometimes you do what you have to.

As a side note, you can make that new single member dimension useful Call it something like last_update and in your batch process, set the alias to be the data/time the cube was last updated.

Another possible solution would be to export all 12 periods every time so you always know what the columns are. If we need to really only get certain periods, we could do the DataExport to a relational table, export a sub variable and join to get the period we want.

I’ve seen similar questions about ordering the output and ignoring certain columns. Neither can be done. Dataexport, is fast because to grabs the blocks and outputs them. It does it in the order the dimensions exist in the outline (sparse first, then dense) just like an export. So be careful if you switch the order of dimensions or change something from dense to sparse or sparse to dense, the order in your DataExport will change. Because it is pulling the block, it has to write all dimensions.