In this topic, we'll describe how to enable DirectQuery mode for a tabular model project in SQL Server Data Tools.
When you enable DirectQuery mode for a tabular model you're designing in SSDT:
- Features that are incompatible with DirectQuery mode are disabled.
- The existing model is validated. Warnings are displayed if features are incompatible with DirectQuery mode.
- If data was previously imported prior to enabling DirectQuery mode, the working model's cache is emptied.
In SSDT, in the Properties pane for the Model.bim file, change the property, DirectQuery Mode, to On.
If your model already has a connection to a data source and existing data, you'll be prompted to enter database credentials used to connect to the relational database. Any data already existing within the model will be removed from the in-memory cache.
If your model is partially or fully complete prior ti enabling DirectQuery mode, you might get errors about incompatible features. In Visual Studio, open the Error List and resolve any problems that would prevent the model from being switched to DirectQuery mode.
You can now import data using the Table Import Wizard to get metadata for the model. You won't get rows of data, but you will get tables, columns, and relationships to use as the basis for your model.
You can create a sample partition for each table and add sample data so that you can verify model behavior as you build it. Any sample data that you add is used in Analyze for Excel or in other client tools that can connect to the workspace database. See Add sample data to a DirectQuery model in design mode for details.
Even in DirectQuery mode on an empty model, you can always view a small built-in rowset for each table. In SQL Server Data Tools, click Table > Table Properties to view the 50-row dataset.