TM1 REST API

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

TM1 REST API

Post by Wim Gielis » Fri Aug 17, 2018 7:27 am

Hi all,

I have been playing around with the TM1 REST API recently, thanks to the useful information provided mainly by IBM, Cubewise. I use the Postman app on the desktop to test the queries that are sent.

I have identified the following areas where I would like to receive feedback regarding the queries:
  • 1.
For views, there is an example on how to show the subsets that are used in titles, rows and columns. For example: {{protocol}}://{{serverName}}:{{httpPortNumber}}/api/v1/Cubes('{{cubeName}}')/Views('Default')?$expand=tm1.NativeView/Titles/Subset,tm1.NativeView/Rows/Subset,tm1.NativeView/Columns/Subset. Can we combine this with a statement that gives the dimensions by cube ? Such that the output gives us the (combined) information on what is the dimension name, what is the subset name ? In fact, the previous statement can be expanded to show elements too. This would give us everything at once
  • 2.
Continuing the question above, the request gives us all elements in the view. If the view is zero suppressed on rows and/or columns, can we also get back only the elements that survive the zero suppression ? So the elements that remain visible in the cube after zero suppression. That would be more useful.
  • 3.
I am able to request the variables that are used in the data source of a TI process. Can we also include in the statement the variables that are created in the 'Variables' tab of a TI process, but that are not part of the data source ? You know, adding a variable as the concatenation of 2 other variables, and the new variable appears in the Generated Statements but does not belong to the data source. I would like to do an AsciiOutput in the Data tab with all variables from the data source and the variables in the Generated Statements. I can do this when I read out the PRO file contents, but not with the REST API.
  • 4.
Does someone have an example with ServerSettings, ActiveConfiguration, ... for example to read out the LoggingDirectory or similar settings ?
  • 5.
I saw that we can select on for example the Name and hence, reduce the output. Can we reduce the output even more to only have the relevant information ? Like, not giving the unique identifiers of objects and so on. Just the basic strings like dimension names that are part of a cube, for example.

Many thanks for all the pointers !

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Drg
Posts: 84
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: TM1 REST API

Post by Drg » Tue Sep 04, 2018 10:14 am

Hello Wim.

Regarding your questions, some of them are described in the main dock, although I'll notice that they are described in the main dock very poorly, but there is still something there.


https://www.ibm.com/support/knowledgece ... st_api.pdf

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

Re: TM1 REST API

Post by Wim Gielis » Tue Sep 04, 2018 12:01 pm

Thanks, I will go through it when I find the time.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

bgregs
Posts: 37
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: TM1 REST API

Post by bgregs » Thu Sep 13, 2018 12:53 pm

Hi Wim,

This may not be exactly what you are looking for, but I threw together a quick example on how I generally use the Rest API to filter out cube data. While this isn't combined into a single expression (as per your request in question 1), it does allow you to filter cube data i.e. break down dimensions, elements, etc. In regards to question 5, I generally handle the selection of the metadata elements (json returned values) on the client side. IMHO, it's much easier to support and tweak code using JS data arrays than it is to assemble a complex REST call, but this is just preference. Again this is a very quick example and many enhancements can be made, but hopefully this helps give you some ideas for questions 1 and 5!

HTML:

Code: Select all

<!doctype html>

<html lang="en">

	<head>
		<meta charset="utf-8">
		<title>TM1 RestAPI Test</title>
		<!-- Latest compiled and minified jQuery -->
		<script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.3.1.min.js"></script>
		<!-- Latest compiled and minified Bootstrap CSS -->
		<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
		<!-- Latest compiled and minified Bootstrap JavaScript -->
		<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
		<!-- Custom JavaScript/jQuery -->
		<script src="js/main.js"></script>
	</head>

	<body>
		<button type="button" id="btnCubes">Cubes</button>
		<div id="divCubes" style="float: left; width: 50%; height: 100%; position: relative;"></div>
		<div id="divDims" style="float: right; width: 50%; height: 100%; position: relative;"></div>
	</body>

</html>
JS:

Code: Select all

$(document).ready (function () {
	var username = 'foo';
	var password = 'bar';
	
	$('#btnCubes').on ('click', function (event) {
	//	event.preventDefault();
		$.ajax ({		
			type: 'GET',
			url: 'https://server:port/api/v1/Cubes',
			beforeSend: function (xhr) {
				xhr.setRequestHeader ("Authorization", "Basic " + btoa (username + ":" + password));
			},
			dataType: 'json',
			data: '{"username": "' + username + '", "password" : "' + password + '"}',
			success: function (data) {
				var data = data.value
				$.each (data, function (key, value) {
					if (value.Name.indexOf('}') == -1) {
						$('#divCubes').append (key + ' ' + '<a href="#">' + value.Name + '</a>' + '<br/>');
					}
				});
			}
		});
	});
	
	$('#divCubes').on ('click', 'a', getDims);
});


function getDims () {
	var username = 'foo';
	var password = 'bar';

	var cube = $(this).text();
	$.ajax ({		
		type: 'GET',
		url: 'https://server:port/api/v1/Cubes(\'' + cube + '\')/Dimensions?$select=Name',
		beforeSend: function (xhr) {
			xhr.setRequestHeader ("Authorization", "Basic " + btoa (username + ":" + password));
		},
		dataType: 'json',
		data: '{"username": "' + username + '", "password" : "' + password + '"}',
		success: function (data) {
			console.log (data);
			var data = data.value
			$.each (data, function (key, value) {
				if (value.Name.indexOf('}') == -1) {
					$('#divDims').append (key + ' ' + '<a href="#" id="lnkTempDim">' + value.Name + '</a>' + '<br/>');
				}
			});
		}
	});
}

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

Re: TM1 REST API

Post by Wim Gielis » Fri Sep 14, 2018 9:54 pm

Many thanks, I will continue when time permits.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply