Features from SharePoint 2010 Integration with SAP BusinessObjects BI 4.0

1. September 2011 14:11 by Eric in SAP BusinessObjects, SharePoint  //   Comments (0)

The client I'm consulting with asked me about what features the SharePoint 2010 Integration with BI 4.0 (IOMS 4.0) can bring on the table in terms of enriching their Corporate portals, since they have recently successfully migrated their existing BOXI R3 platform onto newly released BI4.0 enterprise server (Note: after confirming with the client, it's a "Ramp-up" instead of "GA" version of the BOE server installation).

After a bit research and reading, I quickly put together a feature list based upon the following major aspects:

  • Search Ability Improvement
  • Collaboration
  • Integration (one-stop-shop)
  • Simple, Rapid Installation and Deployment
  • Accessibility
  • Integrated Security Model with Single Sign-On (SSO) feature

Search Ability Improvement

Unified Search Experience

IOMS provides flexible site searching options allowing users to search just the IOMS site or the IOMS site plus all other SharePoint sites. When all SharePoint sites are search the best match results appear on a single SharePoint search page.

Index and Search BI4.0 Meta Data

Users are now able to conduct free text search across all of this metadata at the same time. In addition, the search index has expanded to include other meta information about files as well as the actual content of the file.

For example, the dimensions, measures and values inside a WebI report are now indexed and made searchable by users. This is very powerful as now users can get results when searching for customers or products, locations or people or anything else that may exist in the content of files.

Diagram 1, the Webi/Crystal reports can be fully searchable through the SharePoint 2010 portal

Collaboration

The authorized users can leverage discussion feature within the SharePoint 2010 portal to collaborate with colleagues by adding comments to documents (such as Web Intelligence document) in the report repository.

Diagram 2, to create a Webi report discussion within the report

The post message can be easily deleted or adjust their importance. The user can also reply back to the sender of the message or respond to the group as a whole.

Diagram 3, to create a message on the Webi Report level

As part of the meta data associate with the Webi Report, the discussion content for the report can also be indexed and searchable through the SharePoint portal

Integration (one-stop-shop)

Authorized user can manage their content directly from SharePoint. This includes the ability for users to:

  • Set report schedules
  • Edit report properties
  • Set parameters
  • View report history
  • Delete files
  • Send files to inbox
  • Email or FTP
  • And many more

Diagram 4, there are lot functionality available to manage the report through the SharePoint portal User Interface

In addition users can add or upload new content from Local machine to BusinessObjects without having to leave IOMS.

Users can open, view and interact with the content directly from SharePoint. Depending on the type of file, the file may be opened directly in a web browser (such as in the case of a crystal report, WebI document or Xcelsius dashboard) or in the appropriate thick client application (such as in the case of opening a Word document, PDF file)

Diagram 5, the Webi report schedule can be set up through the SharePoint portal

The URE or content explorer webpart or Document explorer is flexible now to choose columns you would want to see, number of items you would want , choosing the start folder etc.

IOMS includes SharePoint webparts that allow administrators to enhance existing SharePoint sites with business objects content. Out of the box IOMS 4 ships with a number of SharePoint WebParts that allow you to enhance other SharePoint sites with BI reports, dashboards and content stored in BusinessObjects.

Diagram 6, the Webi Report can be viewed/updated/embedded within the SharePoint Portal

For example, an Xcelsius dashboard containing sales metrics could be embedded into the landing page of a SharePoint sales site. When users open the sales site the dashboard is refreshed to show the latest information on sales for that region or team.

Introduced helpful Units like “Recently viewed” and “Recent Searches” on the Out-of-box template Home Page to enable quick navigation

Diagram 7, stunning quick navigation from Out-of-box integration templates

Simple, Rapid Installation and Deployment

No pre-requisites before installation

In the previous version of IOMS XI3.1 installation, it requires extra .net SDK and the corresponding fiddling as a pre-requisite. As for the latest version, all the pre-requisites have been already bundled within the installation package without any extra software download required. All you need to do is to “double-click” on the installation executable file.

Diagram 8, one click for the installation, no more Hush Hush

Easy configuration with BI4.0 CMS Integration (minimum information required from CMS Server)

It doesn’t require the tedious configuration procedure as the previous version and can be quickly set up by supplying the following information to the installation wizard:

  • CMS Name with Port number
  • CMS Administrator Logon details
  • URL to the server that hosts BI Launch Pad

Diagram 9, minimum set-up required from BusinessObject Enterprise

Wizard-driven Installation

IOMS wizard driven installer and SharePoint site definition template allow it to be quickly deployed with minimal configuration as a dedicated SharePoint site.

Once deployed SharePoint administrators can further tweak and enhance the IOMS site as required.

Diagram 10, the deployment and configuration option are fully wizard-driven

Accessibility

Users can browse to access content stored in their BusinessObjects inbox, personal folders, public folders or categories. The users permissions and rights as set in BusinessObjects are still enforced by IOMS ensuring that users are only able to access stuff they are authorized to access.

Diagram 11, authorized user can quickly access the information through the SharePoint portal

IOMS is built on the 4.0 Business Intelligence platform and leverages the new Platform features for Auditing, Search and other areas. Being built on the BI Platform means that IOMS can be remotely administered using the web based Central Management Console.

Diagram 12, almost all the BI4.0 new features available on the SharePoint portal after IOMS installation

Integrated Security Model with Single Sign-On (SSO) feature

