Corona challenge: Export all data on an element

Post Reply
Wim Gielis
MVP
Posts: 2366
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Corona challenge: Export all data on an element

Post by Wim Gielis » Tue Mar 24, 2020 10:03 am

Hello all,

I hope that everyone is safe during these challenging Corona times !

As people tend to stay in house (mandatory by the government), I thought it might be good to use this time for a new TI process in Bedrock.
We can export the data on an element with '}bedrock.cube.data.export'. Often, customers ask me: 'Wim, can I remove these elements in dimension X ?'
Usually, you will need to create views and see where you have data, or export all data to a text file (using the interface of Architect for instance).
But if you have a lot of cubes with that dimension, it's not something practical. Likewise if you need to evaluate a number of elements instead of 1.

So I was thinking, does it exist in Bedrock ? Looping over all cubes that match a certain dimension, then use '}bedrock.cube.data.export' for that cube and done !
I don't think it exists, but in fact, it's not very difficult to do ourselves.
See attached processes, that uses a different lookup process (it's mine) and next to that also }Bedrock processes (not included).

Actually, I see 2 main use cases for this exercise:
1. exporting data on an element to have a kind of dump (multi-cube)
2. checking whether data exists on the element (in whatever cube)

For 1, consolidated elements could be useful too. For 2, probably, lowest level elements make sense the most.

However, now we have the challenge. I don't think that the Bedrock data export process supports PA alternative hierarchies.
If we want to export all data on a consolidated element, it can have multiple values depending on the hierarchy that we choose.
Hence, the cube output should be done for all hierarchies that use the element.

I see that the process '}bedrock.cube.view.create.bymdx' exists, it can be used since data will need to be exported through an MDX cube view instead of a regular view.

Anyone who fancies a challenge ? :-)
TECH_extract data on element.pro
(3.68 KiB) Downloaded 11 times
WG_CUBE_lookup_dimensions_in_cube.pro
(8.73 KiB) Downloaded 4 times
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Post Reply