If you are unable to create a new account, please email support@bspsoftware.com

 

Cognos DMR Package - Query cannot be supported in a single fact stream

Started by smithy2582, 27 Mar 2017 07:05:01 AM

Previous topic - Next topic

smithy2582

Hi All,

I have a DMR package which returns the following message in the Cognos analytics dashboardiing tool when using measures that are calculated from two measures from two separate two fact tables. The package is published in DQM mode and there is a common dimension between the two fact tables.

'The aggregate expression .................................... can not be supported in a single fact stream query.'

For example I have a sales fact table, an enquiry fact table and a Date dimension. Sales fact and Enquiry Fact both join to the Date Dimension. A calculation is defined in Framework manager to calculate Sales Ratio as Sales Count (From Sales Fact) / Enquiry Count (From Enquiry Fact) but when this is used on a dashboard showing Month (From the date dimension), it returns the above error.

I would have expected Cognos to go off and do a stitch query and happily bring back an answer. but it doesn't. Any ideas on how I fix this, or explanations as to why it doesn't work would be appreciated.

I am using Cognos 11.0.6 on Windows.

Thanks in advance.

MFGF

Quote from: smithy2582 on 27 Mar 2017 07:05:01 AM
Hi All,

I have a DMR package which returns the following message in the Cognos analytics dashboardiing tool when using measures that are calculated from two measures from two separate two fact tables. The package is published in DQM mode and there is a common dimension between the two fact tables.

'The aggregate expression .................................... can not be supported in a single fact stream query.'

For example I have a sales fact table, an enquiry fact table and a Date dimension. Sales fact and Enquiry Fact both join to the Date Dimension. A calculation is defined in Framework manager to calculate Sales Ratio as Sales Count (From Sales Fact) / Enquiry Count (From Enquiry Fact) but when this is used on a dashboard showing Month (From the date dimension), it returns the above error.

I would have expected Cognos to go off and do a stitch query and happily bring back an answer. but it doesn't. Any ideas on how I fix this, or explanations as to why it doesn't work would be appreciated.

I am using Cognos 11.0.6 on Windows.

Thanks in advance.

Hi,

Seems to work for me using the sample GO Data Warehouse (Analysis) package. I can bring in Revenue from the Sales area, Sales target from the Sales target area, Year from the Sales > Time dimension, and add a calculation to subtract Sales target from Revenue and filter on a year. Can you test using the samples and try to replicate your issue?

MF.
Meep!