Forum Discussion

MrPatrick's avatar
MrPatrick
Icon for Helper I rankHelper I
6 years ago
Solved

Desktop 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 re...
Tips and Tricks
  • vanessafvg's avatar
    vanessafvg
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.