Forum Discussion
Helper IDesktop efficiancy in modelling
In my model is a table loaded from a SQL database, in my model the table is transformed and filtered to 10 coloumns and ~180000 rows. The source table is ~7 million rows and 25 columns and is at a remote site over a slow(ish) link. Every time I make a small change in my model (for example I just changed a calculated column from whole numbers to text) desktop has to go away and pull the entire 7mil rows from the database.
I've just realised that the change I made was incorrect and want to change it back, this will again pull in the 7mil rows from the database, all for a simple type change on a single column.
Is there any way that I can improve the efficiency or performance of this process? I'm still very much learning and so making lots of mistakes changes like this on an ongoing basis and it's getting abit tedious.
MrPatrick I am assuming you are pulling the data via direct query? do you just select the whole table or do you tye in your own sql statement
You can adust your sql statement which you can modify and select only pulling a smaller subset of rows until you have done everything you need to. (ie select top 100 * from table)
also if you can be sure to put a no lock on if the data is static, if you the data is updating, that is probably not advised
5 Replies
- vanessafvg
Community Champion
MrPatrick I am assuming you are pulling the data via direct query? do you just select the whole table or do you tye in your own sql statement
You can adust your sql statement which you can modify and select only pulling a smaller subset of rows until you have done everything you need to. (ie select top 100 * from table)
also if you can be sure to put a no lock on if the data is static, if you the data is updating, that is probably not advised
MrPatrickThank you for this, you were correct, I'm using directQuery and applying a SQL statement at the beginning of hte modelling process is vastly more efficient. I'd sat an thought that it'd have been useful to be able to apply a filter at load but not thought to actually look up if it was possible, turns out I should have done.
I'll go through the other answers before selecting a solution but you have helped me greatly already.
- amitchandak
Super User
For performance part , refer if these can help
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://maqsoftware.com/expertise/powerbi/power-bi-best-practices
v-lili6-msftCommunity Support
hi MrPatrick
You could refer this blog to optimize your report as below:
https://www.thebiccountant.com/speedperformance-aspects/
Regards,
Lin
If you have access to the SQL database, try to create a "View" for report development purposes.
For example, if your SQL table name is "FactTable" with data of last 5-6 years, then creating a "View" of the table with an SQL code like this will give you a smaller subset of the actual FactTable that you can use for your report development in Power BI.
CREATE VIEW FactTableForTesting AS SELECT * FROM FactTable WHERE DocumentDate >= '01/01/2020'Instead of fetching the data from the table "FactTable", you can fetch data from the view "FactTableForTesting".
Once you do all the changes and your report is ready to publish, you can go to the advance query editor and just change the data source name from "FactTableForTesting" to "FactTable". This time it will fetch all your data.
Alternatively, you may filter the data in power query(M) also during the report development phase, and rely on the Query Folding.
