Using SQL SERVER and Cloud Hosted Databases with OBIEE 11g

If your organisation is committed to using Oracle Business Intelligence Enterprise Edition (OBIEE) 10g/11g /12c as their BI solution, you aren't thereby committed to using Oracle throughout your organisation. You can use a range of data sources including SQL Server, and save a great deal of money by doing so. Sadly, Oracle will only support the use of the venerable SQL Server 2008 R2. Zafar Ali demonstrates how to connect OBIEE to the world beyond Oracle

Most of the enterprises that use Oracle Business Intelligence Enterprise Edition (OBIEE) 10g/11g /12c for their business analytics and reporting 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 solution. By using these other data sources, It can keep options open for the business, and avoid having to commit to an Oracle-only solution. This has the advantage of avoiding the perpetual license and the initial Oracle database setup cost but you lose the advantage of Oracle support and the integration of heterogeneous technologies. You also lose the core Oracle Data Warehouse features such as parallel processing, bitmap index, cost based optimizer and the Very Large Database (VLDBs). However not all enterprises need these features and many are still using Oracle Database 11g/12c with Oracle 7i or 8i anyway. The loss of these features are therefore unlikely to prove to be 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+:

  1. On premises Oracle Database (Enterprise Edition (EE), Standard Edition (SE))
  2. Open source Databases such as MySQL, MariaDB and PostgreSQL
  3. On premises MS SQL Server,
  4. Cloud Services such as Azure SQL Database, Business Intelligence Cloud Service (BICS), Oracle Cloud (PaaS, OaaS, IaaS), Amazon AWS, Rackspace etc
  5. 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:

Environment Type

Processor Type

Server Core

RAM

OS

Production

Intel x86_64

4

64

Linux OS 7

Test

Intel x86_64

4

32

Linux OS 7

Dev

Intel x86_64

4

32

Linux OS 7

For time being we will ignore the cost of the Extract Transform and Load (ETL) element, and will focus on the estimated cost of Oracle Database setup on these three server environments.

Oracle DB Type

Processor

Unit Cost

Total Cost

Standard Edition

4

$17.5K

$70K

Oracle DB EE

4

$47.5k

$190K

Partitioning Option

4

$11.5k

$46K

Tuning Pack

4

$5k

$20K

Diagnostics Pack

4

$5k

$20K

This will come to a total estimated 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

  1. Support and Software update: 11% on top of standard cost
  2. 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, of course, 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, PostgreSQL that are more difficult to make direct comparisons with.

Microsoft SQL Server 2008 R2:

Unfortunately, Oracle does not support any of the recent versions of SQL Server. Oracle currently only 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. Other later versions seem to work but they aren’t supported by Oracle.

Business Intelligence Publisher data source configuration

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 an Oracle-provided tool called Repository Creation Utility (RCU).

Unfortunately installing this 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.

  1. Case sensitive collation in order that database can reflect case sensitivity for repository naming convention:

[Note: my Server is UK English, if you have an USA locale installation then your DDL command will be like ]

  1. Row version is required, which is achieved by issuing below DDL:

After running this script, navigate to RCU.bat and wait for RCU screen to pop up 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:

  1. Native Gateway, such as Oracle Call Interface (OCI) for Oracle – This is always the default and preferred option as it gives best support and performance.
  2. Data Direct ODBC, set of drives included with OBIEE, which enable connection to SQL Server, MySQL, Hive etc –The 6.0 data drivers are provided by Oracle and are installed with OBIEE.

  1. Native ODBC: a set of drivers are included to connect with Teradata, Oracle TimesTen In-Memory Databases.

There is another kind of connection available via 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 Repository

Mainstream support for SQL Server 2008 and SQL Server 2008 R2 ended on July 8, 2014 and technical support ends in July 2019. It is possible to download SQL Server 2008 from the Microsoft website but you would need to discuss licensing directly with your Microsoft representative.

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 Microsoft SQL Server 2008 Price List:

Environment Type

Processor Type

Server Core

RAM

OS

Production

Intel x86_64

4

64

Windows 2008 R2

Test

Intel x86_64

4

32

Windows 2008 R2

Dev

Intel x86_64

4

32

Windows 2008 R2

For time being we will ignore Extract Transform and Load (ETL) element, and will focus on the cost of SQL Server Database setup on three above environments.

Microsoft DB Type

Processor

Cost

Total Cost

SQL Server % Saving against Oracle Database

Standard Edition

4

$7.5k

$30K

59%

Enterprise Edition

4

$28.5k

$114K

40%

So SQL Server 2008 R2 will probably appeal to the customers who are looking for Oracle Business Intelligence (OBIEE) 11g as a 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 with a workload closer to your setting.

OBIEE VS SQL Server 2008R2 Performance

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, administration is simpler, and you have 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 database seems to work OK but it needs to be tested with a realistic data load and throughput

Conclusion:

With all the comparisons in hand, what should we conclude? Oracle Business Intelligence Enterprise Edition (OBIEE) 10g+ seems to allow us to design database systems that fit the company’s budget and its requirement for integrating heterogeneous systems. Obviously, Oracle cannot support non-Oracle products but it is more of a problem that support for SQL Server is so meagre, bearing in mind that MS SQL support for 2008R2 will finish in March 2019. Hopefully, by then Oracle will have gotten around to certifying more recent versions of SQL Server!

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 mainstream relational databases could be the answer.

Looking to the future, it is good to see that OBIEE integrates with a variety of Cloud databases via PaaS, IaaS and SaaS. OBIEE also integrates with Business Intelligence Cloud Services (BICS) There are two major advantages of moving to BICS:

  1. It’s entirely a thin-client, with no need to install local BI admin or Oracle Fusion Middleware (OFMW).
  2. 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.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

  • 1659 views

  • Rate
    [Total: 3    Average: 4.3/5]