Another multiple packages in a DM question

[chmcnm]

I've seen a few posts and I think the answer is "it can't be done" but just want to verify. Any help is much appreciated.

I inherited a spider web. I left for a few years and now am back cleaning up.

The previous developer built a DM using two packages (not DQM enabled if that matters). The version had to be around 11.0.9. The DM had no relationships. The report that used the DM had 3 queries. One query for the 1st package, another for the 2nd package and then a union of the two queries with the results page using the union query. It worked apparently.

We recently upgraded to 11.1 r7. The report started throwing an odd error that had nothing to do with anything on the report. It was a modeling error in the package. I fixed and published. No error now but the report will not get data. It runs and returns nothing. I've rebuilt everything and same issue I run the queries as tabular data with no data returned. If I create the queries independently in separate reports they work.

Before I go back to the client I want to verify that two packages in a DM is not an option. Thanks.

[bus_pass_man]

You have two questions.

One is the problem you have with your report no longer returning results.

What was the error message?  That could help troubleshooting why you have encountered the data issue or at least eliminate the post hoc ergo propter hoc.

What was your fix? How do you know that your fix did not cause the problem you are observing? Assuming that no other change was made, and if there was it might be helpful if you tell us about it. 

What sorts of troubleshooting have you attempted so far and what results did you get.  Do you have a pre-upgrade environment available in which you can compare things like the SQL to the R7?  Do you see warnings or errors in the log files, especially the XQE logs (assuming you are using DQM, which is another question, what query mode are you using?)  How do you know that the union, although producing results, was actually producing correct results? 

What constitutes 'rebuil[ding] everything'?

The fact that the two queries return results when used separately indicates that there is a problem with the union.  You might want to try to identify what elements of the union is causing the problem by systematically controlling for each element by eliminating it, seeing the results and then restoring it and eliminating another element.




The second question is how to deal with multiple FM packages and stage them so that they can be used in a single application.

You might want to eventually end up with a single model which conforms as best as possible to Cognos best practices, where you have defined your dimensions¹ with query path ambiguity dealt with using aliases etc.  Another approach would be to replicate the union in the module but that would only deal with this specific case; you need to think about how to build a general application.

For the former, there are lots of questions to be answered.  One is are there dimensions in both packages which represent the same thing? If so, are they modelled consistently?  For example, non-trivial matters for investigation would include how each dimension's determinants are defined and whether the query items in both dimensions are the same.   No matter what, how are you going to deal with the existence of the two objects? 

There are several approaches to the problem.

One would be to merge the two FM packages using the FM linking functionality. The questions above would need to be dealt with in FM.

Another would be to create views in the module using the objects of the FM packages and to create the relationships between them. This would essentially mean modelling the application in the module but you would then, if you've done it right, have a general application which could used in many reports and dashboards.

In some cases you could create views which would represent star schema groupings but you would need to define column dependency so that your dimensions are defined in the view.  It would be best if you stick to the conventional dimensions and facts approach, at the very least at it would reduce maintainability. 

Assuming the union is just a matter of merging two rectangles of data which have the same metadata, you could create views in the module which represent the two source queries and the create a union of them in the module.  As I said earlier, this is a tactical approach and could have single version of the truth issues as well as maintainability issues.  You would probably want to have something which could be used in any number of reports and dashboards, not just by report authors but ordinary users.



1.  Dimensions, the Kimballian relational thing not the DMR thing with hierarchies, levels and members, which is a concept built on top of the Kimballian thing.