Oracle Tips for MSBI Devs #3: Choosing Drivers

24 04 2012

When working with Oracle, drivers are truly a pain to get working correctly.  I will discuss my preferred choice and why for the following tools – SSIS, SSAS, and SSRS.

SSIS Drivers

Without much question, you should use the Attunity tools for working with Oracle data in the Data Flow task.  In SSIS 2008, the SSIS Connector is free and can be found here:  http://www.microsoft.com/download/en/search.aspx?q=oracle%20connector.  It includes the connection manager, source component and destination component.  Without a doubt this is the only way to work with Oracle data components in the Data Flow task.  (NOTE: I cannot find the SSIS 2012 equivalent at the moment.  However, Matt Massan’s blog post after PASS Summit 2011 notes more work is being done with Attunity.)  UPDATE: I wrote this prior to a blog post from Matt Massan on support for SSIS 2012 with v2.0 of the Microsoft Connector.  Check out Matt’s update on this: http://blogs.msdn.com/b/mattm/archive/2012/04/04/microsoft-connectors-v2-0-for-oracle-and-teradata-now-available.aspx.

However, this connector does you no good when working with the Execute SQL task.  In SSIS 2008, I use the OLE DB provider from Oracle to create the connection used with Execute SQL Task.  In my work with procedures in my first tip, I used the OLE DB provider with 2012 as well and it worked fine.

SSAS Drivers

When working with SSAS 2008 and, until I know differently, SSAS 2012, I would recommend using the Oracle OLE DB driver from Oracle.  This driver is not the fastest I have seen (third party drivers are marginally faster and the .NET driver is faster as well), but it has provided consistent results for the right price.  Third party drivers will improve the throughput, but not substantially.  The Oracle provided .NET driver is faster as well, but has an unchangeable active query timeout of one hour.  If you have any processing times that exceed this, it will unceremoniously drop the connection.  For these reasons, I have stuck with the OLE DB provider from Oracle which is not necessarily the fastest, but it has been the least painful to work with.

SSRS Drivers

It is with SSRS I have seen mixed results.  Primarily because of the better performance in the Oracle .NET driver.  If you can guarantee that your reports will return their data in under an hour, this seems to be the best option.  However, if you want to manage to a single driver set across all tools, you may find that the management of the OLE DB driver as the only driver makes sense in your organization.

Test, Test, Test

I have given you my experience using the drivers above.  However, you may find value in purchasing a third party driver or you may find a different experience when you implement in your environment.  Be sure to test and understand the implications in maintenance and system cost when choosing different drivers across your solutions.





Great User Group Meeting Tuesday, Now on to Madison and SQL Saturday #118

19 04 2012

PASSMNLogoMinnesota SQL Server User Group Meeting Review (4/17/12)

First of all, I have to thank the awesome speakers who presented at our user group meeting on Tuesday night, April 17.  Mark Souza ( T | B ) for a great question and answer session.  He was open about SQL Server and where it is heading.  Some items that interested me is that SQL Azure was running the SQL Server 2012 platform in production by December 2011.  Microsoft is committed to releasing more changed through the SQL Azure platform at a faster clip.  This will allow them to continually improve the quality of releases for the boxed version as well.  (Speaking of which, the boxed version is not going away any time soon.)

Mark also spoke about leaving data at its source and using tools to bring the data together for reporting and analysis.  He touted the Hadoop integration efforts as key part of this strategy.  If you have followed my blog for a bit, you know that I do a lot of work with Oracle and MSBI.  As a result, I had to ask when a good version of Oracle drivers would be available.  While he did not have a good answer on this at the time, he did mention that OData is a data access solution for me to look at. Look for a future blog post as I explore this protocol to ease some of my Oracle.

After Mark finished answering the variety of questions asked by the crowd we handed the microphone to Itzik Ben-Gan ( T | B ).  As usual, he took an entire hour to help us understand that we did not know all of the possible ways to use the APPLY operator.  However, I also learned about the DBCC OPTIMIZER_WHATIF function which allows you to change the system settings the Optimizer uses to create a plan.  For instance, DBCC OPTIMIZER_WHATIF(1,16) will tell the optimizer to use 16 processors in its plan.

SQL Saturday #118 – Madison, WI (4/21/12)sqlsat118_web

Finally, I will be out at SQL Saturday #118 in Madison this weekend.  I have two presentations there – A Window Into Your Data: Using SQL Window Functions and Performance Monitoring and Tuning in SSAS.  Maybe I will see you there.





Why I am excited about SQL Server 2012 (Part 2)

28 03 2012

Earlier this month I published a blog entry on this same sumagenic-custom-soltionsbject.  In honor of the local Minneapolis launch event, I decided to expand the list.  You can find five more reasons I am excited out on Magenic’s blog.

Here is the link and enjoy SQL Server 2012.

http://magenic.com/Blog/WhyIAmExcitedaboutSQLServer2012Part2.aspx





PASS for Today (and Tomorrow)

20 03 2012

I justPASSMNLogo wanted to plug two events that are free SQL Server training.

