getting data in xml format in TM-1

[Jeroen Eynikel]

Hi,

I have been asked by a client whether it is possible to somehow push data in xml format (webservices) into TM-1.
(the goal is to get real real-time updates of TM-1 models)

I have absolutely no idea whether this can be done.
I suppose there may be other products in the cognos stack able to do this or maybe something could be done using the api but I haven't even got an idea on where to start.

Does anyone have any experience with this?

Jeroen

[blackhawk]

Yes! This can be done using Enterprise Services. That is exactly what it is designed for.

It gives you the ability to not only push and pull data to and from TM1 via XML, but it also gives you access to many of the API function calls via web services. So now you can build .NET applications that talk directly to TM1 without knowing how to use the API.

So that I don't get in trouble with the moderator , if you want more information, I can discuss this with you in the commercial section, or you can send me a private message and I can fill you in on more details.

[Mike Cowie]

I have been asked by a client whether it is possible to somehow push data in xml format (webservices) into TM-1.
(the goal is to get real real-time updates of TM-1 models)

I have absolutely no idea whether this can be done.
I suppose there may be other products in the cognos stack able to do this or maybe something could be done using the api but I haven't even got an idea on where to start.

Hi Jeroen:

If you're looking at core TM1 functionality then no, there is nothing native about TM1 that can be accessed as a web service (e.g., a RESTful Web service that allowed various HTTP GET/POST operations against TM1). TM1's main modes of accessing data are fairly prohibitive of any kind of extensibility; what I mean is that you can't even insert a custom function, class, etc into Turbo Integrator in any way. You can use some TI functions like EXECUTECOMMAND to fire off an executable that does something more complex, but that's not quite the same thing as extensibility. You're also limited to TI's available data source types, and XML isn't one of those types, currently.

I've once had to access data from a web service for a client, basically from a central service that was used to validate information being entered into TM1. Our solution was basically to program an executable/application in C# that operated outside of TM1 and allowed TM1 to call it via EXECUTECOMMAND. Because web service calls to a web service you don't control aren't always the quickest to return, we did much of this in a batch mode (e.g., TI exported data to a file, kicked off the custom EXE to process against the web service, and left a file for TI to pick up later).

The bottom line is that to do anything like you're describing there almost certainly going to be custom development involved. One option is to use the native TM1 APIs. As you may have already surmised, there are many other 3rd party product options out there, too, that can help depending on what it is you're trying to do and what is the nature of the data being pushed to TM1 via XML/web service. For what you describe, you may not even need to do any TM1/TM1 API-related programming and could, instead, develop a program to simply consume/handle the XML data being pushed to TM1 and translate it into a form that TM1 can easily read with native tools (e.g., generate a flat file from XML that Turbo Integrator is able to read in once it sees it appear). My advice would be, whatever you do, to try and keep it as simple as possible.

If data is being entered directly into TM1 you'll effectively have a real-time TM1 model. For data that cannot be entered directly into TM1, you can certainly aim for the goal of real-time updates of TM1 models, but the closer you try to get to that goal you may want to look at the actual benefits to users vs the costs required to get there. Not just the cost of any additional packaged software or additional software development, but the costs of ongoing support and skills needed for support when something goes wrong.

Regards,
Mike

[Jeroen Eynikel]

Hi Mike,

thanks for your advice. I am basically investigating what options there are out there to do this.

The bottom line is that being able to do this would be an enormous advantage over a competing solution within the same company,
and the business needs really require as close to real time updates as possible.

Business doesn't care about how this is technically achieved, IT is very much in favor of webservices.
Since skills with the API are imo extremely rare and I don't have them myself I am biased towards a third party solution if I can find one.

Jeroen

[Mike Cowie]

Hi Mike,

thanks for your advice. I am basically investigating what options there are out there to do this.

The bottom line is that being able to do this would be an enormous advantage over a competing solution within the same company,
and the business needs really require as close to real time updates as possible.

Business doesn't care about how this is technically achieved, IT is very much in favor of webservices.
Since skills with the API are imo extremely rare and I don't have them myself I am biased towards a third party solution if I can find one.

