Connecting Oracle Database to Microsoft Office SharePoint Server 2007 – Concepts


Introduction

Microsoft Office SharePoint Server 2007 best definition is a framework build using SOA, to provide the needed functionality to build and connect line of business applications.

With the integration with SQL Server 2008 Analysis services, Integrations Services and Microsoft Office Excel Data Mining Add-on we can build Business Intelligence Solutions that relay on different data sources like Oracle Database.

Business Data Catalog

Business Data Catalog is a new business integration feature in Microsoft Office SharePoint Server 2007. It is a shared service and it enables Office SharePoint Server 2007 to surface business data from back-end server applications without any coding. Business Data Catalog bridges the gap between the portal site and your business applications and enables you to bring in key data from various business applications to Office SharePoint Server 2007 lists, Web Parts, search, user profiles, and custom applications.

Business Data Catalog provides built-in support for displaying data from databases and Web services. That is, you can use Business Data Catalog to display data from your SAP, Siebel, Oracle Database or other line-of-business (LOB) application via Web services or databases.

image

BDC enables this connection by defining data connection and application definition in the BDC metadata database, once the LOB application is registered and its connection is defined, then we can use this connection to display the required data on the MOSS lists, document libraries and web parts. 

Business Intelligence Enabled Approach

Microsoft introduce BI with the SQL Server Analysis Services, the role of SQL Server is to host the data warehouse info cube and provides the needed functionality for the data mining, OLAP and data retrieval.

image

As shown in the above diagram, in order to populate the SQL Server DWH database we need an ETL tool that will synchronize the changes from the Oracle Database on periodically manner.

After the data is synchronized in the DWH then the SSAS will analysis process it in the info cubes and make it available for reporting and data mining.

In the interface layer we can use the SQL Server 2008 Report Builder 2.0, Microsoft PerformancePoint Server 2007 and the Dashboard Designer to design and build professional reports, KPIs and scorecards.

All the reports, dashboards, KPIs and scorecards can be displayed on MOSS as the unified user interface using MOSS Report Viewer, Report Explorer, MOSS KPI lists and MOSS dashboards.

As an innovative way to help business users to build their own reports in an efficient and easy way Microsoft presents the Data Mining Add-on in Microsoft Excel.

In Excel normal business users can build their reports using common Excel functionality with the data connection to MS SQL Server 2008 Info cubes and the drag and drop functionality.

Note: the same entry is available on my blog on it.toolbox.com, See the entry on it.toolbox.com

I hope that helped.

Ahmed

Advertisements

3 thoughts on “Connecting Oracle Database to Microsoft Office SharePoint Server 2007 – Concepts

  1. My question is that what if the Oracle Web Application has some daashboards defined based on user (who should see what) and we want to import this to the SharePoint for Dashboard and KPIs, will we be able to still maintain the security of who should see what?

    Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s