Export Cube data in CSV file, with all Months as columns

[SBK88]

Hi All,


I have a cube with dimensions Country, Product, Month, Year.
I want to export the data in csv file

Country Product Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
C1 P1 ------ ------- Values ------- -------
C1 P2 ------ ------- Values ------- -------
C1 P3 ------ ------- Values ------- -------
C1 P4 ------ ------- Values ------- -------
C1 P5 ------ ------- Values ------- -------
C2 P1 ------ ------- Values ------- -------
C2 P2 ------ ------- Values ------- -------
C2 P3 ------ ------- Values ------- -------
C3 P1 ------ ------- Values ------- -------
C3 P2 ------ ------- Values ------- -------
C3 P3 ------ ------- Values ------- -------
C3 P4 ------ ------- Values ------- -------
C4 P1 ------ ------- Values ------- -------


Need Help
Kindly suggest

[Wim Gielis]

Hello,

This topic has been discussed a number of times before.
Usually you would create a view on the cube as the data source, and in the Advanced > Data tab you would use CellGetN 12 times to get the individual month values.
Then you concatenate the result and do AsciiOutput.

[SBK88]

Thanks Wim

[SBK88]

It was working fine apart from one issue that I am getting duplicate records extracted in ASCII file.
Need help to remove those duplicate values.



Thanks

[Wim Gielis]

How can you have duplicate records?
Is your loop over months incorrect?
Data source cube view ?

[SBK88]

This is the code I am using .....

IF ( cFlag > 0 );
ASCIIOUTPUT(cFile, '----------------------', '----------------------', '----------------------', '----------------------', '----------------------', '----------------------', pYear, pScenario, '----------------------', '----------------------', '----------------------', '----------------------', '----------------------', '----------------------') ;
ASCIIOUTPUT(cFile, 'Company', 'Cost Center', 'SAG Cost Element', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') ;
cFlag = cFlag -1 ;
ENDIF;


vM1 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Jan', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM2 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Feb', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM3 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Mar', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM4 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Apr', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM5 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'May', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM6 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Jun', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM7 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Jul', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM8 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Aug', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM9 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Sep', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM10 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Oct', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM11 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Nov', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );
vM12 = NUMBERTOSTRING ( CellGetN(cCube, vCompany, vProfitCenter, vCurrency, 'Dec', pYear, pScenario, vCostCenter, vSAGCostElement, 'Value') );


ASCIIOUTPUT(cFile, vCompany, vCostCenter, vSAGCostElement, vM1, vM2, vM3, vM4, vM5, vM6, vM7, vM8, vM9, vM10, vM11, vM12) ;

[Wim Gielis]

Is your data source correct?

[SBK88]

Yes data source is correct

I have created a view with all leaf level elements.
Measure Dim - Value
Month Dim - Jan .... Dec

[qml]

Yes data source is correct

I have created a view with all leaf level elements.
Measure Dim - Value
Month Dim - Jan .... Dec

No, it's not 'correct'. No wonder you are getting duplicates. Think about it. You are cycling through all the months in the data source and then doing it a second time in your code. Change your data source so that there is only a single element selected from the Month dim.

A lot depends on how consistent and dense your data is. If having data on any given tuple for, say, Jan means that you are also guaranteed to have data for all other months then just have Jan in your source view's subset. If data is more 'random' than that you'll need to construct your source view in a cleverer way - your goal is to not miss any data, but also not to spend eternity cycling through empty cells in the cube. The best way to achieve that might be to use the total of all months as the sole element in the Months subset. But then your source view would need to be set up in such a way so that:
- zero suppression is on (this is the default anyway),
- consolidations are not filtered out,
- for all all other dimensions you use subsets that contain all the leaf elements only (because of the point above).

[whitej_d]

And as an extra note, if you use a 'Total of Months' and zero suppression is on, you need to make sure somehow you don't miss rows where the total of all months is zero, but the individual months are not. E.g. Financial data which has been posted against an account in Jan, but reversed out in Feb. You still need a complete set of monthly data.

If this can happen, you might be into the realms of creating an 'absolute' measure, and using 'Total of Months', 'Absolute Value' as the data source.

[qml]

the total of all months is zero, but the individual months are not

Hmm, I have seen this claim before, but it seems almost impossible in practice, doesn't it? Most decimal numbers cannot be represented exactly as binary floating point numbers (more on this can be found e.g. here) and so in most real-life scenarios the calculated result will be ever so slightly off.

Example:
Create a simple rollup A = B + C + D
Now input data:
B = 5.7
C = -5.6
D = -0.1
Now, A should calculate to 0, but will in fact be 0.00000000000000052736 due to the unavoidable floating point inaccuracy. And it will not be removed from a zero-suppressed view, even though for most intents and purposes TM1 will round the above result to 0 and display it as such.

Personally, I have never seen a practical case where a consolidation of more than two numbers would ever yield exactly zero and lead to zero suppression removing the result, but of course I guess there could conceivably exist such rare (?) cases. I would be happy to learn about them from someone who has encountered this problem.

[SBK88]

Thanks bro ....

"Total of Months" worked for me.
Luckily we dont have any such case (Parent -0 , Child-value)

[whitej_d]

Hi Qml,

I used to believe exactly the same thing, until I spent about a day going crazy wondering why the monthly totals in my csv export didn't reconcile with the monthly totals in my cube view!

