Archive for the ‘ep cookbook’ Category

You are currently browsing the archives for the ep cookbook category.

Cognos 8 EP troubleshooting import data from package

Tip of the day: Having common metadata layer for BI and Enterprise Planning is really cool, especially with the ability to load data from BI packages to EP via links. Just that it sometimes doesn't work. But there are always some workarounds.

With mostly very cryptical error messages or without any.
One of the problems might be just that DataManager service (which does the transfer) cannot allocate enough memory for operation. This is what we've encountered just today.

Good news are that this parameter is easily configurable, you can just edit cognos_install_path\bin\dataimportserviceconfig.xml file and set JMX (Java maximum allocated memory) to something more appropriate than default 128M (1024 is a nice number, for example).

Try this if your links fail ;)

PS: In this 'wonderfull' Java world, setting JVMX is a common boost-up technique. And there's a lot of places to apply it. Think about Cognos Rich Client Platform (Cognos 8.4 Contributor) for start...

Posted on August 6th 2009 in cognos, ep cookbook, ibm | 2 Comments »

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 »

More on choosing Dimension for Publish

I already wrote some notes on choosing dimension for publish in this post.

Today I just want to add some of points:

1 If you're up to publishing some serious amount of data, variant with adding a dummy dlist with 1 item and using it as dimension for publish seems a very bad idea.  You're terribly slowing proccessing and wasting tablespace. So you have to choose a dimension for publish to reduce table size and speed things up

2 Dimension for publish must be:

a Stable. It shouldn't change or change very rarely, since these changes will cause Framework Manager models \ ETL models changes.
b Have sane (~3-33) number of elements. If less than 3, there's no win in performance. If more than 30, ensure it won't change, because big dimensions change more often than small ones.

3 There are dlists that are naturally stable aka measures, like {Quantity;Price;Sales}

4 In other cases timescale seems a really good choice. For example, if months dimension doesn't have year signs in name (like Jan, Feb, Mar), columns in publish won't change ever.  To work with timescale published data you can use sql unpivot queries, that will virtually turn it into view publish, but it'll way more effective than publishing it by 1elem dimension in first place. I've settled for this variant in my current project. Moreover, if you wrap this unpivot sql into ETL procedure, you can treat all published data uniformly while loading into datamarts (publish all needed cubes on timescale, use the same etl procedure).

Posted on July 5th 2008 in bi, cognos, ep, ep cookbook | No Comments »

Enterprise PLanning Objects Naming Convention.

As I've promised, here are naming conventions I tend to follow in EP projects.
Setting up naming rules doesn't seem so crucial at project start, but generally hits on you on the head on final phase, when development turns to support. And trying to support your colleagues model will leave you thinking about naming objects the same way more than once ;) So naming conventions are one of main project documents for me now.

I really expect and encourage comments on this naming proposal. Maybe together we will create something like Sun Java Code Conventions )
Read the rest of this entry »

Posted on May 16th 2008 in analyst, blogging, cognos, ep cookbook, tips | 4 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 »

Modeling transactions in Contributor

There are some situations when you definitely need some way of recognizing new input from user. These are rather rare occasions, but when it's nice to have such a tool is sack.

One of the applications was discussed in this communities topic. By the way, I'm spending much time on communities these days (it's a very slow site, so no surprise) and I recommend it as a valuable resource for finding cognos-related answers. A steady pace of 10 topics a day gives a rather solid knowledge base now, after 4 months of uptime. If only it was faster, sigh...

So how to catch the fact that user changed this specific cell?

Simple, you just have to remember about virtual dimensions and calculation rules to build a "trigger".

1 You add a simple dlist {value, old_value, new_value_flag}, with new_value_flag = (value?=old_value). new_value_flag is a dlist-formatted element with (yes\no) elements.
2 After user inputs new data into cell, this flag will change to yes (by IF formula) and you can use this data by writing an accumulation dlink using only new data (select yes value from new_value_flag virtual dimension).
3 When you've finished processing freshly inputed data, you just run another link that copies value to old_value, and that sets the flag back.

Ps: It's too bad we didn't follow that topic on communities to a logical end.

Posted on April 17th 2008 in cognos, ep, ep cookbook | 7 Comments »

Cyclic calculations in Analyst or “Make yourself an own @SliceUpdate”

In the old times, before SliceUpdate first appeared ) we've thought that one thing analyst lacked to be really all-in-one tool was ability to do loops. For, while, until -- all that nice stuff. And when SliceUpdate was introduced it became clear that @Test(Restart) allows you to cycle macro execution on whatever basis.

Using @Test(Restart) allowed doing a lot of interesting things, like calculating optimal production plans (repeat until reach given condition), even a sample calculation of admixtures for zink was made once ;)

But most common use of @Test(Restart) was building your own Slice update macro. A detailed instruction follows.
Read the rest of this entry »

Posted on February 21st 2008 in cognos, ep cookbook | No Comments »

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 »

Ways of calculating Running Count to import data into Multi-Line models

I've nicked the term "Multi-Line" model out from some cognos best practices presentations. Never known it was called that way )

Multi-Line is only way to go when you have a potentially huge dimension only a tiny bit of which should be available to end-user at a time. Like employee planning, whole dimension of 10k people, strictly less than a 100 in department. So you create a fake dimension of 1..100 and add an Employee name column, decreasing cube volume by 100. Access tables + cut-down might help, but it's sometimes better to allow people pick up any employee,client, product given up that there won't be more than fixed number of rows.

Main problem with this modeling technique arises when it's time to import data in such cube. That data usually doesn't have an 1..100 running count attached, so it's your task to add it.

In this post I'll sum up the ideas of how to calculate running count split by elist (that's usual, ain't it?).

As a example, I'll use this simple table

dept emp salary
Finance Pete 100
Finance Ann 200
Finance Jo 300
HR Nick 100
HR Sam 200

And you have departments as an elist, so you have to number rows so that numbering will restart for each dept. Numbering requires an order so let's alphabetical order of employee names.

So this is what we want to get:

dept emp salary running_count
Finance Ann 200 1
Finance Jo 300 2
Finance Pete 100 3
HR Nick 100 1
HR Sam 200 2

Read the rest of this entry »

Posted on February 1st 2008 in BI&CPM, bi, cognos, ep, ep cookbook, large dimensions, open source | No Comments »

Contributor Audit Reports

A common question of ep admins is: "How can I see all users currently logged into planning wrt applications they're using?"
Something like: "Steve is now editing Capex and Sales Planning, and Mary is the only one in HR application now".

The task relatively simple for 1 application (yet required view publish for version prior to 8.2, now there's a special table with users). You just join nodestate with users and states tables and enjoy nice pie chart of submitted,in progress and not started. Even a direct drill-through in application, if you'd like.
But it becomes messy as more and more applications are added.
I've seen variants in Cognos ReportNet, PHP, IIS log parsing and liked none.

Yet all applications are listed in pad datastore and you just have to iterate the "1 application solution".
So I just dream of audit package for EP that would automatically add all applications and give admins some rest of programming and framework changing,

Did anyone out there do any of audit reporting on EP? What were your problems & solutions?

Posted on December 23rd 2007 in cognos, ep cookbook | 1 Comment »

FireStats icon Powered by FireStats