TM1 Java API - pull data from view?

Post Reply
snkfischer
Posts: 3
Joined: Wed Jun 13, 2012 2:20 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: Excel 2007

TM1 Java API - pull data from view?

Post by snkfischer »

All,

I'm newer to Java. I'm running Eclipse version Kepler, and connecting successfully to TM1 9.5.2.

I'm able to:
1. establish a connection
2. Connect to a particular cube
3. Extract a single value out of the cube using the sample code that IBM has provided in the installation > sample code folder

The ultimate goal is to pull a view of data from the TM1 cube, take the data in that view and convert it into XML.

As stated above, I can pull a single value out of the cube using the "getCellValue()" method. However, I don't want to just pull an individual cell, one at a time. I'm wanting to:
1. Open a particular view
2. Use that view as a reference to cycle through and extract the data values in the cube (or convert the view into an array, which would be better)
3. Ultimately convert all of the view data into an XML like structure.

I'm stuck on #2. Looking at the TM1 Java API documentation (or lack thereof :roll: ), here's a block of code that I'm experimenting with:

Code: Select all

TM1Cube oCube;
TM1View view;
TM1Val arr;
TM1Val ret;

view = oCube.getView("JavaTest");
view.constructArray();
//System.out.println(arr.getBoolean());
								
System.out.println(view.getNumberOfArrayColumns());
System.out.println(view.getNumberOfArrayRows());
				
System.out.println(view.getString().toString());
System.out.println(view.getArraySize());
				
ret = view.getArrayValue(0,0);
System.out.println(ret.getInt());
ret = view.getArrayValue(1,1);
System.out.println(ret.getInt());


I'm successfully pulling the view object from the TM1 cube. However, once I have the object, I'm not sure what to do with it.
I've experimented with the view.constructArray() method, but not clear on what it does. I receive a "true" value back, which indicates that it did *something*.
The "getNumberofArrayColums()" and "getNumberOfArrayRows()" return "10" and "7" respectively, so I'm assuming that it's loaded something.

How do I retrieve the information out of the array? What does the "constructArray() actually do?
Any thoughts would be helpful! Sample code of extracting data from TM1 cubes other than the sample IBM provided would be awesome.
snkfischer
Posts: 3
Joined: Wed Jun 13, 2012 2:20 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: Excel 2007

Re: TM1 Java API - pull data from view?

Post by snkfischer »

Ok, so I realized that I actually was pulling the information correctly. I threw in a couple of for loops to make sure that I was looking at all values of the TM1Val array:

Code: Select all

TM1Cube oCube;
TM1View view;
TM1Val arr;
TM1Val ret;

//get oCube
oCube = oServer.getCube ("Consumer Dashboard");
if (!oCube.isError()){
				
   view = oCube.getView("JavaTest2");
   view.constructArray();
								
   System.out.println(view.getNumberOfArrayColumns());
   System.out.println(view.getNumberOfArrayRows());
   System.out.println("");
				
   for (int x = 1; x <= view.getNumberOfArrayColumns(); x++){
					
      for (int y = 1; y <= view.getNumberOfArrayRows(); y++){
         //System.out.println("Matrix: " + x + " + " + y); 
	       ret = view.getArrayValue(x,y);
	       System.out.println("Value of: " + x + " + " + y + " is: " + ret.getInt());	
	      }	
   }					

}
else{
   System.out.println (oCube.getErrorMessage());
}
I just didn't realize what the array looked like compared to the view in the TM1 cube viewer. Once I compared the values in the array to the view, it's easy to understand how the array is structured.
snkfischer
Posts: 3
Joined: Wed Jun 13, 2012 2:20 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: Excel 2007

Re: TM1 Java API - pull data from view?

Post by snkfischer »

