Hi all
I have the following issue:
We have a value that is a snapshot per customer per month. This needs to be summed when other dimensions are used (e.g. plants), but when the date dimension or customer dimension is used, it needs to to take the last value for that. We build this is in the framework (DMR) with a regular aggregate set to 'Sum' and aggregation rules on the KPI for date and supplier set to 'last'.
The problem now is that if date or supplier or both is on the report, no value is shown. I've investigated this and I found this on the IBM KB:
The Cognos software is behaving as designed. The root cause of the main discrepancies between CQM and DQM for the reports associated with this APAR is that for DMR, aggregate rules of type LAST in CQM will return the last non-NULL value of the measure, while DQM will return the value of the last member in the set whether it is NULL or not. The DQM behavior is intended: it is consistent with OLAP industry standards and is behaviour that has been requested by many Cognos customers.
It seems that because there are dimension values without a fact record, it takes the last record, which results in a null value. It should take the last 'not-null' value.
Has anyone encountered this issue before and found a workaround?
We're using Cognos 10.2.1 on a SQL server 2008 environment.
thanks
Can you suppress dimension values for which there are no facts? Our ETL process eliminates any dimensions that don't have fact values so that our DMR emulates Transformer in this regard. In our case it wasn't to solve an issue like yours but simply to avoid unnecessary clutter in the member trees.
@Lynn,
that won't be an option as the dimensions are common dimensions used by other facts as well :(
The work around we used - in this case for cumulative measures - is to avoid using the aggregate rule for DMR . In our case setting the rule had very severe performance impact since the measures where storedin rather large facttables. The resulting generated SQL was the bottleneck.
We basically defined a CASE against the fact , setting all values to zero except the one that should be considered last. The problem was that this meant defining additional measures to be used within analysis (switching from the 'normal' measures) Performance was only slightly worse, but it's not a very user-friendly solution I guess..
I wonder if a stand alone calculation of some sort might provide options to consider?
@ blom0344
we already have a lot of KPI's so adding them like this will complicate things a lot for the end users.
@ Lynn
What calculation are you thinking of? We tried to remove the LAST function from the framework and add it in the report. There is no last function to be used in report studio, but by using a rank and then filtering it works. The down side is that this needs to be done every time an end users wants to use this KPI. Not really user friendly...
You have lastChild and lastPeriods functions available to you on the reporting side. I was thinking you might use these somehow in a stand alone calculation, perhaps with a completeTuple that will then pick up the currentMember for any other hierarchies.