Author Topic: Some tough Q and A in Cognos  (Read 122 times)

0 Members and 1 Guest are viewing this topic.

Offline CyborgRepublic

  • Full Member
  • *
  • Posts: 207
  • Karma: +0/-0
    • View Profile
Some tough Q and A in Cognos
« on: April 02, 2009, 06:23:39 AM »
What is meant by Junk Dimension?
How do you perfom while running the report?
Where will you see the time of running report?
Size of the cube?
What are the types of prompts in ReportNet?
What is macro and how it will be work?
What is difference between Cognos and Cognos ReportNet?
How do you create cube in ReportNet?
There are 10 facts are there.How will you connect all?

Problem Description

What are looping joins and how do you resolve them?

Solution Description

Looping joins could potentially return incorrect data.
An example of a looping join

B -> C
A -> < > -> D
E -> F

When you select an item from table A and D Impromptu will try to choose the shortest path e.g. if A -> D existed then this is the path Impromptu will take. But in the above situation the two paths are equal and so Impromptu has to make a choice e.g. A -> B -> C -> D OR A -> E -> F -> D . Impromptu makes it\'s choice based on how the catalog was constructed which cannot be altered once it\'s created; order of the tables in the catalog.
The two paths could return different results depending on the relationship between the tables in the path.
The report would be fine IF Impromptu chose the expected path. The choice is not always right.

Eliminating looping joins prevents Impromptu from making the wrong choice. To eliminate looping joins you can either break unnecessary joins e.g. reports do not need a join between table F and D e.g.

B -> C -> D
A -> <
E -> F

But if you need all the joins use ALIAS tables to break the looping join. Add an alias table for table A and break the join from table A and E e.g.

A -> B -> C
> -> D
Alias A -> E -> F

Both solutions could affect existing reports.

2) Title:
 Looped joins
 Nov 04 1999
Applies To:
 Impromptu - 2.0
Impromptu - 3.01
Impromptu - 3.03
Impromptu - 3.04
Impromptu - 3.5
Impromptu - 4.0
Impromptu - 5.0
Impromptu - 7.1


Problem Description

Under the Joins dialog on the Analyze Tab it states a Loop join is present. What does this mean and how can it be resolved?

Solution Description

A Loop Join occurs when there are multiple paths between database tables. An example of this is A joins to B and B joins to C and C joins to A.

The proper definition of join strategies in an Impromptu catalog is crucial to the success of an ad-hoc reporting environment. Impromptu shelters the user from having to know any of the technical information about the database including name location table and column names and join strategies. The Impromptu Administrator must be very thorough in their definition and testing of the join strategies. Impromptu provides an ability to analyze the joins and determine any anomalies. The most common is the Loop Join.

The implications of the loop join is that there is no way to predetermine which of the various join paths will be used by Impromptu when creating the SQL. SQL is dynamically generated for each report as it is created and before it executes. For example to create a report using columns from tables A and C we could join from A >B >C or directly from A >C. In some cases both of these joins would result in the same data being retrieved. However in other cases it may result in different data. Impromptu will always try to use the shortest route in joining multiple tables. It will also try to use the tables that are already included in the query rather than including an additional table.

There is no hard and fast rule to resolving Loop Joins. There are four basic resolutions:
1. Break the join
2. Create alias tables with different join strategies
3. Use the join expression editor to specify the join
4. Modify SQL
Each of these resolutions is done for a different reason and may have some issues associated with it. Determine the best resolution for your situation by analyzing the data with regards to the results required from the join structure.


The join structure looks like this:


This is producing incorrect results. To resolve this issue make table C an alias to omit the loop in the join structure and this will result in data displaying correctly.

Correct Join Structure:

A C alias

Techronnati | where technology never sleeps

Some tough Q and A in Cognos
« on: April 02, 2009, 06:23:39 AM »

Mountain View


Related Topics

  Subject / Started by Replies Last post
1 Replies
Last post September 17, 2008, 11:51:09 AM
by h2obubbler
6 Replies
Last post December 14, 2016, 10:43:15 AM
by MrSpecialist
1 Replies
Last post September 09, 2016, 08:33:28 AM
by nomadic
0 Replies
Last post September 09, 2016, 12:56:08 AM
by nomadic
0 Replies
Last post August 23, 2017, 11:04:25 AM
by rpmolecule

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.