I've received a couple of requests for information on how I got this to work. I'm clearly no java programmer, but I did was able to write enough code to connect, retrieve values from views, etc. I'm attaching what I threw together in Eclipse, along with a screenshot of how I have the Eclipse project set up.
TM1 Java Files.zip
TM1 Java files and screenshot
(57.05 KiB) Downloaded 643 times
Wim Gielis
MVP
Posts: 3103
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 Java API - pull data from view?

Post by Wim Gielis »

Thank you for sharing the information.
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
User avatar
tianoklein
Posts: 41
Joined: Fri Jun 13, 2014 1:23 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Java API - pull data from view?

Post by tianoklein »

Hello,
I'm trouble with this code... I really don't understand why don't work, and it's drive my crazy.

Code: Select all

package com.tm1.consdb;
import com.applix.tm1.*;

public class TM1Connect {
	
	public static TM1Server InitConn(){
		
		TM1Bean bean = new TM1Bean();
		bean.setAdminHost("localhost");
		bean.setAdminPort(5495);
/*ssl 5498 	/non-ssl 5495*/
		System.out.println("Nr. of Servers: " + bean.getNumberOfServers());
		
		//TM1Server tm1_server = bean.openConnection ("tm1test", "admin", "");
		//int x = bean.getNumberOfServers();
		//System.out.println(srvr.getBuildNumber());
		//System.out.println(x);
		return null;
		
	}
	
}
The result is

Code: Select all

Nr. of Servers: -1
User avatar
tianoklein
Posts: 41
Joined: Fri Jun 13, 2014 1:23 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Java API - pull data from view?

Post by tianoklein »

Hello,
I'm trouble with this code... I really don't understand why don't work, and it's drive my crazy.

Code: Select all

package com.tm1.consdb;
import com.applix.tm1.*;

public class TM1Connect {
	
	public static TM1Server InitConn(){
		
		TM1Bean bean = new TM1Bean();
		bean.setAdminHost("localhost");
		bean.setAdminPort(5495);
/*ssl 5498 	/non-ssl 5495*/
		System.out.println("Nr. of Servers: " + bean.getNumberOfServers());
		
		//TM1Server tm1_server = bean.openConnection ("tm1test", "admin", "");
		//int x = bean.getNumberOfServers();
		//System.out.println(srvr.getBuildNumber());
		//System.out.println(x);
		return null;
		
	}
	
}
The result is

Code: Select all

Nr. of Servers: -1
BrianL
MVP
Posts: 264
Joined: Mon Nov 03, 2014 8:23 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2 PA2
Excel Version: 2016

Re: TM1 Java API - pull data from view?

Post by BrianL »

Pretty sure everything defaults to SSL enabled. Are you sure the admin server is even listening on the non-ssl port?
User avatar
tianoklein
Posts: 41
Joined: Fri Jun 13, 2014 1:23 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Java API - pull data from view?

Post by tianoklein »

Hello BrianL
Thanks for your advice... I really needed...

I change in Cognos Configuration the parameter: "Support non-SSL Clients?" And still having troubles...

Image

I change my code, adding setDebugMode like this:

Code: Select all

package com.tm1.consdb;
import com.applix.tm1.*;

public class TM1Main1 {
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		TM1Bean bean = new TM1Bean();
		/*changed*/ 
		bean.setDebugMode(true);
		/*changed*/
		bean.setAdminHost("localhost");
		bean.setAdminPort(5495);
		bean.refreshServers();
		System.out.println("Nr. of Servers: " + bean.getNumberOfServers());
	}
}
and the error changed to

Code: Select all

