One of the great things about the Simba MDX Provider for Oracle OLAP is that the power of ad hoc query and analysis is now in the hands of the end user. Gone are the days where you need to submit a request to IT to produce a new report from your corporate BI system, where turnaround is measured in days or weeks. Now any user can use Microsoft Excel to create their own ad hoc analysis or reports in minutes, with data accessed directly from the Oracle OLAP database, ensuring a single version of the truth.
However, with power comes responsibility. Powerful new tools like Excel PivotTables using the Simba MDX Provider for Oracle OLAP likewise demand the user to act responsibly.
We recently had a customer that wanted to do some ad hoc multidimensional analysis on an account dimension with 250,000 members and a product dimension with 20,000 members, over time. In Excel, it seemed perfectly natural to build your PivotTable with these two hierarchies on the same row axis, and then time on the columns, and sales in the measures, and then begin to drill and filter as necessary. When the performance was not as instantaneous as they have come to expect with the MDX Provider and Oracle OLAP, further investigation was needed.
What is going on here is that Excel is literally asking Oracle OLAP to move heaven and earth to get the data for the report — this has nothing at all to do with the Provider. Excel was asking for a crossjoin of two dimensions that would result in 5,000,000,000 rows being returned!
When a DBA is presented with a request like this, Best Practices dictate that they do not do this humongous crossjoin before filtering. A DBA will first filter on each dimension to narrow the search before doing the crossjoin. That results in a manageable result set returned in a fraction of the time.
To achieve the same result in Excel, you need to add one dimension first and filter down to just the level you are interested in, then add your next dimension and filter, etc. That will make the result set from Oracle much more manageable.
Just because Excel and the MDX Provider for Oracle OLAP make something easy to do, doesn’t mean you shouldn’t be careful doing it. Use Best Practices whenever you are doing analytics between two large dimensions.