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

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: 1480
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Loading data in Cube with Picklist TM1

Post by Wim Gielis » Wed Aug 02, 2017 3:03 pm

Just use the normal way of loading the data.
TM1 pickists will not validate the incoming data, though.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

Drg
Posts: 40
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 » Wed Aug 02, 2017 3:19 pm

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

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: 1480
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Loading data in Cube with Picklist TM1

Post by Wim Gielis » Wed Aug 02, 2017 3:55 pm

Please can you provide a clear example with screenshots of table, cube and desired output ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

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

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 324 times
table.PNG
table.PNG (37.56 KiB) Viewed 324 times

User avatar
tomok
MVP
Posts: 2294
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 » Wed Aug 02, 2017 5:04 pm

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.

Drg
Posts: 40
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 » Thu Aug 03, 2017 6:20 am

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

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 223 times
cube_view.PNG
cube_view.PNG (5.93 KiB) Viewed 223 times

User avatar
tomok
MVP
Posts: 2294
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 » Thu Aug 03, 2017 11:15 am

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.

Wim Gielis
MVP
Posts: 1480
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Loading data in Cube with Picklist TM1

Post by Wim Gielis » Thu Aug 03, 2017 11:18 am

tomok wrote:
Thu Aug 03, 2017 11:15 am
If 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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

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 » 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

Drg
Posts: 40
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 » Fri Aug 04, 2017 2:09 pm

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