Power BI on Premise not cloud

[Sudhakar510]

Hi We have a Power BI Report Server but we using direct query rather than Import Method.

Eventually we would like to convert to Import Mode but business says it related to cost because of additional RAM usage.

if we would like to optimise the code by writing CTE query method but won't support isn't it? What other query methods we can use to optimise?

Please can you explain strong reason why Direct is not better than Import? Direct Disadvantages and Import Advantages..

and also we are not intrested in Live data because all we need is Current Day-1 Data and we are not expecting latest data always because it holds same data in database on whole day.

[Sudhakar510 · Accepted solution]

Thank you @josef78 ,

And also please let me know if I  am wrong. In Import method, we can write different type of SQL Methods like Select Statement by joining 10 Different Tables or CTE still it can be faster.. so this techniques we can use in Import Method to optimise it

but in Direct Query for some reason it is not accepting CTE method. Is it something that it will accept only Select Statement in Direct query? or any methods does it accept?

Because currently our SQL guys are saying to use direct query and not to use Import Method.

If it is slow in direct query, they are asking to improve the query instead of extending RAM and we have only 64GB RAM at the moment.. If we want to join 5 different tables still it is slow. Its taking good 5 mins to run the report some times.

[josef78]

Main difference is in analytical query performance. Depends on size of your data and design of reports (if are more like as interactive pivot tables/charts or more as static row tables). Typically, PBI reports are interactive with drilling and slicing, and which needs fast(online) query response for analytical queries on large data. This is solved by in-memory columnar database (vertipaq) which is used in import mode.

Direct Query advantage is that it is real time. Main disadvantage is that there is slow query response for online analytical queries on large data (which is not usable for interactive reports), but it can be partially solved by column store indexes (also vertipaq based) in SQL Server database.

Import Mode, main advantage is excellent analytical query performance and also single place for all business calculation. Disadvantage is that is not real-time and need processing, and also RAM consumption (but thanks great compression is much smaller then data size in source database).

Live connected (with SSAS), advantages are same as in import mode (because there is same column store inmemory engine (vertipaq)), but model is on dedicated system, and allows bigger models, incremental processing, and so on.

[Sudhakar510 · Accepted solution]

Thank you @josef78 ,

And also please let me know if I  am wrong. In Import method, we can write different type of SQL Methods like Select Statement by joining 10 Different Tables or CTE still it can be faster.. so this techniques we can use in Import Method to optimise it

but in Direct Query for some reason it is not accepting CTE method. Is it something that it will accept only Select Statement in Direct query? or any methods does it accept?

Because currently our SQL guys are saying to use direct query and not to use Import Method.

If it is slow in direct query, they are asking to improve the query instead of extending RAM and we have only 64GB RAM at the moment.. If we want to join 5 different tables still it is slow. Its taking good 5 mins to run the report some times.

[Shahfaisal · Accepted solution]

---

@Sudhakar510 wrote:

And also please let me know if I  am wrong. In Import method, we can write different type of SQL Methods like Select Statement by joining 10 Different Tables or CTE still it can be faster.. so this techniques we can use in Import Method to optimise it

but in Direct Query for some reason it is not accepting CTE method. Is it something that it will accept only Select Statement in Direct query? or any methods does it accept?

Because currently our SQL guys are saying to use direct query and not to use Import Method.

If it is slow in direct query, they are asking to improve the query instead of extending RAM and we have only 64GB RAM at the moment.. If we want to join 5 different tables still it is slow. Its taking good 5 mins to run the report some times.

---

@Sudhakar510 - There are some limitations when it comes to using CTE in Power BI. You can turn your sql code into a view and use that view in Power BI and it should work. Please note the following important concepts whil you make the decision:

1. CTEs don't necessarily improve query performance. These queries are executed on the fly when users run the reports. 

2. You mentioned in your prior post that you don't need live/realtime data and you are fine with a 24-hour lag. If this is the case, I suggest you develop an ETL process to turn your queries into physical tables (dimensional model ideally).  This will give you a much better query performance experience. If for some reason, you are not able to materialized tables, try creating some indexes on the tables.

3. A slow query isn't necessarily an indication of low memory. It could be because the tables are large and there are no indexes on them or it could be due to bad design or could be due to other reasons. 

4. You also mentioned that your business is opposed to import method because it would require more memory - I am not sure if you do. Since your server has 64GB memory, can you limit the memory for SQL Server usage and the rest can be used for Power BI? If this server is dedicated to Power BI, you can also try some dynamic options with import method- for example, when the data is refreshing in the night, you allocate a good chunk of memory to SQL server, and then during business hours, limit the memory usage for SQL Server so that Power BI Report Server can use most of it. See https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configu...