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.

 

Create Reports Using PowerPivot Data Source

You can create new SSRS reports, Excel reports, or PPS Dashboard reports based on PowerPivot workbooks that are published in a SharePoint PowerPivot Gallery. PowerPivot data access on a server is supported through SharePoint 2010 with SQL Server 2008 R2.

SSRS Reports

You can use either Report Designer in BIDS or Report Builder to access the PowerPivot data.  The Reporting server could be in native mode or in SharePoint integrated mode.  Not like the Excel reports which include embedded data, SSRS reports always access PowerPivot data as an external data source.

In order to support PowerPivot connections from Report Designer, you must have the SQL Server 2008 R2 OLE DB Provider for Analysis Services installed.  Windows authentication is also required to integrate between the SSRS server and the PowerPivot server.  More information could be found at http://msdn.microsoft.com/en-us/library/ee210631.aspx.

Steps for SSRS Reports:

• Create a new Report Server Project in Visual Studio 2008 or BIDS
• Add new data source in Share Data Source

• Using the Microsoft SQL Server Analysis Services Type with Connection String pointed to the SharePoint location for the PowerPivot spreadsheet, for example: Data Source="http://SharepointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx"

• Click on the Credentials tab to set the Authentication to be Windows and click on OK button.

• Add a new report and select the Share data source just created, then click on Next.

• Click on the Query Builder button

• The Query Designer will be opened with a Sandbox cube

• You now should be able to work on your MDX query to get your result as needed.

 

Excel Reports

You could also create new Excel reports to access the PowerPivot workbooks that you have permissions with.

Steps for Excel Reports:

• Open Excel, go to Data tab and select From Data Connection Wizard on Other Sources menu

• On the Data Connection Wizard window, select the Other/Advanced option and click on Next button

• Select the Microsoft OLE DB provider for Analysis Services 10.0 and click on Next

• Enter the data source in the Data Source textbox as : http://SharepointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx and click OK

• The Select Database and Table window will then be opened with Sandbox Cube been selected to be used. Click on the Next to save the data connection file and finish the wizard. Here’s what the connection string may look like:

Provider=MSOLAP.4;Persist Security Info=True;Initial Catalog=PowerPivotDemo Sandbox 6042a0d5-355c-47ed-b24c-d458bfecd529;Data Source=http://SharePointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Data Source Version=5/9/2011 5:27:11 PM

   

• You now should be able to use the Data from the PowerPivot data source to create your excel report

 

Performance Point Server Reports

Performance Point Server on SharePoint 2010 also supports PowerPivot data source.

Steps for PPS Reports:

• Open PPS Designer and connect to the SharePoint site
• Create a new Analysis Service data source

• Using the following connation string: Provider = molap; datasource = http://SharePointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx
• In the Cube drop down box, select the sandbox cube

Since Excel Pivot Tables that go against PowerPivot data do not support the write back, offline cubes, and drill through,  there are some known issues when using the PowerPivot data source in PPS reports such as:

• Show Details error

• Error running data source query. DrillThrough is not supported because the server is running in VertiPaq mode.

 

 

Additional References:

PowerPivot Connection Type (SSRS)

Plan for PowerPivot Integration with Reporting Services

PowerPivot Features

ErrorCode Enumeration

Enhance PowerPivot Cube using Visual Studio 2008 R2

PowerPivot is a self service BI tool that you may use it to prototype your work quickly. The SQL Server Analysis service that PowerPivot cube used is a VertiPaq storage mode which is different from the traditional  MOLAP, ROLAP or HOLAP cube. It is a read only temporary cube that PowerPivot Service creates and manages. You may persist or enhance it using Visual Studio 2008 (SQL Server 2008 R2). 

Here are the steps to enhance it using Visual Studio:

• Backup and Restore the PowerPivot Sandbox database with a different name on the VertiPaq Mode SSAS.
• Open the Analysis Service Database option to point to the restored database from Visual Studio to create a new project.

  

• Once the project is created, you could see all the dimensions and a cube called Sandbox in the project.

  

• The Data Sources contains a connection string that connects to the original data source used by the PowerPivot workbook.
• The Data Source Views contains all the tables from the PowerPivot workbook as Named Queries.
• Since it is a read only cube, if there is any modification or trying to process the cube, you will get the following error

• To overcome the read only issue, you need to detach and then attach it by unselecting the Read-only checkbox to change the ReadWrite mode to be ReadWrite.

 

• Now you should be able to add a new MDX or DAX measure and then deploy/process it.

• You could view your result in both the Visual Studio or SSMS.

• If you try to restore the PowerPivot Cube on a traditional SSAS Server, you will encounter the following error: Backup and restore errors: Restore failed. The backup file was created on a server with DeploymentMode=1 and the current server is running DeploymentMode=0. (Microsoft.AnalysisServices)