Loading data in Cube with Picklist TM1

Post Reply
Ana Ventura
Posts: 10
Joined: Fri Sep 04, 2015 5:56 pm
OLAP Product: SSAS, TM1
Version: 10 2 2
Excel Version: 2013

Loading data in Cube with Picklist TM1

Post by Ana Ventura »

Hello everyone,

I want to load data into a TM1 cube with TI process, but my cube has a dimension with picklist elements.

How can I load the data from my table in sql to the cube with a picklist? The data that is on the picklist exists in my fact table that i want to load.

Do you have any idea that i can do this process?

Thank you for your help,
Ana Ventura
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: Loading data in Cube with Picklist TM1

Post by Wim Gielis »

Just use the normal way of loading the data.
TM1 pickists will not validate the incoming data, though.
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
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Loading data in Cube with Picklist TM1

Post by Drg »

Make a picklist based on a dimension or a subset(dynamic)

Option 1
Before uploading data in cube, upload a dimension
After filling the cube cell's

Option 2
As the cube is filled with data, check have element in the dimension(dimix func) if there is no element, then insert it into the demension DIRECTLY and then fill the cell of the cube.
Ana Ventura
Posts: 10
Joined: Fri Sep 04, 2015 5:56 pm
OLAP Product: SSAS, TM1
Version: 10 2 2
Excel Version: 2013

Re: Loading data in Cube with Picklist TM1

Post by Ana Ventura »

Thank you your for answer.

I have a table with columns that represent my dimension elements, for example, i have a dimension RUB with two elements:
Services;
Client;

This elements has a picklist with data from other dimension.
My table in database has two columns Services and Client with the information that i want to load in my TM1 cube in this elements with picklist. I want to load data from my table into picklist.

When i inserted the query in TI process, in my variables i have more elements that exists than in the cube, so i have an error. So how i can actualize my picklist elements on TM1 cube?

So, how i can load the data this two columns in my table on my TM1 cube?

Thank you,
Ana Ventura
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: Loading data in Cube with Picklist TM1

Post by Wim Gielis »

Please can you provide a clear example with screenshots of table, cube and desired output ?
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
Ana Ventura
Posts: 10
Joined: Fri Sep 04, 2015 5:56 pm
OLAP Product: SSAS, TM1
Version: 10 2 2
Excel Version: 2013

Re: Loading data in Cube with Picklist TM1

Post by Ana Ventura »

yes sure.

I have a table with: Services, client (image on attachment table.png) this table has a information that i want to load in Rubricas, Tipo Serviço that this elements are a picklist in my TM1 cube, you can see this in elements.png.

So i want to create a TI process to load this columns and update cube with a picklist?
The information that i have in my table i want in the picklist. It's a process to load data in cube.

My output is to load data that is in the table in the cube, for example, i Want to load "OS" (information in table) in a element "Tipo Serviço".

Thank you for your help,
Ana Ventura
Attachments
elements.PNG
elements.PNG (9.71 KiB) Viewed 11137 times
table.PNG
table.PNG (37.56 KiB) Viewed 11137 times
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Loading data in Cube with Picklist TM1

Post by tomok »

Can you be any less clear? Let's forget about the picklist for the moment because a picklist is nothing more than a user interface tool to control what a user can MANUALLY enter into a cube view or websheet. The picklist is going to have absolutely no bearing on what you can load into Tipo Servicio via TI because a TI process can write whatever it wants, regardless of whether you have a picklist on that measure. If you simply want to load the values from table.PNG into your cube (into the Tipo Servicio element) then just write a simple CELLPUTS statement in there and be done with it. If your intent is to load the values from table.PNG into the cube AND ALSO update your existing picklist then just add a DIMENSIONELEMENTINSERT or SUBSETELEMENTINSERT command in there, depending on what the picklist is based on.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Loading data in Cube with Picklist TM1

Post by Drg »

If i understand you correct you need this:

In prolog write this code

Code: Select all

rowcount=0;
In metadata or data
write this code

