Non-Oracle Databases integration with OBIEE
Most of the enterprises that use Oracle Business Intelligence Enterprise Edition (OBIEE) 10g/11g /12c will be using Oracle Enterprise Database as their underlying source of data. However, there are plenty of other options: By adopting other relational database systems as well, such as SQL Server, NoSQL, or cloud hosted databases, it is possible to provide a more versatile, scalable and cost-effective reporting system. It can keep options open for the business, and avoid committing to an Oracle-only solution with their upside of no perpetual license and initial Oracle database setup cost but downside are: no oracle support, integration of heterogeneous technologies and losing core Oracle Data Warehouse features like parallel processing, bitmap index, cost based optimizer, Very Large Database (VLDBs) and so on – keep in mind not all enterprises needs these features and many are still using Oracle Database 11g/12c as Oracle 7i or 8i – lack of such features are not a show stopper.
We have five main options for data sources that we can use to integrate with Oracle Business Intelligence Enterprise Edition (OBIEE) 10g+:
- On premises Oracle Database (Enterprise Edition (EE), Standard Edition (SE))
- Open source Databases such as MySQL, MariaDB and PostgreSQL
- On premises MS SQL Server,
- Cloud Services such as Azure SQL Database, Business Intelligence Cloud Service (BICS), Oracle Cloud (PaaS, OaaS, IaaS), Amazon AWS, Rackspace etc
- NoSQL, Hadoop or other Big Data technologies.
Oracle Database:
The cost of deploying a small sized Oracle Data Warehouse (DWH) on three servers (Dev, Test and Production) with following hardware specification – based on the current Oracle Price List: The three servers and their platforms would be:
For time being we will ignore the cost of the Extract Transform and Load (ETL) element, and will focus on the cost of Oracle Database setup on these three server environments.
This will come to a total cost of $276K per server (Total of $828K to cover the cost of 3) for perpetual licenses for Oracle Database Enterprise Edition. In addition, there are other cost factors such as
- Support and Software update: 22% on top of standard cost
- Ongoing annual maintenance cost of $100K/year
Now if we then factor in the cost of hardware and other resources, the company will end up paying around $500K in its first year and perhaps an estimate of $200K per annum afterward. You could get a cheaper Oracle Database version such as Standard Edition (SE), which doesn’t have an option of add-on, losing the Tuning Pack, the partitioning option and various other high-end Data Warehouse (DWH) performance-tuning features.
Alternative to Oracle DB:
If we start looking around for alternative RDBMSs to Oracle Database, we will find two main conventional RDBMSs that are competitive: IBM DB/2 or Microsoft SQL Server. For this article we will not be looking at other options such as MySQL, PostrgeSQL that are more difficult to make direct comparisons with.
Microsoft SQL Server 2008
Oracle currently support Microsoft SQL Server 2005, 2008 and 2008 R2 for Oracle’s Repository Creation Utility (RCU) and BI Publisher connectivity – based on OBIEE 11g (11.1.1.7.0) certification matrix.
Installing MS SQL Server 2008R2 is done very straightforwardly by downloading the package from Microsoft website. Once installed it will create an entry on the windows start menu
Each Oracle Business Intelligence system (BI domain) requires its own set of database schemas. We can use Oracle’s Business Intelligence 10g/11g/12c with Microsoft SQL Server 2005/2008/2008R2 by creating Oracle’s Business Intelligence schemas (Metadata Services (MDS) and Business Intelligence Platform (BIPLATFORM)) via Oracle provided tool called Repository Creation Utility (RCU).
Unfortunately installing Oracle’s Repository Creation Utility (RCU) for SQL server is not quite as straightforward as on a native Oracle Database. It requires a few changes to the SQL Server Database before one can use Repository Creation Utility (RCU) on SQL Server 2008 R2. I created a SQL Server database named “OBIEE” and then run these two DDL scripts.
- Case sensitive collation in order that database can reflect case sensitivity for repository naming convention:
ALTER DATABASE OBIEE COLLATE LATIN1_GENERAL_CS_AS
[Note: my Server is UK English, if you have an USA locale installation then your DDL command will be like ]
ALTER DATABASE OBIEE COLLATE LATIN1_GENERAL_CP1_CS_AS
- Row version is required, which is achieved by issuing below DDL:
ALTER DATABASE OBIEE SET READ_COMMITTED_SNAPSHOT ON
After running the above script, navigate to RCU.bat and wait for RCU screen to popup and then follow on screen instructions until you reach the screen “Database Connection Details”
Once RCU checks all the pre-requisites, it should look like the following screenshot
Once the RCU has successfully installed required schemas, it should look like following screenshots.
OBIEE 10g/11g/12c supports three primary ways of connecting to data sources:
- Native Gateway, such as Oracle Call Interface (OCI) for Oracle – This is always default and preferred option as it gives best support and performance.
- Data Direct ODBC, set of drives included with OBIEE, which enable connection to SQL Server, MySQL, Hive etc – required 6.0 data drivers are installed with OBIEE installation.
3.
3.Native ODBC, set of driver included to connect with Teradata, Oracle TimesTen In-Memory Databases.
There is another kind of connection available called JDBC but only available from OBIEE 12c and not fully documented and supported by Oracle.
Connection to OBIEE 11g/12c is fairly straightforward too – the SQL server 2008 R2 driver (as shown in the above snapshot), which comes with OBIEE, works fine, with the connection process involve creating an Open Database Connectivity 2.0/3.5 (ODBC) Data Source Name (DSN) to SQL server database and then importing all the tables into the
[Note: Microsoft does not sell SQL Server 2008 R2 anymore, but DVDs are available from Ebay or Amazon]
Let’s explore the Microsoft cost for deploying a small sized Data Warehouse (DWH) on three servers (Dev, Test and Production) with following hardware specification – based on the legacy
For time being we will ignore Extract Transform and Load (ETL) element, and will focus on the cost of oracle database setup on three above environments.
So SQL Server 2008 R2 will probably appeal to the customers who are looking for Oracle Business Intelligence (OBIEE) 11g as reporting tool with a cheaper underlying Database.
OBIEE Connection’s performance to SQL Server 2008 R2 worked fine with Sample data and three concurrent users, but obviously you would need to test more rigorously.
Cloud Platform:
So far we have been discussing the cost of running OBIEE more or less “on premise”. However there are a variety of Cloud (PaaS, IaaS, SaaS) options available to integrate with OBIEE. The advantages of using the Cloud-based options are that there is no local installation, simplified administration, and no hardware cost: Some services would also result in less on-going cost. In simple terms, the structure of cloud architecture could be similar to this:
In this case, instead of charging for software and support upfront you are charged monthly or hourly based on instance size. Extreme performance Package will cost $5.28/hr or $46k/year, or $138k/year for the three servers, which is still lower then the Oracle “on premise” maintenance cost.
In most cases, cloud services will appeal to the most of the companies where there is temporary BI requirement, budget constraints, or where it is important to avoid the initial cost of hardware.
Connecting to cloud based database is same as connecting to regular “on premise” database via SQL Developer/Toad for Oracle or in Repository – the direct ODBC driver that comes with OBIEE works fine with most of the known cloud services.
Connection to Oracle Cloud database seems to work OK but it needs to be tested with a greater data load.
Conclusion:
With all the comparison in hand, what are the conclusions? The wide range of heterogeneous database options supported by Oracle Business Intelligence Enterprise Edition (OBIEE) 10g+ allows us to design database systems that fit the company’s budget, its appetite to integrate heterogeneous systems, while keeping in mind Oracle would not support non-Oracle products and MS SQL support for 2008R2 would finish in March 2019.
If you keen to have Oracle Business Intelligence Enterprise Edition (OBIEE) 10g+ as your Business Intelligence (BI) product but you need to cut the total cost from £720K to 114K, then Microsoft SQL Server or other databases could be the answer.
Going forward a variety of Cloud (PaaS, IaaS, SaaS) and Oracle integrate Business Intelligence Cloud Services (BICS) options sound-interesting depending on how well customers are keen to move existing data onto Cloud servers and Oracle Business Intelligence Cloud Services (BICS) integration with other Cloud databases but there are two major advantages of moving to BICS:
- It’s entirely a thin-client, with no need to install local BI admin or Oracle Fusion Middleware (OFMW).
- It can be licensed monthly, depending on data usage.
Now as a disclaimer, I am mainly an Oracle person and I cannot speak in depth about the Microsoft product, but it could be that you would lose a feature that you already rely on if you switch to a different RDBMS: This might be something like Parallel query, bit map indexation or cost-based optimization. However Oracle and SQL server are generally comparable in their features, and many Small Medium Enterprise (SME) companies may not require to use all the unique features of Oracle.
About Author:
I have more than 10 years experience of Business Intelligence (BI) in two leading BI stacks (Oracle and Microsoft), cross platform data migration and integration. I always explore possibilities of integrating cross platform BI technologies for clients to stitch heterogeneous systems to save cost, cut waste and improve efficiencies.
No Comments