Framework Manager Packages 1

[UcfMike]

I've created a Framework project and added three tables to it. Together the tables forma many to many relationship. I've created the proper relationships between the tables. I've also created a package, and brought all three tables into the package and then published the package.

When I go into either Query or Report Studio, and build a report off the package, it's clear the tables aren't joined. What am I doing wrong? Do I have to bring the Joins into the package as well?

 

[mfgf]

Hi,

When you say the tables are not joined, what makes you arrive at this conclusion? Are you getting an error saying "Cross joins are not permitted for the user..." or are you getting repeated data displayed in your report?

If it's the former, then this is an indication that your relationships are not defined correctly in Framework Manager. If it's the latter, then your relationships are being recognised, and the problem is with the logic of how the relationships have been set up.

Generally many-to-many relationships between two query subjects are a bad thing in modelling (often referred to as chasm traps). Can you give us more details about how the relationships are defined? It's possible you may need to bring in extra tables to resolve the many-to-many relationship.

Best regards,

MF.

 

[UcfMike]

I've come to this conclusion for the following reason:

When I create a report with the above mentioned package, I get the results from both tables, but they're clearly not related. When I look at the generated SQL, I'm left with two seperate select statements. The results for instance would be the top 10 rows from one table and the top 10 rows from the other table, but there isn't a relationship between them.

I'm not modeling the data in Framework Manger. I've been asked to recreate the relationships that are in the database, so I'm just hooking together two tables through the create relationship command.

 

[mfgf]

Hi,

When you view the SQL, my guess is that you're viewing the Native SQL, not the Cognos SQL - correct?

If you look at the Cognos SQL, do you see a single query with Coalesce function(s) near the top and a FULL OUTER JOIN in the middle?

Assuming I'm right, you're seeing a Stitch query in your report, which is being generated because the Query Engine thinks your report is spanning multiple Fact tables.

If so, this is not a bug - it's expected behaviour for a query spanning multiple facts and is implemented to avoid double-counting. If the reality is that you do not have multiple fact tables in your data, you would need to remodel the tables in Framework Manager to create a virtual star schema with just one Fact query subject.

Can you provide more details on the relationships you defined (particularly the cardinality) and which items are being retrieved in your report?

MF.

 

[blom0344]

I am probably way off the mark, but during my fasttrack course it surprised how easily it is to create 2 queries with report studio without Cognos even warning that all objects are already available in the first one..

Did you check in he universe what happens if you create a new query subject with objects from 2 joined tables? (sample)

Ties Blom

 

[UcfMike]

For the sake of troubleshooting the problem I've been working with a project that has three tables, lets call them X Y Z to keep it simple. X is joined to Y in a one to many relationship, (1,1 - 1,N) and Z is joined to Y in a one to many relationship (1,1 - 1,N)

The report simple grabs the top rows from each table, no joins show, so a Cartesian product.

I have looked at the Cognos SQL, and it appears it trys to join them, but it's not working. It does create a full outer join.

It sounds like Framework Manager isn't going to be happy if I simply try to recreate the join strategy that makes up the database.

 

[mfgf]

Hi,

If you're seeing a full outer join in the Cognos SQL, then you are definitely seeing a stitch query. As I mentioned previously, this is intended behaviour for a multi-fact query. The problem here is that the relationships you have defined are causing the query engine to mistake two of your tables for Fact tables - it uses the cardinality of the relationships to differentiate fact from dimension.

In reality, you should always remodel the data in Framework Manager to avoid these unwanted query splits (among other things). If you do not do this, you cannot guarantee accurate, predictable results from your reports.

Best practice dictates that the data should be modelled using a multi-tier approach. Tier 1 should reflect your data source tables and joins (as it does in your project at the moment). Tier 2 should remodel the data into virtual star schemas by creating new model query subjects emulating fact and dimension tables in a star-schema presentation layer. New relationships should be defined between dimension and fact as 1..1 -> 1..n. Determinants should also be defined for the common (conformed) dimension query subjects. Tier 3 should be used to create individual star schema groupings (usually as shortcuts), and your packages should be based on the tier 3 structures.

In a nutshell, you cannot just bring in tables and publish packages directly from these structures and expect to achieve valid results in your studios.

Hope that helps.

MF.

 

[UcfMike]

Thanks for the help. I've never done data modeling, since my job was just report creation.

Looks like it's time to break out the Ralph Kimball books.

 

[UcfMike]

Follow up Question.

If I create a project with two tables, and join them in a one to many relationship, Cognos still trys to product a full outer join. Why?

 

[UcfMike]

I did finally find a solution. In the definition in the determinants tab, FM had added an extra determinant. Once it was removed, the joins were created correctly.

 

[blom0344]

The 3-tier approach that mfgf so eloquently has proposed is the current methodology we are taking with our standard framework. It is official Cognos best practice and we understand that it is the way how Cognos deals with multi-fact queries.
The downside of this strategy is that it creates very elaborate and complex SQL statements for rather simple queries and in some cases the SQL generated is more complex than need be (additional joins for instance)

I am interested in how Cognos would propose to solve the common header-detail structure of many transactional databases.

Example:

We have an order header table with additional details in a detail table. Both are fact tables.
Now, in the second tier I can create a query object that merge header and details into one query item. In this way my order facts are within one factobject.

However, if I want to create a report on the number of orders I will then have to query both header and details.
This is rather costly, cause just running the query on the header table would be a scan over a much smaller table.

Personally, I have the feeling that Cognos has introduced a very complex modelling methodology that has been solved far better by Business Objects with the 'context' solution.

Ties Blom