Improve performance Power BI desktop for report server

[lightknight]

Hi everybody,

I have a huge dataset coming from mobile in-app events tracking and my company deploys Power BI Report Server (ver. Jan 2023) to visualize the data. Data is fetched daily and stored in a schema located on a on-premise SQL Server database. Data volumn grows expotentially (1 million rows appended each day). I design a star schema including 1 fact table and 4 dimension tables and use direct query to connect all tables which are stored in the SQL Server. I have troubles with performance of report, the report takes about 1 to 2 minutes to present all visuals when end users interact with it. Can anybody suggest me a good model/type of storage mode in PBI and version of Power BI Desktop for report server to solve my problems? Thank you in advanced.

[lbendlin · Accepted solution]

Data volumn grows expotentially (1 million rows appended each day)

No need to embellish this - you can call it a linear growth if it is one.

Keep track of the queries issued by your report server against your SQL data source, and optimize your database for these queries.  Make sure indexes and statistics are updated after each substantial insert.

Consider loading your data into an SSAS cube or a SSAS Tabular instance, and accessing it via live connection. That way you can emulate the incremental refresh feature of the Power BI service.

[lightknight]

Thank you @lbendlin