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

JustTheFacts

We're having a bizarre problem with Analysis results in 12c which is: at seemingly random times, columns will be empty in the Results tab. I can't put up a picture due to corporate data protection concerns, but to help visualize, if the request criteria is:

Customer, YearMonth, Location, Revenue

Then the results may be:

Customer	YearMonth	Location	Revenue
Acme	201701		1000
Foo	201701		2000
Bar	201702		3000

More details:

• The physical SQL is correct, and returns data in the suspect column
• The suspect column changes if the analysis changes; in the above example it's Location, but if I add Product to the analysis maybe it's Customer that goes blank and Location shows up
• It is not always the same ordinal position that is blank - it may be the 1st column or the 10th (so far at least, it's never been a measure though)
• The suspect column does not change if filters are added/removed or if columns are rearranged
• Session log shows no errors or unexpected post-processing of query results
• It is not a problem with column formatting (i.e. white text on a white background); the cells are actually empty
• Export to Excel has empty cells, export to CSV has two consecutive commas where the data would be
• Tested with both IE and Chrome, on multiple different client machines
• Tested using Skyrios and FusionFX
• This is an 11g -> 12c upgrade, however I'm using a fresh (empty) 12c RPD as a starting point, with only a tiny test model
• The chosen view does not matter - Table, Pivot Table, Chart - the column will be empty in all of them

Needless to say this is a deal-killer for this upgrade. Now that I've solved my (self-inflicted) problem with uploading RPDs - needed to get that fresh, empty 12c RPD loaded up - I've got to solve this issue or we're dead in the water. An SR to Oracle is guaranteed, but if you've ever dealt with Oracle support...well...you know.

Any suggestions are appreciated.

Robert Angel

Double check your content levels fact to dimension, both should have a dimension hierarchy level set for the content that you are using as a combination, so per your example I would expect whichever dimension provides the location to have content level set as detail and the fact table providing revenue to have the corresponding level set for that dimension, presuming no aggregate fact tables are involved.

Hope this helps,

Robert.

[Deleted User]

Beaten to the punch by @Robert Angel - it's your content levels in your logical facts and dimensions which are messed up and hence the engine can't properly do cross-star queries and aggregate nicely.

But the columns affected are anything but "random". They reside in different fact tables in your business model which are tied to conformed or non-conformed dimensions. The former case not showing data means your model is seriously messed up because conformed dimensions should result in data and for non-conformed it just means the work hasn't been done completely and non-conformed dimension aggregation hasn't been set to the "Total" level in the fact.

JustTheFacts

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.

[Deleted User]

Ok apologies. Read that one too fast. Never seen that one before nor can I reproduce it on any of my environments (nix or Win),

This is one of the reasons I find it really bad that the public SampleApps have all disappeared because it would give everyone an easy platform to reproduce issues - or not in your case.

So you said Skyros and FusionFX...even if I can't force the behaviour with either of those myself...have you tried Alta?

Gianni Ceresa

So you have an upgrade 11g to 12c where the existing analysis randomly (so not all the time) have one of the columns being empty. For the same analysis once it works fine, once column A is empty, once column B is empty and so on, right?

What's the status of cache? Do you have that also with newly created analysis? As you said it's upgrade but using an almost empty RPD: is the RPD new or the old one where you deleted pieces? What's the status of other things (catalog / security): the migrated one or a new one or pieces of the old one you changed?

Is the LSQL consistent? If you execute the LSQL do you get the same behaviour?

JustTheFacts

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.

Ebin Cherian

Any chance to post a sample rpd with this issue ? Also do you have any entry in the Aliases section in Presentation layer ?

Gianni Ceresa

justthefacts wrote: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.

This is interesting ! (at least I find it interesting)

So for a correct LSQL you get a column empty back, because the component evaluating LSQL is the BI Server it would point more to something in the model or the the BI Server but at least taking the Presentation service out of the loop (so if you query the BI Server directly via ODBC/JDBC you will also get a blank column etc.).

Do you maybe have another server around? Same thing happening there?

[Deleted User]

Yeah some cross-checking would be good. This sounds rather peculiar.

JustTheFacts

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!