Page 1 of 1

TM1 Java API - pull data from view?

Posted: Fri Feb 14, 2014 7:18 pm
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.

Re: TM1 Java API - pull data from view?

Posted: Fri Feb 14, 2014 8:28 pm
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.

Re: TM1 Java API - pull data from view?

Posted: Tue Jul 15, 2014 3:33 pm
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 194 times

Re: TM1 Java API - pull data from view?

Posted: Tue Jul 15, 2014 5:09 pm
by Wim Gielis
Thank you for sharing the information.

Re: TM1 Java API - pull data from view?

Posted: Mon Mar 23, 2015 1:12 am
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

Re: TM1 Java API - pull data from view?

Posted: Mon Mar 23, 2015 1:12 am
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

Re: TM1 Java API - pull data from view?

Posted: Mon Mar 23, 2015 3:46 pm
by BrianL
Pretty sure everything defaults to SSL enabled. Are you sure the admin server is even listening on the non-ssl port?

Re: TM1 Java API - pull data from view?

Posted: Mon Mar 23, 2015 7:45 pm
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


Re: TM1 Java API - pull data from view?

Posted: Mon Mar 23, 2015 8:14 pm
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.

Re: TM1 Java API - pull data from view?

Posted: Mon Mar 23, 2015 9:49 pm
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

Re: TM1 Java API - pull data from view?

Posted: Fri Mar 27, 2015 1:42 pm
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.

Re: TM1 Java API - pull data from view?

Posted: Mon Mar 30, 2015 1:12 am
by tianoklein
Anyone of you know how to create an attribute in a dimension??

Re: TM1 Java API - pull data from view?

Posted: Mon Apr 13, 2015 1:37 pm
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 :)

Re: TM1 Java API - pull data from view?

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

Re: TM1 Java API - pull data from view?

Posted: Mon Jul 17, 2017 12:15 pm
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: