You might have seen this and have a workaround for this. However it is a surprise that it cannot work.
Now what am I talking about?
Long gone are the days where you were using jexport, all of us moved to DATAEXPORT. (I still sometimes use Jexport, it is a cool CDF)
This one is about me getting a call from a colleague “I’ve an issue with DATAEXPORT command, no matter what I FIX it exports all the children.”
I tried this on a modified version of Sample Basic.
I’ve Years in my outline, also Actual is a parent with two children.
You really don’t need to mention DataExportLevel ALL; that is the default. I got the required output.
I gave him a call and told him that “It’s is working fine for me.” He informed that “Scenario” is a sparse dimension on his cube. Modified Sample Basic, ran the calc and what do I see.
It is exporting the values of Actual’s children.
Not only it does that, it completely ignores the FIX statment and exports everything for Scenario. Is this documented somewhere.
DataExportDynamicCalc ON | OFFON—(Default) Dynamically calculated values are included in the export.OFF—No dynamically calculated values are included in the report.DescriptionSpecifies whether a text data export excludes dynamically calculated data.Notes:Text data exports only. If DataExportDynamicCalc ON is encountered with a binary export (DATAEXPORT BINFILE …) it is ignored. No dynamically calculated data is exported.The DataExportDynamicCalc option does not apply to attribute values.If DataExportLevel INPUT is also specified and the FIX statement range includes sparse Dynamic Calc members, the FIX statement is ignored.
It says DataExportLevel INPUT is specified and range includes Sparse Dynamic Calc members, then the FIX statment is ignored. Is that what I’m seeing? But I’ve (this is why you see the DataExportLevel set to ALL earlier) set it to ALL. Could this be a bug, it maybe.
A dynamically calculated sparse member, doesn’t involve in block creation, so if I try that with DataExportNonExistingBlocks ON; can it work?
The explanation for DataExportNonExistingBlocks is right after DataExportDynamicCalc
DataExportNonExistingBlocks ON | OFFON—Data from all possible data blocks, including all combinations in sparse dimensions, are exported.OFF—(Default) Only data from existing data blocks is exported.DescriptionSpecifies whether to export data from all possible data blocks. For large outlines with a large number of members in sparse dimensions, the number of potential data blocks can be very high. Exporting Dynamic Calc members from all possible blocks can significantly impact performance.
I think I just got lucky (I was going under the impression that it is a bug). It works with NonExistingBlocks
Update
I was working on this blog post yesterday and saw Glenn’s update on similar (same?) issue today morning (I’m still going ahead with this post). Seems like we both got the call 🙂
Furthur testing reveals that with DataExportNonExistingBlocks and DynamicCalc Sparse parent member it won’t export missing blocks.
Oh wait You don’t believe me. Let’s prove it.
My export must have 1941 lines (and should have a Row with all #Missing)
Nope I just have 1938 lines and nothing with all #Missing.
Nope no #Missing rows 🙂 (It’s strange I know, it think it is bug which happen to creep into from 11.1.2.3.501 – a good bug)
Did I prove Glenn wrong (no I didn’t), however I like to think that I did so that I can complete a virtuous cycle (Glenn torturing Cameron, him torturing me and if and only if I can torture Glenn). Well that’s lot of an ask 🙂 and lot going on
While I did not run the tests myself. I have a very happy consultant (and I will bet you a lot it is not the same person you helped) who showed me it did work in her case. It might be version specific as I don't know what version she was running. Hers is a small cube. She told me when she ran it on another cube that was much larger, it took forever to run thus my warning about exporting a lot of data.
Glenn, yes it is version specific I think it is from 11.1.2.3.501 version. I'll check in previous version and update where the bug started. A good to have bug I would say in this particular case 🙂
Thanks for this post Celvin. This helped me a lot today!!!