SSRS Against an Oracle Data Source

My continuing coverage of integrating Oracle and SQL Server … Today’s post is a bit of a cheat: I’m just going to point you back to a blog post that I did when I was with Oracle where I showed you how to leverage SQL Server Reporting Services (SSRS) against an Oracle data warehouse that was loaded from the Oracle Primavera enterprise project portfolio management product (EPPM): http://blogs.oracle.com/EPPM/2010/05/new_p6_reporting_database_r2.html.

The reason that I felt compelled to post that example when I was with Oracle is that I would constantly run into customers who were using 1 of 2 products for ad-hoc reporting: Crystal Reports or SSRS. And usually SSRS and Report Builder where there because customers had existing investments in SQL Server. Of course, Crystal Reports was there because (a) it’s been around forever and (b) most of the customers that I worked with were actually SAP customers, not Oracle Apps customers. Which means that they received Crystal Reports licenses.

Anyway, long story short, that link above is a decent example of how I was able to pull in Oracle database data directly into an SSRS report. Very easy and straight-forward. Also, if you are going to use the SSMA migration assistant from Microsoft to migrate your database from Oracle to SQL Server, you will need to recreate your Oracle Reports in SSRS. There are a few companies out there that will work with you as consultants to migrate those reports for you.

Advertisements

Something for Oracle PL/SQL Developers in Denali

Getting back to our common theme on this blog regarding Oracle & SQL Server coexistence, migration and integration, I want to touch today briefly on a new feature in SQL Server v-next / Denali / SQL 11 / SQL 2011, whatever you wish to call it at this time. Let’s call it by code name Denali. CTP 1 is avaliable for download here. For as long as SQL Server has been around, developers have had not-so-nice things to say about the limitations on the T-SQL Identity column property, used for a similar numeric sequences in tables to PL/SQL sequences. It should be noted that sequences are standard SQL objects and SQL Server has used identity to implement similar functionality. Interestingly, Oracle-to-SQL Server migration tools such as SQL Server Migration Assistance for Oracle, a free download from Microsoft.com, utilizes its own sequence generator when converting to T-SQL. This way, you can use the Oracle CURRVAL and NEXTVAL.

With SQL Server identity, you do not get flexibility in that it is tied to a single table while sequence objects are not. They are separate manageable objects. You also do not set min, max and steps cannot be modified in an identity column.

Here is sample code for creating a sequence:

SET NOCOUNT ON;
USE AdventureWorks2008R2;

— Specific INT in most cases
CREATE SEQUENCE dbo.MySequence AS INT
  START WITH 1
  INCREMENT BY 1;

— Generate next value for sequence
SELECT NEXT VALUE FOR dbo.MySequence;

— In your table, you can store that sequence as an INT column
CREATE TABLE dbo.MyTable
(
  MyID INT NOT NULL,
  MyName VARCHAR(50) NOT NULL,
  CONSTRAINT PK_ID PRIMARY KEY(MyID)
);

INSERT INTO dbo.MyTable(MyID, MyName) VALUES(NEXT VALUE FOR dbo.MySequence, ‘Mark’);

Here are my favorite 2 postings that I’ve read so far to give you good depth of knowledge on using the Sequence object in CTP 1 of Denali from T-SQL guru master Itzik Ben-Gan here and here.

Oracle Data in SQL Server, Part 3: Oracle Replication

I’ll finish-up on my series on data migration from Oracle to SQL Server with a few notes on migrating data from Oracle to SQL Server utilizing the SQL Server replication functionality. A SQL Server 2005 & 2008 enterprise edition feature called Oracle Publisher to enable transactional or snapshot replication from Oracle to SQL Server is very useful, particularly to DBAs. Application developers can migrate, integrate and mix & match Oracle and SQL Server data through OLEDB, SSIS, .NET, etc. But DBAs who focus on database management and do not wish to get dirty in the marsh of Visual Studio or BIDS, can use SSMA or T-SQL to configure SQL Server replication with Oracle.

This is very easy to configure, can be maintained through the traditional SQL Server replication monitoring mechanisms already familiar to you and performs very well.