From an Authentication and SSO standpoint, users can login directly to BOE from SharePoint using their Active Directory, LDAP or BusinessObjects Enterprise Credentials. In addition Single Sign-on workflows can be supported using Kerberos or CA SiteMinder.

Diagram 13, the user can log on the SharePoint BI4.0 portal just with their AD login credentials

Well, I'm pretty sure that there are some features I missed out here, but will definitely keep everyone updated as I go with the integration and implementation phases.

Truncate & Shrink Transaction Log files on SQL Server 2008 by a nifty SQL script

30. August 2011 10:21 by Eric in SharePoint, SQL Server  //   Comments (0)

Had a task yesterday to restore a few SharePoint 2010 site collection Production databases back to the Development environment. Then turns out that the development SQL Server VM doesn't have enough hard-drive space available for those production humongous sites, after getting confirmed by the system administrator that they wouldn't allocate any extra space to that the development environment for my case, thereby I just have to go down the path with truncating & shrinking all those fat transaction log files there.

Well, in terms of how to truncate the transaction log files on SQL Server 2008, there are heaps resource available online, you could simply find the following tips right away by simply Googling it;

SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008

http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/

How to truncate and shirnk Log files in SQL Server 2008

http://madhuottapalam.blogspot.com/2008/05/faq-how-to-truncate-and-shrink.html

My scenario is a bit different in terms of more than 30 SharePoint databases there, and I just don’t want to shrink and truncate their transaction logs individually like how it used to be done. (I just want to be lazy in the productive and reusable way)

By default, the SHRINKFILE script can be simply like this:

USE [WSS_ApplicationUsage]
GO
ALTER DATABASE [WSS_ApplicationUsage] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(N'WSS_ApplicationUsage_log', 1)
ALTER DATABASE [WSS_ApplicationUsage] SET RECOVERY FULL WITH NO_WAIT

GO

However, in my case, an enterprise edition SharePoint 2010 server has more than 32 databases, so that's why I quite reluctantly execute the script repeatedly for that many databases, not to mention, it's quite easy to case human error as well.

On the other side, it's just not reusable approach at all, by which I have to hardcode the database and transaction log file names each time when I need to switch to the different database. (Please don't criticize my laziness).

Based upon all those factors, I created this following script to get my job done gracefully in a reusable and extensible way

First off, I created a temp table #TransactionLogFiles, by which I store all the database and log files name in there, so that I could shrink the log files later on:

CREATE TABLE #TransactionLogFiles (DatabaseName VARCHAR(150), LogFileName VARCHAR(150) )
-- step 1. get hold of the entire database names from the database server
DECLARE DataBaseList CURSOR FOR 
SELECT name FROM SYS.sysdatabases

WHERE NAME NOT IN ('master','tempdb','model','msdb','distribution')

Then, after knowing the database name, I could quickly work out the corresponding log files name and insert all their details into the temp table #TransactionLogFiles I created in the previous step by utilizing a SQL Cursor

DECLARE @DataBase VARCHAR(128)
DECLARE @SqlScript VARCHAR(MAX) 
-- step 2. insert all the database name and corresponding log files' names into the temp table
OPEN DataBaseList FETCH
NEXT FROM DataBaseList INTO @DataBase
WHILE @@FETCH_STATUS <> -1 
BEGIN

SET @SqlScript = 'USE [' + @DataBase + '] INSERT INTO #TransactionLogFiles(DatabaseName, LogFileName) SELECT '''
+ @DataBase + ''', Name FROM sysfiles WHERE FileID=2'
--SELECT @SqlScript
EXEC(@SqlScript) 
FETCH NEXT FROM DataBaseList INTO @DataBase END

DEALLOCATE DataBaseList 

Lastly, iterate each row from the temp table #TransactionLogFiles, then change the database recovery mode to "simple", pass the checkpoint, then shrink the corresponding log file, switch back the database recovery mode back to "full" once the truncation has completed.

-- step 3. go through the each row and execute the shrinkfile script against each database log file on the server
DECLARE TransactionLogList CURSOR FOR 
SELECT DatabaseName, LogFileName FROM #TransactionLogFiles 
DECLARE @LogFile VARCHAR(128) 

OPEN TransactionLogList FETCH
NEXT FROM TransactionLogList INTO @DataBase, @LogFile
WHILE @@FETCH_STATUS <> -1 
BEGIN 
SELECT @SqlScript = 'USE [' + @DataBase + '] '
+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY SIMPLE WITH NO_WAIT '
+ 'DBCC SHRINKFILE(N''' + @LogFile + ''', 1) '
+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY FULL WITH NO_WAIT'
EXEC(@SqlScript) 

FETCH NEXT FROM TransactionLogList INTO @DataBase, @LogFile END
DEALLOCATE TransactionLogList
--SELECT * FROM #TransactionLogFiles

-- step 4. clean up
DROP TABLE #TransactionLogFiles

Just simple like that. All the fatty transaction log files can be truncated within a couple of seconds.

Horray! My job is gracefully done here and hopefully if someone comes across the similar issue later on, they could leverage the script to save a lot time and energy on this ;)

Extensibility

I always like to put "Extensibility" into consideration when it comes to the development.

If look at the script above carefully, you would find SYS.sysdatabases and sysfiles tables, in other words, if you have any particular condition here, for example, you would like to only shrink the transaction file size above 4GB, so just put the extra Where clause around that, then this script could fully work on for your scenario. Enjoy!