As many of you already know I have a mixed attitude to Cognos BI DMRs (dimensionally modeled relational source) models:

• performance is allways a consideration, full-table scans are unpredictable
• there’s always ‘local processing’ vs ‘database only and some things not working’ dilemma
• using multidimensional functions in reports is oh-so-hard to debug when they don’t work in complex situations.

Even after 4 years working with Cognos BI I still have to start multidimensional reports with simple cases to prove that functions work as I expect them to (sigh).

BUT at least Cognos has those functions!

We’ve just spent more than a week trying to replicate members() function in Oracle BI project. Function is as simple as can be, it just returns all dimension members from all levels in a single list (see documentation on IBM site). And if you add measure to report – correct totals will be calculated. Oracle BI has the dimension with levels and aggregations concept, but no such function, so you have to turn over your head to achieve this. Replicating dimension table in logical model as many times as there are levels seems the easiest solution.

I’m really frustrated by this. Definitely not the problem I’d like to spend time trying to solve  :(