When I set-up my first Oracle Publisher from SQL Server 2008 replication, I used these steps outlined here. You will need to run a script that is a PL/SQL file dropped in your SQL Server instance install directory. I ran it from SQL*PLUS as “system” and it set-up a replication user in Oracle that the Oracle Publisher will use from SQL Server’s replication engine. Also please note from that link above with the steps that you need to follow, that you must have access to the Oracle server or the Oracle client libraries so that you can reach the Oracle database while you are setting up the replication.

On my latest test servers, I have Oracle co-located with SQL Server. But the SQL Server that you set-up as an Oracle replication server will not likely have the Oracle database co-located there like I do. Therefore, use the Oracle TNS and SQL*PLUS utilies to test connectivity before going through this process and getting a headache trying to troubleshoot the configuration process.

Once you’ve configure the publication, you can then monitor and manage the replication as you would with SQL Server replication sources.

Oracle Objects and PL/SQL in SQL Server

As a continuation of one of my primary themes at the MSSQLDUDE blog here, I would like to provide some more info for those Oracle DBAs and developers out there reading this. In this instance today, I’ll touch a little bit on a few things that you should be aware of when converting from Oracle databases to SQL Server. Keeping in mind that at its core, both are indeed relational databases and have grown up together. When Microsoft or Oracle adds capabilities to their database product, you can be the other will do the same shorlty thereafter.

There are 2 primary links that I would like to point you toward to kick things off: the first one here is for the SQL Server Migration Assistant (SSMA) tool which is a free download from Microsoft. It is a great tool and has been around for quite awhile. The original makers of that tool, DB Best, are excellent Microsoft partners and the IP was sold to Microsoft years ago and is now available for you as a free download to convert Oracle PL/SQL, objects and data into T-SQL and SQL Server. It does many other things as well such as estimate what will or won’t automatically convert, how much effort is needed to complete the migration and many options, too many to note here, that you can configure to control how similar, but not identical, features will convert from Oracle to SQL Server.

The other link here is to a 160+ page document from the DB Best team and is an invaluable tool to have in your toolbox in addition to the SSMA tool for your migration projects. Of course, the Help documentation that is part of the SSMA is also useful, including details on how to use the data testing capabilities in SSMA which are invaluable before you decide to enact the code & object changes on the target SQL Server schema. Both the free guide and free tool, though, I use quite a bit on these projects.

Here, then are a few highlights that I would like to point out for both DBAs and developers to keep in mind when converting from Oracle to SQL Server. The links above are the tools and documents that list the full set that you should be aware of when embarking on an Oracle migration project. But I run into these a lot and picked out a few for you as gotchas and that should be of interest to you:

1. Oracle Packages – To emulate Oracle’s “packages”, which allow Oracle developers to package-up Oracle database objects like variables, functions, procedures, etc, you need to place those objects into the SQL Server “programmability” category separately in SQL Server. The SSMA tool and DB Best documents use naming conventions and schema names to categorize the package contents.

2. Oracle Sequences – A long-time favorite of database pros has been arguing over table-agnostic sequences vs. SQL Server/Sybase style identity columns (look for sequences in SQL 11!). To convert from Oracle sequences to SQL Server, you can either convert to identity columns in your target SQL Server tables or use the SSMA option which generates a SQL Server stored proc to emulate the Oracle sequence NEXTVAL and CURRVAL functionality is in this example from the DB Best doc:

Oracle

INSERT INTO employees (id, name)

VALUES(employees_seq.nextval, ‘David Miller’);

Transact-SQL

DECLARE @nextval numeric(38, 0)

EXECUTE sysdb.ssma_oracle.db_sp_get_next_sequence_value ‘customers’,’dbo’,’employees_seq’, @nextval OUTPUT

INSERT employees (id, name) VALUES(@nextval, ‘David Miller’)

