THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Create a #dax Measure in a Session in #BISM #Tabular

Thanks to Greg Galloway that pointed me out this is possible, in this post I want to show how to create a measure local to a session. This could be done in SQL Server Management Studio, even if it is probably not so useful for a Tabular model considering that you can easily define measures local to a DAX query. However, maybe you will have a reason to do that (I can only imagine a long list of measures defined in a session that are used in small queries sent from a client – it could be an idea for custom clients connecting to Tabular that sends many short queries using the same measures, for example a dashboard or a set of charts).

First, you have to connect to Analysis Services by including the Cube=<cubename> in the connection string. By default, the cube name of a tabular model is “Model”, so you can use Cube=Model in the connection string. You can do that in SQL Server Management Studio by placing that string in the Additional Connection Parameters, as you can see in the following picture.

connectioncubemodel

Then, instead of using DEFINE MEASURE before EVALUATE statement, like you would do in the following statement:

DEFINE


    MEASURE 'Internet Sales'[Internet Total Sales] = SUM([Sales Amount])
    MEASURE 'Internet Sales'[Internet Total Product Cost] = SUM([Total Product Cost])
    MEASURE 'Internet Sales'[Internet Total Margin] = [Internet Total Sales] - [Internet Total Product Cost]
EVALUATE
SUMMARIZE
(
    'Internet Sales',
    'Date'[Calendar Year],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Cost", 'Internet Sales'[Internet Total Product Cost],
    "Margin", 'Internet Sales'[Internet Total Margin]
)

You can define measures by using the CREATE SESSION statement. Please note that if you didn’t set the Cube in the connection string, you would get an error.

CREATE SESSION
MEASURE 'Internet Sales'[Internet Total Sales] = SUM([Sales Amount])
MEASURE 'Internet Sales'[Internet Total Product Cost] = SUM([Total Product Cost])
MEASURE 'Internet Sales'[Internet Total Margin] = [Internet Total Sales] - [Internet Total Product Cost]

At this point, you can reference the measures you defined in your DAX query:

EVALUATE
SUMMARIZE

(
    'Internet Sales',
    'Date'[Calendar Year],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Cost", 'Internet Sales'[Internet Total Product Cost],
    "Margin", 'Internet Sales'[Internet Total Margin] 
)

I’d be interested to hear whether you find some scenario in which it is useful using this feature!

Published Tuesday, February 28, 2012 2:47 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Greg Galloway said:

One trick I just figured out is that you can specify the name of the cube inline instead of having to put it on the connection string:

CREATE SESSION MEASURE [Model].'Internet Sales'[MyCalc] = SUM([Sales Amount])

It also looks like session measures aren't documented, so proceed at your own peril!

http://msdn.microsoft.com/en-us/library/hh548656(v=sql.110).aspx

February 28, 2012 11:29 AM
 

Marco Russo (SQLBI) said:

Very good Greg - thanks!

Marco

February 28, 2012 5:56 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement