TM1 REST API

Wim Gielis
MVP
Posts: 1889
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
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. SOLVED, see below

Points 1-5 are solved by using Python code but I might prefer a non-Python / generic way if possible and not too elaborate

Many thanks for all the pointers !

Wim
Last edited by Wim Gielis on Sun Jan 13, 2019 11:07 pm, edited 3 times in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

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: 1889
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
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 ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

bgregs
Posts: 56
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: 1889
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
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 ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

MariusWirtz
Posts: 3
Joined: Sat Apr 08, 2017 8:40 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016

Re: TM1 REST API

Post by MariusWirtz » Mon Jan 07, 2019 4:30 pm

Hi Wim,

the 5 points that you brought up here could be accomplished with TM1py through very little custom code.
This is how I would approach it.

# 1 Print out summary of a cube view

Code: Select all

from TM1py import TM1Service

with TM1Service(address='10.77.19.60', port=12354, user='admin', password='apple', ssl=True) as tm1:
    # get the view definition from TM1
    view = tm1.cubes.views.get_native_view(
        cube_name="Weather Data",
        view_name="Default",
        private=False
    )

    # pretty-print the view definition
    print("### Titles (Dimension | Subset | Selection)")
    for title in view._titles:
        print(title.dimension_name, "|", title.subset.name, "|", title.selected)
    for axis_label, axis in zip(["Rows", "Columns"], [view._rows, view._columns]):
        print("### " + axis_label + " (Dimension | Subset |  Elements or Expression)")
        for axis_selection in axis:
            print(axis_selection.dimension_name, "|", axis_selection.subset.name, "|",
                  axis_selection.subset.expression or axis_selection.subset.elements)
# 2 From the view definition itself it can not be derived which rows, columns are zero suppressed. In order to get the zero suppressed content of a view you need to execute it. TM1py has different functions to execute views or mdx. Here is one that gives you the coordinates and cells.

Code: Select all

from TM1py import TM1Service

with TM1Service(address='10.77.19.60', port=12354, user='admin', password='apple', ssl=True) as tm1:
    cube_name = "Bike Shares"
    view_name = "2017 Counts by Month"

    # execute a view that is zero suppressed
    data = tm1.cubes.cells.execute_view(
        cube_name=cube_name,
        view_name=view_name,
        private=False
    )

    # print out all coordinates / intersections that were returned
    for coordinate, cell in data.items():
        print(coordinate)
You can also execute a view and get the data as a (pandas) pivot table

Code: Select all

from TM1py import TM1Service

with TM1Service(address='10.77.19.60', port=12354, user='admin', password='apple', ssl=True) as tm1:
    cube_name = "Bike Shares"
    view_name = "2017 Counts by Month"

    # execute a view that is zero suppressed as pivot table
    data = tm1.cubes.cells.execute_view_dataframe_pivot(
        cube_name=cube_name,
        view_name=view_name,
        private=False
    )
    print(data)
# 3 Not sure if I understand your idea correctly, but you could inject an AsciiOutput statement with all variables at the bottom of the metadata tab like this:

Code: Select all

from TM1py import TM1Service

with TM1Service(address='10.77.19.60', port=12354, user='admin', password='apple', ssl=True) as tm1:
    # get process from TM1
    process = tm1.processes.get("_temp4")
    
    # extract name of process variables
    variables_names = [variable["Name"] for variable in process.variables]

    # new line of TI to be added to the process
    new_line = "\r\nASCIIOutput('out.txt', " + ",".join(v for v in variables_names) + " );\r\n"
    
    # adjust process: add new line to the metadata procedure
    process.metadata_procedure += new_line

    # send process back to TM1
    tm1.processes.update(process)
# 4 You can read out config parameters like this

Code: Select all

from TM1py import TM1Service

with TM1Service(address='10.77.19.60', port=12354, user='admin', password='apple', ssl=True) as tm1:
    config = tm1.server.get_active_configuration()
    logging_directory = config["Administration"]["DebugLog"]["LoggingDirectory"]
    print(logging_directory)
(when you are using TM1 11) you can also live update the TM1 config with TM1py, like this:

Code: Select all

from TM1py import TM1Service

with TM1Service(address='10.77.19.60', port=12354, user='admin', password='apple', ssl=True) as tm1:
    config = {
        "Performance": {
            "MTQ": {
                "MTFeeders": False
            }
        }
    }
    # effectively update the .cfg file (and triggers TM1 to re-read the file)
    tm1.server.update_static_configuration(config)
    # ask TM1 Server about the currently applied config parameters
    config = tm1.server.get_active_configuration()

    print(config["Performance"]["MTQ"]["MTFeeders"])
