Take advantage of SQL Server Business Intelligence tools with Sharepoint
BI - Business Intelligence has become an integral part of data platform since Microsoft revealed the features from SQL Server 2005 version onwards. As per this strategy of Microsoft comprises a suite of server and client-side data integration tools, not to mention about useful, analytical and reporting tools in SQL Server 2005 (and 2008 too) provide the backbone data management infrastructure, while Microsoft Office applications, specifically Microsoft Office Excel, provide the flexibility for information workers to remotely interact with centralized and secure data sources.
Similarly SharePoint Server & Services 2007 version onwards provides greater length of data management & publish as per the business requirements. So to analyze this I can say there are many different ways to analyze external data and publish it to SharePoint sites, including using Excel Services. Few of Enterprise Edition features of the produce you may choose a data integration method that will allow an administrator to more selectively create and deploy data reports and minimize end user control, or you may choose to empower end users by enabling them to access data sources and build custom reports directly inside SharePoint. For the security and privileges Microsoft BI suite includes the flexibility to securely and selectively deploy data based on user credentials.
So how does this BI integration works with SharePoint, using Office 2007 products BI features within the Office suite of products. SQL Server 2005 Reporting Services, Analysis Services, and PerformancePoint Server 2007 extend the core BI functionality by offering enhanced reporting capabilities, data warehousing, in-depth data analysis, and real-time monitoring. Since a year ago I'm using Excel on a greater scale that integrates well with both SQL Server 2005 and SharePoint Server 2007 and plays a pivotal role in providing end users with the tools for data analysis, data-mining construction, and the creation of PivotTables. No doubt that Microsoft has given Excel 2007 power that enhances the user's ability to access and analyze data from SSAS cubes using PivotTables and PivotCharts. Analysis Services data can also be accessed with Excel 2003 via PivotTables, but this involves some additional configuration.
Here is the handy information from Technet about Excel-to-SharePoint and data integration when there is a heavy usage of Excel by Business users. So if you are using Excel 2003, you can create a SharePoint list directly from within Excel by clicking a single cell in the Excel spreadsheet and selecting List | Publish List. This creates a new SharePoint list in Datasheet view. Note that when you are publishing from Excel 2003 to a SharePoint list, only a single worksheet will be published—in other words, if you have an Excel workbook with multiple worksheets, not all worksheets will be published.
Linkage can be maintained between the original Excel 2003 file and the newly created SharePoint list and synchronization can be performed both ways. That is, you can add new rows to the source Excel 2003 worksheet and have the linked SharePoint list updated. Likewise, you can update the SharePoint list and have the source Excel worksheet updated.
Using Excel 2007, you have two options for publishing data to SharePoint:
- You can publish to Excel Services. If Excel Services is installed as part of your SharePoint Server 2007 deployment, a one-way sync will exist between the source Excel 2007 file and the published server version. In other words, updates to the source file can be pushed to an existing published version and changes reflected within a Web page.
- You can export a table within Excel 2007 to a SharePoint list. Once a table has been exported, no linkage remains between the source and destination data. That is, updating the original Excel file will not then update data in the SharePoint list, and data updated in the SharePoint list will not update the original Excel 2007 file.
The main difference between publishing from Excel 2003 and publishing from Excel 2007 to a SharePoint site is that you can publish not only a single Excel worksheet but an entire Excel workbook if you are using Excel 2007 with Excel Services installed. In Excel 2007, the publishing option has been built into the main File menu and will publish an entire Excel workbook to Excel Services.
I believe in the recent times Microsoft site highlights a great way of representing how best the BI-Office-SharePoint technologies can leverage the users that can collaboratively access and analyze data (see below) Source:Technet.
For further tips and tricks about how your can integrate SharePoint with your BI suite refer to Microsoft Office SharePoint Server 2007 Best Practices (Microsoft Press, 2008).
