It’s a little-known fact (but certainly not completely unknown – it was mentioned in Marco, Alberto and my SSAS Tabular book I think) that you can define measures using DAX in the WITH clause of an MDX query. This means you can write queries like the following against an SSAS Tabular model:
with
measure ‘Date'[Demo Calc] =
countrows(‘Date’)
select {measures.[Demo Calc]} on 0,
[Date].[Calendar Year].members on 1
from [Model]
The official documentation, such as it is, is here:
http://msdn.microsoft.com/en-us/library/hh758441.aspx
Unfortunately you can’t use it from Excel 2013 using the new ‘create calculated measure’ functionality; I also talked to the nice people behind OLAP PivotTable Extensions and there are some very good reasons why they can’t support this either.
What use is this then? You’re only going to be able to use it in scenarios where you control the generation of the MDX on the client side, such as SSRS reports, which may not be all that often; in fact, in these situations you might be better off writing the whole query in DAX. It’s only going to be useful when you need the power of MDX and DAX in the same query. For example, you might want to take advantage of DAX’s superior ability to detect multiselects, but write all your other calculations in MDX. I’m clutching at straws here though! Still, it’s an interesting thing to know about. Please leave a comment if you can thing of a situation where you can use it…
Good to know because you can use this functionality with my frontend OpenView:)
I saw that people tried to take a look at OpenView. You can find the details through my blog.
There is free personal edition of OpenView
http://analyzerhakan.wordpress.com/
Reblogged this on analyzerhakan and commented:
I think of putting this functionality into OpenView
Hi Chris:
I work as team leader of BI team in IMSHealth, Dhaka. We generally are trying to shift from SSAS Multidimensional to SSAS Tabular. Previously we built complex cubes and wrote complex MDX to generate the output which would go as feed of a .NET application to develop Dashboards. I was trying to replace the cube with tabular and MDX queries with DAX queries. But found that, it is not that straightforward. Even a simple userdefined hierarchy cannot be selected by DAX (by EVALUATE) where as it is pretty easy in MDX. I guess these features are not present in DAX. So, Can you suggest me about how we should move towards tabular ? My little understanding says – we can replace the cube with tabular data model and query the tabular with MDX but not with DAX. We can redesign the tabular database with the power of DAX. If, we use the same MDX for cube and tabular model, will the output for tabular be faster than output for cube ? Please shed some light on me. Thanks in advance.
Hi Tanvir, it’s wrong to say that Tabular will always give you better performance than Multidimensional – there are too many factors to consider. All you can say is that it’s going to have different strengths and weaknesses. So maybe you should reconsider why you want to move to Tabular? You’re right, a lot of things that are easy in MDX are hard in DAX; it’s also true that a lot of things that are hard in MDX are easy in DAX.
Hi Chris:
Thanks for your insight and clear remarks. I know there are specific fields where multidimensional is better than tabular and vice versa. But so far I sensed that Microsoft has not been very optimistic about investing more on multidimensional and we saw that in sqlserver 2012, 2014. Rather they are focused on in-memory architecture. So, I was wondering of switching the technology specially for our online dashboards, you know pretty much better as you were here. So, for a relatively smaller data-size, can we do some pilot project by replacing the cube with tabular and restructuring the MDX reports a little bit and see the difference of performances ? or You can suggest something else ? I would be really grateful if you can show some technical roadmap, please. Thanks in advance.
Yes, certainly MS have been investing more in Tabular and Power Pivot recently. In theory, yes you could replace your Multidimensional cube with a Tabular model and your dashboard should still work with only minimal changes to the MDX. However this could be misleading: the hardware requirements for Tabular are very different, so you would probably need to use a different server; you would also need to make sure you have tuned your model properly too. So I don’t think it’s easy to compare Multidimensional with Tabular unless you are willing to redesign your solution for Tabular. It wouldn’t hurt to run a quick test though and see what the results were.
Thanks a lot, Chris. I have a clearer vision now. I will let you know about the quick test of tabular instead of multidimensional. Let’s see what happens 🙂
Hi Chris:
As discussed, I started experimenting on tabular for custom dashboard application for our projects. As stated earlier, I almost generated most of the reports. But one thing I am facing with lot of struggle is : using the generate function. We need to build some reports like, sub-brands by Geo where, Geography in different layers (user-defined hierarchy) generate top 10/20 sub-brands under them (a lot of sub-brands in total), here MDX is much faster in multi-dimenstional whereas MDX falls into some infinite loop while querying from tabular. So, is there any work around ? Or, what do you suggest in this regard – how to implement such reports from tabular with high performance ? As I told you earlier, we need to generate cellset from backend and supply to our application that builds charts, tables and other customized stuffs to build customized web dashboards for pharma clients.
I’m afraid I don’t know of any workaround and I don’t know why queries using Generate() would be slow. Maybe you can open a support call with Microsoft?
Hi Chris:
We have sub-brand count = 41944 and Geo count = 85, So for each geo, among 41994 sub-brands, top 20 are considered. So, do you think, yet it should work fast ?
Honestly, I don’t know. It could be a problem with SSAS or it could be something to do with hoe you’ve designed your model.
Hi Chris
Thanks for this article. Very interesting. I’m wondering, is it possible therefore to use DAX to define a calculated measure in the calculated measures script of a cube ?
Thanks – Adam.
No, I don’t think this is possible, but I would need to check.