3. CURSORS – The dreaded cursor. This is where my earlier posts here @ my blog around the evil cusror originated. I used to cursors a LOT when I was an Oracle developer. And I ran several Oracle-to-SQL migration projects which lead to quite a bit a dealing with cursors. T-SQL does not require the use of cursors the way that PL/SQL procedure do. And SQL Server T-SQL does not optimize cursors very well or in the same manner that sets returned from selects are optimized. Therfore, if you can, rewrite cursors into new T-SQL statements. But if you cannot and want to convert, use SSMA to convert the PL/SQL code to T-SQL and then consult the document I’ve reference in this posting about how to emulate Oracle’s cursor arguments and remember that SQL Server supports different types of cursors such as FORWARD, READ ONLY, etc., and go back and understand which one is best for each use of the cursor to ensure optimal performance of the cursor T-SQL code.

Bring Oracle Data into SQL Server

This is part III of my continuing series on working with Oracle for SQL Server pros …

In this Oracle posting, I’ll focus on data integration as part of an ETL strategy where you will build a SQL Server data warehouse from Oracle data sources. This is a very common practice and allows you to take advantage of the awesome SQL Server data integration and ETL capabilities that you essentially get for free as they come with the SQL Server product by way of SSIS (SQL Server Integration Services).

What I’ll do is point out to you a few links to dive deeper, some gotchas, best practices and approaches that I have taken in the past that were successful. If you are not careful about your approach to integrating Oracle databases into SQL Server databases, you may find yourself quickly falling victim to degrading performance that fails to meet your most basic SLAs and data management needs.

The most obvious and easiest mechanism that I find to connect to Oracle data source from SSIS is to use the Microsoft OLE DB Provider to Oracle (see pic below). There are also many other providers besides Microsoft for Oracle. But with SQL Server, you get this OLEDB adapter from Microsoft out of the box. I’ve used it many times, it works just fine. There is a good paper on SQL CAT here which you can use to see some of the tests and timings recorded and published by the Microsoft SQL customer advisory team when connecting to Oracle from SQL Server, it’s very helpful for you as you set your service level agreements with your business users. A few things to watch out for include that you must know the Oracle database network name that is being broadcast from the TNSNAMES configuration on your Oracle database sources. If you do not have access to the Oracle servers, make sure that you’ve collected a read-only login to the database, the TNS NAME and the server host name (physical or virtual).

OLEDB, however, is not generally the fastest mechanism to use to access data from Oracle. That being said, not every use case where you utilize SQL Server either as a DW or for an application that needs data from Oracle, requires super-fast access to the Oracle data source. For instance, an ETL job that runs nightly may have a large window with which it can use to pull data from, not requiring lightening-speed data access to the Oracle sources.

However, if you need to access Oracle faster than OLEDB, SQL Server 2008 ships with a set of high-speed adapters for Oracle from Attunity that you can use with SQL Server. To use the Attunity-provided Oracle adapter with SSIS, go to this Microsoft download link here. Once you add the Attunity drivers, you can then select those from your SSIS data source or data target to take advantage of the improved data movement speeds.

Oracle OBIEE with SQL Server

I promised several weeks back to begin blogging more about my experiences with and at Oracle over the years. I’ve been tardy in doing do, but today I’m getting at it!

I want to start with Oracle BI because I worked quite a bit on this product while @ Oracle where we built packaged analytics products using OBIEE. OBIEE is the Enterprise Edition of OBI from Oracle and is now in release 11g, which just hit the market this summer (2010). It was finally updated using new ADF capabilities and other improved features which came from the Oracle acquisition of Siebel.

OBIEE started as a CRM Sales Analytics product from Siebel and Oracle primarily uses it to sell packaged analytics offerings for their ERP applications. But you can use it as a customizable BI tool, which many customers do. It is quite pricey, but if you have invested in it within your business, you can still utilize your SQL Server sources and applications.

First, SQL Server can be a source database. When you are in the OBI Admin Tool, you can use the MS SQL ODBC driver to bring in your SQL Server database sources as is demonstrated in this blog here.

Secondly, the OBIEE metadata, reports and KPIs are exposed via ODBC, meaning that you can including those in your Microsoft SharePoint BI solutions or in your SQL Server BI applications such as is discussed nicely here, laying out some options.

I’m not going to say that the second part above is easy. In fact, I have experience with Oracle customers that sometimes would fall back to the OBIEE add-in for Office to get at OBI reports. But I have used the SQL Server data source approach with OBIEE in the past to build dimensional models in OBIEE and it worked quite well without too much effort.