SQL Dump After Either Upgrade or Downgrade the PowerPivot

Recently I upgraded the PowerPivot Excel add on to the version 2 to work with new DAX. I experienced excel crush when I opened the workbook.  The error message showed as:

SQLDUMPER.EXE
Unable to open file \\?\Program Files\Microsoft Analysis Services\AS OLEDB\10\FlightRecorderCurrent.trac error 2

I tried to uninstall and re-install the program, but it didn’t work.  I have to manually delete the directory C:\Program Files\Microsoft Analysis Services\AS OLEDB\ and repair the PowerPivot, then it works.

Same situation happened again when I downgrade my PowerPivot back to version 1 to be compatible with my client’s environment.  Once I manually remove the C:\Program Files\Microsoft Analysis Services\AS OLEDB\ directory and then repair the program, it works again.

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

Create a SSAS BISM Tabular Model Project

To create a SSAS tabular model project, you will need to install the BIDS (now called Microsoft SQL Server Data Tools in SQL 2012) and have a SQL Server Analysis Services running in tabular mode (xVelocity in-memory analytics engine (VertiPaq)). It is recommended that the AS and the BIDS are installed on the same machine.

Install an Analysis Services instance running in tabular mode

• Add new feature to existed instance or add new instance

 

• Select the Server Mode: Tabular Mode and add yourself as the administrator

 

You could verify the AS Server mode using the SQL Server Management Studio and note the icon next to the server name in the Object Explore. You could also check the DeploymentMode property (0 =Traditional,  1 = PowerPivot for SharePoint, 2 = Tabular) in the msmdsrv.ini file.

Create New project

• Open Visual Studio 2010 and select New project
• There are three types of project template as shown below:
  • Analysis Service Tabular Project: Use this template to create an Analysis Service project with tabular models
  • Import from PowerPivot: Use this template to create a tabular project by extracting the metadata and data from an existing PowerPivot workbook
  • Import from Server (Tabular): Use this template to create a tabular project by extracting the metadata from an existing tabular AS server

• Once you create a project, you could import your data source by clicking on the Model on the toolbar and select the Import From Data Source to start the Table Import Wizard.

• You could either create a connection or use an existing connection to import the tables or data you need.  There are lots of choices such as Relational databases, Multidimensional Source, Data Feeds, or Text Files.

• You could change your model view from Data View to Diagram View by either clicking on the Model View on the Model or the icons on the right low corner.

• You could manage the relationships or create hierarchies using the Diagram View.  You could create and manage measures in Data View.
• Once you finishing modeling your project, you may deploy it to your AS Tabular model server to be used.

Issue or Error

You may encounter the following error: Unable to connect to default workspace database server, which indicates that you have not yet set up a default workspace server.

To fix it, you could click on the Options from the Tools menu and then select the Analysis Services option to set the default workspace server and deployment server.

 

If you are interested about how to use the model once you created, you may want to check out my other post : Publish BISM Tabular Model Database Connection on SharePoint 2010.

 

Reference:

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

http://blogs.msdn.com/b/analysisservices/archive/2011/07/13/welcome-to-tabular-projects.aspx

How to add SSIS Custom component to the SSIS Toolbox in Denali CTP3

Recently I try to convert the SQL 2008 SSIS packages to “Denali CTP3” version. Those packages are using the custom components. After recompiling the customer components to reference the SQL 11.0 objects, I need to add them into the SSIS Toolbox.

In BIDS 2008, I could open the Choose Toolbox Items option from the Tool menu to add those customer components.

      

 

In SQL Denali CTP3, the new SSIS Toolbox does not have a "Choose Toolbox Items" option. It will automatically detect new custom components, and add them to the SSIS toolbox.  If you cannot find them, you could do the following:

• Make sure you add your task/component to the appropriate SSIS folder i.e. “C:\Program Files\Microsoft SQL Server\110\DTS\PipelineComponents\” or “C:\Program Files\Microsoft SQL Server\110\DTS\Tasks\”
• Open the SSIS Toolbox by clicking the SSIS Toolbox button that is located on the top-right corner of the package design surface.

• Right click on the Toolbox and select the Refresh Toolbox option.

Reference:

http://social.technet.microsoft.com/wiki/contents/articles/ssis-toolbox-in-sql-server-denali-ctp1.aspx

Come Join with Mariner

My employer, Mariner, is currently looking for talent individuals with strong SSIS, SSRS, and SSAS experience.  Mariner is based in Charlotte, North Carolina. 

We are a company with SQL MVPs and MCPs who are talented, dedicated, and fun to work with.  If you have experience developing Microsoft BI solutions and like to work in a truly collaborative team environment, then please consider Mariner.

The best way to inquire is through the email address on the Jobs page of the Mariner corporate website.

How to Display No Row Information on SSRS Report

When  report rendered, most users would like to see the records if there are data returned and some kind indicator or message if no data returned.  One old technique could be:

• Add a Textbox with no data return message below the Tablix.

• Set the Hidden property for the Tablix to be True if the row count equal to 0 or False if row count great than 0.

• And set the Hidden property for the Textbox to be the opposite of the Tablix.

Here is an easy simple way to do it for SSRS 2008 by using NoRowMessage property:

• No additional textbox needed and the leave the Hidden property for the Tablix as default which will be false.

• Set the NoRowMessage with the message you would like to be displayed when no data returned.

• You may set the Tablix Font properties to use the correct size or style and set the TextAlign to be Center to display the message on the middle of the report.

• You could also format your message using expression as below to display them in three rows text. The “VBCRLF” is Visual Basic Carriage Return and a Line Feed function that could be used in expression to break the line.