Categories
Results in report null, but query returns values
Hi, I have a strange behaviour in one of my reports:
The query that runs behind this report returns results normally, however on report we see nulls in these columns. To make it even stranger, this only happens on some rows, not all.
To give an example (obviously the report in question is a bit more complex)
CityID
| CityID | CityName | Count of Employees |
|---|---|---|
| 1 | Moscow | 20 |
| 2 | London | 10 |
| 3 | New York | 40 |
| 4 | Berlin | (null here) |
The query that I get from session logs returns Count of Employes for Berlin = 50. So why is null displayed here?
To add to this, if I filter the report in the prompt to CityID = 4, so that only the row with Berlin is displayed, I get the correct Count of Employees!
| CityID | CityName | Count of Employees |
|---|---|---|
| 4 | Berlin | 50 |
If I check the query again, it is identical except for 'where CityID = 4' condition added to it.
What could be causing this strange behaviour?
Thanks and regards,
kzane
Answers
-
Does anyone have any idea what could be causing this?
0 -
Have you checked the BI Server logs (NQServer.log and NQquery.log) to check what SQL is being sent down to DB for both queries? That would be a starting point then you can look at repository based on your findings.
0 -
Hi Joel,
yes I have. The query returns correct data from DB in both cases (I already mentioned this in the first post), but the report shows nulls in the first case. This is why all of this is so strange to me.
0 -
I would ensure that the OBI server is connected to the right backend.
Also, if any non development environment is disabled for logging and you are taking query generated by OBI Development server to the other environment, there are no typo errors especially in where conditions.
Regards.
0 -
If the physical query works, then looks like a logical issue in the RPD and how you have your facts and dims joined and content levels/hierarchies setup.
0 -
Hi Thomas Dodds,
I checked the RPD. To be a bit more specific, I use 3 different fact tables in this report and 2 different dimension tables.
I made sure that each of these fact tables are indeed connected to the two dimension tables (on logical and physical layer) and that the content level for the three fact tables, is the same for both dimension tables.
So:
Fact Table 1 - Connected to Dimension A on level A1 and Dimension B on level B1
Fact Table 2 - Connected to Dimension A on level A1 and Dimension B on level B1
Fact Table 3 - Connected to Dimension A on level A1 and Dimension B on level B1
Now the problem appears as soon as I put in one fact field from Fact Table 3. Then the data from Fact Table 1 and 2 displays nulls (but only for some rows, not all), UNLESS I filter to for example one CityID (see my opening post). So this CityID displayed nulls for Fact 1 and 2 when it was unfiltered and when filtered to just this CityID, it displayed the correct numbers for Fact Table 1 and Fact Table 2. Might also mention here that CityID is the lowest level of the dimension.
So my question now is... from the RPD it looks like Fact Table 3 has the same connections/settings as Fact Table 1 and 2. So why is it causing this issue?
Something to add as well: In the session log where you can see the physical query, this report runs 3 queries (for each Fact Table) with /*+ STAR_TRANSFORMATION */ in it. All these queries return correct data when I run them on the database. I think OBI somehow doesn't merge these queries correctly...
0 -
post the logical and physical queries, we'll take a look.
0
