Data missing in random columns in Analyses (12c 12.2.1.2)
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.
Answers
-
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.
0 -
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.
0 -
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.
0 -
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?
0 -
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?
0 -
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:
- The same Column A will be blank as it was before
- A different column will now be blank - possibly the column I just added, possibly some other column that was previously showing data
- 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.
0 -
Any chance to post a sample rpd with this issue ? Also do you have any entry in the Aliases section in Presentation layer ?
0 -
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?
0 -
Yeah some cross-checking would be good. This sounds rather peculiar.
0 -
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!
0 -
The good thing is we never heard about that and also don't remember I saw somebody else asking for that on here.
So it smells a lot like a one-time issue (so only on one server for ... no idea which reason).
As you explored quite few options around RPD etc. I would leave that one as possible source of problems as secondary thing (of course not excluded it can still come from there).
An better not to comment about how easy the migration is
It actually is really simple if your 11g was a perfectly clean state of the art model, if it was a normal setup used for read ... it's always a bit different
You are on linux, right? Which version? (not that it's supposed to matter but never know)
0 -
Just out of curiosity: Have you already run a vanilla RPD on that server to see whether you get the same beahviour? I.e. eliminating "the RPD" as a problem source or confirming it. One step after the other with webcat next etc etc
0 -
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.
0 -
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?
0 -
Yes and no. I was proposing you take a simple SampleAppLite and the use the datamodel command to upload the RPD.
But since you mention it - you can also import one of the BAR files which come with any base install. Like the SampeLite BAR which contains the RPD from my above use case. For that you'd use importServiceInstance.
Mind you that will switch out RPD, webcat and the system-jazn, so your roles and responsibilities.
0 -
Moving to RHEL is a good move (lately all the people having a Windows server in front of them doing an 11g to 12c upgrade are really thinking to install a linux VM hidden somewhere ).
Good thing you could test the LSQL directly : so Presentation service is out of the loop, the issue is between BI Server and the data source, but because the physical query works fine ....
I would really point on a different server to test, last "quick" test to see if it's just this single install which has an issue.
Just a random thing : would it be possible that your source is loading so frequently that tables or columns are empty because reloaded (or views rebuilt or something) ?
And that, no luck, when you test the physical query everything was loaded and so the query is fine?
For the test importing SampleLite BAR, as Christian said it will wipe everything, so make backups and also export your current instance first.
0 -
Thanks again both of you for helping work through this. Our product manager just finished a fresh migration of our 11g production RPD/Webcat/Jazn to a different 12c environment (still RedHat) and I tested that installation with the same results - random this or that column is blank.
The subject areas I'm using to test are either daily or monthly loads, so it's not a data load issue. Caching is turned off in the RPD physical layer.
We're going to put in an SR to Oracle, but as I mentioned at the start...not looking forward to that experience.
I also have loaded up SampleAppLite.bar, but the XML data sources aren't set up correctly yet. Once I get that sorted out I'll do another test.
0 -
No luck ...
Also because it's a kind of really annoying issue as you can't really find out a pattern
Would be nice if you can come back with an update once you heard something (I hope) from Oracle.
In case you have time/resources you can maybe also try a 12.2.1.1.0 ? (as you are now on 12.2.1.2.0)
0 -
So, sort of related because I'm trying to get SampleApp working - when I import the BAR file, everything seems to work, but the RPD is not updated. The command I am running in WLST is:
importServiceInstance('/software/obiee/enterprise/user_projects/domains/bi','ssi','/software/obiee/enterprise/bi/bifoundation/samples/sampleapplite/SampleAppLite.bar')
And amongst all of the logging output I see:
INFO: Successfully run importServiceInstance for RPD plugin oracle.bi.bar.si.framework.plugin.RpdMetadataManagerPlugin
I have tried this both while services were up and running (restarting using stop.sh/start.sh after the import) and while services were stopped (after stop.sh). Either way, the webcat and JAZN are successfully "updated" to SampleApp, but I'm not getting the SampleApp RPD. My little test RPD is always left behind.
I haven't yet wrapped my head around how RPDs are handled in 12c, but for what it's worth, "liverpd.rpd" (at ssi/metadata/datamodel/customizations) does not have the current timestamp on it; seems to be from the last time I did "uploadrpd". However, the "default_diff.xml" file (in the /default subdirectory of the above directory) has been touched and has a current timestamp.
What am I doing wrong?
0 -
Have you tried replacing the logical column with a constant value (just edit the column definition) to see if that displays?
0
