Report not executing nor give an error

[diasbrad]

Hi Friends,

I am facing a very unusual problem. I have a report which is executing for a long time and it neither gives an error.

Nor it gives an output.

The report used to work a week back, but now it keeps on executing.

One observation that I noticed in one of the query item is that there is a Query Subject that is use, but the Query subject is not appearing in the Package published in Cognos Connection.

For Example:

[Oppty Transition Report].[Oppty Transition Report].[Dim Opportunity Natural Key]

First is the Name Space > Oppty Transition Report
Second is the Query Subject >[Oppty Transition Report]
Third is the Query Item > [Dim Opportunity Natural Key]

The first part and the second part is not appearing in the Package that is used to build the report.

There are 2 different query with different logic as per the business requirement that use this Query Subject. When I try execute both query one give me an output where as the other keeps on executing.

I tried to rebuild the query which does not work from the one that works but I face the same issue.

Currently I do not have access to the Frame Work model.

I have only access to the Cognos Connection.

Is there a way how to fix this report from Cognos Connection or analyse what could be the issue?

As of now I am not being able to identify what is the exact issue.

Thanks In Advance
Brad

[Raghuvir]

Hi Friends,

I am facing a very unusual problem. I have a report which is executing for a long time and it neither gives an error.

Nor it gives an output.

The report used to work a week back, but now it keeps on executing.

One observation that I noticed in one of the query item is that there is a Query Subject that is use, but the Query subject is not appearing in the Package published in Cognos Connection.

For Example:

[Oppty Transition Report].[Oppty Transition Report].[Dim Opportunity Natural Key]

First is the Name Space > Oppty Transition Report
Second is the Query Subject >[Oppty Transition Report]
Third is the Query Item > [Dim Opportunity Natural Key]

The first part and the second part is not appearing in the Package that is used to build the report.

There are 2 different query with different logic as per the business requirement that use this Query Subject. When I try execute both query one give me an output where as the other keeps on executing.

I tried to rebuild the query which does not work from the one that works but I face the same issue.

Currently I do not have access to the Frame Work model.

I have only access to the Cognos Connection.

Is there a way how to fix this report from Cognos Connection or analyse what could be the issue?

As of now I am not being able to identify what is the exact issue.

Thanks In Advance
Brad

Hi Brad,

is this the issue with a particular report or all reports ?

Regards

[diasbrad]

Thanks Raghu for you reply.

This is happening for a specific report.

[bdbits]

Do you have access to Framework Manager? You can reverse engineer the model out of the content store.

Since you are missing a query subject, can you ask whoever is doing the modeling about it? Seems like that is the core issue for you.

[diasbrad]

Thanks Bdbits, for the replay.

I just got in touch with the FM team and they mentioned that the the Name space and Query subject is kept hidden in the model that is why is not seen on the Package. They have not mentioned what is the reason for hiding it.

But for a test purpose they created a test model of the same model and published it by un-hiding the Query Subject.

After that too I am facing the same issue.

But, what I noticed that in the by right clicking on the field in the Properties the Datatype is Character and the Usage is Fact.

Could this be a reason that the report is taking long to execute ?

Another thing that in noticed in the report is that the field what was getting filtered was a text field but the elevator was a numeric value.

For Example

The Filter Field Expression

"Subscription in (?Sub?)"

Where Subscription Field Values were (Subscription, One Time)
Where as ?Sub? the static values were (1,0)

So, I was thinking if this could be the reason the report was going into a loop. But surprisingly it was not giving me an error for mismatch of data type it keeps on executing.

Thanks in advance
Brad

[bdbits]

So they only recently made a change to hide some things? And this report was created before they did so? I would venture to say most things in my FM models are hidden except for presentation layer objects, and there are many good reasons for it. But you would not normally expose them in the first place, then hide them later as this breaks reports - as you have seen.

It would not normally make sense to have characters datatypes as facts. The ?Sub? should be fine - "Sub" will be the name of the prompt. I wonder if maybe this is the team's first project? There seem to be a lot of basic things being missed, e.g. hiding things that may be in use without studying impact. If so, an investment in some training is probably in order. Just a suggestion.

I am left feeling this report needs a pretty big overhaul, maybe even to rebuild it with the current package. In Report Studio, choose Tools > Show Generated SQL/MDX. Copy the generated SQL and paste it into your database's native query tool. See if it can possibly work, and use explain plans or whatever aids your query tool offers to determine the performance bottlenecks. This should provide you enough feedback to first, determine why it is taking so long, and second, help figure out what should be done to fix it. You may need to request model changes to fix some things.