Page 1 of 1

Loading data in Cube with Picklist TM1

Posted: Wed Aug 02, 2017 3:00 pm
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

Re: Loading data in Cube with Picklist TM1

Posted: Wed Aug 02, 2017 3:03 pm
by Wim Gielis
Just use the normal way of loading the data.
TM1 pickists will not validate the incoming data, though.

Re: Loading data in Cube with Picklist TM1

Posted: Wed Aug 02, 2017 3:19 pm
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.

Re: Loading data in Cube with Picklist TM1

Posted: Wed Aug 02, 2017 3:37 pm
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

Re: Loading data in Cube with Picklist TM1

Posted: Wed Aug 02, 2017 3:55 pm
by Wim Gielis
Please can you provide a clear example with screenshots of table, cube and desired output ?

Re: Loading data in Cube with Picklist TM1

Posted: Wed Aug 02, 2017 4:17 pm
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

Re: Loading data in Cube with Picklist TM1

Posted: Wed Aug 02, 2017 5:04 pm
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.

Re: Loading data in Cube with Picklist TM1

Posted: Thu Aug 03, 2017 6:20 am
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' );

Re: Loading data in Cube with Picklist TM1

Posted: Thu Aug 03, 2017 10:34 am
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,

Re: Loading data in Cube with Picklist TM1

Posted: Thu Aug 03, 2017 11:15 am
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.

Re: Loading data in Cube with Picklist TM1

Posted: Thu Aug 03, 2017 11:18 am
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.

Re: Loading data in Cube with Picklist TM1

Posted: Fri Aug 04, 2017 1:10 pm
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

Re: Loading data in Cube with Picklist TM1

Posted: Fri Aug 04, 2017 2:09 pm
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