Click here to jump straight to the third instalment starting at part 17.
Click here to jump straight to the final instalment starting at part 23.
Part 1: Introduction
There's nothing intrinsically special about the number 1000, or multiples thereof. It's purely by historical chance that we use a base 10 numbering system which makes such numbers appear to have a significance that they really don't. Nonetheless, it feels somehow viscerally wrong not to acknowledge such milestones in some way. For others, I generally make mention of them reaching an x000 post count in the General forum. For myself, I like to write an article which goes beyond merely answering a question on how to format a websheet.
For my 4000th post I wrote a moderately lengthy article on using TM1's classic API in VB.net. At the time of writing it had garnered over 8000 hits which is somewhat extraordinary for a relatively niche area of knowledge.
(Though when it comes to "lengthy", you ain't seen nothin' yet.)
For this, my 5000th post, I'm sticking with a similar theme. However this time I'm writing something that most of you who know my past posts probably never expected me to write, given the antipathy that I've expressed toward Java in the past. And that is, of course, an introductory article on using the Java client API. And I emphasise that this is the client API, not the one used to do TI extensions. However you have to crawl before you can walk, and the client API is a decent place to understand Java skills first.
NOTE: For clarity and in line with the "Please don't ask for credit as refusal may offend" policy, while I'm happy to answer general questions if I can, please don't expect me to debug / write your code for you. I ain't getting paid for this, remember. And you ain't seen my rates.
Some Background To The Content
As this article was being drafted, I was approached by Cubewise to do a presentation at their Sydney conference for 2015.
(If there is a Cubewise conference near you, attendance is highly recommended. The amount of information about future and current developments that you get at those things is brilliant, as is the list of guest speakers.)
Accordingly this article was redeveloped to serve both purposes; it stands in its own right, but also served as the symbiotic basis for the speech that I gave last Thursday. And as the poor unfortunates who attended that speech know, I screwed up the timing of that speech royally by vastly underestimating the time needed to present it, meaning that the most interesting bits were omitted. (And indeed, I'm still not sure how I managed it quite that badly but one should never assume that the whole is the sum of the parts when it comes to time.) In this article I'm limited only by the amount of time you can keep your eyes open and how fast my fingers can move.
This article is intended to help you understand, in a way only touched upon in the speech, unfortunately:
- When and why you might need to use an API-based solution (and why you may not want to);
- Why you might use Java for that solution;
- For those who have a background in VB/VBA (even if it's just in Excel) but limited or no understanding of Java, what to expect when making the transition to the Java language;
It can't be a complete tutorial on either Java itself, or the TM1 API for Java, but at the end of this article you will hopefully at least a guide to get you started if you decide that this is something that you want to pursue.
If you come across terms that you aren't familiar with, take a look at the end of the article for a Glossary. I'll keep moving that post to the end of this thread with each instalment so that you can always find it.
About The Intended Audience
As with the presentation, as with my YouTube channel, as with any audience greater than about 10 people, the difficulty in writing articles such as these is that you can never be certain what the knowledge level or expectations of your audience are.
If you have no knowledge at all of coding principles, you will get lost in this article. Hopefully you'll still get a flavour of the possibilities, though. Also when my TM1 Bytes series reboots on the TM1Channel on YouTube in a couple of months you'll find that there is a parallel series called Programming Bytes which teaches the essential principles of programming to non-programmers; it's primarily intended for people who want to learn to automate Excel but does go beyond that.
The base level audience member is expected to have some knowledge of coding principles, probably from working with VBA in Excel, even if it's just using the macro recorder in Excel and fiddling around with the code to customise it.
If you consider yourself competent in VBA, so much the better.
If you've dabbled in the VBA TM1 API and want to know how to jump the gap to Java, great, but you don't need to have any previous API experience. Where I cross refer to equivalent functions in the classic API, you can just ignore those with no harm done.
If you're a capable Java coder but haven't played around with the TM1 API, you're possibly going to get a bit bored with the Java basics that I cover in the first few instalments but will hopefully still get some value out of it toward the end.
At the Conference I mentioned that this class of reader would be the ones throwing rotting produce at the podium and yelling "Your code sucks, Kirk". Surprisingly that didn't happen. (In part because I ran out of time to show the bulk of the code that was supposed to be the heart of the talk, as I touched upon in the previous section.) But as I mentioned then, it does highlight an important point; you don't need to be a gun Java coder to be able to develop working code.
I certainly don't regard myself as such; I only started getting seriously into Java in the last couple of months when I realised that the wind was blowing too strongly in the direction of Java in the TM1 world to bother resisting it. Similarly if you understand basic coding principles, and basic Java principles, you can produce useful code as well. Maybe not the most efficient code, maybe not the most elegant, but functional… and sometimes that's good enough.
And of course there was doubtless one final class of attendee at the Conference; the ones who made me think of an occasion when General George S Patton stood before an audience and said that they were there to "see whether I am indeed as big a sonofabitch as some of you think I am". I'm sure that they were not disappointed.
Still, the way that I completely screwed up the presentation (see above) means that it's not merely unlikely that Ben H will ever invite me back to do another one. I suspect that he in fact won't even let me through the audience door next year unless I sneak past by growing a suitably obscuring Joshua Chamberlain-style moustache, wearing a poncho and a sombrero pulled down over my eyes, and registering under the name Dr. A. Nony Mousse PhD from the TM1 Technical Academy in Malmo Sweden. Consequently for the foreseeable future you're stuck with articles like these, and the TM1 Channel.
About The Use Of APIs In General
An application programming interface (API) is exactly what the name suggests. You have an application – in this case, TM1 – and you write programming code to manipulate that application via an interface that the application exposes. This allows you to automate the application, and extend its functionality. This "what" part is easy.
The "when to do it" part is less so.
There are two words that I learnt in SQL Server development; "Fear Complexity". They should be engraved over your keyboard, whatever application you use or support.
When you decide to implement a solution using any API rather than the native functionality of the application, you instantly make the solution more complex. The solution needs to be documented, which often as not is something "for tomorrow". And when the documentation (if it's done at all) is needed, it can never be found. More complex solutions are harder to maintain and to upgrade. They're less transparent to end users and to people who take over ownership of the solution after you.
Consequently there needs to be a specific reason for using an API solution, and usually that reason is that you have a business problem which simply cannot be solved in any other way. We have occasional questions on the forum where people ask "can we do X, Y and Z using the API?" and the correct answer is frequently something like "Well yeah, you can... but why not use an action button instead?"
At the Conference Hubert Hijkers (the scarily smart Chief Architect of TM1 Server; ask him to talk to you for 45 minutes about the trie structures (see the Glossary if you think I've misspelt that) used inside the TM1 server some time and you'll see what I mean by "scarily smart") was demonstrating the new RESTful (REpresentational STate) API. He said something to the effect that previously, using an API solution was a last resort, but that the RESTful API may make it the first port of call. Make no mistake, I regard the RESTful API, now that I've seen it in action and have some idea of how to approach it, as being one of the most utterly freaking brilliant leaps forward for TM1 in years and I can't wait to use it in anger; I already have a bunch of things that I have in mind. But those are things that I simply cannot achieve with built in functionality.
And the complexity issue remains, especially as RESTful, brilliant as it is, probably isn't exceptionally friendly for non-technical users. I would maintain that the use of an API, any API, will remain and should remain a last resort. (But by all means, play and learn, and be aware of your options.)
The Example Used Here
The example that I'll be using is one where we need to obtain stock prices from a web service on the Internet and load those values into a cube. This needs to be done on demand.
Obviously you can't do this entirely within TM1 because there is no native web service data source for TI, and there probably never will be because there are far too many nuances and variations to such data sources anyway.
We could conceivably have an application that pulls the web source into a text file, and have that text file picked up by TM1, but you then need to ensure that the file has finished writing before attempting to load it. This necessarily introduces a gap between the time of the information and the time of availability.
In this case using an API solution seems to be a decent fit because although there are the workarounds that I described above, they don't really solve the key problems.
Of course, I'm fudging a bit here because I won't be using real time stock data. That costs money and I'm not spending it just to write an article. Instead I'll be using the Yahoo Finance API to suck down values from that service which, as with all free stock services, is delayed by about 20 minutes. However first it's free, second unlike Google, Yahoo is not known for arbitrarily pulling its APIs (do a search for Google Translator API Spring Cleaning if you want to learn new and exciting words of profanity from developers), and third, and best of all, there's a Java library that wraps around the Yahoo Finance API which returns the data as nicely formatted objects so that I can focus on showing you the TM1 stuff without having to also show you raw web access code and turning the later instalments of this article into "War And Peace, Volume XII".
The cube is made up of 6 dimensions:
- SP_Exchange
- SP_StockCode
- SP_DateCode
- SP_TimeCode
- SP_UploadTime
- SP_Measure
The stock code is of course the stock listing code on that exchange.
The date code comes to us from a Yahoo Finance Calendar object (explained later), and is converted into a text string to get the element name. I format it as YYYY-MM-DD and it represents the date that the data relates to.
The timecode comes from the same Calendar object and is formatted as HH:mm:ss, though in reality the source data doesn't appear to have a granularity greater than 1 minute. This represents the time at which the stock data applied, and it appears to represent my local time rather than the local time of the exchange. (The Java library that I use with the Yahoo Finance API seems to provide some methods for conversion but for the purposes of this exercise I haven't felt a need to explore that path.)
The Upload Time is a string in YYYYMMDD_HHmmss format (plus an element called Latest, which receives a copy of the last set of numbers uploaded as well) which indicates when the upload was done. It has a single consolidation called Total Loads which exists not because it has any real world utility, but simply to allow me to show you how to use the API to assign different weightings to elements in a consolidation. (It weights the "Latest" element as 1 and the other elements as 0.)
The SP_Measure dimension contains the elements Last Volume (the volume of stocks traded that day up to the time of the upload), Last Price (the last sale price of the stock), Last Offer (the lowest offer to sell the stock at that time) and Last Bid (the highest price a buyer was willing to pay at that time).
The final Java application can be called by a scheduled TI chore, and it will update the cube accordingly. It needs to:
- Get the data from Yahoo Finance;
- Scan it to see if there are new exchange or stock codes and create the elements and update the dimensions accordingly;
- Scan for any date or time codes that haven't previously been used and add those to the dimension... yeah, OK, I'm slightly lying there. As you'll see when we get to the code, for simplicity's sake I'm not actually checking whether any of those elements exist first. I'm calling the Java API method to add them into the dimension regardless. Just as with the TI DimensionElementInsert function, if the element is already there it doesn't matter. However in a real world solution I'd probably check for the element first and update the dimension only if there were any new ones found to save the processing overhead of doing a dimension update. But a point I shall belabour a little in this article is that this is not designed to be a production system application. Not in the sense that it's unusable (it's in fact quite usable and fully functional and capable of being applied in the real world, as the results in the screenshot below show), but in the sense that at all times it was built with the purpose of teaching specific concepts rather than operational efficiency in mind.
- Create the Upload Time timestamp code and add it to the Total Loads consolidation, weighting it as zero.
- Load the values into the cube to both the current timestamp element and the Latest element.
Why Java, Why Now?
Those who know my history know that I do not love Java. The issues that I have with Java as a language remain unchanged:
- It's a case sensitive language. This harks back to the days when people were expected to serve computers rather than the other way around. (Java dates define "the epoch" as beginning on 1 January 1970, and that tells you all you need to know.) I regard case sensitivity as nothing more than syntax errors waiting to happen, and there's a particular pitfall that you can encounter with Java as we'll see. But to be fair to Java it's intended to be a cross-platform language running on operating systems (typically any descendant of the UNIX family) which are themselves case-sensitive. Consequently there probably wasn't a lot of choice in the matter.
- Java is a language which is descended from the C language which means that each block of code is surrounded by squiggly brackets (called braces in Australia, and that's the term I shall use throughout) rather than actual words like If / End If or Do / Loop or Function / End Function as they are in VB. And for mine those squiggly braces are also syntax errors which are waiting to happen if you don't match them up properly, a fact that I was reminded of as I was writing the demonstration application for this article. Lovers of C languages and haters of VB claim that this difference makes VB "verbose". I say it makes VB "intuitive". PotaYto, potAHto…
- Java is utterly, utterly awful at easily creating graphical user interfaces. I could have a full user interface with data grids and flashing lights done in VB.net by the time I had managed to draw the first panel in Java. And even when you do build them, they tend to be slow and chunky and clunky; just look at any of the Java applications (like Performance Muddler) in TM1 10.2. This is probably an unavoidable by-product of being cross platform, since operating systems handle graphical user interfaces in different ways.
- Java has a well-established track record of security issues.
- First, the transition of the TM1 web server over to Java-based Apache Tomcat means that server side Java skills will be useful. Maybe not essential, but useful. In theory we may even be able to write additional code in Java to extend the user experience of your TM1 Web site, though with Apache Web still being essentially version 1.0 I don't think anyone is feeling suicidal enough to try that just yet. (And, admittedly, there will eventually come a point when "Prism" (or whatever it is finally called) will probably make TM1 Web as we know it redundant. But in the interim, Java skills will not go astray.)
- Second, and perhaps most importantly, IBM has seen fit (probably for the reasons described above) to provide additional functionality to the Turbo Integrator language using Java extensions. Consequently if you're not at least familiar with the Java language you'll deny yourself the opportunity of using those extensions. And that means that your problem-solving toolkit as a TM1 administrator will be diminished. Let's be honest, there hasn't been a lot of development of the TI language since... ever. Java may (and I concede that this is speculative) become increasingly important in the development of TI code. The present implementation of this is quite limited (I recall one MVP describing it as something of a hack and a workaround), but again this is version 1.0 and there may be further development down that path.
- Note: I repeat that this article is about the client side Java API, not the TM1 extensions which do not use the interface that I'll be showing you. They're a separate issue. But as I said, the client side Java API is a good place to cut your teeth on the language.
- The GUI problem is immaterial if all you want is a command line solution. And in the case of much automation (including the case that we'll see here) a command line is all you need.
- The Java API libraries are used as the basis for Android, which is continuing to gain market share hand over fist. Android itself doesn't use Java in the way that we'll be looking at, but it does (for the moment at least) use its language. The potential to leverage TM1 data on an Android platform should be obvious, though there are a couple of headwinds here. The biggest is the current legal stoush between Google and Oracle over the right to use the Java APIs in Android. If Google spits the dummy hard enough and decides to replace the Java APIs with native Android ones, that advantage goes out the window. Though given the current size of the Android codebase, there would be a lot of inertia to overcome in such a change. As noted above it's not like Google hasn't screwed developers over in the past, but such a change may hurt them harder than the developers and way harder than Oracle. Who knows, maybe it'll even let Windows Phone (which is not actually a bad product but is screwed by the fact that hardly any third parties bother writing apps for it) back into the race; stranger things have happened. The future is always an unknown, despite some people being paid quite tidy sums as "company visionaries" to predict it. (Incidentally, if anyone wants to pay me 250 large per year for sitting around on a bean bag wearing a skivvy, pontificating about how I see the future evolving and trying excessively hard to look cool, you'll find my contact details on my profile. I'll even bring my own dartboard.)