cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
itpragya Frequent Visitor
Frequent Visitor

power bi report server very slow

Dear All,

 

I have created a dashboard on my Power BI Desktop which has Row level security implemented at the Database level.

 

When I try to access this dashboard as the Service account, the dashboard loads without any delays. But if I try to load it as any other user, it takes atleast 10-13 minutes to load it. Can someone please let me know what might be wrong?

 

I have tried reading the logs, but except for not being able to delete the expired logs, I do not really see an error. If someone can help,I would happily share the logs too privately?

 

Thanks

10 REPLIES 10
stpnet Established Member
Established Member

Re: power bi report server very slow

So the first thing to check is, is it PowerBI that's slow or is it the DB?

 

Have you added a profiler trace to the backend database?

 

If so you should be able to see the queries that are being sent. You can troubleshoot the performance of these queries either in SQL or in SSAS depending on what type of source you are using.

 

if you have both fast and slow queries, you can trap both and then check out the difference in the queries and the query plans that are produced. This shoudl help you identfy where the bottleneck is

 

If you have imported the data its a bit harder as you will have to connect to the hidden SSAS instance but that's equally do-able, just a bit fiddly.

 

 

 

itpragya Frequent Visitor
Frequent Visitor

Re: power bi report server very slow

Hi @stpnet ,

 

Yes, I have checked on the DB side, but the queries are pretty fast(0-7 secs), plus there are just about 300,000 rows that are being fetched. The server has 128GB RAM and 16 core processor so I believe it's not an infrastructure issue too. The server just runs the SQL Server and the Power BI Report Server. Just FYI, I am using SQL Server 2016, Power BI Report Server August 2018 and we are not using SSAS, but a direct query.

With my service account, the dashboard loads instantly but not with the user account. Any help?

 

Thanks

philipplenz Established Member
Established Member

Re: power bi report server very slow

Hm.. does the users are in many groups and the groups are mapped as a login to the datasource?

Do you have any traces on the user accounts or row level security enabled wich are disabled for the service account?

itpragya Frequent Visitor
Frequent Visitor

Re: power bi report server very slow

@philipplenz 

 

Hi, 

Below is the function I am using to implement the RLS.

 

CREATE FUNCTION [dbo].[func_PLAN_KEY_FILTER]
( @PLAN_KEY AS nvarchar(10) )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS AccessRight
WHERE @PLAN_KEY in (select a.PLAN_KEY from dbo.PROGRAMMES a
inner join dbo.SECURITY_GROUP b on (a.GROUP_NAME = b.GROUP_NAME and b.LOGIN_NAME=system_user)) or system_user = 'ServerName\ServiceAcctname' ;
GO

 

Do you believe any problem with the function? I think the service account bypasses the subquery.

 

Thanks 

Highlighted
itpragya Frequent Visitor
Frequent Visitor

Re: power bi report server very slow

@philipplenz 

 

Also below is the security policy associated with it:-

CREATE SECURITY POLICY [dbo].[SecurityPolicy]
ADD FILTER PREDICATE [dbo].[func_PLAN_KEY_FILTER]([PLAN_KEY]) ON [dbo].[ABC]
WITH (STATE = ON, SCHEMABINDING = ON)
GO

stpnet Established Member
Established Member

Re: power bi report server very slow

So you're running two different queries (or query plans at least), one for regular users, one for the system account. 

 

I would attach a profiler trace to the SQL backend and then run my front end as the service account and again as an end user (test user) and see what the differenece is in response time. If its fast as the system user then that suggests its not a rendering problem/issue but a data query issue. So assuming its slow when a user identity is passed in you could look at optimising the SQL

 

Looking at your function there are a couple of options.

 

You could create an index on your SECURITY_GROUP table that is keyed on the LOGIN_NAME and includes the GROUP_NAME, ideally you could cluster the table on those two columns but that may have issues if you have dupliacte rows. You may also need an index on your PROGRAMMES table on the GROUP_NAME including the PLAN_KEY. This would leave your function unchanged.

 

The other is to expand the whole thing. So turn your JOIN into an indexed VIEW (SCHEM_BINDING will be required) and then index the view on LOGIN_NAME and PLAN_KEY. You can then query the view using an EXISTS test which will be quicker. Something like

 

 

RETURN SELECT 1 AS AccessRight
WHERE EXISTS(SELECT 1
             FROM Indexed_view 
             WHERE ( PLAN_KEY = @PLAN_KEY 
                AND  LOGIN_NAME = system_user )
                OR ( system_user = 'ServerName\ServiceAcctname'  ) 

 

itpragya Frequent Visitor
Frequent Visitor

Re: power bi report server very slow

@stpnet 

Thank you so much. Let me try this and let you know the results of my testing.

itpragya Frequent Visitor
Frequent Visitor

Re: power bi report server very slow

@stpnet I tried to do this, but the table has duplicate records. So, doesn't work.

 

Also, the function on my UAT Server works absolutely fine, while not on the Prod. Any ideas, please?

 

stpnet Established Member
Established Member

Re: power bi report server very slow

Do you have the same number of records in the table UAT and PROD?

 

 

If you are getting dupicate records this gives rise to two options. If there shoudln't be duplicate records then you have an issue upstream. If you are expecting duplcate records (this could be entirely fine of course) just create a regular NON CLUSTERED NON UNIQUE index on the columns instead. that way the duplicates won't matter.

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)