A long time back while working on a relational ad-hoc package request I ran into an issue. I have been unable to explain the cause for it and the solution involved a work-aound that I am unhappy with. I am posting it here hoping that someone might be able to offer a better solution or even validate my reasoning on the issue.
Let us assume the ad-hoc package involves the query subjects - Product, Revenue and Forecast. Product behaves like a dimension due to being on the 1 side of the 1-N relationship to the other 2 query subjects. I have created a calculation say Calc A in the Product Query subject. The package also has a stand-alone calculation say Calc B defined as Revenue / calc A.
In Query Studio, when you drag Product and Calc B the data is off. The SQL generated by Cognos involves a min on Calc A even if aggregates are set to Total or Sum.Including the function Total inside of Calc A also doesn't help. No determinants are involved as the fact tables are at the same granularity and join to the dimensions at the same granularity.
My assumption on this issue now is that Cognos treats Product as a Dimension and hence includes a Min on the dimensional data item.
My work-around to the above issue was to create a shortcut to Product and join it to Product with a cardinality to make this behave like a fact. This has the downside of the table being queried twice and involves a stitched query when calc B is pulled into the query. Since the Dimension table in my case was not huge there wasn't much performance impact.
I will otherwise have to manipulate cardinalities to make Cognos treat Product as a fact which is not advisable as it impacts other queries and still will not remove the stitched query. Any thoughts on this?
Thursday, December 16, 2010
Subscribe to:
Post Comments (Atom)
Posts
2 comments:
Are Calc A and Calc B in the business layer or the data layer? Any calculations done on the data layer will force Cognos to use the data layer queries as subqueries, with the calculations on top.
Calculations are in business layer. Data layer consists of simple select * from query subjects to avoid meta data call-backs. The query doesn't include any sub-queries only the min calculation on the dimension query objects.
Post a Comment