exporting data by month
Posted: Thu Jul 02, 2009 7:22 pm
Hi all
Today, I needed to export data from a cube to a flat file. The cube has 15 dimensions, one of them being a Months dimension. There you have a consolidation for "Total months", containing 12 months as children. The customer wanted to have 12 columns for the months. In front of these the metadata.
Now, you could look at the consolidation for Total months. Create a view with TI code, holding the consolidations that are non-zero. Set this as the data source for a process, and write the ASCIIOUTPUT (a.o. 12 CELLGETN statements).
But Total months could be zero while the individual months are not... You would neglect these in the view. Looping through all cells is not an option of course.
What I did was:
- create a view containing the data at the level of the months (not the conso)
- create a temporary cube with the same dimensions as the original cube
- write a 1 in the correct combination in the temporary cube, for all combinations that should come in the text file (store them for instance on month January, doesn't matter, as long as you recall where you put them
)
- create a view containing the ones
- create the ASCIIOUTPUT statement
- destroy the temporary cube
It works very well.
Any thoughts on this? Could it be done better?
I thought about using rules, to populate a measure when the measure to be tracked, is different from zero. This would work but creates extra memory consumption. The export to a flat file is not very often. A view extract would list the values of the months one below the other as 1 long list; even not when the view itself is nicely laid out as 12 columns in the column area.
Greetz,
Wim Gielis
Today, I needed to export data from a cube to a flat file. The cube has 15 dimensions, one of them being a Months dimension. There you have a consolidation for "Total months", containing 12 months as children. The customer wanted to have 12 columns for the months. In front of these the metadata.
Now, you could look at the consolidation for Total months. Create a view with TI code, holding the consolidations that are non-zero. Set this as the data source for a process, and write the ASCIIOUTPUT (a.o. 12 CELLGETN statements).
But Total months could be zero while the individual months are not... You would neglect these in the view. Looping through all cells is not an option of course.
What I did was:
- create a view containing the data at the level of the months (not the conso)
- create a temporary cube with the same dimensions as the original cube
- write a 1 in the correct combination in the temporary cube, for all combinations that should come in the text file (store them for instance on month January, doesn't matter, as long as you recall where you put them
)- create a view containing the ones
- create the ASCIIOUTPUT statement
- destroy the temporary cube
It works very well.
Any thoughts on this? Could it be done better?
I thought about using rules, to populate a measure when the measure to be tracked, is different from zero. This would work but creates extra memory consumption. The export to a flat file is not very often. A view extract would list the values of the months one below the other as 1 long list; even not when the view itself is nicely laid out as 12 columns in the column area.
Greetz,
Wim Gielis
