Archive for the ‘contributor-only’ Category

You are currently browsing the archives for the contributor-only category.

TM1 + Enterprise Planning, When, Why and How?

This post will be some extracts from a recent PoC, where we made a complex planning system including a rich scenario modeling part. So it was our first big TM1 + EP integration experience.

Some basic definitions:
Cognos Enterprise Planning (aka Adaytum EP) is a very scalable system, all computing is client-side, nice workflow organization, nice GUI (version 8.4 Eclipse one especially) with huge limitations when it comes to the moment of “show me the whole budget in one place” or to serious what-if modeling. But scaling is trivial, each server acts as many clients simultaneously (number of cores\CPUs) and adding more servers scales the system linearly. So to build really scalable systems, we made Contributor only working places, with all analysis done in Cognos 8 BI and Analyst was turned into a modeling tool. In adaytum and cognos 7 times a lot of things(consolidating, what-if) could be done (and to be honest, still can be) in Analyst, but just up the point you reached 40mln cells cube limit in Analyst, then you started dividing cubes and the system became nightmare. Transforming the same consolidation task into series of Contributor applications connected by admin links gave huge performance enhancements, but the "show me the whole budget" limitation was a major problem.
Cognos TM1 (aka Applix TM1) is an in-memory OLAP engine, ultra-speed calculations, server side computing, excel based gui (with web publication), limited workflow capabilities (compared with current EP), scalability limitations (imagine putting servers across whole Russia to ensure response time)

So when it comes to a planning system with large user base – it’s EP, but if some serious modeling and calculations are requied as well – it’s time to fire up TM1 as well.

Overall interaction scheme seems pretty simple:
tm1ep

1) Most of users work in EP inputting budgets, submitting and doing all other workflow related activities
2) After some user actions (saving budget, submitting, accepting it) data is transferred into TM1.
a. Detecting the desired action is easy – we’ve got Event Studio for that (see here and here).
b. Incremental publish is fired and changed elist is republished – that’s pretty fast
c. Then there’s a problem to extract only changed data from publication. That’s the same as with incremental admin links, you just write a simple database procedure that logs last data transfer time and create views showing only recent data (as usual, you want code samples – drop a line)
d. Changed data is bcp’ed\sqlloaded to TM1 server
e. TM1 TurboIntegrator process is fired, loading the extracted data into TM1
3) Analysts work with “live” data in TM1, doing what-if analysis (they dynamically add scenario versions for this purpose), having access to all enterprise budget and actual data.
4) Then a version could be used for top-down propagation and exported into EP (that’s pretty simple, just some writing into EP applications import tables)

The whole system seems pretty healthy, data transfers happens with 1-2 minutes latency (we used Save as trigger action). Doing any kind what-if analysis on a budget version only 2 minutes "stale" with ultra-speed seems very impressive to me. For instance, in that particular PoC the goal was to speed up overall company budget consolidation, including complex allocation and elimination rules. It took over 8 hours in their current system. And only 4 minutes in Tm1 -- imagine all the benefits you can get from that kind of speed.

Since there are more and more rumours about coming TM1+EP bundle, I just hope that step 2 will be more automated. For example, they can track delta xml's sent by contributor client, containing all data updates, instead of publishing and bcping. But I can see no way they can get rid of neccesity of creating 2 models, one in Analyst for Contributor applications and another in TM1. Although dlists\dimensions can be synchronized, calculations are utterly different (you can target specific cube cell in TM1 formula and only the cube slice in EP).

Posted on May 14th 2009 in cognos, contributor-only, ep cookbook, tm1 | 7 Comments »

Contributor transactional logic example

Finally, we've solved problem stated in communities. And Robert allowed me to publish resulting library here as well. See attached zip file.

Problem statement

We have 2 cubes, one containing product groups sales (for example) and the other containing detailed product data. Users should be able to correct overall group totals and correct detailed product distribution.
If group A contains Product 1 and Product 2 and if Total(A) is 100, it's initially split 50\50.
User should be able to modify Product 1 to 75, yielding result of 75\25.
Normal break back won't work this way, because it'll see 75\50 as profile for break back and will produce 60\40 as a result.

So you'd need to create an own break back. That's easy )
Read the rest of this entry »

Posted on April 29th 2008 in cognos, contributor-only, ep cookbook | 1 Comment »

Incremental Administration Links

In 8.2 we've got incremental publishing, allowing us to create real-time reporting on contributor data and that sets up new frontier in model data transfer speed. Now the slowest part of models are administration links (who'd guessed that when 7.3 appeared).

Here are some ideas of how to speed admin links up, using the same principle as in incremental publish: transfer only changed data. So when only 2 CFO's submitted data, all 200 don't have to be moved and recalculated once again.
Read the rest of this entry »

Posted on February 11th 2008 in contributor-only, ep, ep cookbook | 1 Comment »

