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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JesperP
Advocate I
Advocate I

Multidimensional or Tabular model?

Hi,

 

I hope some of you can give me some advise on the following:

We are currently building our datawarehouse to use with Power BI specifically.

 

As we would like to keep the data on premise, we made sure we have all the required pre-requisites.

(MS SQL 2014 BI edition, Enterprise Gateway, etc)

As a test, we created a Cube with a couple of dimensions and measures in a multidimensional model, and hooked it to Power BI.

Seems to work fine, however: there seem to be some limitations. I have spend the last year learning DAX, working with both Excel en Power BI, but it seems if I 'live' connect Power BI to our Cube, I am unable to add Measures, Calculated Collumns, and such. We can create them in the Model of course, but that is all in MDX and works quite a bit different than DAX.

 

If we where to move to a Tabular model, we could add the measures and calculated columns in the model by using DAX, but are there any downsides to using tabular? What would your preference be? A list of pro's and con's of both models in regards to Power BI would be most welcome!

 

Thank you for any help you can provide! Smiley Very Happy

 

1 ACCEPTED SOLUTION

@JesperP Here is some food for thought, based on my opinion.

The Power BI Desktop, and all the modeling done in the tool is based on tabular. So it is a seemless transition between any development in the Desktop and what you may do in the model.

When using MD, Power BI is still using DAX to make calls to the MD cube, so there are some limitations and not all MD features may be supported.

Tabular (seems) to be where Microsoft is investing the vast majority of resources (based on the SQL 2016 release). It also has the potential to be much faster because it is (or can be) stored in memory, whereas MD is all on disk.

Another benefit (I'll be blogging on soon) is that you can directly connect your Desktop to the local in memory copy of the model when you are working on it. (looks like <modelname_YourName_GUID>) This is a huge development win for me, as I can test out my measures and calcs against the visuals right away prior to checking them in and deploying them.

 

If you are already well versed in DAX, my personal opinion is that you head down the tabular route.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

7 REPLIES 7
ankitpatira
Community Champion
Community Champion

@JesperP i would suggest you go through this articles thoroughly to understand the difference based on your case whether you're going to use power bi service to connect or power bi desktop.

 

Multidimensional - https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-ssas-multidimensional/

 

Tabular - https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-analysis-services-tabular-data/

 

Thank you for your Reply, I also found those two links. Although they do provide valuable information, it stil isn't very clear about what Power BI features are supported. We found out by trying that, when using Multidimensional, some features were grayed-out.
We really want to create most of the logics/measures in our model itself, but it would be very convenient if we still have the ability to create some measures/calculated columns in the reports itself for instance.

I also don't really know what the main differences are of both models in General. I understood tabular could be slower when you have lots of data as opposed to Multidimensional.

Basically I want to know what would give us the most seamless-experience with Power BI. As we are still in an early stage, we can now build it however we want. Later, this could become an issue. 🙂

@JesperP Here is some food for thought, based on my opinion.

The Power BI Desktop, and all the modeling done in the tool is based on tabular. So it is a seemless transition between any development in the Desktop and what you may do in the model.

When using MD, Power BI is still using DAX to make calls to the MD cube, so there are some limitations and not all MD features may be supported.

Tabular (seems) to be where Microsoft is investing the vast majority of resources (based on the SQL 2016 release). It also has the potential to be much faster because it is (or can be) stored in memory, whereas MD is all on disk.

Another benefit (I'll be blogging on soon) is that you can directly connect your Desktop to the local in memory copy of the model when you are working on it. (looks like <modelname_YourName_GUID>) This is a huge development win for me, as I can test out my measures and calcs against the visuals right away prior to checking them in and deploying them.

 

If you are already well versed in DAX, my personal opinion is that you head down the tabular route.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi Enco1978,

 

Thank you for your reply. This is also what I assumed, I think tablular would give a more 'seamless' transition.

Also I think DAX works a bit easier than MD, but maybe thats just a matter of perspective.

 

Where can I find your blog? Smiley Happy

@JesperP Sorry for the delayed response. I didn't see you had replied. Currently i post all my blogs to my company site.

https://www.concurrency.com/author?user=sbauer

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi @Seth_C_Bauer,

 

Is there a document that lists the DAAX functions that is not supported when connecting to SSAS tabular in live as well as direct mode.

 

 

Any help is greatly appreciated.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@CheenuSing Good question.. I am not aware of any documentation that compares the available functions for the newer versions of DAX (in Power BI) vs. an older version of SSAS Tabular in "Live Connection" mode... 

There is a Direct Query resource, but I can't call it a comprehensive guide for the above question.

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors