Can I force a relational DB query to use SQL instead of MDX?

[jackg_tor]

Hi,

We have a relational model built on top of a DB2 database.  All of our reports, to date, have used either Lists or bar charts and I can see that the generated SQL/MDX is always SQL.  Today, a customer complained that his report that contains a crosstab was not working properly and when I investigated, I found that the generated SQL/MDX is MDX.  There are two problems with this:

1.  I know absolutely nothing about MDX except what I read this morning because of this issue.

2.  The report was incorrect because the MDX seemed to want every displayed key value to be unique but they're not.  Specifically, we have a field called "Application" that has some rows with null values and some with empty non-null values.  Both of those display as the same blank entry in Cognos.  With a List, I get two rows for those two distinct values but with the crosstab, I get only one row and the count included only the rows with the nulls and not the rows with the spaces.

I worked around this particular problem by changing the expression for Application to map null to '<null>' but there are probably other places this could occur so I would rather just avoid MDX altogether.  Anyone know how to do that?

I've used crosstabs with other models and never had this issue before so I don't know what's causing it here (although with a different database, different model and different version of Cognos, there's lots to choose from).

Thanks,
Jack

[Blue]

Has someone changed the report to use an MDX / dimensional function or macro?
Has someone changed the model / package so that the referenced item comes from a relational or hierarchical (with determinants) structure now?
Has there been a software update, even just a fix pack?

[jackg_tor]

Robert, thanks for replying.

I can't really investigate changes because this is a new report and, as far as I know, the first one to use a crosstab.  However, I did use your questions as hints and came up empty.  Specifically:

- The model has only two hierarchical dimensions and they were just an experiment.  I don't think that they're used in any reports and definitely not this one.

- There are no functions or macros of any kind used in the report except for an implicit count.

- I should mention that we're running 11.0.1.  I know that's a few fixpacks behind now but upgrading is not our priority right now.

I've attached the xml for the entire report (only 284 lines) in case someone is willing to take a few minutes to look at it.  Without the model, the problem will not be reproducible but I've verified that it can still be loaded and viewed.

Also, here is the generated MDX in case this means anything:

SELECT
  [TBdimension_3].[TBhierarchy_3].[TBlevel_1].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL,  PARENT_UNIQUE_NAME ON AXIS(0),
  ORDER([TBdimension_4].[TBhierarchy_4].[TBlevel_1].MEMBERS, [TBdimension_4].[TBhierarchy_4].CURRENTMEMBER.PROPERTIES("MEMBER_CAPTION"), BASC) DIMENSION PROPERTIES PARENT_LEVEL,  PARENT_UNIQUE_NAME ON AXIS(1),
  {[Measures].[Measures].[TBmeasure_0]} DIMENSION PROPERTIES PARENT_LEVEL,  PARENT_UNIQUE_NAME ON AXIS(2)
FROM [ISOps Integration DB]  CELL PROPERTIES CELL_ORDINAL,  FORMAT_STRING,  VALUE

Thanks,
Jack

[MFGF]

Hi,

We have a relational model built on top of a DB2 database.  All of our reports, to date, have used either Lists or bar charts and I can see that the generated SQL/MDX is always SQL.  Today, a customer complained that his report that contains a crosstab was not working properly and when I investigated, I found that the generated SQL/MDX is MDX.  There are two problems with this:

1.  I know absolutely nothing about MDX except what I read this morning because of this issue.

2.  The report was incorrect because the MDX seemed to want every displayed key value to be unique but they're not.  Specifically, we have a field called "Application" that has some rows with null values and some with empty non-null values.  Both of those display as the same blank entry in Cognos.  With a List, I get two rows for those two distinct values but with the crosstab, I get only one row and the count included only the rows with the nulls and not the rows with the spaces.

I worked around this particular problem by changing the expression for Application to map null to '<null>' but there are probably other places this could occur so I would rather just avoid MDX altogether.  Anyone know how to do that?

I've used crosstabs with other models and never had this issue before so I don't know what's causing it here (although with a different database, different model and different version of Cognos, there's lots to choose from).

Thanks,
Jack

Hi,

If you create a crosstab using a relational model, and see MDX being generated rather than SQL, this tells me one very important thing... You're using a package published in Dynamic Query Mode. In this case, you can probably use the DQM logs to see what SQL is actually being generated.

Cheers!

MF.

[jackg_tor]

Hi MF,

Let me offer a very belated thanks for your help on this.

You're right that we are using DQM but that was done because the Cognos doc implies that it's the recommended approach.  I was going to change it but since I first posted the problem, it's only occurred for this one table so I decided to stick with the work-around for now.

MF, can you elaborate on the DQM logs you mentioned?  Where can I find them?

Thanks,
Jack

[MFGF]

Hi MF,

Let me offer a very belated thanks for your help on this.

You're right that we are using DQM but that was done because the Cognos doc implies that it's the recommended approach.  I was going to change it but since I first posted the problem, it's only occurred for this one table so I decided to stick with the work-around for now.

MF, can you elaborate on the DQM logs you mentioned?  Where can I find them?

Thanks,
Jack

Hi Jack,

Look in <install location>\logs\XQE. You should see a folder corresponding to each query. Within each folder, look for JDBCLog.txt.

Cheers!

MF.

[jackg_tor]

The only folder in logs/XQE is requestDumps and that's empty but perhaps we don't have the right log setting to create the logs you're talking about.

At this point, it's more of a curiosity than a real problem so don't worry about it unless you happen to know the setting, offhand.

Thanks,
Jack

[MFGF]

The only folder in logs/XQE is requestDumps and that's empty but perhaps we don't have the right log setting to create the logs you're talking about.

At this point, it's more of a curiosity than a real problem so don't worry about it unless you happen to know the setting, offhand.

Thanks,
Jack

Hmmm. You might need to generate the logs using Dynamic Query Analyzer. It's been a little while, so I'm rusty :)

MF.