I've just done a bit of random testing in v 10.2.2 FP6, and about 50% of the combinations I enter do end up suppressing the c-level.

Eg. (1 -1) and (0.5 + 0.5 -1) both result in zero suppression at the C-level, whereas your example, and (0.2, 0.4, -0.6) do not suppress the C-level. As far as I understand from the links in the article, the floating point accuracy issue only occurs in decimals, so any integer reversal would always be suppressed as it could always be represented accurately in binary and therefore sum to exactly zero. I think the same is true for some decimals, but the exact proportion of decimals which would cause issues I have no idea.

Another point to consider might be how many csv exports there are out there which do not contain a full set of monthly data. If you think about it, usually reconciliation and checking is usually done on the totals!

[whitej_d]

In fact, any decimal reversal with just 2 numbers will always sum to exactly zero and be suppressed. eg. 0.123456789 -0.123456789 so anything that is mis-posted to an unused account and then reversed out the following month, for example would be removed. Or a customer who gets a refund and has no other sales transactions. Also, there's quite few currencies around the world which would largely not work in decimals (JPY, THB) and therefore be at risk.

[qml]

I've just done a bit of random testing in v 10.2.2 FP6, and about 50% of the combinations I enter do end up suppressing the c-level.

Eg. (1 -1) and (0.5 + 0.5 -1) both result in zero suppression at the C-level, whereas your example, and (0.2, 0.4, -0.6) do not suppress the C-level. As far as I understand from the links in the article, the floating point accuracy issue only occurs in decimals, so any integer reversal would always be suppressed as it could always be represented accurately in binary and therefore sum to exactly zero. I think the same is true for some decimals, but the exact proportion of decimals which would cause issues I have no idea.

I have run some tests too and it looks there are (at least) two fairly obvious scenarios where the result of adding up non-zero binary floating point values will give exactly zero:
a) All components are integers (as you said, integers don't suffer from the precision problem).
b) All components come in pairs of +value/-value e.g. 1.234 - 1.234 = 0 or 1.1 + 2.2 + 3.3 - 1.1 - 2.2 - 3.3 = 0 (this makes sense as both numbers in such a pair have exactly the same binary representation except for the sign bit).

I can't really argue that any of them is impossible in practice, so it is indeed something worth being aware of. Thanks whitej_d.

[Wim Gielis]

This is the reason why I prefer to first have a view of level 0 months. Output in the same or a different cube which combinations should be picked up. A flag value of 1 would suffice. Then a second TI process exports the data using CellGetN and AsciiOutput based on a view containing your temporary flag values.

[paulsimon]

Hi

Two other approaches

A)

Create a consolidation called Chk for Period Data

Make it consolidate the 12 periods with weights from random numbers. Then even if you have a scenario like Jan = 2 and Mar = -2, which would give a Total Year of 0, the random weights should still mean that the Chk for Period Data consolidation gives a non-zero result if any period has data. Then as before create a view of all base level elements and the Chk for Period Data consolidation and for each record CellGet the individual periods.

B)

If the Period dimension is the last in the cube, or second to last and you only have a measure like Value ...

Create a Prev Value variable for each dimension in the cube.

Check for break of dimension - one of more dimensions being different to before

If so then output vP01value .. vP12value

If not then set 12 variables vP01value, vP02value, .. according to the Period Element, and store the Value in these

You need special logic to deal with the first and last record but you should be able to work that out.

Regards

Paul Simon

[Wim Gielis]

A)

Create a consolidation called Chk for Period Data

Make it consolidate the 12 periods with weights from random numbers. Then even if you have a scenario like Jan = 2 and Mar = -2, which would give a Total Year of 0, the random weights should still mean that the Chk for Period Data consolidation gives a non-zero result if any period has data. Then as before create a view of all base level elements and the Chk for Period Data consolidation and for each record CellGet the individual periods.

Interesting approach !

If we were to kill the consolidated element Chk, we would require a separate (small) TI process to delete the element in the Prolog tab ?

[lotsaram]

A)

Create a consolidation called Chk for Period Data

Make it consolidate the 12 periods with weights from random numbers. Then even if you have a scenario like Jan = 2 and Mar = -2, which would give a Total Year of 0, the random weights should still mean that the Chk for Period Data consolidation gives a non-zero result if any period has data. Then as before create a view of all base level elements and the Chk for Period Data consolidation and for each record CellGet the individual periods.

Interesting approach !

Interesting approach indeed. One that never occurred to me. I have never bothered to get into the nitty gritty of floating point decimals and what that means for zero supression. When faced with this requirement in the past I have opted for C) to have a dummy rule calculated leaf month with value of 1/0 fed from all 12 actual leaf months. Then in the view process the dummy month.

I like Paul's idea though and will probably use that in future. Any extra overhead of the dummy consolidation and maybe some element security to hide it from users and ensuring leaf subsets for processing on every dimension except month I think comes out positively versus the extra overhead of needing to create the rule.

[Wim Gielis]

I like Paul's idea though and will probably use that in future. Any extra overhead of the dummy consolidation and maybe some element security to hide it from users and ensuring leaf subsets for processing on every dimension except month I think comes out positively versus the extra overhead of needing to create the rule.

I would delete the consolidated element rather than adding element security.
DimensionElementInsertDirect (in the PROLOG), DimensionElementComponentAddDirect (in the PROLOG) and DimensionElementDeleteDirect (in the EPILOG) seem to be working very well.
This will be my future solution, thanks Paul.