Archive for January, 2012

Tabular Model

January 30, 2012

One of the most exciting features in SQL Server 2012 is the introduction of the Tabular Model. The Tabular model along with the multidimensional model comprise the Business Intelligence Symantec Model (BISM).

To be honest when the Tabular Model and the BISM where introduced along with DAX I was more than a little skeptical. It seemed like yet another addition to the already complex Microsoft BI stack.  It was presented as ‘individual’ or ‘Group’ BI that allowed users to quickly and easily develop their own models. But didn’t scale because it had to fit into memory and could not handle complex requirements like Parent-Child relationships financial calculations.

However, after playing around with it for a little and talking some of the leaders in that space I have completely changed my mind. I will address my initial concerns in a general manner below:

1. Scalability – While it true that the Tabular Model is an in-memory database system. The memory limitation can be overcome by using the DirectQuery query mode (or one of the “hybrid mode”) which passes the query to the SQL Database Engine. When combined with Columnstore indexes you can build BI solutions that scale to the enterprise level.

2. Complexity – Although DAX has not reached the level sophistication as MDX to able to handle complex requirements, the gap is closing and it is closing fast! Here are some good examples:

 Parent/Child Hierarchies

Unary Operators

In summary I am now convinced that Tabular and DAX are not going to complicate MS BI but simplify and allow me to more quickly and on a more iterative basis deliver solutions to my clients. I am excited about the future.

Here are some of the best resources that I have found:

Cathy Dumas BLOG – great info.

SQLBI: Marco and Alberto have tons of great DAX information.

DAX Editor: The DAX Editor for SQL Server an extension to Visual Studio 2010 that implements a language service for the DAX language. The DAX language is the expression language used for SQL Server Analysis Services tabular models. This extension can be used when editing DAX queries and also for writing DAX measures.

MDX and DAX formatter

DAX in the BI Tabular Model Whitepaper and Samples: This whitepaper and sample workbook introduce Data Analysis Expressions (DAX), a formula expression language used to define calculations in PowerPivot for Excel® workbooks and Analysis Services tabular model projects authored in SQL Server Data Tools.

PowerPivot-info: The 1 stop shop for all things DAX/PowerPivot/Tubular!

DAX Resource Center: This Wiki includes articles, whitepapers, videos, and samples published by both Microsoft as well as experts in the Business Intelligence community. Because this is a Wiki, you too can contribute. If you have some great information about DAX and how you use it in your organization, please share it!


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