Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
vanessafvg
Super User
Super User

SSAS Tabular Vs SSAS Multi Dimensional

if you are using power bi to develop dashboards from a cube, do you think its better to create the cube in tabular rather than MDX and why?

 

currently we are creating a cube in via direct querry mdx, and ive already noticed that i can't create measures in in power bi  but i am able to do that in tabular.

 

thoughts?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




14 REPLIES 14
sdjensen
Solution Sage
Solution Sage

Well that question totally depends on what features you need to build the cube. There is still some very nice features in Multidimensional that isn't available in Tabular. But if you can build the cube you need in Tabular then I would go that way. Power BI's own models are build on the same engine as Tabular models hence there is a better "understanding" between Power BI and Tabular than Power BI and Multidimensional.

 

A few weeks ago I attended a session with Chris Webb where he talked about this question and he listed several things that isn't supported in Power BI when using direct query to Multidimensional, but also stated (and I agree with this) if you already have a Multidimensional cube you should only switch to Tabular if there is things you need to do in Power BI that is not supported for Multidimensional. However if you want to start over and build a new cube I would go with Tabular unless there is features that you need that is only supported by Multidimensional.

/sdjensen

@sdjensen ps do you know where i can find what is not possible to do in MDX direct query vs Tabular direct query?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Here is some of the things that currenly doesn't work with live connection to SSAS MD:

  • Q&A
  • Quick Insights
  • Creating report-level measures
  • Quick measures
  • See Records
  • Forecasting on line charts
  • Clustering on scatter charts
  • Explain increase/decrease

 

SSAS MD features that isn't currently supported by Power BI:

  • Cell security – you will not be allowed to connect!
  • Actions
  • Named sets
  • Binary images stored in dimension attributes
  • Hiding unknown members
  • HTTP connections
  • Translations
  • Setting (most) connection string properties
  • Writeback

 

/sdjensen

@sdjensen oh wow thats a big one, role based security doesn't work with mdx and direct query in power bi?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




My post didn't mention RLS, but you can read about the limitations about RLS here: https://docs.microsoft.com/en-us/power-bi/service-admin-rls#limitations

 

RLS has never been used for live connections to Analysis Services this also apply to Tabular.

 

"Only ETL, and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premises model."

/sdjensen

@sdjensen yes the scenario here is to implement role based security in an MDX cube, using a direct query connection on Power Bi Report Server, sorry i dont quite understand what you saying it does work or it doesn't?  will check out the documentation





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@sdjensen also what is ETL?

 

  • Only ETL, and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premises model.

extract transform load (ie imported mode?)





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




An ETL source could be a SQL database where you import data into your model. You can't user Power BI RLS for live connection to cubes - here security is handled with roles in the cube.

/sdjensen

@sdjensen this is what i am referring to

 

SSAS MD features that isn't currently supported by Power BI:

  • Cell security – you will not be allowed to connect!




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




That is not the same as RLS. That is a special multidimensional feature, but if you set it up it will NOT work in Power BI, but Cell security is not that same at "normal" security on multidimensional cubes.

/sdjensen

@sdjensen yes i never mentioned RLS i said role based, which is both cell and dimensional, i am looking for somethign that gives granular security.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Fair enough, but as far as I know it's only Cell security that is not supported.

/sdjensen

@sdjensen hi i am not referrring to power bi RLS , i am referrign to role based security in the mdx cube.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@sdjensen thats what i was thinking we are starting from scratch.

 

thanks





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.