Incremental Publish in EP

Just found an excellent option "Table-only Publish Post-GTP" that detects changes that make incremental publish incompatible and republishes application. It should be set to "Yes" by default, imho.

See how incremental publish can streamline your reporting in these posts: 1, 2.

Posted on December 15th 2007 in cognos, contributor-only, ep cookbook, tips | 2 Comments »

More on real-time reporting on Contributor

Since I've got a couple of questions considering how to do it, I'll get in more details on setting up Event Manager to detect application changes. See original post, and mind the second PS, especially.

Some background:
datadatetime column in nodestate table of application database is changed when user saves data, so we want to detect this event.
You need to add a framework package, targeting nodestate table (all columns, except xml data, to fasten things up).
And you'll have to add a publish task (an incremental if you want it fast), and publish it to Cognos Connection.

You need to set up Event Manager like:
1 Create new event
2 Set up event condition of (1=1), or something that evaluates to "true"
3 Add job of publish
4 Go to manage task execution rules
4.1 Set up key of nodeguid (unique elists)
4.2 Set up Ongoing Events to Change and select datadatetime as change key. That is every time datadatetime "changes" ) an event will be raised.
5 Schedule the event on 1 min basis and you're "on air"

Posted on November 8th 2007 in bi, cognos, contributor-only, ep cookbook | 7 Comments »

That parallel-parallel Contibutor

Remember me muttering about array languages?

Just in case, famous Jhost7.exe is a J language execution process. So every computation in Contributor is performed by array-processing J machine, packed to xml and held as a blob in nodestate table.
So it's APL in Analyst and J in Contributor.
It's really cool, because array processing is functional & extremely parallel in nature.
So Contributor can beat virtually any competitor if folks at JSoftware (or in Cognos) will add a decent parallel-processing support and multi-core processors continue their march. It's a damn hard task, but it's easier than adding parallel support to imperative language products like Microsoft PP.

As far as I digged into J sparse matrix support there was a lot to be included and a lot to parallelize. It's so sad that the core code is closed by JSoftware...

Posted on October 12th 2007 in cognos, contributor-only | 2 Comments »

Cognos Enterprise Planning Design principles

Usually these are the signs of coming disaster in an EP system:
1) too many dlists of the same "dimension", some elements thrown out for cutting volumes, calculating or not calculating smth, and you have all seen them laying around just because they seemed to consistent at the time of creation.
2) manually maintained access tables and elist's
3) manual allocation in dlinks

Such systems are consistent at the moment of project inception, but as time flows, people change, one or more dlists don't get updated (well, they kinda lay in another lib, are named differently or smth), one or more AT doesn't get there in time and etc.
And in some period of time (rather short) you end up holding a trembling scycraper by rapidly inserting straws. Terrible, ain't it?
This is a common situation in my practice. It's just because it's so easy to build simple things in Analyst that you miss the point where those simple things have formed one of Mandelbrot's set's.
Nowadays, with "we do all in Contributor small interlinked applications" motto it's more than serious. It's hard to find an outdated dlist in Analyst, while digging through macro, but 10 times as hard to debug a sequence of admin links with respect to access tables used.

There are really some ways to avoid these things. There's no 100%-proof recipes, just some common sense.

Let's go step by step, starting from dlist's.

Handling Dlist's

The main Idea: All dlist, forming one dimension should be maintained through one d-cube.
This is very good with hierarchical dimensions, such as products, so we'll use them as example.

  Brand IsUsedInCalc1 IsUsedInApplicationX ...
Product1       ...
Product2       ...
....       ...
ProductN       ...

Add as much attributes as you need. So if you're gonna do a dlist with not all products for application Z, you just add a column, mark required elements and make it an import link for a dlist.

The sub Idea: All dlist updates and sequencing application updates should be run by a single macro.

So after you create such a dlist for app Z, you add it's @DListUpdate in the UPDATE_ALL_DLISTS_MACRO and add an app Z sync & gtp in there. Therefore -- 1 place to change, 1 button to trigger all changes.

Problems

1) No way to import calc options. Seriously, I don't why it is that hard to do, but you just can't do it (if anybody knows -- drop me a line). So Accounts dimension is hard to handle.
2) No simple way to write formulas. You can write them in text and import Formulae attribute, but adding "{" is annoying.

Some tricks

1) You can use all those DlistItemImport (from cubes, list, odbc's, fm packages) macros instead of import links
2) If you need some sorting, some non-trivial (for Analyst) text operations like spliting\joining strings, adding codes and that stuff -- you can export the cube to a txt file, put an odbc source on it and have a full-blown SQL-92 with CONCAT,SPLIT,LIKE, ORDER and all that nice stuff.
3) You can have a more custom table like

  Name
1 Product1
2 Product2
... ...

and do all the input in cube, w\o changing the list