# 5 You can query the dimension names of a cube like this:

Code: Select all

from TM1py import TM1Service

with TM1Service(address='10.77.19.60', port=12354, user='admin', password='apple', ssl=True) as tm1:
    dimension_names = tm1.cubes.get_dimension_names(cube_name="FX Rates")
    print(dimension_names)

Using TM1py generally saves you from reinventing the wheel and provides you a solid and tested layer underneath your custom code.
Only downside of using TM1py really is that you need to use python, which IMHO is generally a good choice, but may not be suitable for all cases.

Cheers,

Marius

bgregs
Posts: 56
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 » Mon Jan 07, 2019 6:56 pm

Just my 2 cents, tm1py is a great library, but its biggest downfall is that it is not heavily supported. I've experimented with it throughout the years, but even now it looks like it's last update was almost a month ago. Depending on what your use case is, it could be a perfect fit. As a general rule of thumb though, it makes me shudder to imagine using an unsupported 3rd party library in a production situation. :shock:

Again, it is a fun library to play around with and could have some great use cases for helpful tools in a dev environment. My experience with other languages however has taught me to be cautious (maybe overly so) about using any open source library that could lose support as soon as the devs move onto bigger and better things. ;)

FWIW, you must also take a serious look at who will support the application in the future. If you have dedicated IT resources that are fine with tinkering with python (or JS/HTML/CSS for that matter), then no problem. But if you rely on consultants or a team that doesn't have that skill set, you may very easily dig yourself into a very deep hole.

Wim Gielis
MVP
Posts: 1889
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis » Mon Jan 07, 2019 8:12 pm

Thank you very much Marius and bgregs. Good to see that it can be done, thanks.

To give you a bit more background: this is not supposed to be used by users in a production environment, it's more targeted to increase my own efficiency in working with TM1. Most of all, point 2 above would be a timesaver: restricting a view in the Cube Viewer to only those combinations that remain when zero suppression is applied. What a PITA when we have to do this manually (snapshot to Excel, copy/paste again selections, and so on).

5 is also taking time for nothing: if I need to write an AsciiOutput to debug a certain TI process, it would be good to have the variables listed in the AsciiOutput. I can now do this more or less with tools like AutoHotKey or Excel VBA, but that's all pretty workarounds (read out the PRO file contents of the active process and so on). It works with AHK but that's about it - the AsciiOutput statement is injected at the position of the cursor.

I follow bgregs, this isn't something for production models, but then again, I would be the user of these tools to increase efficiency and remove manual work - rather than users executing Python scripts that could break down and cause problems. This being said, installing extra software on production environments is what I want to avoid. An exe for AHK or Notepad++ and its plugins are about the most I would do generally.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

David Usherwood
Site Admin
Posts: 1340
Joined: Wed May 28, 2008 9:09 am

Re: TM1 REST API

Post by David Usherwood » Tue Jan 08, 2019 4:20 pm

@bgregs, I don't think you are being quite fair in your post. TM1PY is written and supported by Marius Wirtz of Cubewise Germany. In my opinion (I have been working with TM1 since 1995) he has done a stonking job at rendering the TM1 REST Api accessible to those of us who don't wish to become full-on web service progreammer types.

I worked with an early release (over an early release of the Rest API) and I found Marius to be very responsive to queries and potential bugs. I can't speak for how he spends his days but I strongly suspect he is very busy building TM1 projects for customers and looks after TM1PY in his spare time. I do not see that he is obliged to pump out new releases unless IBM rolls out major new functionality or bug fixes, which they do not do very often.

TM1PY is not - to state the obvious - an end user tool, but a toolkit for developers to build scripts to address specific tasks which are not readily achievable via the standard TM1 tools. In the past we have had to dig into the choking horror which is the C API, the URL API, or the Java API. TM1PY is far more usable than all of these. And being Python-based there is the significant benefit of access to the huge range of Python libraries - I did a proof of concept implementing IRR calculations in thirty lines of code using numpy and pandas. I have also made some progress in using TM1PY within the new-ish Python script support by Microsoft PowerBI to pull data directly from TM1.