TM1 Error-> Error creating socket - java.net.ConnectException: Connection refused: connect
java.net.ConnectException: Connection refused: connect
	at java.net.DualStackPlainSocketImpl.connect0(Native Method)
	at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
	at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
	at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
	at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
	at java.net.PlainSocketImpl.connect(Unknown Source)
	at java.net.SocksSocketImpl.connect(Unknown Source)
	at java.net.Socket.connect(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.connect(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.<init>(Unknown Source)
	at sun.security.ssl.SSLSocketFactoryImpl.createSocket(Unknown Source)
	at com.applix.tm1.TM1NetClass.ConnectToHost(TM1NetClass.java:217)
	at com.applix.tm1.TM1NetClass.ConnectUsingIPv6(TM1NetClass.java:201)
	at com.applix.tm1.TM1NetClass.Connect(TM1NetClass.java:167)
	at com.applix.tm1.TM1Bean.getAdmEntryList(TM1Bean.java:462)
	at com.applix.tm1.TM1Bean.getNumberOfServers(TM1Bean.java:292)
	at com.applix.tm1.TM1Bean.refreshServers(TM1Bean.java:303)
	at com.tm1.consdb.TM1Main1.main(TM1Main1.java:13)
TM1 Error-> Error creating socket - javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at sun.security.ssl.Alerts.getSSLException(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.fatal(Unknown Source)
	at sun.security.ssl.Handshaker.fatalSE(Unknown Source)
	at sun.security.ssl.Handshaker.fatalSE(Unknown Source)
	at sun.security.ssl.ClientHandshaker.serverCertificate(Unknown Source)
	at sun.security.ssl.ClientHandshaker.processMessage(Unknown Source)
	at sun.security.ssl.Handshaker.processLoop(Unknown Source)
	at sun.security.ssl.Handshaker.process_record(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.readRecord(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.performInitialHandshake(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
	at com.applix.tm1.TM1NetClass.ConnectToHost(TM1NetClass.java:224)
	at com.applix.tm1.TM1NetClass.ConnectUsingIPv4(TM1NetClass.java:189)
	at com.applix.tm1.TM1NetClass.Connect(TM1NetClass.java:171)
	at com.applix.tm1.TM1Bean.getAdmEntryList(TM1Bean.java:462)
	at com.applix.tm1.TM1Bean.getNumberOfServers(TM1Bean.java:292)
	at com.applix.tm1.TM1Bean.refreshServers(TM1Bean.java:303)
	at com.tm1.consdb.TM1Main1.main(TM1Main1.java:13)
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at sun.security.validator.PKIXValidator.doBuild(Unknown Source)
	at sun.security.validator.PKIXValidator.engineValidate(Unknown Source)
	at sun.security.validator.Validator.validate(Unknown Source)
	at sun.security.ssl.X509TrustManagerImpl.validate(Unknown Source)
	at sun.security.ssl.X509TrustManagerImpl.checkTrusted(Unknown Source)
	at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(Unknown Source)
	... 15 more
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(Unknown Source)
	at java.security.cert.CertPathBuilder.build(Unknown Source)
	... 21 more
TM1 Error-> Error creating socket - java.net.ConnectException: Connection refused: connect
java.net.ConnectException: Connection refused: connect
	at java.net.DualStackPlainSocketImpl.connect0(Native Method)
	at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
	at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
	at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
	at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
	at java.net.PlainSocketImpl.connect(Unknown Source)
	at java.net.SocksSocketImpl.connect(Unknown Source)
	at java.net.Socket.connect(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.connect(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.<init>(Unknown Source)
	at sun.security.ssl.SSLSocketFactoryImpl.createSocket(Unknown Source)
	at com.applix.tm1.TM1NetClass.ConnectToHost(TM1NetClass.java:217)
	at com.applix.tm1.TM1NetClass.ConnectUsingIPv6(TM1NetClass.java:201)
	at com.applix.tm1.TM1NetClass.Connect(TM1NetClass.java:167)
	at com.applix.tm1.TM1Bean.getAdmEntryList(TM1Bean.java:462)
	at com.applix.tm1.TM1Bean.getNumberOfServers(TM1Bean.java:292)
	at com.tm1.consdb.TM1Main1.main(TM1Main1.java:14)
javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at sun.security.ssl.Alerts.getSSLException(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.fatal(Unknown Source)
	at sun.security.ssl.Handshaker.fatalSE(Unknown Source)
	at sun.security.ssl.Handshaker.fatalSE(Unknown Source)
	at sun.security.ssl.ClientHandshaker.serverCertificate(Unknown Source)
	at sun.security.ssl.ClientHandshaker.processMessage(Unknown Source)
	at sun.security.ssl.Handshaker.processLoop(Unknown Source)
	at sun.security.ssl.Handshaker.process_record(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.readRecord(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.performInitialHandshake(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
	at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
	at com.applix.tm1.TM1NetClass.ConnectToHost(TM1NetClass.java:224)
	at com.applix.tm1.TM1NetClass.ConnectUsingIPv4(TM1NetClass.java:189)
	at com.applix.tm1.TM1NetClass.Connect(TM1NetClass.java:171)
	at com.applix.tm1.TM1Bean.getAdmEntryList(TM1Bean.java:462)
	at com.applix.tm1.TM1Bean.getNumberOfServers(TM1Bean.java:292)
	at com.tm1.consdb.TM1Main1.main(TM1Main1.java:14)
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at sun.security.validator.PKIXValidator.doBuild(Unknown Source)
	at sun.security.validator.PKIXValidator.engineValidate(Unknown Source)
	at sun.security.validator.Validator.validate(Unknown Source)
	at sun.security.ssl.X509TrustManagerImpl.validate(Unknown Source)
	at sun.security.ssl.X509TrustManagerImpl.checkTrusted(Unknown Source)
	at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(Unknown Source)
	... 14 more
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(Unknown Source)
	at java.security.cert.CertPathBuilder.build(Unknown Source)
	... 20 more
TM1 Error-> Error creating socket - javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
Nr. of Servers: -1

BrianL
MVP
Posts: 264
Joined: Mon Nov 03, 2014 8:23 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2 PA2
Excel Version: 2016

Re: TM1 Java API - pull data from view?

Post by BrianL »

Looks like it's still hitting SSL errors.

Why don't you want to use SSL? All you need to do is import the TM1 ssl certificate into your java keystore. You can find instructions at http://www-01.ibm.com/support/docview.w ... wg21414527.
User avatar
tianoklein
Posts: 41
Joined: Fri Jun 13, 2014 1:23 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Java API - pull data from view?

Post by tianoklein »

Wow!
Finnaly works..

I would really appreciate if you had any examples to share with me. Could be very simple, such as inserting an item in the dimension, for example.
This would help me a lot as is the operation of TM1 with Java.

Thanks
User avatar
tianoklein
Posts: 41
Joined: Fri Jun 13, 2014 1:23 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Java API - pull data from view?

Post by tianoklein »

Hello Folks,

I found some samples on the web.
http://www.tm1hub.com/index.php/forum/t ... -using-cam

If anyone needs some assistence, let me know, I'm glad to help :P

For while, who needs assistence its me.... :P

Anyone of you know how to create an attribute in a dimension.
User avatar
tianoklein
Posts: 41
Joined: Fri Jun 13, 2014 1:23 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Java API - pull data from view?

Post by tianoklein »

Anyone of you know how to create an attribute in a dimension??
kjanssen
Posts: 17
Joined: Mon Jan 19, 2015 12:43 pm
OLAP Product: Cognos Express
Version: 10.2.2
Excel Version: 2010

Re: TM1 Java API - pull data from view?

Post by kjanssen »

snkfischer wrote:Ok, so I realized that I actually was pulling the information correctly. I threw in a couple of for loops to make sure that I was looking at all values of the TM1Val array:

Code: Select all

TM1Cube oCube;
TM1View view;
TM1Val arr;
TM1Val ret;

//get oCube
oCube = oServer.getCube ("Consumer Dashboard");
if (!oCube.isError()){
				
   view = oCube.getView("JavaTest2");
   view.constructArray();
								
   System.out.println(view.getNumberOfArrayColumns());
   System.out.println(view.getNumberOfArrayRows());
   System.out.println("");
				
   for (int x = 1; x <= view.getNumberOfArrayColumns(); x++){
					
      for (int y = 1; y <= view.getNumberOfArrayRows(); y++){
         //System.out.println("Matrix: " + x + " + " + y); 
	       ret = view.getArrayValue(x,y);
	       System.out.println("Value of: " + x + " + " + y + " is: " + ret.getInt());	
	      }	
   }					

}
else{
   System.out.println (oCube.getErrorMessage());
}
I just didn't realize what the array looked like compared to the view in the TM1 cube viewer. Once I compared the values in the array to the view, it's easy to understand how the array is structured.

hello there,

i got some data using you snippet but i have the problem that for all values that are part of dimensions, i only get an (as it seems) index of the value inside the subset used for the view.

how can i get the value/name of the element inside my dimension?

e.g. i get the following result:

Code: Select all

╔═══════════╤═══════════╤═══════════╤═══════════╤═══════════════════════╗
║ Column 1  │ Column 2  │ Column 3  │ Column 4  │ Column 5              ║
╠═══════════╪═══════════╪═══════════╪═══════════╪═══════════════════════╣
║  String:  │  String:  │  String:  │  String:  │  Int: 1               ║
╟───────────┼───────────┼───────────┼───────────┼───────────────────────╢
║  Int: 1   │  Int: 1   │  Int: 14  │  Int: 1   │  String: -112,569364  ║
╟───────────┼───────────┼───────────┼───────────┼───────────────────────╢
║  Int: 1   │  Int: 1   │  Int: 14  │  Int: 6   │  String: -210,673472  ║
╟───────────┼───────────┼───────────┼───────────┼───────────────────────╢
║  Int: 1   │  Int: 1   │  Int: 14  │  Int: 7   │  String: -1531,277164 ║
╚═══════════╧═══════════╧═══════════╧═══════════╧═══════════════════════╝
Columns 1-4 are dimensions on the left
the four strings in the first row are non existend cells.
the "Int: 1" is the one value i have in data columns (dimension on the top)
and the four "Int: *" in each row are the index values in my subsets

now i need to know how i can get the actual value of the dimension values.
eg:
col 1 int 1 = "EU"
col 2 int 1 = "Germany"
col 3 int 14 = "Sales"
col 4 int 1 = "Product A"
col 4 int 6 = "Product F"
col 4 int 7 = "Product G"


what i have tried was:

Code: Select all

	for (int i = 1; i <= view.getNumberOfArrayAxes().getInt(); i++) {
				TM1Val numberOfArrayAxisTuples =
				        view.getNumberOfArrayAxisTuples(new TM1Val(i));
				System.err.println(numberOfArrayAxisTuples);
				for (int j = 1; j <= numberOfArrayAxisTuples.getInt(); j++) {
					System.err.println(view.getArrayAxisTuple(new TM1Val(i),
					        new TM1Val(j)));
				}
			}
as result i got:
Int: 1
Error: InvalidViewType
Int: 3
Error: InvalidViewType
Error: InvalidViewType
Error: InvalidViewType
but i no idea why it returns the InvalidViewType error. :(

Thanks :)
kjanssen
Posts: 17
Joined: Mon Jan 19, 2015 12:43 pm
OLAP Product: Cognos Express
Version: 10.2.2
Excel Version: 2010

Re: TM1 Java API - pull data from view?

Post by kjanssen »

ok i found that i have to use view.getColumnSubset(i) & view.getRowSubset(i) and then use thier getElement()
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: TM1 Java API - pull data from view?

Post by Drg »

kjanssen wrote: Mon Apr 13, 2015 2:58 pm ok i found that i have to use view.getColumnSubset(i) & view.getRowSubset(i) and then use thier getElement()
Maybe y read this.

You were able to return the tuple using the method getArrayAxisTuple??? :oops:
Post Reply