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
pmdci
Advocate V
Advocate V

Measures and CalcColumns in Power BI vs Tabular model (replace SSAS with Power BI?)

Hello,

 

I have a data-mart on-premise, and on top of that I have an ssas tabular model.

 

I've been playing with Power BI on my deskop and I noticed how it allow us to create our own calculated columns and measures. If that is the case then I am wondering if a tabular model is at all necessary. I mean, couldn't I simply connect straight into the data-mart and add all measures and calculated columns within Power BI instead?

 

I am sure that this might be the case of undocumented features and limitations. So I was hoping if someone else could help me shed some light on this?

 

Thanks for your help.

 

Regards,

P.

1 ACCEPTED SOLUTION
andre
Memorable Member
Memorable Member

You need Analysis Services if you have one of the following requirements:

 

  1. You need to have row level security
  2. Your data set is greater than 250MB
  3. You need to have a fine grained control over how your model is refreshed from the source data (partitioning)

 

If the aforementioned requirements are not present, then using Power Pivot or Power BI Desktop to build your model and publish it to Power BI is a valid and wonderful choice.

 

View solution in original post

9 REPLIES 9
andre
Memorable Member
Memorable Member

You need Analysis Services if you have one of the following requirements:

 

  1. You need to have row level security
  2. Your data set is greater than 250MB
  3. You need to have a fine grained control over how your model is refreshed from the source data (partitioning)

 

If the aforementioned requirements are not present, then using Power Pivot or Power BI Desktop to build your model and publish it to Power BI is a valid and wonderful choice.

 

Thanks Andre!

 

Actually I got all confused due to the overlap of BI products by Microsoft. But one thing that put it all into perspective for me is that Power BI's modelling features are like PowerPivot in Excel. Once I got that in my mind, then it all made sense.

 

It is simply "PowerPivot for Power BI" -- for the lack of a better term.

 

Regards,

P.

Thanks everybody for their input.

 

Just to clarify: The rationale behind my question is whether I can remove SSAS server and have all the calculated columns, measures relationships and so on created using the Power BI desktop designer instead. This way Power BI would connect straight into the relational DB (the data mart).

 

My understanding from Andre's answer is that the modelling features of Power BI have some limitations:

 

  1. No row level security
  2. Model cannot be greater than 250MB
  3. No fine grained control over how model is refreshed from the source data (partitioning)

 

Am I right to assume that if this limitations are overcome by MSFT, then an SSAS server would no longer be necessary in my case?

 

Regards,

P.

 

PS: Apologies for the late response. I got no email alerting me of replies into the thread.

Greg_Deckler
Super User
Super User

I don't see why not, you will need the Power BI SSAS connector, which supports row level security, here is more info:

http://sqlblog.com/blogs/jorg_klein/archive/2015/03/19/power-bi-analysis-services-connector-security...

 

Once you suck information into Power BI, or Excel PowerPivot for that matter, it is in a tabular model, so not sure why you would need another tabular model in between.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi smoupre,

 

I think you misunderstood my question.

 

My question is whether I could simply bypass SSAS, connect straight into the relation DBs and have all measures, calculated columns and relationships that exist in the SSAS created in Power BI instead. Therefore rendering SSAS irrelevant.

 

Regards,

P.

pmdci,

The value of the tabular model is that you can directly connect to it from the Power BI service. Create your reports up there, and its a live connection that automatically refreshes whenever you update your model on the back end.

The desktop designer can be used as your replacement for the tabular model, but if you already have one, you are just adding an extra step. I build my report visualizations on the site.

The AS Connector can be downloaded from your app.powerbi.com site (click the down arrow in the upper right)

More info:

https://support.powerbi.com/knowledgebase/articles/471577-configure-a-power-bi-analysis-services-con...

 


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 Eno1978,

 

I think you misunderstood my question.

 

What I am trying to find out is if I can simply replace the SSAS tabular model by creating measures, calculated columns and relationships in Power BI instead -- thus making SSAS irrelevant.

 

Bottom line: If I could replace SSAS with a cloud-only feature, I would.

andre
Memorable Member
Memorable Member

@pmdci, SSAS is actually part of Power BI Desktop and Excel Power Pivot.  When Microsoft incorporated SSAS into the Desktop and Power Pivot, several enterprise features were made unavailable (security, partitioning, etc) but from the pure modeling perspective, you have pretty much the same features in SSAS and Desktop/Power Pivot.

 

I think that not having a clound only SSAS like product, or Platform as a Service (PaaS) Analysis Services (similar to SQL Azure) is a big miss for MIcrosoft and I hope that it gets addressed soon.

 

 

@pmdci the Power BI desktop is not a "cloud only" feature, and the site doesn't allow you to build models.

Regardless, Could you move your model into the desktop tool? "Sure". Here are the trade-offs.

 

SSAS - row level security (Desktop - none)

SSAS - Does not store you information in the cloud (Desktop will)

SSAS - Live connection to your model (Desktop - only update up to 8 times a day)

 

I would not compare the modeling capability in Power BI directly to SSAS as equal in performance and capabilities. In terms of the tools, I would say (in general) the modeling tool in the desktop tool is designed for small projects, and SSAS is best utilized in larger company wide implementations. (especially if you have the option).

 


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
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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

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.

Top Solution Authors
Top Kudoed Authors