上QQ阅读APP看书，第一时间看更新
How to do it...
- Create a new Power BI Desktop file (.pbix).
- Create a Query using DirectQuery Data Connectivity mode.
- Click on Get Data and choose SQL Server database.
- Select a view to be used by the model via the navigator.
Figure 23: Creating a DirectQuery Data Source
- Duplicate the initial query and revise the Item value of the Navigation step to reference an additional view supporting the model:
Figure 24: Referencing views from the Formula Bar
- Repeat step 3 until all the dimensions and facts are being accessed by their associated SQL view.
- Create a data source query to store the server and database connection information. This is similar to the previous recipe (duplicate a query and use the Source step) but to maintain the DirectQuery connection, do not remove or alter the navigation step; simply point the in to the Source step.
Figure 25: A DirectQuery Data Source Query
Note from this example that M queries do not have to follow a top-down order and do not have to return the final step or variable. In this case, the second variable expression is ignored and only the source database is referenced. The fact and dimension table queries used in the DirectQuery model can reference this query.
- Disable the Enable Load setting of the source query and update the individual queries to reference this query.
Figure 26: DirectQuery Model Queries
- When complete, the data source query will be grayed out (Figure 30) and all queries will reference this source.
- The Report Canvas view will confirm that the model is in DirectQuery mode via the status bar at the bottom right (Figure 27) and the Data view, which is visible for import models, will not be visible.
Figure 27: DirectQuery Status in Power BI Desktop