SharePoint Saturday Presentation and Samples

Hi all,

Thanks a lot for everyone honored us by being there in the SharePoint Saturday event 16th October 2010.


Presentation Cover


In this post  – and the updated ones – I’ll post all the related content of my session [SharePoint 2010 Business Intelligence Insights] ISA

You can download the presentation file from here

Then, to configure the lap machine please follow the following steps:

  1. Download the 180 days evaluation software from the following links, if you don’t have licensed ones:
    • Windows Server 2008 evaluation from here.
    • SQL Server 2008 R2 evaluation from here.
    • Office Professional Plus 2010  evaluation  from here, this one only works for 60 days unfortunately Sad smile.
    • SharePoint designer 2010 from here, this one is free Smile.
    • Download the SQL Server 2008 R2 AdventureWorks sample database from here.
    • Download PowerPivot for SharePoint and excel from here, this one is free Smile.
  2. Follow the following guide to have SharePoint 2010 installed here.
  3. When you been asked to create a site collection, create one using the Business Intelligence Center Template.
  4. Install the SQL Server 2008 R2 AdventureWorks sample database executable.
  5. Follow this guide to install the analysis services database.
  6. Install Office Professional Plus 2010.
  7. Install PowerPivot fro excel ( the download page has a small guide for that).
  8. Install PowerPivot for SharePoint using this guide.
  9. Configure Excel Services using this guide.
  10. Add the Data Connection Library on your BI Center to the trusted data connection libraries on the Excel Services manage page.
  11. Configure the unattended service account for PerformancePoint Services using this guide.
  12. To be able to open Office files from SharePoint without annoying unknown errors enable the Desktop Experience Feature using the following guide.


Now, you have the presentation and lap machine up and running.

Kindly, note that additional configuration steps will be added within their related demos, as the above setup is the basic one.

Please, try to create the machine and let me know if you need any help.

ISA in the following posts, I’ll create samples using the above created machine on the different tools of the Microsoft BI framework.

I hope that helped


Connecting Oracle Database to Microsoft Office SharePoint Server 2007 – Concepts


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.


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.


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, See the entry on

I hope that helped.


Business Intelligence Solution Architecture using Microsoft Technologies

In the last two weeks, I was working on a POC and solution architecture for a client that has a Business Intelligence needs.

I can’t go so much in the details for confidentiality reasons :), but I’ll share a common architecture for a business intelligence solution.

First, to be able to follow up with this post, I suggest to go and search for the following common concepts:

  • Business Intelligence
  • ETL
  • EAI
  • Scorecards
  • KPIs
  • Info cubes
  • OLAP
  • Data marts
  • Data warehouse

I won’t speak so much in here on these concepts, because I’ll ISA in the following posts talk about the POC I had made for the client piece by piece with sample code 🙂


Solution Architecture

As you can see in the above image we got a lot of layers and technologies involved, of course not all these technologies and layers required all the time but its need come from the actual solution requirements and business needs.

Below is a very abstract description about each layer in the above diagram:

Presentation layer

Service as the web interface fro the BI solution, it’s a MOSS portal with the Report Center template.

We can use SSRS reports, PerformancePoint Server scorecards, KPI lists and Excel Services to provide the end user with interactive dashboard and BI scenarios. 

Also the SSRS and PerformancePoint Server serves as the core components in storing the report templates, rendering the reports  to the portal and design the reports itself.

We can use the report builder for the SSRS and the Dashboard designer for the PerformancePoint Server.

Business Intelligence layer

The OLAP, SSAS and SQL Server database serves as the BI and data analysis layer.

In this layer all the complex analysis, data warehouse activities and OLAP activities done to provide the multi dimension data to the above layers.

We can use Business Intelligence Development Studio to develop SSAS packages and databases.

Integration layer

Two things we should take care while investing time in this layer EAI and ETL.

For most of the scenarios and as a good practice too, that we use BizTalk for EAI and SSIS for ETL, that’s when we experience the true power of each technology.

We can use Visual Studio to develop both BT Orchestrations and SSIS Packages.

Database layer

SQL Server 2008 is a great tool and so much enhanced to be able to serve data warehousing and BI solutions.

As our data store and DBMS, it will connect the whole solution technologies and make them interact together.

Finally, as I said this was made for a quick POC for a client, so I’ll post in 3 or 4 posts piece by piece of my final solution and in each piece I’ll explain all the related concepts and technologies.

Thanks for my friend Debashish – the data warehousing expert – for his continues support and education 🙂

I hope that helped