Query in Dimensional hierarchy

[dsg1]

Hi,
I am designing a DMR in the Framework Manager. When I am defining the levels in the hierarchies while creating a regular dimension, I am pointing the lowest two leves as unique levels, and setting the scope of the levels with the measures. Is it okay if I point two levels in a hirerarchy to be unique, as both of them have different scopes?

[MFGF]

Hi,

The question to consider is whether the business key values for these two levels are unique throughout the whole hierarchy.

MF.

[raro]

I have a similar request to solve with Analysis Studio and DMR:

Our fact database holds values for each level of the hierachy.
AS ist supposed to get the node value from the database instead
of calculating the sum of all childs.


In fact the people want to use AS as a nice filter-system instead
of using analysis-function of OLAP with DrillThrough, Down, Slice&Dice..

How do a pursuade AS to get the corresponding value to each node
of the hierarhcy from the database

This is our ID-Hierachy:
  0100
  |--- 0110
  |--- 0120
  |     |--- 0121
  |     |--- 0122
  |
  0200
  |--- 0210
  |--- 0220

It is a parent-child table:
pardent child
------------
null    0100
null    0200
0100  0110
0100  0120
0120  0121
0120  0122
0200  0210
0200  0220


Each "Child-"node has a value in the fact-table:

ID      Value
---- -------
0100  10
0200  5
0110  8
0120  3
0121  5
0122  7

Doing it the standard modell-way, I created a QuerySubject for each Level,
joined them together via parent-child and linked the lowest leave-level to the
fact.

When I pull the 0120 node from the hierarchy-tree into AS
it will link the level below, pull the  corresponding values for 0121+0122,
have a total for 0121+0122, which in my case would be 12.

But, the correct value for the node 0120 is 3 and it is right in the database.

So ... and ideas on how to pursuade Cognos to get the data from the database
instead of calculating it?

By the way:
The system is a financial system. The finance-controllers don't like cognos calculating  totals. "They" rather like to add up each position of the P&L statement themselves.

regards,
raro

[delight]

This is our ID-Hierachy:
  0100
  |--- 0110
  |--- 0120
  |     |--- 0121
  |     |--- 0122
  |
  0200
  |--- 0210
  |--- 0220

As I know You Can't do the "external aggregation" using DMR [see attached file]

It's very interesting...using DMR You've modeled unbalanced (ragged) hierarchy? HOW?

[raro]

It's very interesting...using DMR You've modeled unbalanced (ragged) hierarchy? HOW?

... created a QuerySubject for each Level,
joined them together via parent-child and linked the lowest leave-level to the
fact.

[delight]

... created a QuerySubject for each Level....

Thank You for your answer!

I Suppose using this prudential approach we'll see an one level hierrarhy in Analysis Studio (AS) for each level of unbalanced hierrarchy such as using SAP BW 'cubes' as a datasource.
But I'm looking for this visualization of hierrarchy in AS as ragged tree structure of dimension elements:

  0100
  |--- 0110
  |--- 0120
  |     |--- 0121
  |     |--- 0122
  |
  0200
  |--- 0210
  |--- 0220

So we can't do it using DMR, can we?
[PP Transformer cubes helps us  :)]

[bobsey]

I have a similar question to the post by raro:

I would like to use AS on a relational source.  Assuming that using DMR is the only way to use AS, is it possible to configure DMR in a way which aggregate level data is retrieved directly from the database instead of being calculated 'bottom-up' from the detail data each time?

I already have a fact table containing unique dimension values and data for all aggregate levels, and in my dimension sources, my dimension id's are unique across all levels of each dimension.

All I want is for the resulting Cognos SQL to simply select the aggregate data directly from the fact source using the appropriate aggregate level dimension level (ie. NO group by)

I find it difficult to believe that there is no way to force this kind of behaviour.

All comments welcome

Thanks

[raro]

I find it difficult to believe that there is no way to force this kind of behaviour.

We opened a case with Cognos Support and had THE german DMR support specialists on site.
Replies in this thread also confirm the bevhaviour.

IMHO there is no way (up to now).

Maybe we should send an ER (enhancement request) to Cognos?

If we find more than 5 users requesting the same feature, i would take the job and prepare the ER.

[bobsey]

Thanks for the reply

If there is no way to do this then how can the DMR approach ever be expected to deliver acceptable query performance with significant data volumes and measures with an average number of dimensions (6?) each having multi-level hierarchies?

Presumably, there is a reliance on the database being able to rewrite queries seamlessly to use pre-prepared aggregate data?

Without this being in place, I cannot see how DMR can ever perform well, especially when calculations (variances, time comparisons, etc) are introduced into these queries.


Is DMR a new feaure in Cognos 8?   If so, perhaps raising an ER would be a good approach. 

Thanks again

[raro]

Dear forum-user, be encouraged to leave a note in this thread! We need your feedback for the ER!

Thanks.

[raro]

If there is no way to do this then how can the DMR approach ever be expected to deliver acceptable query performance with significant data volumes and measures with an average number of dimensions (6?) each having multi-level hierarchies?

cynical?

Without this being in place, I cannot see how DMR can ever perform well, especially when calculations (variances, time comparisons, etc) are introduced into these queries.

Speed is imho ok on small amount of data, e.g. gosales. Analysing data >1M rows is not possible.

Is DMR a new feaure in Cognos 8?   If so, perhaps raising an ER would be a good approach. 

Yes, DMR was introduced in C8. Will collect votes for an ER.

Have a nice day.

[bobsey]

Hi there,

I genuinely wasn't being cynical, just realistic - how many real-world implementations analyse data sets of smaller than 1M rows?  Not many is the answer.

I think an enhancement request would be a good idea - consider this one vote!!

In the meantime, I'd still very much like to hear from anyone who feels that there may be a way to 'trick' DMR into retrieving aggregate data directly from a database.

Thanks

[almeids]

I genuinely wasn't being cynical, just realistic - how many real-world implementations analyse data sets of smaller than 1M rows?  Not many is the answer.

If "not many" is truly the answer then IT is doing the business analyst community a disservice.  Analysis, aka decision support, should not require an excrutiating level of detail and should be focusing on aggregate trends and interrelationships.  If details are necessary to decision making they should be made available separately via drill-through.  Too many implementations show a disregard for practical volume concerns.

Presumably, there is a reliance on the database being able to rewrite queries seamlessly to use pre-prepared aggregate data?

Yes...so if that is a critical requirement despite all practical design attempts, you should be running a database that supports it.

[jparkgb]

Hi there,

We are trying to do exactly the same thing.

We are embarking on a major BI project and our users want the Cognos front. It has been formally evaluated and it is felt it is the best for our needs.

However, in order to scale to a large number of users effectively over large data sets without spending LOTS on CPU and extra disks (eg use MOLAP technology in the database to carry out aggregation) we need this functionality.

We are talking to our Cognos representatives about this, but unless this support can be added we will most likely look at another tool - which is a real shame.

Another vote to count!

Thanks

James

[x___Lee___x]

Hi,

We were looking at using an Oracle data source using the embedded OLAP functionality and have also come against this issue. It seems Cognos in it’s latest release isn’t aware that the data can be held aggregated in the data model, so it pulls back all the data and trys to aggregate it itself. This is particularly important as the Oracle 10g and 11g database have an internal MOLAP engine which is effectively invisible to the query tools as it is accessed via SQL. The functionality is there in the database, we just can’t get Cognos to use it.

It’s also essential to remember if you’re pulling back big data sets and then aggregating this can take for ever â€" or never return. If the tool was aware this data was already aggregated in the database it could simply pull the values back required. Doing this relationally would be a nightmare and I wouldn’t recommend this approach for large data sets.

Interestingly Business Objects is aware of aggregate levels…..

Thanks

Lee

[almeids]

I have not taken advantage of these capabilities so take this with a grain of salt, but my understanding is that Oracle can take care of the aggregate awareness...i.e. any aggregation queries against detail data for which Oracle maintains preaggregated results would be redirected transparently by the database engine to the aggregate tables.  In theory, this should work with any front end including Cognos.  What sort of design considerations, setup or overhead it involves I can't say.

[KevSull]

Just throwing my tuppence in the ring - we were looking at using Cognos as it was the perfect tool for the job, until we found that it lacked direct support for Oracle MOLAP, and sorry this was a showstopper for us. Come on Cognos, sort yourselves out!

[jparkgb]

I have not taken advantage of these capabilities so take this with a grain of salt, but my understanding is that Oracle can take care of the aggregate awareness...i.e. any aggregation queries against detail data for which Oracle maintains preaggregated results would be redirected transparently by the database engine to the aggregate tables.  In theory, this should work with any front end including Cognos.  What sort of design considerations, setup or overhead it involves I can't say.

The problem is that whilst the older report net supported this, Cognos 8 server specifically writes querys to return low level data, which it then aggregates itself. So even if the aggregated data is available in Oracle, the db will never dish out the aggregate as Cognos 8 specifcally asks for the detail so Cognos can do the aggregation. You can check this by looking at the SQL it fires to the database.

[almeids]

I understand now, thanks jparkgb.

[raro]

OK ... next week the Cognos show is taking place in Las Vegas.

I gave this thread to the Cognos User Group responsible from Germany-Switzerland-Austria. The CUG is traveling to Las Vegas and hopefully will meet the adequate Cognos-people.

[raro]

Las Vegas did not bring any news to this discussion :'(

We adressed this case directly to PM at Cognos Canada now. Let's see.

Would be pleased to collect more votes in order to put a little more emphasis on "DMR + auto-aggregation" it.

[raro]

ProductManagament (PM) at Cognos Canada had a WebEx Session with us last week.
The problem is understood.
DMR needs a "autosum off" feature.

PM will set  up a business case now.

To put more pressure on it, we need your support!!

Please send me a personal message stateing your
personal and company name, as well as a small
letter of interest / two sentences comenting interest
to hand it on to Cognos PM.

Thanks for you help!!!!

Together we can make this change happen.

regards,
RaRo

[christerb]

Would be pleased to collect more votes in order to put a little more emphasis on "DMR + auto-aggregation" it.

I can't find any way to send personal messages. Anyway, you have my vote. I work in online gaming and we have billions of transactions so we would really benefit from this. Currently we use powercubes built on aggregated tables, but they still take hours to build so I would like to be able to investigate Oracle OLAP.

/Christer