First, Thomas LaRock (@sqlrockstar) and Jason Strate (@stratesql) are teaming up to bring you “Choose Your Own Adventure – Performance Tuning”. Join us at the Microsoft Technology Center in Edina, MN or online for this great adventure. This adventure starts at 3:00 PM CDT. More details can be found at http://minnesota.sqlpass.org.

Is that all?, you ask. No. Starting tonight at 0:00 GMT (7:00 PM CDT) is 24 Hours of PASS. Check out the awesome roster of speakers24HOP_Speaker including the likes of Denny Lee from SQLCAT, Marco Russo, and Dejan Sarka. These sessions run through the night and will be close captioned in 15 languages, making it a truly international event.  Oh, did I also mention, I will be speaking at this as well.

Take advantage of these free training opportunities by leaders within the SQL Community.  We all look forward to seeing you at both or either of these events.





Why I am excited about SQL Server 2012

7 03 2012

By now many of you have had the opportunity to hear or see something about the next release of SQL Server.  It is later on March 7, launch day.  I have enjoyed a number of sessions on the SQL Server Launch site.  Many people have talked about what they look forward to in the product, so I thought I would discuss some of the things that excite me.

image

SQL Server Integration Services Improvements

I think the updates to SSIS make the best case for early adoption of SQL Server 2012.  If you are currently using SSIS or are planning to use SSIS, the changes to the platform are significant and reduce the overall development time for enterprise class solutions.  And yes, developers will see many changes that will further help productivity, “beyond rounded corners.”

ColumnStore Indexes

As I work with large data stores for reporting and loading SSAS, this index type will greatly improve performance.  Once again Microsoft has made significant improvements for large, set based query operations.

Enhanced Window Functions

I have done a lot of work with window functions in Oracle lately.  I am thrilled to see similar functionality being added to SQL Server.  This allows for more complex aggregations and query results while not sacrificing performance.  Join me during the 24 Hours of PASS to see these functions in action.

Power View

Wow.  This visualization tool really fills a gap within in Microsoft’s BI offering.  I am excited to see this in operation at customers as this is a game changer for the end user.  The replay capability is both cool and very functional.

Extended Events for SQL Server Analysis Services

While I have not worked with extended events in SQL Server 2008 as much as I would have liked to, the introduction of extended events for SSAS will allow us to follow the path taken by many SQL Server DBAs who have used these events.  This will allow us to monitor SQL Server more closely while minimizing the impact we experiences using SQL Profiler.

These are just a few items within SQL Server 2012 that excite me, what excites you?





Upgrading Denali CTP3 to SQL Server 2012 RC0

4 12 2011

When I started looking into the upgrade path for this, I saw a couple of notes online about the fact that it was not possible.  I turns out there is a Connect item on this issue (https://connect.microsoft.com/SQLServer/feedback/details/709371/no-option-to-upgrade-from-sql-denali-ctp3-to-sql-2012-rc0-version#details).  In that item was a work around to use the SQL Server 2008 R2 upgrade option.  When you start the RC0 installer, choose the Upgrade option which is last I the list as shown below.

image

This will launch the setup wizard and start the Setup Support Rules check after which you will be prompted to select the instance you wish to upgrade.  In my case I have two named instances with CTP3 – DENALI and TABULAR as well as the Shared Components including SSIS.  (NOTE:  The CTP3 version number is 11.0.1.1440.19.)  I started with upgrading my DENALI instance which had all of the services installed.  On this instance, the Analysis Services instance was installed to support multidimensional databases.  (The TABULAR instance only has a tabular Analysis Services Instance.)

As you can see in the Select Features dialog you are not able to change the selected features when upgrading to RC0 from CTP3.

image

My first “gotcha” – this may negatively affect my SharePoint 2010 install.  In particular, integrated Reporting Services.  I chose to risk it and continue.

image

I left the Instance name and accepted the supplied Instance ID in the next step.  I made no changes on the next three steps – Disk Space Requirements, Server Configuration, and Error Reporting.

Second issue I ran into was related to Visual Studio 2010.  I order to pass the next step I needed to update it to Service Pack 1.

image

The installer for Visual Studio 2010 SP1 can be found here:  http://www.microsoft.com/download/en/details.aspx?id=23691. After I had that installed, I had to reboot and then continue with the SQL Server 2012 install.  This allowed me to successfully pass the Upgrade Rules operation and I was now ready to upgrade.

After getting no errors during the upgrade, I was required to reboot.  Now to check the instance.

image

Looking good.  I was able to work with the updated versions of SQL Server 2012 RC0 in SharePoint as well.

All in all, it appears that the upgrade succeeded successfully as noted in the comments of the Connect item.  I hope you have a similar experience.

UPDATE: I did run into an issue when trying to execute 2012 SSIS packages from Management Studio.  A error regarding logging was raised.  This issue has been posted on Connect and you can find the information here: http://bit.ly/vTUjcr.  I have not tried the work around yet which requires fulling uninstalling CTP3 then reinstalling.








Follow

Get every new post delivered to your Inbox.

Join 734 other followers

%d bloggers like this: