TM1 Cube -> Export as text data in a TI
-
- 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
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!
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!
-
- MVP
- Posts: 3654
- 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
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- 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
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
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
-
- MVP
- Posts: 3117
- 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
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.
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
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
-
- 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
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?
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?
-
- Community Contributor
- Posts: 287
- 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
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?
-
- MVP
- Posts: 3654
- 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
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.
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.
-
- 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
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"
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 (51.53 KiB) Viewed 5839 times
-
- Community Contributor
- Posts: 287
- 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
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
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
-
- 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
Hy asheevel,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.
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?
-
- MVP
- Posts: 3117
- 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
Hello,
Regarding the Bedrock question, is it related to what I posted here:
https://www.tm1forum.com/viewtopic.php?f=3&t=15134
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
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
-
- MVP
- Posts: 3654
- 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
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.
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.