3 Replies Latest reply on Sep 13, 2012 10:32 AM by 952584

    Error When Using SImba MDX Driver to Connect to Cube

      Hi there. I have successfully built a cube and overcome lots of initial issues (mainly down to my lack of knowledge). I am using the Simba MDX driver to connect to and query my cube through Excel. Works great. There are 2 things I was wondering if someone could help with:

      1. When I place my Time hierarchy in the Report Filter and attempt to select more than 1 member, I get an error message. My hierarchy goes Year - Period - Day and If I attempt to select 2 periods (2012 P6 and 2012 P7) then the error occurs. Note it only occurs in the report filter. Each level of my time dimension has been set up with a Short and Long Description and I have set them to be INTEGERS. The error message is below and I don't really know where to start. I have validated that all of my underlying data is correct and present and I can't see anything amiss:

      **[Oracle][ODBC][Ora]ORA-37162:OLAP Error**
      **XOQ-01600: OLAP DML Error "Values of type NUMBER are expected." while executing DML**
      **ORA-06512: at "SYS.DBMS_CUBE", line 88**
      **ORA-06512: at "SYS.DBMS_CUBE", line 134**
      **ORA-06512: at "SYS.DBMS_CUBE", line 154**
      **ORA-06512: at "SYS.DBMS_CUBE", line 144**
      **ORA-06512: at "SYS.DBMS_CUBE_UTIL", line 379**
      **ORA-06512 SQL state = HY000, Native err = 37162**

      2. This is a minor thing. If I place a filter on a dimension when it appears within a row/column axis (as opposed to a report filter), then the grand total does not reflect the items that have been removed - it still shows the original grand total. Is there a way to change this behaviour.

      Any hints, especially on the first error, would be greatly appreciated. Thank you.
        • 1. Re: Error When Using SImba MDX Driver to Connect to Cube

          Question 1:

          I have repeated the steps you just described using the Simba MDX Provider for Oracle OLAP connected to the OLAPTRAIN sample data set, and I did not come across any issues.

          Can you please indicate:
          - what version of Oracle OLAP?
          - what version of Excel
          - what version of Simba MDX Provider for Oracle OLAP?

          Can you test against a different cube, such as OLAPTRAIN, and see if the issue persists? If it persists, please contact Simba Support at support@simba.com, or contact me directly. If you do not see a problem connecting to a different cube, then likely the issue is with your cube.

          Question 2:

          The feature you are looking for is called "Visual Totals." In Excel this is enabled by going to PivotTable Options > Totals & Filters, and unchecking the box "Include filtered items in set totals." As this is somewhat challenging in a MOLAP cube, I suggest you follow up with Oracle.
          • 2. Re: Error When Using SImba MDX Driver to Connect to Cube
            Hi Mike

            Thanks for taking the time to help me out.

            1. Oracle
            2. Excel 2007 (but I get it also on Excel 2010).
            3. Simba

            Unfortunately I don't have the rights to install OLAPTRAIN on my database so all I have are cubes that I've created. I am going to change the datatype of the LONG and SHORT descriptions to be VARCHAR and see if that makes a difference. I did contact Simba but they suggested talking to Oracle as it's clearly an Oracle error. If you can help, then that would be great but I'll let you know how I get on with the data type change.

            As for the Visual Totals thing, I've tried on Excel 2010 using the option you mention but this makes no difference (but then I wasn't using named sets, only the inline filtering on the members) so I'll keep playing with this.

            Thanks again Mike.

            • 3. Re: Error When Using SImba MDX Driver to Connect to Cube
              Hi Mike just an update for you. Previously I had the following for each of my short and long descriptions:

              Year: Year_id (number of the form 2012)
              Period: Period_Id (number of the form 201201)
              Day: Date_Id (number of the form 20120101)

              Note all the source columns are defined as numbers.

              I have now changed the mappings so that these attributes are varchars and are mapped as:

              Year: to_char(year_id)
              Period: 'P'||substr(to_char(period_id),5,2)||'-'||to_char(year_id)
              Day: to_char(calendar_day,'dd-Mon'yy')

              This builds great and I don't get my Oracle error when doing a multi-select in the report filter.

              So I'm still none the wiser as to why I'm getting the error, but this gets me round it.