CAN'T GENERATE A JOIN SQL WITH FM 8.3

[TEVAMANA]

Hi all,

I got a very strange problem and I hope someone had solved this problem and can help me:

Environment: Cognos 8.3
Content Store: Sybase 12
Source : Sybase 12

I defined two layout:

Database view and Reporting View
In Database View, I defined all the relationships between tables, and determinants.  I got one fact table and 6 dimension tables.

In Reporting view
I created Query Subject pointing on tables of Database View, and all the relationships between tables.

I publish the package and test my package with a label of a dimension table and a measure from my fact table, I generate the sql and SURPRISE, the sql generated got no joins but
as many select as labels from dimension tables:

Select toto from fact table

Select label from dim table  etc....

When I check a previous package the sql generated is:
Select toto, label from fact table left outer join dim table on code=code

So I tested to change the governor, to add relationships.... IMPOSSIBLE TO GENERATE A JOIN BETWEEN MY FACT TABLE AND DIMENSION TABLES.

Did someone get an idea about my problem??? (it is 8 years I work with Cognos products and 5 with ReportNet and Cognos 8, neved had this problem before but it is the first time I woth with Sybase and Cognos 8.3)

Thanks a lot for your help

[TEVAMANA]

I may have found a solution in the Cognos KB:

http://www-01.ibm.com/support/docview.wss?rs=0&dc=DB500&q1=sybase+and+cognos&uid=swg21333703&loc=en_US&cs=utf-8&cc=us%3C=en

This is exaclty the issue I get except the fact it is about impromptu 7.3 and no Cognos 8.3, but I already modifyed the file they talk about in Cognos 8.3 environment (\cognos\cer2\cogdmct.ini = Impromptu 7 and c8\bin\cogdmct.ini in Cognos 8) to try to solve another issue so I will let you know if this solves my problem.

[TEVAMANA]

This didn't solve my problem.

It is really strange, in fact I solved my problem by changing the way I was building a model.  If I renamed columns before defining relationship, it was impossible to generate a join... .

Had many troubles with 8.3 and 8.4...

Do someone get the same issue ?

[halkosj]

You say you defined all the relationships in the Database View.  Then you mentioned relationships in the Reporting View.

Which is it? You can only have relationships in one place.

[rockytopmark]

halkosj is right... best place to create relationships is database layer, then all the higher, subscribing layers (like your reporting view) will inherit those relationships.  If you create 1 or more joins in a "higher" layer of your model, you will be forced to create ALL the relationships at that layer... only do this if necessary, which is hardly ever the case.

[twlarsen]

Hey,

So I have a model that I inherited that is setup somewhat the same way, with relationships in the database and the reporting view.  When adding additional items, I try not to use relationships in the reporting view.  However, half of the time it requires me to make the join and the other half I don't have to in the reporting view.  Is there a reason why sometimes I'm allowed and sometimes I'm not?

Todd

[blom0344]

halkosj is right... best place to create relationships is database layer, then all the higher, subscribing layers (like your reporting view) will inherit those relationships.  If you create 1 or more joins in a "higher" layer of your model, you will be forced to create ALL the relationships at that layer... only do this if necessary, which is hardly ever the case.

I'd say this very much depends on the nature of the database. With a strictly transactional database you will need to create your own virtual stars in the modelling layer. When you model on starbased datamarts, then perhaps no additional joins may be needed. Reducing normalization will require combining source-tables to new model objects and if you have to go down that path, then you need to go all the way in the modelling layer..

[rockytopmark]

Blom -

I totally agree.  However, I would say that a well formed datamart would not need the "modeling layer" as it is already designed to conform to Star Schema DW principles.   Cognos suggests the extra, middle tier (Modeling, Development, etc..) is not needed in these cases, unless there will eventually be a RDBMS change in the future... which it may then make this switch transparent.

If you are modeling off a transactional database, first off, good luck... and second, yes, you will probably need to emulate Star Schema design principles in a tier between the physical layer, and the presentation layer .... the Modeling layer ... and thus, your reporting relationships defined there indeed.

[blom0344]

Yes, I Agree. Though I have not yet seen the perfect datamart you mention. There are always additional demands that seep into the model over time.. :'(

[rockytopmark]

Hmm... I guess I have been lucky then... most of my project's DataSources are pretty solid if not very solid to Star Schema principles.  The key has been to communicate the needs and requirements early and often to the Data team, and get them on-board with the fact that the Cognos tier (and team) of the BI project is the most important customer to the Data tier and team.  In the less common cases where we are coming in after the DW has been designed and built, I'd say that is when we see some challenges.

