Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 das...
POWER BI Reports in web application
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.

 

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous 

       

      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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymous 

         

        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