I think your challenge around support is more relevant to the question of who is to support the scripts using TM1PY (or whatever non-core TM1 logic you include in your application). I am quite aware that TM1 applications have a habit of falling into disuse when the 'knowledgeable administrator' moves on, and this is accentuated if the content is not core TM1 dimensions/cubes/rules/processes. I do not think there is an easy solution for this although good documentation which is regularly updated does help.

bgregs
Posts: 56
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 » Tue Jan 08, 2019 4:48 pm

@David,

I'll start by saying I full agree, and that by no means did I intend to downplay the significant improvement to the REST API that tm1py has managed to implement. I respect Marius' commitment to the community and can only imagine how difficult it must be to maintain opensource projects (especially with the responsiveness that he has!).

I also agree with the fact that tm1py is very much a toolkit used to extend and create more flexible implementations for us developers that don't want to muddle around with a lack of IBM documentation. ;) I apologize for my lack of clarity in my previous post, but I _am_ in favor of using a library such as tm1py for ad-hoc development tools. It's a perfect fit and can really cut down development time.

Where I struggle to see its usefulness is in a production environment (whether that's in the form of tools or dependent processes). If Marius were to retire or decide to take a break from the project and IBM decides to change something in the way the REST API is implemented, I could foresee a painful experience. Naturally, there's the strong possibility that somebody else would pickup the project, but it just seems like too much risk for me personally.

Again, in no way, shape, or form did I ever mean to imply that what Marius and those who have contributed to tm1py have done is insignificant. It is a major accomplishment and something for which we are all thankful. My only concern is more based in the opensource nature of the project, and therefore, its long term support. I hope that helps to clarify my standpoint, and I'm always open to further discussion! :D

Wim Gielis
MVP
Posts: 1889
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis » Sat Jan 12, 2019 3:43 am

Marius,

I would like to come back on the 5th item above.
I will only use the TM1 REST API for a few things, one of them begin the wish to retrieve the dimension names based on a cube name.

So this URL:

http://aex199:8001/api/v1/Cubes('Aexis_ ... elect=Name

gives me in a browser:

{"@odata.context":"../$metadata#Dimensions(Name)","value":[{"@odata.etag":"W/\"26565daf22887161a551fe600fbbc005972d57aa\"","Name":"Jaren"},{"@odata.etag":"W/\"708364f3b711a6c2b7db532f9c2f2700c6864025\"","Name":"Maanden"},{"@odata.etag":"W/\"c5aebf69245b6e7f89ffde3eb2aa8d1f32e17971\"","Name":"Consultant"},{"@odata.etag":"W/\"a28acf2b428ba1c9ab0e4738db8a02ac7d6dd1a3\"","Name":"Project"},{"@odata.etag":"W/\"1201614e63a5fc83d18d084b577c9d9b03e2bdf8\"","Name":"Aexis_Planning_Msr"}]}

In the URL I filter on the "Name" and this gives me less output than if I do not filter on Name.
Still, why do we get those unique etag's or ID's ? Can we get rid of them or is the only viable solution to parse the JSON responsetext ?
With a number of filters this could also work but I would almost assume that the REST API should be able to only return the dimension names (in this case, it's a cube with 5 dimensions).

Many thanks for your insights.

My overall aim is to ask the user for a cubename and select a zip file (of the TM1 data directory), after which the script unzips the relevant files from the zip file (.cub file, .rux, the dimensions .dim files, }ElementAttribute .cub and .dim files, etc.). Asking for selections and unzipping selected files from a zipfile is already done by myself and it works, but for a hardcoded cube name and dimension names.

Next to this there will certainly be other areas where I just need a list of cubes, dimensions or processes (without resorting to elements in control dimenions).

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

User avatar
Mike Cowie
Site Admin
Posts: 441
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: TM1, MSAS
Version: Anything thru 11.x
Excel Version: 2003 - 2016
Location: Alabama, USA
Contact:

Re: TM1 REST API

Post by Mike Cowie » Sun Jan 13, 2019 10:17 pm

Hi Wim:

Add this to your Accept header to tell TM1 not to return the OData metadata etags:
odata.metadata=none

Hope that helps cut down on some of the noise for you!

Best,
Mike

Wim Gielis
MVP
Posts: 1889
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis » Sun Jan 13, 2019 10:51 pm

Got it ! Many thanks !
04.png
04.png (26.26 KiB) Viewed 318 times
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

lotsaram
MVP
Posts: 3163
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 REST API

Post by lotsaram » Mon Jan 14, 2019 10:08 am

@bgregs

Thanks for your recognition of the tm1py project.

I should preface this by saying I really don't want to get into a fight here, but I disagree with the opinions you expressed in both your initial post and your subsequent response to David.
bgregs wrote: Just my 2 cents, tm1py is a great library, but its biggest downfall is that it is not heavily supported. I've experimented with it throughout the years, but even now it looks like it's last update was almost a month ago. Depending on what your use case is, it could be a perfect fit. As a general rule of thumb though, it makes me shudder to imagine using an unsupported 3rd party library in a production situation. :shock:

Again, it is a fun library to play around with and could have some great use cases for helpful tools in a dev environment. My experience with other languages however has taught me to be cautious (maybe overly so) about using any open source library that could lose support as soon as the devs move onto bigger and better things.

FWIW, you must also take a serious look at who will support the application in the future. If you have dedicated IT resources that are fine with tinkering with python (or JS/HTML/CSS for that matter), then no problem. But if you rely on consultants or a team that doesn't have that skill set, you may very easily dig yourself into a very deep hole.
bgregs wrote: Where I struggle to see its usefulness is in a production environment (whether that's in the form of tools or dependent processes). If Marius were to retire or decide to take a break from the project and IBM decides to change something in the way the REST API is implemented, I could foresee a painful experience. Naturally, there's the strong possibility that somebody else would pickup the project, but it just seems like too much risk for me personally.

Again, in no way, shape, or form did I ever mean to imply that what Marius and those who have contributed to tm1py have done is insignificant. It is a major accomplishment and something for which we are all thankful. My only concern is more based in the opensource nature of the project, and therefore, its long term support. I hope that helps to clarify my standpoint, and I'm always open to further discussion! :D
Regarding tm1py suitability for production applications
Here I have to disagree strongly with your opinion. TM1py is a framework and you can certainly build production grade applications on top of it, and many people have, including several "brand name" TM1 customers with very large models where having robust and maintainable production applications are certainly of paramount importance. TM1Py is a toolkit (just as TM1 itself is, or any database or other framework for that matter). As with any framework you can build with it what you will. TM1Py will continue to be developed, but what is already there is more than sufficient to support developing productive utilities and apps.

Regarding long-term support
I work with Marius and he has done a fantastic job in bringing TM1Py to life. However, there's also more than Marius behind TM1Py now as it is being supported as an open source project by Cubewise. Even without Marius, there is a strong enough community and a team of maintainers to keep the project going were Marius to leave the project. Moreover, as the project is open source anyone finding a bug or wanting to develop a feature can fork the code and create a pull request to merge the change back into the core of TM1Py. This is a great strength of open source development. It just relies on a large enough community of people willing to contribute.

Regarding updates and responsiveness
I don't know about you but I can see multiple commits to the project in Dec and Jan. Also don't forget that the TM1Py project is bigger than just the TM1Py repository, there's also the TM1Py samples and RushTI (and if you want an example of a productive app built with TM1Py look no further than RushTI.) I don't know what's required for your definition of "heavily supported" but I I think the team is pretty responsive to fixing bugs and to supporting new Rest API features with every update from IBM ( ... and again it's open source people also have the ability to fix things themselves locally). Over the last 2 years IBM have been adding a lot of features to the Rest API, and consequently there have been many additions to TM1Py to support these. But as the Rest API becomes stable then any library/wrapper interfacing to it is also going to reflect this (i.e. if all features are covered then the work is done, and there won't be many changes, this is normal).

Regarding internal supportability
My opinion differs here. Yes for sure back maybe 5 or more years ago most TM1 developers and administrators came from a finance or finance systems background where a skillset centered on Excel, VBA and SQL in addition to TM1 was the norm. That's changed. While finance skills and literacy are still critical, the average TM1 developer is now far more likely to have some "standard programming knowledge" with python, R, javascript being the most typical. Certainly this is true now for 50% or more of our consultants at Cubewise. With our world merging with predictive analytics and data science expect this trend to continue. So in short I don't believe an application built in python to be a maintenance liability, quite the contrary, in fact IT departments of many customers are much more comfortable about something developed in a standard language.

Just in general / other
The whole point of APIs is that they must be stable with strict enforcement of backwards compatibility as new features are introduced. IBM is betting the house so to speak on TM1's Restful API. All the new UIs (PAW, PAX, etc) rely on the Rest API. IBM is not about to change the behaviour of the Rest API on a whim. This wouldn't just create issues for TM1Py but would break any UI using the Rest API. Never say never but I think we can safely discount IBM making major breaking changes to the API, there's too much at stake. Therefore I think any speculation about "what would happen if IBM were to change the API?" is if not fear mongering then pretty fanciful. If this were to happen IBM would have some mightily pissed off customers, and I don't think they want that.

@Wim - sorry to hijack your thread!
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Wim Gielis
MVP
Posts: 1889
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis » Mon Jan 14, 2019 11:53 am

No problem Lotsaram. As long as it's useful I don't mind that much.

Just a shout out, if anyone else would know of solutions to 4 out of 5 questions (see OP), while still staying in the TM1 REST API itself and not resorting to additional libraries: feel free to comment. Thanks.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

bgregs
Posts: 56
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 » Mon Jan 14, 2019 1:01 pm

@Wim - sorry! This is my last post, I promise :)

@Lotsa

Thank you for taking the time to respond! I realize maybe my posts were too blunt, and I apologize, I hope there are no hard feelings. I must say I did not expect tm1py to have such a strong support network. If the community here thinks that I'm going down the wrong path (i.e. excluding usage of the library for unfounded concerns), then maybe it's time for me to rethink my standpoint. You guys are the experts, and that's why I came here, to learn.

It appears I have some more research to do on the topic, and I rescind my original statements (if only it were that easy on the internet ;) ). Again, I hope this didn't rub anybody the wrong way, that was never my intention. If anything, this whole conversation has helped open my eyes to other points of view, so thank you to everyone that shared their opinions with me.

TL;DR

I put my foot in mouth (as happens fairly regularly). I need to take another look at tm1py under a different lens and reconsider my standpoint on it.

lotsaram
MVP
Posts: 3163
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 REST API

Post by lotsaram » Mon Jan 14, 2019 4:15 pm

Hi @bgregs

Sorry I didn't mean to shout you down.
I have no problem with being direct and opinionated. I am famously blunt myself. :oops:
And I have no issue with difference of opinion, that's healthy.
It's just that in this instance I felt something needed to be said as my (and others) actual experience in terms of creating productive utilities based on TM1Py was at odds with your opinion which seems more based on speculation than actual experience (and I stress I don't know that, I'm only inferring that from your post.)

@Wim I think your point about "no additional libraries" might be misplaced. The point about libraries is to avoid re-inventing the wheel and focus on more value-adding activities. This is basically what TM1Py allows you to do and most of the hard work and abstraction of dealing with the API is already done for you and you only need to focus on solving the business problem. (Also the one of the best part of the Rest API is as long as the Rest API port is accessible then whatever other software you might need can be ANYWHERE).
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Wim Gielis
MVP
Posts: 1889
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis » Mon Jan 14, 2019 4:36 pm

lotsaram wrote:
Mon Jan 14, 2019 4:15 pm
@Wim I think your point about "no additional libraries" might be misplaced. The point about libraries is to avoid re-inventing the wheel and focus on more value-adding activities. This is basically what TM1Py allows you to do and most of the hard work and abstraction of dealing with the API is already done for you and you only need to focus on solving the business problem. (Also the one of the best part of the Rest API is as long as the Rest API port is accessible then whatever other software you might need can be ANYWHERE).
I see your point. The idea is to have small lightweight 'tools' that I can use myself on production environments, tools that just make my (TM1) life easier and more efficient and involving less manual work. For example, AutoHotKey asks for a 7zip backup file location and a cube name, and extracts all relevant files from the backup, puts them in a folder, adds a cfg file with standard property values, adds a shortcut to TM1s.exe and starts the shortcut. That's all done but for this I need to know the dimensions of the cube in question. Yes, a TM1 chore could export that information very easily and I pick up the text file. But if a certain customer / model does not have this chore or TI process, I might lose the time I gain with the other automation.

