Additional rows getting created for various views in the output

[pravin.cognos]

Dear Gurus.

I am using Cognos 10.1 and Noetix as the View generator basing on Oracle R12 db.

The views in the FWM are created by Noetix. I am using them to create a report. 36 views are being used to create the report. As per the database, there should be only one row generated for particular prompt selection. The issue is for some views, a new row is created in the report output,sample output attached. Not sure why is this happening. Till row AX the columns are from one view. New row creation is happening after that. Also some columns have data in all the rows (Same data) and some have only in the first row. Why is this happening?

As per my knowledge, this issue might be due to Conformed Dimensions or Determinants in FWM QS. I dont have an idea on how to resolve this issue. Please help.

Thanks
Pravin

[MFGF]

Hi,

You haven't given us any clues about how you have modeled your objects in Framework Manager. How are your relationships defined? Have you followed the best practices documentation and resolved all the traps then modelled as star schemas?

Regards,

MF.

[pravin.cognos]

Hii MFGF

Thanks for your reply.

Noetix has created the model as well. Actually my access is revoked to FWM now so unable to post the the model diagram. The model has more than 500 QS in it. I am sure that the model is not created as per cognos standards as I dont see determinants in any of the QS, the model does not appear either like a star or snowflake and almost each QS is related to 1 or more QS with different cardinalities. I have attached the generated query SQL from the cognos report as this is the only thing I can access now.

I will provide the details once I have access again. With this info, is it possible to identify what might be causing this issue and how to resolve this.

Regards
Pravin

[MFGF]

Hi,

Can you post up the Cognos SQL rather than the native SQL? I can see a large number of what look like separate native queries, but this doesn't really give any clues as to how they are being merged or utilised.

Cheers!

MF.

[pravin.cognos]

Dear MFGF

Thanks for ur reply. I have attached the cognos sql. Its quite lengthy.

BTW Happy New Year 2013 :)

Regards
Pravin

[MFGF]

Wow! So your report spans 36 different fact tables? Really? That's the assumption the query engine is making, based on the query you posted up. I very much doubt this is the intent. It looks to me like your model is inappropriately designed, and needs some major rework to provide accurate, consistent results.  I think you need to start by talking to the person responsible for the model and discussing the issues with them.

Good luck!!

MF.

[pravin.cognos]

Hey MFGF,

Thanks, I did not expect a reply on NYE ;) happy to see it though..

Bad news is, I am the person supposed to be responsible for the model :(

This model was designed by Noetix Workbench. I have changed the cardinality of the tables to be one Fact table(1..N) in the center and 35 dimension tables surrounding it (1..1) and removing the unused tables. The model previously used to look like a weird steering wheel, I tried to make it a better looking steering wheel (screen prints attached). But then the report stopped responding and always ended in 'time out' error.  The QS doesnot have any determinants in them.

If I need to resolve this, where should I start? I am ready to take up any amount of dirty work.. All I need is to know the reason behind this and solution. Pls guide me how to resolve this issue.

Wish you a Happy New Year 2013 !!!!

Thanks
Pravin

[MFGF]

The SQL you posted doesn't reflect a single fact table model, though. It shows 36 different fact tables. Are you sure you don't have the cardinalities inverted with your dimensions at the 'n' end and your fact a the '1' end? The diagrams are too small to be able to see the cardinalities...

Cheers!

MF.

[pravin.cognos]

Good Morning MFGF,

Actually the model created by Noetix has them inverted (dimensions at the 'n' end and fact a the '1' end) (Before, in the pic). I have modified them to be dimensions at the '1' end and fact a the 'n' end (After, in the pic). But then the report stopped responding. I never got an output. Few dimension tables (6) need to have an outer join with the center fact table, I have set them to be so [DT(1..N) and FT(1..N)]. Even then the report never ran. Is there any possible solution to resolve this issue?

Thanks
Pravin

[MFGF]

I would suggest that Noetix is an, um, shall we say "less than optimal" method of creating metadata for Cognos 10. :)

Does your report really need to span 36 different tables? Really? That's one humungous report!! Are there really that many dimensions in reality, or is Noetix just duplicating the same information over and over again?

In your position, I would look at the real reporting requirements, figure out which of the underlying tables are required to fulfil those requirements, then create a new model bringing in just those tables. Model according to best practice, and you should hopefully end up with a much sleeker, more efficient model.

Just my tuppence :)

MF.

[pravin.cognos]

Dear MFGF,

Yeah, I agree Noetix is causing more pain than gain for me  :D

Small info about Noetix: Noetix creates views from the underlying db tables and these are used to create the model by Noetix. The columns come from more than 36 db tables actually.

The report needs to pick data from 36 views, no escape. All these are not fact views as most of them are dimension views. I tried to model by taking only 36 views but the star design with one view in the center is causing this report to hang if I am going by best modelling practices. (1 Fact - Rest Dim)

1. Can I use conformed dimensions to resolve this issue?
2. There are no determinants in this model. Can you please let me know how to use them, in case that is the reason for this issue?

Thanks
Pravin

[MFGF]

Determinants will likely not be relevant here, as you are using only a single "Fact" object in your model. They are there to allow correct aggregation to happen in a stitch query which spans multiple fact tables held at different levels of granularity (eg a Sales fact table held at Date level and a Sales Target fact table held at Month level).

I'm still struggling to see how a report that spans 36 different dimensions can be useful to anyone? What business issues does the report solve? It sounds more like a data dump to me...

Cheers!

MF.

[pravin.cognos]

Hi MFGF,

Thanks for the quick reply. To answer the business purpose, yes, it is a data dump that spans most of the views that are present in the module.

I have another question.

What if there is an outer join between the fact table and the dimension table? Will cognos consider all the tables with 1..N as a fact table?

Thanks
Pravin

[MFGF]

The optionality (inner or outer join) will not impact the Query Engine's decision on what is a fact and what is a dimension. The rules it uses are:

If the query subject is at the 'n' end of *all* relationships used in the query, it is treated as a Fact
If the query subject is at the '1' end of any relationship used in the query, it is treated as a Dimension

Cheers!

MF.

[pravin.cognos]

Dear MFGF

Then I think I dont have a fact table in my model. The center one acts like a fact table as it is at the 'n' end of *Most of the* relationships used in the query..

Need to see how can I work this out  :o

Thanks
Pravin

[pravin.cognos]

Dear MFGF/All

My report query is asking for 'Local processing'. I am not sure in what scenario it chooses a 'Local Processing'. I have gone thru the documentation but I could not understand it completely. Can you please throw some light on :

1. How/why a query chooses Local processing over Default?
2. What are the advantages, disadvantages of this?
3. If it is bad, what changes are to be done to the query to change this to default.

Best Regards
Pravin

[MFGF]

Hi,

Local processing is generally utilised where it does not make efficient sense to include the processing in the database query (or where specific functionality is not supported by your database). For example, a stitch query uses Full Outer Join syntax in the Cognos SQL. Many databases do not support Full Outer Joins, so the two sides of the FOJ are resolved to two separate database queries, then the results of these are stitched together locally on the Cognos server emulating Full Outer Join behaviour.

Cheers!

MF.

[pravin.cognos]

Thanks for the Nice explanation MFGF  :),

Now I know the real reason behind the pop ups my report is throwing asking me to choose Local processing.

Cheers
Pravin