Cognos , outer joins and OLTP schema

[blom0344]

I have been taught that using outer joins is a no-no in the world of Cognos data-modelling.
However, I need to rebuild some BO universes that work with outer joins.
These universes relate to OLTP schema's and some outer joins cannot possibly be eliminated.

Now, there is no option to use a DWH (cause this is a temporary situation)

The second issue is that of factless facttables. Some transaction tables have zero facts.
In BO modelling I would create measures like:

Code Select Expand

count(object) or

Code Select Expand

count(distinct object)

and this measure would be aggregated against dimensions.

pure and simple from an SQL perspective.

So, the second question would be: how to create the facts?

[MDXpressor]

I wouldn't say Modelling an outer join is not allowed, it is just a dangerous practice if your users don't understand the behaviour of an outer join.

Again, you can create this type of calc in the Framework Model.  Right click on the Query Subject you want to add the calc to, select Edit Definition.  Go to the Calculations tab and click add, this will put you into the Calculation Definition window and you can SQL your heart out.

Hope this answers your question!

[blom0344]

Modelling the outer join is not by choice. It is simply the consequence of having to build a framework on an OLTP type database.
The consequence of only using inner joins may then result in data-loss.

I am not sure what you mean by adding a calc. Do you mean creating measures for factless facttables?

[MDXpressor]

I am not sure what you mean by adding a calc. Do you mean creating measures for factless facttables?

Yes, that's what I meant.  Did I misunderstand your question?

[blom0344]

I think we're talking about the same thing. However, I have seen developers create a model query subject out of a factless facttable by adding an additional object with a value 1.
This is then used as a pseudo-fact. So, instead of:

Code Select Expand

count(order)

the measure would be:

Code Select Expand

sum(additional object)

The last aggregate would simply summarize the 1 values, but yield the same outcome.

Summarizing integers is even faster than counting strings, so that would be a benefit..

[anand_study]

In case if someone needs it

https://www-304.ibm.com/support/docview.wss?uid=swg21382347
This suggests that do not define the outer joins in the FM model and do not deny them via the Governor setting. Instead, simply define the outer join in the report (using two queries and then join those two in third query).


             --------- Q1
            |
Q3  <---
            |
             --------- Q2

[blom0344]

Hmm, reviving a four year old post   :o  Back then I was trying to make the switch from Bobj. to Cognos   ;)

You solution will work in plenty of cases, but it demands a lot of insight for those who merely build reports.  Especially in situations where nullable FK's are used, defining outer joins is inescapable

[the6campbells]

From a query planning perspective, the presence of outer joins can limit opportunities to re-order joins etc because of the implied semantics of outer joins. This can translate into performance hits.

Nulls to any business users are confusing but some DBA's think by putting not null constraints on all columns precludes null and may forget that A LOJ B or A ROJ B or A FOJ all have the potential to introduce nulls. Their existance can impact the semantics of a predicate and expressions which users and even 'SQL capable' folks may overlook.

In Report Studio you have a layout pattern called Master-Detail which can present data where-by for each master row a query is executed in the detail layout object. This pattern preserves master rows which had no details - in effect it is an implict outer join without having to use outer joins. Subject to the business requirement authoring your layouts using page-sets and master details may enable solutions which removes/reduces using an outer join.

Impromptu offered a feature which is not in C8/10 which had somewhat limited user value. It simply overrode the model joins and turned all outers into inners. Again, casual users and people not familiar with the data are less likely to know and to check if they start playing with joins.

[the6campbells]

In case if someone needs it

https://www-304.ibm.com/support/docview.wss?uid=swg21382347
This suggests that do not define the outer joins in the FM model and do not deny them via the Governor setting. Instead, simply define the outer join in the report (using two queries and then join those two in third query).


             --------- Q1
            |
Q3  <---
            |
             --------- Q2

The 'issue' for SQL execution with outer joins will fall into these categories:

1) It restricts the choices the optimizer can apply to re-order the join sequence because it has to honour the semantics of the outer join.
2) It potentially increases the amount of data at any stage in the execution tree
3) The RDBMS vendor may have crumby execution options re temporary space, falling to NLJ vs using hashing etc etc

The business user 'issues' are:

1) Did they get the expected output - did they want a sparse or dense result set
2) Do the expressions (filters, calcs) account for the potential of null values
3) Business users do not know what nulls are (nor do they care) more often than not - even SQL programmers make mistakes or are fooled by non-ISO SQL behaviour of their vendor (null || 'hello' = null not 'hello') etc.

In *SOME* situations the user wants data to be preserved (sparse answer) and subject to data volumes it may make more sense to determine if MASTER-DETAIL/PAGE SETS (master detail), disjoint layouts etc meets the same goal and does so without explicit OUTER JOINS.

Explicit joins in a report should be used with caution if rendering as interactive HTML with relational - consider disabling query re-use in that report.

In general, the 'urban legend' of outer joins isn't without substance but many persons don't understand SQL semantic issues distinct from how their RDBMS may deal with them or how to read a query plan to try to figure it out. Sounds harsh but generally fact.

[cognostechie]

It is true that outer joins would slow things down but it is also true that outer joins are a neccesity especially when dealing with an OLTP system. I have done quite a few projects when FM was reading from OLTP systems and outer joins was always part of the deal. At that time, Cognos was the company (before takeover by IBM) and consultants from Cognos had joined hands with the software company owning the OLTP system and they created FM model with outer joins. One solution to avoid that is to create Cubes instead of FM Model but then it also depends on your volume of data and the level of granularity you need in the reports. There are a couple of software companies who have chosen only to use Cubes and they don't even have a FM Model.