Using InetSoft's BI Software to Access Multidimensional Databases

The multidimensional database is another way to provide data for OLAP operations. Taking the place of tables in relational databases, multidimensional databases organize data into cubes that contain measures and dimensions. This technology is often referred as MOLAP. The primary benefit of using MOLAP is enhanced performance because of pre-aggregation.

MOLAP has no industry standard query language, although a few popular mechanisms exist. The Data Modeler uses these technologies to access meta¬data from MOLAP databases and present it uniformly. Because the meta data from a MOLAP database contains all of the information needed for the InetSoft data modeler engine, models for multidimensional databases are automatically created by the Data Modeler.

Multidimensional databases are most commonly provided by a part of a relational database. Both the Microsoft SQL Server Analysis Services and Oracle OLAP server are OLAP add-ons. ESSbase is a specialized multidimensional database that is also marketed as an IBM OLAP server as an add-on to the DB2 database server.

For supported OLAP databases, the New Data Source wizard will ask if an OLAP domain should be created at the last step. A ‘Yes’ answer will instruct the Data Modeler to create a special node called ‘Domain’ under the data source. ‘Domain’ captures all cubes inside the multidimensional database that define the dimensions and measures.

“Flexible product with great training and support. The product has been very useful for quickly creating dashboards and data views. Support and training has always been available to us and quick to respond. - George R, Information Technology Specialist at Sonepar USA

Connecting the BI App to MS SQL Server Analysis Service

In order to use a Microsoft SQL Server OLAP domain, it is necessary to create a SQL Server JDBC data source. The OLAP domain uses Microsoft Analysis Services for the OLAP server.

For Microsoft SQL Server 2000, the following JAR files must be included in the system classpath:

• msbase.jar
• mssqlserver.jar
• msutil.jar

For Microsoft SQL Server, the following JAR file must be included in the system classpath:

• sqljdbc.jar

Connecting the BI App to an Oracle OLAP Server

In order to use an Oracle OLAP domain, it is necessary to create an Oracle JDBC data source. The following files, which can be downloaded from the Oracle website for Oracle 10g, must be included in the system classpath:

• ojdbc14.jar
• nls_charset12.jar
• collections.jar
• olap_api.jar
• xmlparserv2.jar

Connecting the BI App to a DB2 OLAP Server

In order to use an Essbase OLAP data source, it is necessary to create a DB2 JDBC data source. The ess_japi.jar file from the Essbase Enterprise Server installation must be included in the system classpath.

Read how InetSoft was rated as a top BI vendor in G2 Crowd's user survey-based index.

What Are the Disadvantages of Multidimensional Databases?

Multidimensional databases, while powerful and efficient for certain types of data analysis, also come with their own set of disadvantages. Here are some of the key drawbacks:

1. Complexity: Multidimensional databases can be complex to design, build, and maintain. They often require specialized knowledge and expertise in data modeling, schema design, and query optimization. Managing the multidimensional data structures and hierarchies can add to the complexity, especially as the size and complexity of the dataset grow.

2. Scalability: While multidimensional databases excel at fast query performance for analytical workloads, they may face scalability challenges with very large datasets or highly concurrent access patterns. As data volumes increase, the performance of multidimensional databases may degrade, requiring additional hardware resources or optimizations to maintain acceptable performance levels.

3. Limited Flexibility: Multidimensional databases are optimized for specific types of analysis, such as OLAP (Online Analytical Processing) queries that involve aggregations, drill-downs, and pivoting of data along multiple dimensions. However, they may not be as flexible or adaptable to ad-hoc queries or changes in data requirements compared to relational databases or NoSQL databases.

4. Data Integration Challenges: Integrating data from diverse sources into a multidimensional database can be challenging, especially if the data is stored in different formats or structures. Data transformation and cleansing processes may be required to ensure consistency and compatibility with the multidimensional model, adding complexity and overhead to the data integration process.

5. Cost: Multidimensional databases often require specialized software licenses and hardware infrastructure, which can be costly to acquire and maintain. Additionally, the expertise required to design, implement, and manage multidimensional databases may necessitate hiring or training skilled professionals, further increasing the total cost of ownership.

6. Maintenance Overhead: As with any database system, multidimensional databases require ongoing maintenance and monitoring to ensure optimal performance, data integrity, and security. This includes tasks such as index maintenance, data backups, performance tuning, and security updates, which can add to the operational overhead for IT teams.