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
Anonymous
Not applicable

Is Power BI based on Access or Excel formulae and Access tables

Hi

Is this the correct way of thinking about Power BI ?

 

1) You can perform all the same calculations you do in Excel?...or Access?

2) You store data in tables, like you would in Access? And join them as you would in Access? or you do in Power Pivots?

3) You can create pivot tables plus all of the same pye charts/others charts that are available in Excel?

4) You can post dashboards and charts etc online

5) You can set up user permissions so that users and clients only see the data you want them to see

 

Additional questions:

1) where is data stored (server/cloud?)

2) are there limits to the amount of data you store in tables?

 

Many thanks

 

Andy

 

 

 

 

6 REPLIES 6
Greg_Deckler
Super User
Super User

Mostly accurate. Power BI uses M code for queries and DAX for the data model but you can do pretty much anything you can do in Excel or Access.

 

Yes, you can join tables in your data model

 

There are actually many more visualizations available for Power BI than Excel or Access and more are coming from the looks of the github playground.

 

Data is stored in cloud

 

Currently there is a 250MB limit on the data model size within the client tools like Power BI Desktop. There are limits of around 10GB for a Pro account currently.


@ 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...
Anonymous
Not applicable

Thanks for your replies guys

 

I'm trying to sense very quickly whether Microsoft BI is the tool for us...we are a media agency looking to produce all of our weekly performance dashboard reports (analysing online, search and social activity) in a BI tool

 

We will therefore have mutliple clients report and, in some cases, data going back to 2012.

 

I'm concerned that 10gb data limit makes Microsoft BI a non-starter.

 

Or is there some way around this? or a different way to configure the database? eg 10gb per client, as an example?

 

 

 

 

 

 

 

There are several limits to think about.. the 10gb limit is for the total amount of data models that have been uploaded in the service.  The good news, however, is that because of the way the data are stored, you will see huge compression effect (sometimes 10X-15X) so 10GB can effectively represent 100GB+ of raw data.

 

The bad news, however, is that a single model cannot exceed 250MB.  If you have more data than that, you will have to create an Analysis Services Database and expose that to your Power BI tenant.

And most notably - sharing to users outside your organization is currently NOT supported - so your clients would have to have the same @organization.com email extension to be able to share to them. And sadly, no information is yet released whether or not that ability is arriving anytime soon...

The 10GB storage limit for Pro users is based on the datasets that are tied to Reports/Dashboards that you are using, and/or have been shared with you. 

 

https://support.powerbi.com/knowledgebase/articles/657085-manage-your-data-storage-in-power-bi

 

This is based on items that are actually stored in the cloud.  So, if you are using an AS Tabular Server, and the AS Connector with that, that does not count towards your storage limit.  That is a way that you can use fairly large data models and not be gated by the storage limits as the data resides in your environment, not in the cloud.

Adam W. Saxton | Microsoft Employee | Business Intelligence
@GuyInACube | youtube.com/guyinacube
GuyInACube
Employee
Employee

The following was a post of limits on the Data Model within an Excel Workbook using Power Pivot.  I mentioned this in your other post.

 

https://support.office.com/en-us/article/Data-Model-specification-and-limits-19aa79f8-e6e8-45a8-9be2...

 

I don't know that the same holds true for Power BI Desktop as we use an AS Tabular instance under the hoods for that.  So, there may be some differences there.

 

 

1) You can perform all the same calculations you do in Excel?...or Access?
[GIAC] From the Data Model perspective, we use DAX for calculations. https://support.powerbi.com/knowledgebase/articles/554619-quickstart-learn-dax-basics-in-power-bi-de...

 

2) You store data in tables, like you would in Access? And join them as you would in Access? or you do in Power Pivots?
[GIAC]Power BI is all about the Data Model. We have to have the data model to do anything. That's where the data is stored. You have a few options with regards to the Data Model - Power Pivot within Excel (can use Excel Data Tab or Power Query to also get data into the Data Model), Power BI Desktop or Analysis Services - Tabular currently. Within the Model you will have tables and you can define relationships along with other artifacts (Calculations, Measures, etc...)

 

3) You can create pivot tables plus all of the same pye charts/others charts that are available in Excel?
[GIAC] The visualization options within Power BI are varied. The following article lists the available visualizations. https://support.powerbi.com/knowledgebase/articles/469552-visualization-types-available-in-power-bi-...

 

4) You can post dashboards and charts etc online
[GIAC] The Dashboard concept is currently only available online within powerbi.com. You can create reports locally within either Excel or Power BI Desktop and then publish those online.

 

5) You can set up user permissions so that users and clients only see the data you want them to see
[GIAC] You have a few options. Basically you can share a dashboard with people in your organization, which would be read only. Or, you can take advantage of the Groups feature (Pro Feature). You can read more about security in this article - https://support.powerbi.com/knowledgebase/articles/667840-power-bi-security

Additional questions:
1) where is data stored (server/cloud?)
[GIAC] This depends on what you are doing. If you have a Data Model within an Excel Workbook, or Power BI Desktop file, we will host the Data Model in the backend in the cloud. If you use the Analysis Services Connector, the data is not stored in the cloud as we will issue queries to your on premises Analysis Services Tabular server via the Analysis Services Connector.

 

2) are there limits to the amount of data you store in tables?
[GIAC] See the first link that I put in this response with regards to limits on the Data Model within an Excel Workbook. Power BI Desktop uses an AS Tabular Instance under the hoods. Here are the limits for the AS Tabular Server, although I don't know if this holds true for the local mode that is used with Power BI Desktop.

https://msdn.microsoft.com/en-us/library/ms365363.aspx#bkmk_vertipaq

 

 

Adam W. Saxton | Microsoft Employee | Business Intelligence
@GuyInACube | youtube.com/guyinacube

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