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.





Recovering SharePoint 2010 with BI on Windows 7

12 07 2011

While working with Microsoft BI projects, I am often required to deliver the visualizations through SharePoint.  On my current project, I have a SharePoint 2010 development environment on Windows 7 that has SharePoint and all the SQL Server services running.  Last week I was preparing to demo our progress when the site became unresponsive.  The demo site had the wonderfully descriptive message:  “An unexpected error has occurred.”  So, I attempted to open the management console but that page could not be found (404 error).  I turned on the expanded error messaging by modifying the web.config file and checked the log files.  The errors pointed to the inability to connect to a database.  Upon further investigation I discovered that three of our databases went suspect including the admin and content databases.

At this point, I proceeded to try to restore my full SharePoint backup.  Which works in all situations except for recovering the admin database. Based on the online resources, this is to be expected.  After conferring with the coworkers, I was able to determine that the admin database was unrecoverable and the best bet was a reinstall of SharePoint.  After working through some options I was able to get the site back up and running without recreating the entire solution.  The rest of the blog walks through the recovery scenario and calls out the nuances of having BI components as part of the content.

Prerequisites

First, the SharePoint Farm must be Farm backed up.  I did this through the Central Administration console. 
(http://technet.microsoft.com/en-us/library/ee663490.aspx)

image

Reinstalling SharePoint

The next step involves uninstalling SharePoint.  I did this by running Setup and choosing the Remove Option.  (The Repair option did not work.) Once I had SharePoint uninstalled, I proceeded to reinstall it.  After SharePoint was installed, I ran the Configuration wizard. This step recreated the Admin database.  Once this was complete, I verified that the starter site and the Central Administration site were operational. 

Recovering the Farm

You can recover the farm from either the Central Administration site or by using PowerShell.  In this case, I used Central Administration as it is easier to select and understand the options. 

I first browsed to the Backup and Restore menu page. From this page, I selected the “Restore from a backup” option under the Farm Backup and Restore header.

NOTE:  In order to run the backup or restore farm functions, the Administration service must be running.  If it is not running, start it in the Windows Services console.

There are three pages or steps to recovering the backup. 

Step 1.  Select the backup to restore.

image

Step 2.  Select the components to restore.

image

For my purposes, I selected the Farm option which restores all of the options to the farm from the backup.

Step 3.  Select Restore Options.

imageimageimage

The first two selections are the most important.  I selected the “Restore content and configuration” and “Same configuration”.  This uses the same application and site names for the full restore of your site.  The “New configuration” option would have required me to rename all of the applications and services I needed to restore. There were a lot of applications and services to rename which I chose not to do.

When I restored at both the client and on my personal development environments I needed to remove the following services before running the restore:

  • Application Registry Service
  • Web Analytics Service Application
  • User Profile Service Application
  • Search Service Application.

When I did not remove these, my restore failed due to duplicate names.  You may run into a similar issue, so removing them before the restore is easiest.  These applications and services should be removed from the Central Administration site.  Once these services are removed, run the restore.

Bringing BI Back Online

At this point, my site would have been operational except I had a Reporting Services webpart on the homepage of my site.  Because of that I saw an error below.

image

As the error message notes, the issue is with the Reporting Services webpart. The issue is that the full restore did not restore the Reporting Services integration.  The integration needed to be redone manually. 

Once Reporting Services was reintegrated I was able to open the site.  However, neither the Reporting Services or PerformancePoint webparts were working. 

image

These issues are primarily security related.  To fix the Reporting Services issue, I openend the data source and reset the credentials.  Once reestablished, the report webpart came back online. 

To resolve the PerformancePoint issue, I first went to Central Administration to refresh the Secure Store Service key.  I was able to use my passphrase refresh the key.  (NOTE: if necessary you can create a new key.) Next, I went in to the PerformancePoint Service Application settings and reset the unattended service account. 

Once these steps are complete, the site was fully restored.  While the full backup is extremely useful for site failures it will not recover all of the BI components and as such feels very incomplete. I hope this saves someone some time later.








Follow

Get every new post delivered to your Inbox.

Join 787 other followers

%d bloggers like this: