Tabular Models with DirectQuery Mode

16/07/2012

תגובה אחת

Tabular models are authored by using the model designer SQL Server Data Tools (SSDT). The model designer creates all models in memory, which means that when you are modeling, if your data is too large to fit into memory, you should import only a subset of data into the cache used by the workspace database.

When you are ready to switch to DirectQuery mode, you can change a property that enables DirectQuery mode. For more information, see Enable DirectQuery Design Mode (SSAS Tabular).

When you do this, the model designer automatically configures the workspace database to run in a hybrid mode that lets you continue to work with the cached data. The model designer will also notify you of any features in your model that are incompatible with DirectQuery mode. The following list summarizes the main requirements to keep in mind:

  • Data sources: DirectQuery models can only use data from a single SQL Server data source. When DirectQuery mode has been turned on for a model, you can use no other types of data in the model designer, including tables added by copy-paste operations. All other import options are disabled. Any tables included in a query must be part of the SQL Server data source. See Data Sources for more information.

  • Support for calculated columns: Calculated columns are not supported for DirectQuery models. However, you can create measures and KPIs, which operate over sets of data. See the section on validation for more information.

  • Limited use of DAX functions: Some DAX functions cannot be used in DirectQuery mode, so you must replace them with other functions, or create the values using derived columns in the data source. The model designer provides design-time validation for any errors that arise when you create formulas that are incompatible with DirectQuery mode. See the following sections for more information: Validation.

  • Formula compatibility: In certain known cases, the same formula can return different results in a cached or hybrid model compared to a DirectQuery model that uses only the relational data store. These differences are a consequence of the semantic differences between the xVelocity in-memory analytics (VertiPaq) engine and SQL Server. For more information about these differences, see this section: Formula Compatibility.

  • Security: You can use different methods to secure models depending on how they are deployed. Cached data for tabular models is secured by using the security model of the Analysis Services instance. DirectQuery models can be secured by using roles, but you can also use security defined in the relational data store. The model can be configured so that users who open a report based on a DirectQuery-only model can see only the data that is allowed to them under their permissions in SQL Server. See this section for more information: Security.

  • Client restrictions: When a model is in DirectQuery mode, it can only be queried by using DAX. You cannot use MDX to create queries. This means that you cannot use the Excel Pivot Client, because Excel uses MDX.

    However, you can create queries against a DirectQuery model in SQL Server Management Studio if you use a DAX table query as part of an XMLA Execute statement.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

תגובה אחת

  1. Auth28/07/2012 ב 14:00

    You have good goals, I like them. My challenge is kepneig focus because every month, it seems, I find something new worth learning about. The key to that is to make the goal relevant to something in life/work make it useful. So if you are able to extend PoSh to have it add value to what you're already doing, it's easier to meet that goal. Same with BI (if you find a project for which it's applicable). The Open Source may be more difficult, as you've noted. Regardless, you are farther along than most of the US You have the goals and you've written them down. Sharing them with a community helps you see them through better, I think!Erin

    הגב