For the import mode dataset, the annual Sales and Margin Plan data is retrieved from a table object within an Excel workbook. In the following fact table query (Sales and Margin Plan), the SalesPlanFilePath staging query is referenced within an Excel.Workbook() data access function:

As you saw in the Power BI Desktop settings section earlier in this chapter, the automatic data type detection option for unstructured sources should be disabled. It's, therefore, necessary to explicitly define the appropriate data type for each column of the Excel table via the Table.TransformColumnTypes() function. The Int64.Type, Currency.Type, and type number arguments used in this function correspond to the Whole Number, Fixed Decimal Number, and Decimal Number data types, respectively.

For a DirectQuery dataset, the Sales and Margin Plan data would be retrieved from a SQL view within the same database as the other fact and dimension tables as shown in the following screenshot:

The cost and time required to integrate the Sales and Margin Plan data into the data warehouse database are one of the reasons that the default import mode dataset was chosen for this project. The limitation of a single database within a single data source is currently one of the primary limiting factors for DirectQuery datasets. In the following screenshot, an error is thrown when trying to utilize two databases from the same database server for a DirectQuery dataset:

 

DirectQuery is a strategic priority for Microsoft and thus current limitations may be eliminated in the near future.