Code: Select all

#your sql table variable 
#Services as v1
#Client as v2
#hand made rowcount
rowcount=rowcount+1;
#here past cod eto update Tipo service piclist dimension

#insert data to cube
CELLPUTS( v1 , 'CUBE NAME' , NUMBERTOSTRING(rowcount) , 'Tipo Servico' );
CELLPUTN( v2 , 'CUBE NAME' , NUMBERTOSTRING(rowcount) , 'Client element' );
Ana Ventura
Posts: 10
Joined: Fri Sep 04, 2015 5:56 pm
OLAP Product: SSAS, TM1
Version: 10 2 2
Excel Version: 2013

Re: Loading data in Cube with Picklist TM1

Post by Ana Ventura »

Hello,
Thank you for answer. but i have a doubt in writing my query in prolog or metadata section.
The query is like:
ODBCOpen( 'DSNname', ' userid' , 'passwd ' );

Squery = 'Select Services, client from table';

How i can associate Services and Client on variables?

In addition to these columns (Service, Client) I have more information on the other dimensions in the table that are in the cube, such as 3Item, 2organizationalStructure. How i can map this column at the TI process?
On attachement you have the all table structure (my data that i want to load into in cube that it has a picklist are: Rubrica, Tipo_Servico
The other columns represent the others dimensions in cube, the OrganizacionalStructure at table is the dimension in filters in cube, and cen is to in filter in cube.


Thank you for your help,
Attachments
table_elems.PNG
table_elems.PNG (53.55 KiB) Viewed 11036 times
cube_view.PNG
cube_view.PNG (5.93 KiB) Viewed 11036 times
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Loading data in Cube with Picklist TM1

Post by tomok »

This sounds like just basic loading of data into a TM1 cube from an ODBC data source. I suggest you consult the documentation as this is covered more than adequately there. Basically, you just create a new TI with ODBC as the data source, plug your query into the query box, which will generate the variables and then you can use the wizard to finish out the rest of the process. The picklist has nothing to do with it unless you are attempting to update the picklist values at the same time (which I asked you earlier and you didn't respond). If you are then you'll need to add the extra code to do that as well.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
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: Loading data in Cube with Picklist TM1

Post by Wim Gielis »

tomok wrote: Thu Aug 03, 2017 11:15 amIf you are then you'll need to add the extra code to do that as well.
In which case you need to join the 2 tables in the query box (or in the relational database), or another approach is to use a second TI process.
I agree with Tom: please read the documentation. ODBCOpen has nothing to do with this basic exercise of data loading.
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
Ana Ventura
Posts: 10
Joined: Fri Sep 04, 2015 5:56 pm
OLAP Product: SSAS, TM1
Version: 10 2 2
Excel Version: 2013

Re: Loading data in Cube with Picklist TM1

Post by Ana Ventura »

Hi everyone,

Thank you very much for your help. I'm going to read the documentation.
I know how i can do the simple process to load data in tm1 cube with odbc data source, but this case i have some data columns on the table in database and i want to load the into the TM1 cube, and this cube has a picklist with information that i have on table.

I will follow your advice.

Thank you,
Ana Ventura
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Loading data in Cube with Picklist TM1

Post by Drg »

Ana Ventura wrote: Thu Aug 03, 2017 10:34 am ODBCOpen( 'DSNname', ' userid' , 'passwd ' );

Squery = 'Select Services, client from table';
Anna use this construct only makes sense in cases of dynamic database queries (when you load different cubes from different tables by one unique process, I do not think you need to do this now) :roll:
Ana Ventura wrote: Fri Aug 04, 2017 1:10 pm Hi everyone,

Thank you very much for your help. I'm going to read the documentation.
I know how i can do the simple process to load data in tm1 cube with odbc data source, but this case i have some data columns on the table in database and i want to load the into the TM1 cube, and this cube has a picklist with information that i have on table.

I will follow your advice.

Thank you,
Ana Ventura
Image
Post Reply