How to Extract Cube data into csv file using TI process

Post Reply
j.calibs
Posts: 11
Joined: Tue Apr 21, 2020 4:13 am
OLAP Product: IBM PAL
Version: IBM PAL 11.0
Excel Version: Version 1908

How to Extract Cube data into csv file using TI process

Post by j.calibs »

I'm still new to TM1 and I need help to extract data from a cube and save it as csv using TI process.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: How to Extract Cube data into csv file using TI process

Post by Alan Kirk »

j.calibs wrote: Thu Sep 17, 2020 3:39 am I'm still new to TM1 and I need help to extract data from a cube and save it as csv using TI process.
I would recommend investing in some training. I'm not having a go at you in saying that but this is TM1 101, so if you can't do this you're really going to struggle. There are a lot of consultants which can provide you with excellent training courses.

I'm not going to give you the fish, but I'll teach you how to catch them.
  • Create a view of the data that you want to extract. Save it as a public view. When you get a bit more confident you can do this in code but for now creating it manually is fine. I recommend doing it not through the cube viewer but by right clicking on the cube and using the "Export as text data..." dialog. If you want consolidations in your output make sure that the "Skip Consolidated Values" box is UNCHECKED. NEVER uncheck the Skip Zero / Blank values box (unless you have a very, very small selection of elements), or you will never be heard from again.
  • Create a new process and set the view as its data source.
  • Go to the variables tab. Set everything to a variable type of string and contents of Other.
  • The prolog tab will execute before the data source is opened; normally you could use this to create the source view, but in this case we don't need to.
  • If there is any code in the metadata tab, it will execute once per record. You can use this to add new elements, etc if you're doing an import. In this case you aren't.
  • Any code in the data tab will also execute once per row. Use the AsciiOutput function to write each row to your file. Note that if for whatever reason you have left one of your variable types as numeric, you will need to convert it before you write it (STR function, or NumberToString function) since AsciiOutput will only accept string values.
This is not the optimal way of doing things, but it's quick, dirty, effective and with training will lead you on to greater and more efficient things in due course.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
j.calibs
Posts: 11
Joined: Tue Apr 21, 2020 4:13 am
OLAP Product: IBM PAL
Version: IBM PAL 11.0
Excel Version: Version 1908

Re: How to Extract Cube data into csv file using TI process

Post by j.calibs »

Thank you for your reply! This helps me a lot!
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: How to Extract Cube data into csv file using TI process

Post by PavoGa »

Alan Kirk wrote: Thu Sep 17, 2020 4:11 am
j.calibs wrote: Thu Sep 17, 2020 3:39 am I'm still new to TM1 and I need help to extract data from a cube and save it as csv using TI process.
I would recommend investing in some training. I'm not having a go at you in saying that but this is TM1 101, so if you can't do this you're really going to struggle. There are a lot of consultants which can provide you with excellent training courses.



This is not the optimal way of doing things, but it's quick, dirty, effective and with training will lead you on to greater and more efficient things in due course.
You're such a softie, Alan.
Ty
Cleveland, TN
Post Reply