Publish a BISM Tabular Model Database Connection on SharePoint 2010

Once you built a BISM Tabular Model project and deployed to SSAS,  you may want to publish it on the SharePoint 2010. A BISM connection allows business end users to access the underline model on SharePoint with addtional security control. It could also utilize the SharePoint quick launch commands to open the Excel Workbook or Power View Report.  For more information about how to create a BISM Tubular Model project, see my previous blog entry Create a SSAS BISM Tabular Model Project.

 

Add the BI Semantic Model Connection Content Type to the SharePoint Library

• Go to the SharePoint Library site that will host the connection file, click the Library in the Library Tools.
• Click the Library Settings
• In the General Settings section, select the Advanced settings

• In the Content Types, Select the Allow management of content types to be Yes, then click OK
• In the Content Types section, Click on the Add from existing site content types
• Add the BI semantic Model Connection

• After click on the OK button, you can see the BI Semantic Model Connection added in the Content type table

 

Set Up Permissions

• Grant the Tubular Model Analysis Service administrative permissions to the SharePoint Service Account.
• Grant the user who is going to use the connection with the Read permission on the Tabular Model database
  
  • Add a Role with Read permission
  • Adding the user to the Role

 

Create the Connection File on SharePoint

• Go to the library page, click on the Documents in the Library Tools.
• Click on the down arrow on the New Document and select the BI semantic Model Connection.

• On the New BI Semantic Model Connection page, specify the server name and database name and then Click OK.

• On the library page, you should be able to see the new connection file.

Use the BISM Connection in SharePoint

Now you may use Excel or Power View to consume the BISM connection file as the data source. 

• If you create the file in PowerPivot Gallery, you could click on either the Open New Excel Workbook or Create Power View Report link on the right upper corner to open the application.

• If you create the file in the Shared Documents page, you could click on the Down Arrow on the File Name to launch either the Excel or the Power View Report.

Additional Reference

http://technet.microsoft.com/en-us/library/hh230813(SQL.110).aspx
http://technet.microsoft.com/en-us/library/gg492136(SQL.110).aspx

SQL Server 2012 – Integration Services Catalog

SSIS in SQL 2012 has a new Integration Services Catalog (SSISDB) that is used for monitoring and managing SSIS projects. The Catalog will store all the integration services objects and will automatically log all the package execution activities when the SSIS project is using the new Project Deployment Model.

In order to use the new Project Deployment Model, you need to create the Integration Services Catalog for the first time.  Otherwise you will get the following error message as:

An Integration Services catalog (SSISDB) was not found on this server instance ("LocalHost").
To deploy a project to this server, you must create the SSISDB catalog. Open the Create Catalog dialog box from the Integration Services node.

Steps to Create the Catalog:

• Open SQL Server Management Studio
• Right click on the Integration Services node
• Click on the Create Catalog…..

• The catalog name is SSISDB. Enter the password for encryption then hit the OK button.

• The new SSISDB will be created and show in two places:
  • Database node
  • Integration Services node

 

• You may view or adjust the catalog properties using the property window or Catalog.Configure_Catalog stored procedure.

Monitor the Execution Activities

After deploy a SSIS project, the data will be collected by the SSISDB.  The build in Integration Service Dashboard could be used to monitor all the activities that executed by the SSIS packages for that project.

• Right Click on the SSISDB node to open the Reports->Standard Reports->Integration Services Dashboard.

• Dashboard Summary Report opened.

• There are hyperlinks on the overview report to navigate to more detail information.

There is no need to create any custom logging in the SSIS packages at all.

Additional Reference:

http://msdn.microsoft.com/en-us/library/hh479588(v=SQL.110).aspx