2 AT and elists

Main Idea: Have a single source (mentioned above) and do everything automatically.

AT and elists can be (and should be) loaded from csv files.
If you add a column "responsible" in the cube above, and calculate a column with "access level" on base of "responsible" (Yes = Write, No = No Data) -- there you go, start loading the thing up.
3,4 dimensions are okay in a separate AT cube, but it should be a cube, not adding rules in applications. It's just that since you'll have some technical applications, you'll have to repeat those AT's more than once -- and that's a dead-end.
Elist are the same, you can export a dlist as elist since 8.1 and enjoy the result, but I'd rather create all those columns myself.

Tricks
1 Maintaining the same Elist Id allows you to change Display names w\o loosing data, so you can work around that.
2 Play around calculating access level. There was a nice topic on cognoise about automaticaly closing past months. So easy to do with @Time BiF.

3 Manual allocations.

Never. No way to find problem afterwards.

Do a CashFlow Account to P&L Account columns in item descriptions and use them as allocation cubes, use allocation tables when desperate.

I'll add more as I remember, comments welcome.

Posted on September 28th 2007 in cognos, contributor-only, ep cookbook, tips | 5 Comments »

Cognos “Real-time” Reporting on Contributor Data

8.2 makes many things nice&simple.

To get a full budget report we need to do the following:
    1) Publish the whole application
    2) Run the report on published data

Publishing a typical application of 100+ elist takes around 20 minutes, so no real-time here.
But 8.2 introduces trickle publish, allowing to incrementally publish only changed data.  This greatly shortens time spent on publishing.

The question now is how to find out that it's time to run incremental publish.  

By pointing Event Manager on nodestate table we can detect any event (save, submit) happening in application. And using Event Manager ability to detect change in specific column (changetimeid for example) we can fire an event each time somebody saves data. This event triggers incremental publish and there you go.
    1) Somebody saves data in contrbutor
    2) Event Manager raises an event, since nodestate table changed
    3) Event triggers a Contributor Macro running incremental publish
    4) Refreshing a report brings out new data
Since only 1 elist is published at a time, speed is astonishing (around 15s in our case).
Maximum lag between entering data and seeing it in report depends on Event Manager request period (minimum 1 min), so in 2 minutes  max people see entered data in reports.

PS. Event manager requires some condition to start an event and doesn't accept simple "true", "forall" or  something, so we write "1=1" conditions )
PSPS. Disallow cache for appropriate reports to avoid reading old data. Set datasource processing to database only and turn off report cache in query attributes.

Posted on August 21st 2007 in cognos, contributor-only, ep cookbook, tips | 6 Comments »

Cognos Contributor easter egg

Can you imagine such a Contributor Application:
2 Cubes (A&B for example), each linked to each other so that when you input a value in a cube A cell, corresponding cube B cell changes to that value, and when you input some value into that cube B cell (the very same changed), cube A cell changes accordingly.
So:

  • 2 cubes
  • 2 cells
  • you can input data in each cell (both writable)
  • date inputed in one cell is transfered to the other

It can be done, I did it as a part of an argument. It's not a thing to use in production, just another thing that "constructor applications" like Cognos EP, meaning EPB as an opposite way, allow.

Posted on April 8th 2007 in cognos, contributor-only | 5 Comments »

Get Data vs Send Data

Nowadays Cognos EP system should look like a set of Contributor Applications exchanging data. That's an axiom, refer to Cognos :)

Since we have these numerous apps, we need to transfer data between them. And we got 2 variants:
1 Get Data extension -- a very nice feature (especially with system links) allowing us to locally execute data transfer from other Contributor applications to the one we're working in. And, moreover only the current elist is used, which speeds up the process.
2 Send Data via administration links arranged into Contributor Macros. Let's assume that we can allow end-user to start such tasks by pressing hyperlinks (i've described underlying technology earlier in this blog, look for "asp macro calls").

But when to use Get Data and when Send Data? That's what I'd like to talk about.

If you're splitting the model into small functional applications (expenses, profits, debts etc to form P&L, CashFlow, Balance as separate applications) and a single person (regional manager for example) should enter his profit data in profit app, expenses in expenses app and get data into P&L app to form his P&L -- you use Get Data in P&L application to transfer numbers.

And if afterwards another person (planning controller) is responsible for Consolidated Company P&L, each unit P&L should be sent to him, not got by him. Controller cannot press get data time from time to get different results. Neither is easy to monitor submission status in the other app. He just wants to be notified when the submitted unit P&L arrives into his application.

Just to summarize it. If it's one person -- Get Data is preferable and if it's 2 or more people, the one finishing work (submitting) should send his results to others. It just distributed systems "Push not Pull" tactics.

Posted on February 15th 2007 in cognos, contributor-only, ep cookbook | 2 Comments »

FireStats icon Powered by FireStats