1 2 Previous Next 29 Replies Latest reply on May 3, 2017 9:17 PM by Gianni Ceresa

    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:

       

      CustomerYearMonthLocationRevenue
      Acme2017011000
      Foo2017012000
      Bar2017023000

       

       

      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.

        • 1. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
          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.

          • 2. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
            Christian Berg

            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.

            • 3. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
              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.

              • 4. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                Christian Berg

                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?

                • 5. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                  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?

                  • 6. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                    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.

                    • 7. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                      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 ?

                      • 8. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                        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?

                        • 9. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                          Christian Berg

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

                          • 10. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                            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!

                            • 11. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                              Gianni Ceresa

                              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)

                              • 12. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                                Christian Berg

                                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

                                • 13. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                                  JustTheFacts

                                  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.

                                  • 14. Re: Data missing in random columns in Analyses (12c 12.2.1.2)
                                    JustTheFacts

                                    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?

                                    1 2 Previous Next