Posts Tagged ‘DAX’

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!