When debugging a report that doesn't retrieve result (with NULL or zero value) for an measure, you may remark that your measure has been replaced with a statement such as:
cast(NULL as INTEGER)
This statement means that OBI can't find a physical relationship between the dimension and the fact table in your logical mapping for the query grain level.
See also: OBIEE - Cast function
To debug it, you have to:
##############################################
-------------------- SQL Request:
SET VARIABLE QUERY_SRC_CD='Report',LOGLEVEL=5; SELECT Dimension.Attribute saw_0, "Fact".Count saw_1, "Fact"."Count" saw_2 FROM "SH" ORDER BY saw_0
+++gerar600:3eb40000:3eb4003b:----2011/08/02 10:14:47
-------------------- General Query Info:
Repository: Star, Subject Area: SH, Presentation: SH
+++gerar600:3eb40000:3eb4003b:----2011/08/02 10:14:47
-------------------- Logical Request (before navigation):
RqList
Dimension.Attribute as c1 GB,
Count:[DAggr(Fact.Count by [ Dimension.Attribute] )] as c2 GB
OrderBy: c1 asc
+++gerar600:3eb40000:3eb4003b:----2011/08/02 10:14:48
-------------------- Execution plan:
RqList <<57013829>> [for database 0:0,0] distinct
D1.c1 as c1 [for database 3023:18568,46]
Child Nodes (RqJoinSpec): <<57013850>> [for database 3023:18568:COBI_DM_LEV_CM_MP,46]
(
RqList <<57013778>> [for database 3023:18568:COBI_DM_LEV_CM_MP,46]
D1.c1 as c1 GB [for database 3023:18568,46],
cast(NULL as INTEGER ) as c2 GB [for database 3023:18568,46],
Child Nodes (RqJoinSpec): <<57013813>> [for database 3023:18568:COBI_DM_LEV_CM_MP,46]
(
RqList <<57013785>> [for database 3023:18568:COBI_DM_LEV_CM_MP,46]
PHYSICAL_DIMENSION_TABLE.Attribute as c1 GB [for database 3023:18568,46]
Child Nodes (RqJoinSpec): <<57013796>> [for database 3023:18568:COBI_DM_LEV_CM_MP,46]
PHYSICAL_DIMENSION_TABLE T144549
) as D1
) as D1
OrderBy: c1 asc [for database 0:0,0]
+++gerar600:3eb40000:3eb4003b:----2011/08/02 10:14:48
-------------------- Sending query to database named COBI_DM_LEV_CM_MP (id: <<57013778>>):
WITH
SAWITH0 AS (select distinct T144549.ATTRIBUTE as c1
from
PHYSICAL_DIMENSION_TABLE T144549)
select distinct SAWITH0.c1 as c1,
cast(NULL as INTEGER ) as c2
from
SAWITH0