[blom0344]

Star Schema's are great for handling facts. Quite a few (advanced) reports we build cover the need to visualize status changes over time. We deal with non-facts in that area. All these type of reports are way out of the average casual report builder, so we add 'special' query subjects based on rather exotic SQL.
For some reason these type of reports have a huge impact with our clients.
I guess these type of structures are not really dealt with in DWH textbooks..

[TEVAMANA]

You say you defined all the relationships in the Database View.  Then you mentioned relationships in the Reporting View.

Which is it? You can only have relationships in one place.

No you can have relationships in two places.  I have designed very complex packages using this way and it works perfectly, this way is based on a DVD from Cognos USA about Framework and modelling (Reportnet v1.1), the purpose of this DVD was to be able to design complex packages with good performance and with expected queries, and they said to do like this:

In database layer, you define the relationships you got in your database (note that I only define relationships in database layer when I work on a complex design just to be sure that the end-users will generate the queries I expect Cognos to generate), the tables you have imported are defined by a select * from.  If one dim table is joined many times to a fact table, you defined all the relationships without creating alias.

In working view or reporting view, you created subject query,alias and defined relationships between your queries subjects.  Imagine you get an dimention table you need to alias twice because you have 3 keys in yours fact table and you need to join three times this table, the relationships you defined in the database layer are not enought...

Otherwise how do you manage many joins between one dim table and one fact table ?

And in a third layout, you can generate Star Schema using shortcuts.


But to come back to my problem, I found the issue (bug):

The issue is when I rename the fields of my working view, it is like Cognos is loosing the relationships...

I have done a test, one table fact, one dim table, a join 1,1 1,n between these two tables.
In the database layer, none relationship is defined.
In the reporting view I defined the relationship (note that I did not rename the fields).

1- Publish package
2- Report with fields of the two tables
3- Tools/Show generated SQL/MDX
4- SQL shows a join query

Now I rename the field of my fact table used to join the dim table:

1- Publish package
2- Report with fields of the two tables
3- Tools/Show generated SQL/MDX
4- SQL shows a stitched query

If I rename the fields with its original name and republish the package, there is not anymore a stitched query......

Conclusion:
1- The fact to define or not relationships in database layer does not impact the generated SQL because with or without relationships defined in database layer, I got the same issue...

2- To solve quickly this issue, I designed a model with only one layout.  In this layout, I use query subject with SQL ( Select field1, field2, field3 etc... from Table) and defined my relationships. and all is fine...

So what do you think about this ?

[TEVAMANA]

Last thing, those who only work with perfect datamart are very lucky, because I rarely work with a star schema. 

And the best place to define relationships is database layer but if you don't have a datamart, and if the design is complex, the best was to define relationships at the two levels in order to force Cognos to generate the good queries. 

For instance Fact_table1 with fields A,A_B,A_C, and D, a DIM_table with fields A and label.
In Database layout, you define the three relationships in one relationship DIM_table.A->Fact_table1.A and DIM_table.A->Fact_table1.A_B and DIM_table.A->Fact_table1.A_C.

In the second layout, you created query subject and alias and then defined 3 relationships between the fact table and the 3 dim tables.  To define relationships in database viewis to be sure that Cognos will generate the good query...

That was true with previous versions, I'am not sure this is still true with 8.3 and 8.4, I need more time to evaluate these news versions.

[blom0344]

I have done a test, one table fact, one dim table, a join 1,1 1,n between these two tables.
In the database layer, none relationship is defined.
In the reporting view I defined the relationship (note that I did not rename the fields).

1- Publish package
2- Report with fields of the two tables
3- Tools/Show generated SQL/MDX
4- SQL shows a join query

Now I rename the field of my fact table used to join the dim table:

1- Publish package
2- Report with fields of the two tables
3- Tools/Show generated SQL/MDX
4- SQL shows a stitched query

Is this a bug? Doubt it. Cognos advises to build the metadata model in a design language that will never be used as a active language.
Example: Create a model in Korean. Add languages English and Dutch as active languages. Publish package. Build a report in the design language and save it. Change language to English and run report. Report shows objects with the active language.
Now change the metadata objectnames for English and republish. The report still works, no problem.
Now change the metadata objectnames for Korean and republish.  The report no longer works.

I believe the reason is that when creating a report the language you build it in is added to the xml definition of the report.

My conclusion is that it is logically impossible to change objects for combo of design/active language..

[edit]:

^{This is all based on deduction and testing. I am not even sure why it works as it works and that it 'works as designed', so any feedback is appreciated}