Hi OneWithQuestion,
The process importing data from SQL Server to PowerBI can be divided into three parts, firstly, SQL Server executes the query to get the result, then transfer the dataset to PowerBI Desktop, after that PowerBI desktop will generate the data model.
>> The slow down appears to be that the data being moved is a large amount of data and thus takes time to pull off disk.
Actually, only the first part involves moving data from disk to memory, we need first checking how long does SQL Server take to execute the SQL query, we can use the SQL Server Profiler to monitor how long it takes for a query. We can also run the query in SSMS and get the time: http://www.sqlserver.info/management-studio/show-query-execution-time/
After SQL Server getting the result, the dataset will be stored in memory, then transfer to PowerBI, this part will not take much time. When PowerBI get the dataset, it will generates the data model and compress the data. This process will take much time, please refer to the following blog: https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/ to get better performance.
>>Does setting up a DW in SQL using in memory tables help with this, or any way to compress the data being transfered, pre-optimize the data before it is consumed by SSAS Tabular or Power BI, etc?
Setting up in-memory table for SQL Server only improve the performance of query in SQL Server, if SQL Server takes long time to get the result, we can consider using it, if it doesn’t take much time, there is no need. To compress data being transferred, we can use the suitable data type for each column, we can create fact table and dimension table if all information are stored in a table.
Best Regards,
Teige