Jeroen

Hi Jeroen:

What solution are you competing with (if you know or can say)? I agree it would be great if you could say with certainty that TM1 can natively do web services in a competitive situation and maybe someday (doubt anytime soon) the core TM1 product will get further into the 21st century and more natively support things like web services.

Regards,
Mike

[blackhawk]

Mike,

I think getting real time updates from TM1 is a very viable and worthwhile endeavor, especially for market or click-stream related data. TM1 traditionally plays very well in the planning systems, but it has been very underutilized as a data source for other applications. We have built a number of operational applications around TM1 which does not use the standard interfaces specifically so that we can take advantage of the aggregation engine that is so powerful. I expect now that IBM has taken over, you are going to see more of this in the future where TM1 is embeded.

Using TM1 as a data source also has been a great boon to non-analytical or less technical users as well because now they can get data into TM1 easily using an application that guides them through the process of their data entry, rather than throwing some excel spreadsheets in their face and expecting them to know what to do next. The TM1 doors for IT are now starting to open and I think you will see this need for XML based real-time data growing.

[Mike Cowie]

I expect now that IBM has taken over, you are going to see more of this in the future where TM1 is embeded.

I'd like to think so, but I haven't gotten the sense it's a high development priority outside of integrating with other components in the Cognos stack, which does seem to be a higher development priority along with ongoing TM1 server engine tuning. There's talk, I'm sure, but I think we're probably at least a couple years away from that being close to a reality. It would be great to have an edition of TM1 that could be easily embedded in much the way many relational and other databases can be embedded in applications. Certainly from the standpoint of someone who develops and is interested in developing commercial software, I'd love if there were more ways to build on top of TM1!

Using TM1 as a data source also has been a great boon to non-analytical or less technical users as well because now they can get data into TM1 easily using an application that guides them through the process of their data entry, rather than throwing some excel spreadsheets in their face and expecting them to know what to do next.

Absolutely agree - I can't think of too many user-facing applications that we've built these days that are Excel-only. Most of our users interact with TM1 via a web interface or some other non-Excel application - most of these users don't have any idea that it's TM1 in the background (nor should they have to know that). Still, one of TM1's strengths is its integration with Excel and that's something I wish would get some product development attention. By all means make it easier for TM1 to provide/consume XML data and interface with more applications. But, why can't development also take the time to update things like the friggin' TM1 Edit Formula wizard in the TM1 Excel client that's been around for years and is actually used by thousands of users every day!

The TM1 doors for IT are now starting to open and I think you will see this need for XML based real-time data growing.

Probably so and I look forward to it, but it seems like for most clients we deal with today they need help with more fundamental things and real-time analytics is probably a phase 4 or later-type project, if you know what I mean. Certainly it gets talked about, but there are often much more significant problems to be solved before we can even think about getting value from real-time updates. That's not to say it isn't coming soon, but we honestly don't see much demand for it in our customer base yet.

And, as far as IT opening up, they're not usually our main target audience, but I'd be happy very happy to reach a day when IT departments are familiar enough with TM1 to not say things like like: "What's a TM1?" and "What do you mean TM1 isn't ODBC compliant?". One of the big challenges with TM1 and IT is that it's usually very easy to get business users (esp Finance, today) to understand the value in a solution, but because business users and IT often have political differences there's often a struggle somewhere along the line with both parties at fault, particularly if you're trying to get TM1 adopted at more of an enterprise level. Also, other competing products have without question done a much better job of gaining general acceptance/awareness within IT than TM1 has, historically. I'm not trying to be too down on IT departments, but they often get caught up in the technology and tools and don't see the bigger picture problems that business users actually need to solve. On the flip side, business users don't always fully understand the technology enough to recognize other applications that TM1 can be used for. I guess that's where the consultants come in - it's just not much fun when you have to play ambassador to both camps.

Regards,
Mike

[blackhawk]

But, why can't development also take the time to update things like the friggin' TM1 Edit Formula wizard in the TM1 Excel client that's been around for years and is actually used by thousands of users every day!

