Data missing in random columns in Analyses (12c 12.2.1.2)

Excellent idea, gentlemen, but unfortunately that is not the problem here.

As I mentioned, I am using a very tiny, simple model to test this and the physical query is correct and returns data for all columns. Additionally, both this sample model and all of our other affected models work correctly in 11g; this issue is specific to 12c.

I am not new at this. Please read all of the bullet details. Thanks.

For a given analysis - that is, the same combination of dimensions and measures on the Criteria tab, ordering is not important and neither is filtering - the same column will always be blank. Whether run directly in Answers, or via the "Open" link in the webcat, or embedded on a dashboard.

If I add a column to the analysis, one of three things will happen:

1. The same Column A will be blank as it was before
2. A different column will now be blank - possibly the column I just added, possibly some other column that was previously showing data
3. All columns will show data (at least I presume this is an option; my experience so far is that once a request is 'broken', adding additional columns will just change which column is empty)

If I remove a column from the analysis, the same 3 options above are in play except that eventually when the query gets small enough all columns will show data. How small is "small"? Well in our converted 11g content, we have analyses which are 10 or 20 columns wide and they're working fine. And we have some which are 5 columns and they are 'broken'.

We noticed this issue during our first round of testing after the upgrade - the developers were all out checking over their dashboards and several reported that this or that request was just missing data. I was able to reproduce using several different models and, eventually, using a simple test model as I'm using now. Note to Christian - we were doing our original testing in Alta, so the problem occurs with that style as well.

The current RPD I have loaded up was created in the 12c Admin tool as a blank RPD. It's not the converted 11g RPD with everything deleted from it.

If I issue the logical SQL directly, I get the same results - in this case column s_4 is blank. If I add dimension X anywhere in the SQL, then s_3 is blank. If I move dimension X or s_3 around in the SELECT clause, still s_3 is blank. If I change out Dimension X for Dimension Y, now s_9 is blank. It is very bizarre.

Security is the migrated 11g setup. So instead of "BIContentAuthor" we have "BIAuthor" and so on. Plus all of our normal roles. I am of course signed in as a BIAdministrator. And this test model has, of course, no special security applied to it. Open to BIConsumer.

I am going to work on two things to help troubleshoot: 1) a munged version of the model so I can post specific examples here and 2) doing a byte count of the physical query results in my SQL tool and compare it to the byte count reported in the session log. I want to know if the data is actually making it back to presentation service or not.

Any other ideas are much appreciated.

I haven't tried a direct ODBC connection yet; I will try to set that up. I'm also going to play with degenerate dimension columns vs. normal, and calculated columns vs straight mappings. We did have one other 12c instance up and running; I'll talk to our product manager about getting access to that for a quick test.

Ebin: there are no aliases; this is a brand new test model in a brand new RPD. I doubt I can post an RPD easily, but I'll see what I can set up. If I can replicate this with an XML data source (currently we're using Teradata) then I could package up something that is self-contained.

I agree this is very peculiar and "interesting" for certain definitions of "interesting". :-) Currently this is a complete roadblock to our upgrade, so management might find less interest and more frustration in it. Especially considering Oracle was very big on how easy 11g -> 12c upgrades were compared to 10g -> 11g which was a nightmare. We've actually been using this tool since it was nQuire, and a few models still survive from that time period, if you can believe it.

If I can solve it (with your help), I'll be Golden Boy for a week - until the next complication.

Thanks for the help!

Our 12c is on Red Hat 6.8. 11g is on Windows so we're changing OS along with the upgrade. To keep things simple, you see. <rolleyes> (not my decision)

I tested the logical SQL using a direct ODBC tool. The empty column comes back "" (empty string) in the raw results. So, that's significant.

I tried comparing byte count, but the reported row width in the session log is 3x larger than even the maximum width of the columns, nevermind the actual data width. So I'm not sure how to validate those numbers.

If I add a filter to the query on the "missing" column, the filter is applied correctly, which makes sense since the physical SQL is correct.

Just out of curiosity: Have you already run a vanilla RPD on that server to see whether you get the same beahviour?

Well, the RPD was created using File -> New Repository from the 12c Admin tool. I then uploaded that RPD (after much difficulty getting the upload to work due to my own stupidity) and created my test model in it in online mode. Is there a more-vanilla RPD that I can use? I know that I can somehow "reset" the service instance, which is supposed to put everything at some kind of default state. Is that what you're referring to?