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:
**XOQ-01600: OLAP DML Error "Values of type NUMBER are expected." while executing DML**
**"SYS.AWXML!R11_MANAGE_CALC_MEMBER('TIME_DE.MDXPROVIDERDDSOCAM0.CALCULATEDMEMBER' 'CREATE TIM_DE_SHORT_DESCRIPTION.ATTRIBUTE' NA**
**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.
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 firstname.lastname@example.org, or contact me directly. If you do not see a problem connecting to a different cube, then likely the issue is with your cube.
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.
Thanks for taking the time to help me out.
1. Oracle 184.108.40.206.0
2. Excel 2007 (but I get it also on Excel 2010).
3. Simba 220.127.116.11
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.
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:
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.