Page 1 of 1
custom ASCIIOutput
Posted: Mon Jul 14, 2008 3:42 pm
by kbogies
Does anyone know how to export via ASCIIOutput with the measures dimension going across the flat file? The data set is too large to build a custom view via the cube viewer, so i'm looking for an 'across' statement. Syntax is below.
ASCIIOutput('\\tm1server\Data\Extract.csv', Vyear, scenario, channels, product, type, measure, Vmonth, Value);
Thanks!
Kbogies
Re: custom ASCIIOutput
Posted: Mon Jul 14, 2008 3:57 pm
by Martin Ryan
Right click on the cube and click "Export as ASCII data..." Just click Export to export the entire cube.
Is that what you mean?
Martin
Re: custom ASCIIOutput
Posted: Mon Jul 14, 2008 4:02 pm
by Steve Vincent
you mean something like;
Code: Select all
m1 m2 m3
a b c 10 20 30
b c a 15 5 35
Where that would normally be 6 seperate lines on the ASCII file?
Not got time to check now, but if that's what you are aiming at i'm guessing an IF / ElseIF loop would be needed, depending on how many measures you have as the embeded IF's are limited to 16. Never tried before, although i have passed such a file back in via TI. 320+ fields, now that was fun!
Re: custom ASCIIOutput
Posted: Mon Jul 14, 2008 5:16 pm
by kbogies
Steve, I think you're understanding correctly what i'm looking to do, however, wouldn't an IF statement work as a filter (not what i want)? Just to clarify, this needs to be a TI process to schedule as a regular chore. Currently I have the statement below in the Advanced Data section, but need to add an "across measures" or similar statement. The end user needs it to be reformatted to have the measures dimension listed across the top rather than one of the columns.
ASCIIOutput('\\tm1server\Data\Extract.csv', Vyear, scenario, channels, product, type, measure, Vmonth, Value);
there are 7 dimensions (Vyear, scenario, channels, product, type, measure, Vmonth) and when exported they appear as 7 columns for each dimension and 1 column for the data. The desired result is 6 columns for the dimensions + X number of measure columns with data filled below... similar to a pivot table look.
Thanks,
Kbogies
Re: custom ASCIIOutput
Posted: Mon Jul 14, 2008 7:11 pm
by Mike L
The problem you see is that TI wants to process one intersection at a time, and you want to output a row from a two dimensional view. There is a way, but it is a little cimbersome:
This assumes the elements of your measure dimension are static, and there is one element, call it measure1, that is aguaranteed to be populated whenever any element in the row is populated.
Set up a view of measure1 as your data source and use additional DB() functions to pull each other measure, like this:
ASCIIOutput('somefile.csv', Vyear, scenario, channels, product, type, Vmonth, value, DB(year, scenario, channels, product, type, 'measure2', month), DB(year, scenario, channels, product, type, 'measure3', month), ... etc. ...);
You can DB() the other values into separate variables in separate statements to make the ASCIIOuput statement manageable.
Re: custom ASCIIOutput
Posted: Mon Jul 14, 2008 8:46 pm
by kbogies
There is no measure which is guarenteed to be populated for each intersection, so i guess that won't work
I'll keep my eyes open for additional suggestions.
Thanks,
Kbogies
Re: custom ASCIIOutput
Posted: Mon Jul 14, 2008 10:51 pm
by Mike L
Ok. That caveat only applies if you are using a zero-suppressed view. If the cube is not sparse you could turn off zero suppression and output the whole thing. It sounds like it is sparse though, so that would give you zillions of empty rows.
Another approach would be to create a new measure guaranteed to have the desired property. It could be a consolidation of other measures, or it could be derived by a rule. This approach makes clutter, and in the case of rules it could hurt performance.
Perhaps your best option would be to process this in two passes:
1) A preliminary process using a view like your original and building a temporary cube without a measures dimension, using CellPutN to send a 1 to each place the view returns a value.
2) A main process using a view of the temporary cube, with CellGetN formulas as above to pull each measure from the main cube for ASCIIOutput. (Yes, I meant CellGetN not DB. Don't get me started on naming things differently in each component of TM1.)
Re: custom ASCII Output
Posted: Tue Jul 15, 2008 9:04 am
by Steve Vincent
Mike is right, you must find a way to get "a" measure populated otherwise you'll never have a datasource that will pick up all of the entries. The only easy way to do that is to consolidate all the measures and use just the consolidation in the datasourceview view. You can then use Cellgen calls to populate a variable for each measure;
Code: Select all
M1 = Cellgen(year, scenario, channels, product, type, 'measure1', month);
M2 = Cellgetn(year, scenario, channels, product, type, 'measure2', month);
M3 = Cellgetn(year, scenario, channels, product, type, 'measure3', month);
etc
And then output the file using;
Code: Select all
ASCII Output('somefile.csv', Year, scenario, channels, product, type, Vmonth , M1 , M2 , M3 );
It will only work tho if you have a consolidation for ALL the measures tho.
Re: custom ASCIIOutput
Posted: Thu Dec 09, 2010 10:55 am
by Eije Wiersema
Hi,
This is a really late reply, but this can be done quite easy. If you have for example three dimensions Account, Entity and Period you can add 12 new variables M1, M2, M3,M4,M5 etc in the TI.
Then put the following formula in for M1=IF(Period@='Jan',Value,'0') and for M2=IF(Period@='Feb',Value,'0') and so on. This ofcourse also can be done with a measure dimension.
Then the asciioutput will look like AsciiOutput('C:\export.cma',Accounts,Entity,M1,M2,M3,...);
Maybe somebody still can use it.
Cheers,
Eije