Querying SSAS 2016

Question

Post reply

Querying SSAS 2016

pietlinden

SSC Guru

Points: 63503
More actions
September 24, 2016 at 3:33 pm

#149062

I have a really dumb question... partly because I don't get the whole Tabular/Multi-Dimensional difference.
Does Tabular just lie and tell you that it doesn't create a cube in the background when in fact it does? I thought that if you have DAX, you have a star schema, but no cube. I'm watching Bill Pearson's PluralSight course on Organizational BI with SSAS... and I'm confused...
is it all the same cube under the covers and you can query it with either DAX or MDX? (Makes me think I've been listening to software sales people too long... "here's a new feature..." [that's been around under a different name for 25 years].
Thanks,
Pieter

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

pietlinden (9/24/2016)
is it all the same cube under the covers

No. https://msdn.microsoft.com/en-us/library/hh212940.aspx

pietlinden (9/24/2016)
and you can query it with either DAX or MDX?

You can directly query Tabular cubes with both MDX and DAX (SQL 2012 SP1 CU4 and greater) but Multidimensional cubes will only accept MDX.

I'm on LinkedIn

Martin Schoombee SSCoach Points: 19128 More actions · Answer 2

PB_BI (9/26/2016)You can directly query Tabular cubes with both MDX and DAX (SQL 2012 SP1 CU4 and greater) but Multidimensional cubes will only accept MDX.

Small correction to the above. You can query a multidimensional cube with DAX as well (called DAXMD). This functionality has been available since SQL Server 2012.

Reference: http://sqljason.com/2012/12/querying-attributes-and-measures-in-dax.html

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Oh, so from a user's perspective, it's effectively the same... thanks!

Martin Schoombee SSCoach Points: 19128 More actions · Answer 4

pietlinden (9/26/2016)
Oh, so from a user's perspective, it's effectively the same... thanks!

Yes and no...there are things you could do much easier with MDX than with DAX, especially when dealing with hierarchies. I'd still recommend using MDX when working with multidimensional cubes...the exceptions most likely being when working in Power Pivot or Power BI.

pietlinden SSC Guru Points: 63503 More actions · Answer 5

Oh okay. Thanks for the clarification. I guess I should learn a little MDX eventually... just so much to cover and so little time! (and not enough brain cells to hold it all)

Martin Schoombee SSCoach Points: 19128 More actions · Answer 6

pietlinden (9/26/2016)
Oh okay. Thanks for the clarification. I guess I should learn a little MDX eventually... just so much to cover and so little time! (and not enough brain cells to hold it all)

I hear you...and MDX is not the easiest language to master either. Start with the others and work your way up to MDX, and good luck.

PB_BI SSCoach Points: 17463 More actions · Answer 7

PB_BI (9/26/2016)
pietlinden (9/24/2016)
is it all the same cube under the covers
No. https://msdn.microsoft.com/en-us/library/hh212940.aspx
pietlinden (9/24/2016)
and you can query it with either DAX or MDX?
You can directly query Tabular cubes with both MDX and DAX (SQL 2012 SP1 CU4 and greater) but Multidimensional cubes will only accept MDX.

I stand corrected! 🙂

I'm on LinkedIn

pietlinden SSC Guru Points: 63503 More actions · Answer 8

Martin,

That's what I was afraid of... My brain might explode.

you know you're in for a rough ride when Ferrari & Russo freely admit that "DAX is hard"... uh oh.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 9

If DAX in any way resembles MDX then I would say yes it is hard. Like any language, if you dont use it you begin to lose this. This holds especially true with MDX. So practice really matters here to gaining a proficiency with MDX.

----------------------------------------------------

PB_BI SSCoach Points: 17463 More actions · Answer 10

Personally I think DAX is the one of the two which is syntactically more coherent with its subject. That is, unlike MDX, Microsoft haven't tried to make it look like SQL (the "SELECT something FROM something WHERE something" paradigm. Although I was once told that originally the keywords were different (CONTEXT instead of WHERE, but I suppose you'd have to ask Mosha Pasumansky!))

What I'm saying is learning DAX gives you a better understanding of the concepts of SSAS Tabular, whereas MDX can lead to confusion when used in the query form, especially if you come from a SQL background. If you find yourself in the latter camp then Frank A. Bannin wrote an unsurpassed article on this very site on the subject: http://www.sqlservercentral.com/articles/MDX/91228/

As others have said though, good luck! It is tricky but it's worth it and (I think) a lot of fun.

I'm on LinkedIn

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

pietlinden (9/26/2016)
Martin,
That's what I was afraid of... My brain might explode.
you know you're in for a rough ride when Ferrari & Russo freely admit that "DAX is hard"... uh oh.

DAX is hard, but you can tackle it if you do it step by step.

Important to know are the execution contexts and how CALCULATE works.

I'm reading the book by PowerPivotPro.com and they explain DAX really well.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pietlinden SSC Guru Points: 63503 More actions · Answer 12

I have both editions of that book. That was the first really good book I found on DAX.

Somewhere here I have F&R's book, which is a bit mind-blowing, but they're great. (Just wish they had an editor... their English is excellent, but not native fluent.)

I would love a whole chapter with a lot of examples of changing the filter context inside CALCULATE() and CALCULATETABLE().

Maybe I'll just have to fight with that some more. I understand the row and filter contexts (I think), but the overrides and stuff are a bit weird.