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