Applied Dimensionality

Just a quick tip: read this cognoise thread thoroughly — really in-depth discussion.

We’ve encountered an annoying Cognos 8 bug recently. 

Usual scenario if you’re creating reports over Enterprise Planning data includes “unioning” data from several applications. 

For example, you have an Operating Expenditures planning application and a Sales Planning application. To make a simple P&L, you just have to show up data from both these applications. So you can either:

Read Edward Roske and Tracy McMullen’s “Look Smarter than you are with Essbase System 9” over the weekend. It’s really the best “technical” book I’ve read over past couple of years. Written in simple and not annoying language, with a lot of humor (I couldn’t contain myself when I’ve read the fight club joke) — definitely a must-have book for any essbaser out there. Found really helpful, although I knew most of the points already. But, oh my, appendixes rock — calculator cache explained is a real gift.

Why am I writing this?

Well, sometime it helped and we now have allocation tables in administration links. Although I’m sure my post didn’t have any impact, some deem hope remains

With so-long waited new Contributor client, with expand\collapse and nest dimensions features. And a lot, lot more. ActiveX free ) Actually, as long as I’m with cognos ep (since adaytum 2.4), contributor was always the same good-old-rigid tool, so it’s a revolution indeed.

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

Today I just want to add some of points:

• 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
• Dimension for publish must be: ** Stable. It shouldn’t change or change very rarely, since these changes will cause Framework Manager models \ ETL models changes. ** 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.
• There are dlists that are naturally stable aka measures, like {Quantity;Price;Sales}
• 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).

If you’re using CVS for your Cognos Framework manager models (and I recommend to do that, it’s essential as multi-developer and backup_to_point tool), you can encounter the problem I’ve met today.

2 excellent Proven Practices documents were published today, describing how to speed up publising (cognos.com login is required):

– using oracle

– using ms sql server

Same old “If you are loading data, drop indexes” roams ;)

I’ve made this simple web-based closed cube drawing tool to explain what closed cubes are and how they’re good in removing redundant aggregates from storage. I’ve wrote this for myself initially to get better insights on different aggregate situations and thought somebody might get interested as well. Go check it out, if you’re interested in how future olap engines will be working )))

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.