Most users of Simba’s MDX Provider for Oracle OLAP are end users, i.e. BI analysts that use Microsoft Excel PivotTables to connect to an Oracle OLAP cube to do ad hoc queries and analysis. While they care what is in the cube – data needs to be accurate, complete, and up-to-date – they don’t worry about how or where it’s constructed.
It falls into the hands of a cube administrator or a DBA to do the work of creating the cubes. Usually, this is done with an administrative tool called the Analytic Workspace Manager (AWM), which allows the user to create a multidimensional data model of relational data stored in the OLAP database. In addition, AWM then allows the administrator to specify the amount of pre-aggregation, compression, and partitioning done on the cube, as well densifying sparse data and administering native Oracle security on the cube data. The administrator can also create sophisticated scripts to assist with calculated measures and calculated members.
But, AWM is not the only tool for the job. If you are a DBA or a data warehouse administrator, you may be more familiar with the Oracle Warehouse Builder (OWB) tool. OWB is more of an ETL (Extract, Transform, and Load) tool that allows the user to integrate various data sources into a traditional data warehouse. OWB has the capability to do all the dimensional modelling and administrative tasks that AWM can. OWB is more of a physical layer tool, whereas AWM is more of an business layer tool. No matter what direction you come from, either tool will get the job done.
That being said, however, there are a few caveats to using OWB for creating cubes that will be accessed using the MDX query language and Excel PivotTables through the Simba MDX Provider for Oracle OLAP. We have a HowTo document on our website that lists the issues you need to be aware of when using OWB to create OLAP cubes for consumption by Excel users. While you are there, take a look at the other HowTo documents and videos we have available that give you additional insights on how to get the most of the Simba Provider for Oracle OLAP.
What’s your preferred tool for creating and administering Oracle OLAP cubes? AWM or OWB? Why? Let me know!