At the moment I have no time nor desire to learn Python if I can do it with TI, AutoHotKey and the REST API, for example. What might take half an hour from Marius and co could well take me the evenings of a week to figure out. Next to this, I also don't know what I would need to install / copy to that production (!) environment to make it work with TM1py. At the moment I have 2 AHK exe files and that's it (other than some ini text files for settings).

Interesting topic, isn't it ? :D
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

Edward Stuart
Community Contributor
Posts: 223
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: TM1 REST API

Post by Edward Stuart » Tue Jan 15, 2019 10:06 am

Have you tried using the REST API with VBA? Could be a fit for your lightweight tools and use a language you are more familiar with?

This was from some testing I ran a while ago inspired by some posts on the IBM Cognos SDK Forum,

Code: Select all


Private Sub GetServers()
Dim myurl As String
Dim reply As String

myurl = "http://localhost:5895/api/v1/Servers"

Dim xmlhttp As New MSXML2.XMLHTTP60

xmlhttp.Open "GET", myurl, False
xmlhttp.setRequestHeader "Content-Type", "text/json"
xmlhttp.Send
reply = xmlhttp.responseText

Debug.Print reply

End Sub


bgregs
Posts: 56
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 » Tue Jan 15, 2019 1:39 pm

Hi Wim,

I had a chance to play with the REST API a little more and narrowed down your question number 4 to the "ActiveConfiguration" metadata parent. Basically, everything is summed up under 4 categories:

Active Configuration:
  • Access
  • Administration (this is what you are looking for)
  • Modelling
  • Performance
Each of those contains many N levels and consolidations with even more data (basically everything you could possibly want to know about how the server is configured). Using the code below, you just need to breakout your JSON object to whatever level you require (in my example you will see I take the data object that contains "ActiveConfiguration" and filter on data.Administration; in theory you could hardcode data.Administration.AdminHost or whatever, but that could be done more dynamically and on a case-by-case basis):

Code: Select all

	$('#btnCubes').on ('click', function (event) {
		$.ajax ({		
			type: 'GET',
			url: 'https://server:port/api/v1/ActiveConfiguration',
			beforeSend: function (xhr) {
				xhr.setRequestHeader ("Authorization", "Basic " + btoa (username + ":" + password));
			},
			dataType: 'json',
			data: '{"username": "' + username + '", "password" : "' + password + '"}',
			success: function (data) {
				$.each (data.Administration, function (key, value) { //You will need to mess with this a little
                    		$('#divCubes').append (key + ' ' + '<a href="#" id="lnkTempDim">' + value + '</a>' + '<br/>'); //This is messy, but demonstrates the basic concept
               			});
			}
		});
	});
Please excuse my paranoia about posting images with server information. ;) I have translated the results of the code listed above into text, the ">" symbol signifies consolidations (so you can easily see how deep the "ActiveConfiguration" parent will let you go). The results of this look like the following:

Code: Select all

{@odata.context: "$metadata#ActiveConfiguration", ServerName: "TM1DEV",…}
@odata.context: "$metadata#ActiveConfiguration"
>Access: {Network: {IPAddress: null, IPVersion: "IPv4", NetRecvBlockingWaitLimit: "",…},…}
>Administration: {ServerName: "TM1DEV", AdminHost: "TEST", Language: "eng",…}
    AdminHost: "TEST"
    AllowReadOnlyChoreReschedule: false
    >AuditLog: {Enable: false, UpdateInterval: "", MaxFileSizeKilobytes: 102400,…}
    >Clients: {PasswordMinimumLength: 5, ClientPropertiesSyncInterval: "",…}
    DataBaseDirectory: "e:\tm1dev\data"
    >DebugLog: {LoggingDirectory: "e:\tm1dev\logs"}
    DisableSandboxing: false
    DownTime: ""
    EnableSandboxDimension: false
    >EventLog: {Enable: true, ScanFrequency: "P0DT00H00M01S", ThresholdForThreadRunningTime: "P0DT00H10M00S",…}
    >ExternalDatabase: {OracleErrorForceRowStatus: "AutoDetect", SQLFetchType: null, SQLRowsetSize: 50, ODBCLibraryPath: null,…}
    >FileRetry: {FileRetryCount: 5, FileRetryDelayMilliseconds: 2000, FileRetryFileSpec: ["*"]}
    >Java: {ClassPath: null, JVMPath: null, JVMArgs: null}
    Language: "eng"
    MaskUserNameInServerTools: false
    PerfMonActive: false
    PerformanceMonitorOn: false
    RunningInBackground: null
    >ServerLog: {Enable: false, LogReleaseLineCount: 5000}
    ServerName: "TM1DEV"
    StartupChores: null
    >TM1Web: {ExcelWebPublishEnabled: false}
    UnicodeUpperLowerCase: false
>Modelling: {MDXSelectCalculatedMemberInputs: true, DefaultMeasuresDimension: true, UserDefinedCalculations: true,…}
>Performance: {PrivilegeGenerationOptimization: false,…}
ServerName: "TM1DEV"

Post Reply