Posts Tagged ‘2012’

SQL Server 2012 Environment Variables

May 20, 2012

There are several new features introduced in SQL server 2012.  There have been some very compelling improvements to SSIS. The new SSIS catalog is one of the most important because it means that SSIS now ships with it’s own Performance Framework.

Another feature and the purpose of this BLOG post is to introduce Environment Variables. This is a cool feature because now you don’t have a dependency on environmental variables in the operating system.  Some Network Admin won’t let BI developers access the OS. Environment Variables also simplify disaster recovery because you don’t have to recreate the environmental variables.

I am not going to give a step-by-step on how to step environment variables. Plenty of tutorials have already been created on the web. What I would like to do is demonstrate  that Environment Variables can be referenced by anything that Environmental Variables can reference this includes project and package level SSIS objects like connection managers, parameters, and variables.

I created a demo package below with project and package level parameters and connection managers:




I created a catalog and folder on SSISDB and deployed it to the server:


Now environment variable can be created and reference the any SSIS object property in the project: (make sure the data types match)


Now you can create the reference which ties the SSIS object property to the environment variable.


Then choose the parameter or connection manager and set it’s value to the environment variable


Notice how you can choose from the package level or project level.


Installing SSAS 2012

May 6, 2012

I don’t usually write posts like this but there have been a few substantial changes to how SSAS 2012 is installed as compared to the previous versions.  The changes also underscore the changes that have occurred to SSAS with the introduction of the Tabular Model.

I am not going to take you through all the steps the. But when you get to the Analysis Services Configuration screen you can now choose to install SSAS in  the traditional Multidimensional Mode or Tabular Mode. You cannot install both at the same time but you must install them one at a time.

In the example below I choose to install the Tabular Model first:


I am not going to walk through the rest of the steps. If you want to install the Multidimensional you will have to re-run the installation again and choose the Multidimensional Mode. See the next three screen shots:




If you have run through the installation two times, one for each mode, then you should see something like the following in SSMS. Notice the two different instances:


The above only serves as an introduction and provides a context to some of the differences you will encounter when working with a Tabular project in SQL Server Data Tools (SSDT) aka the new BIDS.

When you first fire up SSDT (I will miss BIDS because it sounds so much smoother;) and create your first Tabular project see below..


You will be confronted with a strange new question:


You will be asked to choose a workspace server. What this means is that you must choose a previously installed instance of SSAS in Tabular mode. The workspace server will be used during development of the Tabular project in SSDT.  And in fact SSAS automatically creates a new database with the project’s name with a GUID appended to it.


This means in order to develop a Tabular project in SSDT you must have first installed a SSAS instance in Tabular mode. (Don’t be surprised if you see this on a test sometime see my linkedin profile). With that said  I like this change because unlike the old multidimensional project in BIDS which runs in 32 bit during debug, you can now develop a Tabular project in SSDT in 64 bit mode and then deploy it to your 64 bit server. This eliminates the headache of having deal with both 32 bit and 64 bit code when deploying a project.

Additional Resource:

Cathy Dumas: Configuring a workspace database server

Master Data Services 2012

January 18, 2012

As I mentioned in a pervious post Master Data Services for SQL 2012 is a huge improvement over the SQL Server 2008 R2 version. The biggest difference is the ability for users to manage entities directly in Excel!

This is important because now Data Stewards can perform most of their Master Data Management activities in excel.

Master Data Services (MDS) is the SQL Server solution for master data management. Master data management (MDM) describes the efforts made by an organization to discover and define non-transactional lists of data, with the goal of compiling maintainable master lists. An MDM project generally includes an evaluation and restructuring of internal business processes along with the implementation of MDM technology. The result of a successful MDM solution is reliable, centralized data that can be analyzed, resulting in better business decisions.

With the right training, most business users should be able to implement a Master Data Services solution. In addition, you can use MDS to manage any domain; it’s not specific to managing lists of customers, products, or accounts. When MDS is first installed, it does not include the structure for any domains—you define the domains you need by creating models for them.

Other Master Data Services features include hierarchies, granular security, transactions, data versioning, and business rules.

Master Data Services includes the following components and tools:

  • Master Data Services Configuration Manager, a tool you use to create and configure Master Data Services databases and web applications.
  • Master Data Manager, a web application you use to perform administrative tasks (like creating a model or business rule), and that users access to update data.
  • MDSModelDeploy.exe, a tool you use to create packages of your model objects and data so you can deploy them to other environments.
  • Master Data Services web service, which developers can use to extend or develop custom solutions for Master Data Services.
  • Master Data Services Add-in for Excel, which you use to manage data and create new entities and attributes.

For a summary of MDS resources, see the SQL Server Master Data Services Portal.

**Note: You can upgrade to MDS 2012 without upgrading the SQL Server 2008 R2 instance.

To install MDS follow the MSDN product information

MDS Samples Installation

**To Deploy the Sample packages you will need to use MDSModelDeploy.exe

3. List the MDS web services to connect to if you are unsure of the service name. The default being MDS1 as shown by this command.

MDSModelDeploy.exe listservices


4. Deploy the package by pointing to the package file, and connecting to that service listed from step #3. Provide a model name for the model that you want to deploy.

MDSModelDeploy.exe deploynew -package “C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages\customer_en.pkg” -model CustomerSample -service MDS1

MDSModelDeploy.exe deploynew -package “C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages\chartofaccounts_en.pkg” -model ChartOfAccountsSample -service MDS1

MDSModelDeploy.exe deploynew -package “C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages\product_en.pkg” -model ProductsSample -service MDS1

Once the samples are installed then install the Excel plug-in and enjoy


Tech-Ed: Managing Master Data with MDS and Microsoft Excel