Applied Dimensionality

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.

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.

I’ve been a long-term fan of oracle model by clause, talking about here and there, doing some small&funny examples (like eight queens solution in one select) but lacked some real-life necessity to use it. It always turned out that partition by was enough, or some tricky analytical function was already built-in (like trend calculations I wrote about).

DMR stands for Dimensionally Modelled Relational, a Cognos modelling technique allowing to present relational data sources as OLAP cubes (this meaning adding notion of dimensions with hierarchies and measures with various aggregation rules). All OLAP-style queries, roll-ups\drill-downs are then transformed into appropriate sql (group by’s, aggregations) by Cognos Server. It’s not a Cognos-only idea, see Microsoft UDM (Unified Dimensional Model) and Oracle BI EE works essentially the same way. The whole idea of dimensions\measures seems easy to understand (not by relationally bread-ones, though) so this modelling concept is gaining popularity.

One more DIY article. This time we’ll talk about:

• You have more than a dozen Power Cubes (20?50?100?)?
• And since you can use only 2 CPUs per cube on Windows platform and “update-windows” are only narrowing, you have a couple servers (3?5?)?
• And so you get the task of carefully splitting cube creation tasks on those servers, adjusting build schedules to fit the gaps, manually setting start times on windows scheduler?

Time to get an automatic egg-clock load-balancing!

Scrapped it out from my answer on cognos communities, maybe it’ll help someone.

Drill-through is really to tough to debug.

Prior to 8.3 I’ve used following routine:

1. Set the target report filter to optional
2. Add 2 new text item to target report, and change them to ReportExpressions of ParamDisplayValue (?param?) and ParamValue(?param?)

This will show you what is passed and, maybe, will clarify things a bit.

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.

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).

If anything you do is somehow related to databases, you absolutely, definitely have to see these videos:

Part 1:http://www.youtube.com/watch?v=40Lnoyv-sXg

Part 2: http://www.youtube.com/watch?v=GbZgnAINjUw

Part 3: http://www.youtube.com/watch?v=y70FmugnhPU

Go the links from Tom Kyte’s blog.

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.