Critical performance issue - PBI Service

[mcrmn]

Dear Microsoft

[Firstly, apologies for posting this here; ideally I’d like to raise a support ticket but unfortunately my business PBI licence is currently on a Pro trial (until we complete a pilot/test phase before we formally procure PBI and integrate it into the business), so this is the next best step]

I am emailing to inform you of an on-going performance issue with Power BI Services that is unfortunately a serious ‘blocker’ in the progression of the BI ‘self-service’ project I am working on.

When using the PBI Services web portal to view and explore the visualisations, it’s commonly taking 45-90seconds for the visuals on any of the numerous visualizations I’ve created and deployed to PBI Services to successful update (and to stop showing the “spinning round clock”). For the same ‘performance issue’  to happen whenever we’re clicking on any one of the slicers, this unfortunately isn’t useable as a business solution in it’s current condition.

This issue has been going on for a few weeks now, and it’s preventing the initial procurement of 8 new PBI Pro licenses until the issue is resolved.

Some background & context

• The BI solution – I have created a multidimensional data warehouse model (currently running on SQL Server2014 64-bit Standard edition) which gets loaded (i.e. ETLs) each day with production data via a series of SSIS packages.
  • Unfortunately, we’re not currently using SSAS. What I’ve done is use Power BI to “simulate” much of its functionality (e.g. user hierarchies, relationships, time intelligence (for a fiscal year), excluding the import of columns and rows not needed in the “cube”, user-friendly columns and table name, “role-playing” dimensions, etc) all being created in a single dataset within PBI Desktop. This single dataset (and it’s data model) has then been published to our PBI Service  account, automatically refreshed a few times each day and all the other PBI visualisations have then been created using solely the PBI Service as the data source. All visualisations use the ‘Import’ data access method (and not DirectQuery).

NOTE: Within our PBI Services account, the ‘Customer Ops’ workspace contains the visualizations that are intended for business use.

• The entire solution in PBI Services is currently consuming 20MB of 10GB.
• SSAS isn’t currently being used within the office.. That said, I am currently in the process of implementing a reflective solution using SSAS (and a multi-dimensional model),but it’s in the early stages of development.

• About me – I am a Business Intelligence Developer/Analyst currently a few weeks away from passing/obtaining my MCSE (Data Management & Analytics) exam/status, I have 5+ years of production experience in creating SQL Server solutions. The data model I have implemented is in alignment (to the best of my knowledge) with best practices (not including my above point on SSAS currently not being utilised). I have intensively being using PBI Desktop for over a year.

Some ideas on potential causes of the issue

• Emulating the functionality of SSAS solely within PowerBI may be resulting in “too much” processing for it to handle, particularly with regards to the built-in ‘fiscal calendar year’ Time Intelligence I have implement using custom DAX to create the necessary measures/logic pertaining to ‘time intelligence’ calculations.
• An infrastructure lead has advised me that our internet performance is around 2-3Mbps

NB: When using one of the Power BI Desktop files that was used to create the visualisation published to the PBI Service, with connectivity using only the one connection to our PBI Service dataset (mentioned above), this results in much better performance.

• Maybe a combination of the above two points have resulted in the chronic performance issue. That said, I won’t have expected a data model compressed to less than 20MB to be such a struggle for Power BI Services,

The reality

Our PBI account I speak of is current using a trial Pro licence. We are initially looking at purchasing eight PBI Pro licences. However, I first need to get ‘buy-in’ from senior management; I am currently holding back on giving a presentation on the ‘self-service’ solution show-casing the value of Power BI Services/Online, however, I am not willing to present a solution that so clearly isn’t fit for use.

Microsoft have been generous enough to provide me with an extended one year trial Pro licence (circa 4 months remaining). This has enabled me to further develop the BI solution and continue testing ready for business ‘buy-in’.

Any insight & assistance you could provide what be much appreciated.

Kind regards

Richard Nunn MCSA

[mcrmn · Accepted solution]

Hello

Firstly, my sincere apologies for the dramatic delay in response; there’s been a number of altercations over the past few months, one is that I’m moving on to pastures new in a few  weeks. That said, I’ve been meaning to respond to this post to provide my input & responses to the above for several weeks now(!).

@v-qiuyu-msft

Thank you for your response; I skimmed over the insightful Microsoft doc you referred me too and was happy to read that I was following industry ‘best practices’ (except for using PBIs platform to replicate the functionality of SSAS in the absence of SSAS teck) in the overall BI implementation/solution. The  (other) exception was the ‘network latency’ section; this very well could have been a strong factor in this  performance issue, it has always been a serious concern of mine here and had flagged internally. Unfortunately, this matter remains inconclusive.

