Multi-Dimensional vs Tabular and data security

Question

Multi-Dimensional vs Tabular and data security

pietlinden

SSC Guru

Points: 63503
More actions
August 30, 2016 at 2:23 pm

#329701

First off, what I know about MD vs Tabular, you could probably fit on the head of a pin....
but I'm learning using DAX/PowerPivot/PowerBI. (gotta start somewhere!) and then I watched Brad Gall's presentation on Analysis Services. It's not a new video, but it made me wonder... if you want scalability and data security, do you have to use MDX?
Is there a good tutorial on MDX vs DAX? (I have seen some of Bill Pearson's stuff... maybe I need to read more?)
How does Tabular's data security model work?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

but I'm learning using DAX/PowerPivot/PowerBI. (gotta start somewhere!) and then I watched Brad Gall's presentation on Analysis Services. It's not a new video, but it made me wonder... if you want scalability and data security, do you have to use MDX?

Analysis Services handles the security for Mutlidimensional and tabular so, at a high-level, they are both equally secure. The advantage of tabular of multidimensional is that you can do row-level security, there is no row-level security with the multidimensional model.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pietlinden SSC Guru Points: 63503 More actions · Answer 2

Is there a good beginner book on Multi-Dimensional / Tabular? I've been playing with DAX for a while (and I'm still terrible at it!)

Especially one that covers security/RLS.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

pietlinden (8/30/2016)
Is there a good beginner book on Multi-Dimensional / Tabular? I've been playing with DAX for a while (and I'm still terrible at it!)
Especially one that covers security/RLS.

I don't know too much either about the Tabular Model or DAX and have never read a book on the subject; the little I know I have learned online. I've got a few years experience with MDX and am okay with it.

On the MDX side - I thought Microsoft SQL Server 2008 MDX Step by Step was very good. This is the book I used to learn MDX. There does not appear to by any books that cover both MDX and DAX except for Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX. I have not read it but I've read other Wrox books and SSAS and found them helpful (like 3.5/5 stars helpful).

Something I've found very helpful was the Analysis Services Tutorials. They don't cover MDX or DAX too much but they walk you step by step through building a cube and/or workbook. They have a tutorial specific to multidimensional modeling and tabular modeling. I've only done the multidimensional one but it's excellent and it covers security.

Again, my focus has been almost exclusively MDX but I think DAX is similar in this aspect... Other ways I learned MDX were by using tools like Marco Russo's Excel MDX macro. If you build a simple cube you can attach it to an Excel workbook and drop/drop fields. the macro helps you extract that MDX query that get's built behind the scenes. You could probably create or find one that does the same for DAX. You can also attach to a cube in SSAS, browse it and see the MDX expressions you'd use to traverse different parts of the cube. If you know SSRS at all you can attach to a cube and create MDX queries using the Query Designer UI - it allows you to see the auto-generated MDX - which you can play around with and manipulate. I think you can do the same kind of thing with a tabular workbook/DAX but don't know for sure.

The secret, for me, to learning MDX, and I suspect that this applies to DAX, is to understand that MDX is not SQL. People see "SELECT" and "FROM" and "WHERE" and think they're looking at something similar to T-SQL... Not so, MDX is a totally different animal. SQL deals with columns and rows, MDX is dealing with dimensions and measures. There are no columns, there's axes (axis plural)! Measures are what you measure, dimensions are how want to measure it (e.g. sales amount by month, product sold by region, etc). Measures and dimensions intersect (e.g. SalesTotal for June, SaleCount By the Southern Territory).

DAX is similar and seems to be based on Excel.

That's my $0.02. Help it helps a little.

edit: a couple minor typos.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001