Import mode dataset queries

All M queries of an import mode dataset are executed only once per scheduled refresh. Therefore, if sufficient resources are available during these scheduled intervals, the M queries can contain more complex and resource-intensive operations without negatively impacting report query performance. In fact, well-designed data retrieval processes can benefit from report query performance as the source data is prepped to take greater advantage of the compression algorithms applied to import mode datasets. The systems impacted by these retrieval operations depend on the data source, whether the data sources is located on-premises or in a public cloud, such as MS Azure, and the operations of the query itself.

In this project example with an on-premises SQL Server database, the M queries can utilize the database server's resources during each refresh via the query folding process described later in this chapter. In the event that certain M expressions cannot be translated into an equivalent SQL statement for the given source, these expressions will be evaluated by the in-memory M engine of the On-premises data gateway, which is installed on-premises. If the source database was in the cloud and not within an Infrastructure-as-a-Service (IaaS) virtual machine, a gateway would not be required for the refresh, and resources in Power BI, such as Power BI Premium capacity hardware, would be used to execute any M expressions that can't be folded to a source.

For import mode datasets, M queries can be partially folded such that a source database is used to execute only part of the required logic. For example, an M query may contain both simple transformation steps, such as filtering out rows, as well as more complex logic that references a custom M function. In this scenario, a SQL statement may be generated for the initial steps of the query, and the results of this SQL query could be used by another system's resources, such as the On-premises data gateway to process the remaining logic. All steps (variables) within an M query following a step that cannot be folded are also not folded. Likewise, any M step following a Value.NativeQuery() function that passes a SQL statement to a source system will also not be folded. See the Query folding section later in this chapter for more details.