@GilbertQ

Thank you for your response; correct, quite a few. However, as mentioned in my original post was the fact that, due to office circumstances at the time, SSAS teck was NOT being implemented. As I’m sure you know, SSAS is a teck that enables ‘performance tuning’ within its feature-set; since my original post, now that I’ve built, deployed and handling the data processing of the BI solution exclusively in SSAS (once I’d introduced the teck into the office), performance is now “user friendly” as you might expect. Ultimately, just as some offices might “incorrectly” use Excel as a ‘flat file database system’ in a way that the Excel teck isn’t explicitly designed for, PBI was also being used in a way that it isn’t explicitly designed for, and you get ‘real-world’ problems as you might expect.

Thanks all. I am happy for this post to be closed.

[GilbertQ]

Hi there, it appears that there could be quite a few things causing performance issues.

I would consider looking to see if you access your SSAS cube directly if that is fast or slow? Because if the design of the SSAS cube is not optimal then the DirectQuery being sent back to the SSAS cube could potentially be the bottleneck. I have so far never had any issues with the Power BI Service, but more rather from the sources.

Then I would also ensure where you have got the On-Premise Gateway installed has enough resources to handle the direct query loads.

Another alternative is to load everything into Power BI Desktop via Import mode and see how that works in the Power BI Service. That will allow you to see if it the Power BI Service, or another artefact within your environment.

I currently have got running a Power BI Desktop file that is 900MB in size with Import mode, and that is running very fast in the Power BI Service.

Likewise I have got an SSAS 2017 Tabular instance with DirectQuery that too is running almost instantly when rendering and clicking on visuals.

[AndreVar67]

Hi.

I'm having degraded performance in my "PBI Service" reports me too.

Same report, good performance two/three weeks ago Now,  lot of time is needed to complete page refresh or "no recource" message is displayed (I don't use ssas connection because I have multiple sources: Analysis, SQL and some excel. I have imported all data in report)

I have a PBI Pro licence.

Could be possible this problem regarding new PBI version released in April ? ..there is an article that describe a change of page recalc/review depending on user activity.

Thanks

BR

[GilbertQ]

Hi there

Can you see if your data model has changed (As in gotten larger) or if there were new measures added?

[AndreVar67]

Data Model: I deleted a lot of unnecessary (at now) columns and reduced imported records (my target was to expose result of last 5 years and I reduced to last two only.)

Measures: some measures added (and sametime I replaced SUM with CALCULATE+SUMX).

I created a role to manage row-level security. Microsoft documentation: ROLEs shouldn't have impacts in performance (I hope...).

I have also found a recently Microsoft article about new workspaces availability (https://powerbi.microsoft.com/en-us/blog/announcing-new-workspace-experience-general-availability-ga...) and I publicated my report in a new one (it seems a little bit more faster...).

One note again: we usually use PowerBI through internal Company Network (it is a huge worldwide network). I'll ask for possible problems bewteen internal Net and PowerBI servers (external)... but in this case, I should have same (bad) performance like my colleagues, I think. 

Thanks,

BR

---

[AndreVar67]

To confirm myself "row-level" security impacts (or not) on performance, I have added an user in the dataset group like ADMIN.

...and now he has same performance like few weeks ago !...

[GilbertQ]

Hi @AndreVar67 

Great that you deleted the unwanted columns and then reduced the data to the last 2 years.

I would suggest using a SUM instead of a Calculate + SUMX (SUMX is an iterator over rows, which is much slower in certain cases)

If you are experiencing issues when you add RLS, the one question I have due to it being a large organization are you using Power BI Premium?

The reason that I ask is because RLS has a seperate cache per user to ensure that they can only see their own data. And if you are using Power BI Premium this increases the memory allocated, and if you are running out of memory then it will slow down.

[AndreVar67]

Hi @GilbertQ 

1. I tested both (SUM and SUMX) and SUMX in my measures has better performance

2. Yes, I'm working in a big Company.

I have a lot of table connected to the external sources and for each source, a lot of derived queries to check quality and harmonize different data strctures.

...and few final queries I use to expose data in report (hundred thousands records only).

...and I added role control through ALL tables....I have now deleted unnecessary ones and performance is better than before.

Really many thanks for your answers.

Bye!

[GilbertQ]

Thats good to know, sometimes SUMX can certainly be faster.

Yeah if you can make things at the end into the data model as simple as possible it goes a long way to make things faster and easier to use.

[v-qiuyu-msft]

Hi @mcrmn,

There are many facts can affect Power BI report render performance. I would suggest you go through this article and try to optimize reports then test again: https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

Best Regards,
Qiuyun Yu