TM1 Cube -> Export as text data in a TI

Post Reply
Aerouge
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

TM1 Cube -> Export as text data in a TI

Post by Aerouge »

Hello TM1 community.

I am somewhat struggeling with a process automation.

We have a cube with several rule calculated cells we wish to export. (Actually we want to write the data from an element where the rules are in effect to an element without rules (so a hardcopy of the rules results), so we can save time on reports and datamining activities).

Saving a view and doing the manual: "Rightclick Cube -> Export as text data" routine with a presaved view and afterwards importing the generated textfile finishes within 30 seconds (but requires manual interaction via the TM1 Architect).

Using the same view, utilized in the text export, we tried to have the process run via a TI process using AsciiOutput as a first test ... a really simple process.
This process has been running for 2 hours in our Dev-environment and didnt write a single dataset. CPU is working on a single core and no noticable increase in RAM usage.

Question 1:
Is there any way to utilize the same "Export as text data" option we have in the TM1 Architect via a TI Process?

Question 2:
If there isnt the option ... how could we export the results from a rules heavy element to a reporting element in a timely and automatable manner?

TM1 Version is PA 2.0

Thank you in advance for all ideas and insights you can provide!
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 Cube -> Export as text data in a TI

Post by lotsaram »

Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Aerouge
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

Re: TM1 Cube -> Export as text data in a TI

Post by Aerouge »

Hy lotsaram,

thanks for the links.

However I was hoping for a smaller solution than implementing a new dev-environment (Although this might be a good justification to look deeper into Cubewise & Bedrock, which I planned on doing anyway).

Regards Aerouge
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 Cube -> Export as text data in a TI

Post by Wim Gielis »

Indeed, use Bedrock if you don't want to have to roll your own processes, or if there could be an issue with your processes.

In case you don't want to use all Bedrock processes and only the ones that are / could be called from the master process, then have a look here:
https://www.tm1forum.com/viewtopic.php? ... 809#p75289

Copy only the relevant TI processes in your model and still make use of what has been programmed in Bedrock.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Aerouge
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

Re: TM1 Cube -> Export as text data in a TI

Post by Aerouge »

Hy Wim Hy Lotsaram,

thank you for your support. The bedrock processes are impressive!

Still for my current task at hand even those seem not enough (I did mention that the element in question has a lot of active rules).

I just had to abort the copy process after 2 hours runtime. The CPU usage was greatly improved by the bedrock process, the parallelisation worked like a charm and was easy to define, still the runtime remains extreme.

If I use the export to textfile function from TM1 Architect the same view exports within seconds.

So my original question remains... is there no way to utilize this TM1 Architect function via a TI-Process?
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: TM1 Cube -> Export as text data in a TI

Post by ascheevel »

Aerouge wrote: Wed Feb 19, 2020 12:23 pm So my original question remains... is there no way to utilize this TM1 Architect function via a TI-Process?
Using a TI with cube as source view and TextOutput or ASCIIoutput is the same as right-click -> export data in architect. If you're noticing such a difference in performance, it's possible the views you're using in the two options are different (thinking skip consolidations, rules settings). Why not post pics of the 2 view definitions you're using?
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 Cube -> Export as text data in a TI

Post by lotsaram »

bedrock will combine (that is modify) a view passed to it with the filter conditions. If you pass an empty filter that's the same as saying "the whole freekin cube".

Take care in defining the filter (this article explains how the filters work) and watch out for whether you want to include or exclude consolidations and nulls and the performance will be good.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Aerouge
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

Re: TM1 Cube -> Export as text data in a TI

Post by Aerouge »

Hy ascheevel,

I didnt post pics of different views, because I use the exactly same view in both cases (TextExport and TI) ... hence my utter confusion.

Please find the picture of the view definition attached.

Definition is really simple. "N-Elemente" are dynamic subsets containing all leaf-level elements. U_Datenart contains a selection of 5 different elements.

@Lotsaram I tried to recreate the view with a }bedrock.cube.data.copy process, but didnt manage yet to include the subsets (basically a dynamic subset containing all N-Elements of a dimension). As far as I understood the wiki leaving the filter for said dimension empty and consolidations set to 0 should result in all leaf level elements of a dimension (which is what I need)?

Or could I simply pass the pre-created view directly to the process? If I understand the wiki correctly I dont have the option to pass an existing view to the process?

Edit: Here is my Bedrock-ExecuteProcess Function:
ExecuteProcess ( '}bedrock.cube.data.copy'
,'pLogOutput',1
,'pCube','KSR'
#,'pFilter','U_Jahr ¦ 2020 & U_per_Monat ¦ per Jan & U_Projekt ¦ n-Elemente & U_Position ¦ n-Elemente & U_Verbund ¦ n-Elemente & U_KOST ¦ n-Elemente & U_Datenart ¦ n-Elemente'
,'pFilter','U_Jahr ¦ 2020 & U_per_Monat ¦ per Jan '
,'pEleMapping','U_Datenkategorie ¦ akt.Stand -> Ist'
,'pSuppressRules',0
,'pCubeLogging',0
);

When I switch the filter to include the "N-Elemente"-Subsets I get an ProcessError for the }bedrock.cube.view.create function saying:

"Fehler: Prolog Prozedurzeile (351): Das Element "n-Elemente" wurde in Dimension "U_Projekt" nicht gefunden."
Meaning: Element "n-Elemente" wasnt found in Dimension "U_Projekt"
Attachments
ExportView.JPG
ExportView.JPG (51.53 KiB) Viewed 5772 times
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: TM1 Cube -> Export as text data in a TI

Post by ascheevel »

Thanks for posting the pic, I didn't realize you were using the same exact view.

For your cube copy, you'll likely want to export to csv/txt first and then import the exported file instead of intra cube copy. With heavy rules as you say, you can expect slow performance trying to read and write from same cube in the one TI process. Is it possible your original TI with terrible performance had a CellPut action? I know in your original post you mentioned it was a simple AsciiOutput, but thought I'd ask.

}bedrock.cube.data.export
}bedrock.cube.data.import
Aerouge
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

Re: TM1 Cube -> Export as text data in a TI

Post by Aerouge »

ascheevel wrote: Wed Feb 26, 2020 5:28 pm Thanks for posting the pic, I didn't realize you were using the same exact view.

For your cube copy, you'll likely want to export to csv/txt first and then import the exported file instead of intra cube copy. With heavy rules as you say, you can expect slow performance trying to read and write from same cube in the one TI process. Is it possible your original TI with terrible performance had a CellPut action? I know in your original post you mentioned it was a simple AsciiOutput, but thought I'd ask.
Hy asheevel,

thanks for bringing this to my attention. The original process did indeed have a CellPut action, but one not connected in any way to the used view, hence I disregarded that. Still commenting out that CellPut has indeed the effect of speeding up the export to servicable times!

Case solved I guess.

Still the question about how to use subsets in bedrock process remains (out of curiosity and because I somewhat fell in love with the bedrock library).

Can (dynamic) subsets be used with bedrock processes?
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 Cube -> Export as text data in a TI

Post by Wim Gielis »

Hello,

Regarding the Bedrock question, is it related to what I posted here:

https://www.tm1forum.com/viewtopic.php?f=3&t=15134
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 Cube -> Export as text data in a TI

Post by lotsaram »

Bedrock filters take element names as arguments NOT subsets. Read the link on how bedrock filter strings work and you will be fine.

There is a parameter pSubN in the cube.view.create process which will automatically create a "all leaf elements" for any dimension not explicitly referenced in the pFilter parameter. However, you only really need to use this in situations where you are processing specific consolidated elements in some dimensions but wish to process only leaves on other dimensions. Generally when processing TM1 data we are processing leaf data only in which case setting ViewExtractSkipCalcsSet=1 (pSuppressZero=1 for equivalent bedrock parameter) is perfectly sufficient without assigning any subsets at all for additional dimensions that aren't explicitly filtered.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Post Reply