Amen to that! Not to mention simple-to-fix things like the double-control-c in order to copy text from the TI editor. Sheesh! Sorry, a bit off-topic here.

...most clients we deal with today they need help with more fundamental things and real-time analytics is probably a phase 4 or later-type project

Yeah, I agree. That too has been our experience primarily with new clients, but the ones that already have TM1 installed and have worked with it enough, they get it, and we encourage them to go beyond the "Huh? What data?" deer-in-the-headlights syndrome to really doing some amazing things that benefit the organization, more than just centralizing an out-of-control set of spreadsheets. This is when IT starts to look at TM1 as a viable offering (sometimes kicking and screaming) but once they get it, it really starts to take off. Especially, and I will stress especially, if they can work with TM1 in a comfortable environment like .NET, WebServices and XML.

A typical response we get when we approach IT about TM1: "Ha! You want me to develop enterprise applications in Excel?!? Who spiked your Froot Loops?"

[Jeroen Eynikel]

Mike,

the situation is as follows. I have built an operational application using TM-1 to generate forecasts based on historical data and a range of parameters.

It is being used to monitor and forecast energy consumption and supply + energy trading.

The same company is also using sap bi but that is only used for historical data - no forecasts.

If I can get real time updates for TM-1 I can probably completely replace sap BI. If I can not TM-1 will only be used for the users also needing the forecasts which is a small subset of the users.

I.e. TM-1 is already doing more than Sap BI so logically speaking it would make sense to replace sap BI but company politics taken into account unless there is a very clear advantage for TM-1 in regards to frequency of historical updates as well that is unlikely to happen.

Jeroen

[tomok]

Well, maybe I'm just stupid (like my wife is always implying ) but I don't understand why TM1 has to be able to read XML in order to get real-time updates. How real-time does it have to be? Is every minute or 5 minutes OK? If you can live with almost real time you can always insert a SQL data store in between the XML source and TM1. You could write your transactional data, with another app (which would be easy to write in .NET) to records with a date and time stamp column attached. TM1 could then be set to run a TI process every 5 minutes that queries the table for records where the date and time stamp was AFTER the last time it was run, and then update the LAST date and time stamp so that the next query won't pull the old records, or skip the date and time stamp and truncate the table after loading.

I did this very thing once before for an insurance model where they wanted to put policy transactions from their web site into a cube for instaneous premium and loss analysis. We had IT write a small app that copied all the web transactions into a SQL table with a date and time stamp and we loaded TM1 from that every 15 minutes. We could have done it in a smaller interval but 15 minutes was good enough for them.

I urge you to think outside the box. Just because TM1 can't natively connect all the dots doesn't mean it can''t be done, and done quite easily like in this case.

[blackhawk]

Tomok,

Why go to another data mart? Why not go directly to TM1 instead of using an intermediate database? Is the TM1 engine not robust enough to handle transactions? Furthermore, if there is a product out there that provides XML data to and from TM1, why not take advantage of it?

I am curious because although an OLAP engine has not typically played in the transactional world, it doesn't mean that it can never play there. I agree there are many applications that you never want in an OLAP engine, but sometimes, it can be advantageous to avoid another data mart build and maintenance.

There are cases when you want aggregate statictics very quickly after the transactions hit, or in other areas where you are not really wanting to store the individual transactional hits, you are more interested in the cumulative answer as it is progressing through the appropriate cycle.

[Jeroen Eynikel]

Hi Tomok,

Thank you for your valuable comments.

The point is to avoid intermediate apps in so far as possible (this is an IT request) and IT likes XML as a solution over relational interface, .bat files and so forth.
I am not superenthusiastic about having a TI poll for changes all the time as - although it works, I have done something similar in the past, there are other TI's running in the system as well (needed for certain calculations) and I have concerns regarding the stability of the system when multiple TI's are fired simultaneously.

Without going into details, yes there is real business worth in having updates 'the second they happen' so to say.
As said part of the information concerns trading info and that is a VERY short time business.

Jeroen