BISM - DIRECTQUERY OR VERTIPAQ

BISM TABULAR MODEL 有两种模式：DRECTQUERY AND VERTIPAQ。

那么，在实际应用中，应该选哪一种呢？

因此， 有必要了解一下优缺点。

DirectQuery has a number of benefits:

·New data can be retrieved in real time. Loadingdata into the tabular model is not required.

·Larger data sets can be used. If your sourcedata in the SQL Server data source cannot be compressed into a 1 terabyte orsmaller Analysis Services database, consider DirectQuery. Also, if your AnalysisServices database does not fit in half of the memory on the machine hosting theAnalysis Services instance, consider DirectQuery.

·The SQL Server security model can be used. TheSQL Server security model offers some features that Analysis Services does not,for example cell level security compliance with HIPPA data encryptionrequirements, and auditing at the SQL Server level when individuals executequeries at the data source. For more information, see “Additional resources.”

·Memory and CPU resources need not be allocatedto Analysis Services for caching, processing, and querying data. AnalysisServices is not CPU intensive in DirectQuery mode. Analysis Services uses somememory in DirectQuery mode when it computes intermediate results, but thememory requirements are much smaller than when it queries the cache of thexVelocity engine.

·Metadata discovery operations are faster,because data for a DirectQuery enabled model need not be loaded into memory tocomplete the discovery operation. This speeds operations like expandingdatabases in the Object Explorer in SQL Server Management Studio.

There are a few very important design considerations if youare planning to use DirectQuery:

·MDX queries are not supported for a model inDirectQuery mode. This means you cannot use Microsoft Excel or Microsoft PerformancePointas reporting clients on top of a DirectQuery-only model, because these clientsall issue MDX queries.

·As of this writing, the only production-ready reportingclient that has a graphical user interface that constructs DAX queries is PowerView. Power View should be considered a prerequisite for any DirectQueryimplementation in SQL Server 2012.

·Only SQL Server 2005 and later data sources aresupported. The preferred data sources are SQL Server 2012 and SQL Server 2008R2, because these two data sources have superior performance and quality inDirectQuery scenarios.

·Only one data connection can exist in the model.You cannot query two or more SQL Server instances from a DirectQuery enabled model.

·DirectQuery enabled models cannot be created inPowerPivot. Only models deployed to a standalone Analysis Services instance canuse DirectQuery.

另外， DIRECTQUERY不支持CACULATE COLUN, ROW SECURITY AND SOME DAX FUNCTION.

当然，

Analysis Services is optimized for use with the xVelocitycache. Queries typically perform better if they are answered from the cache. Yourpreference when data modeling should be to use the in-memory cache for yourtabular model. DirectQuery is a better choice when you are using Power View andyour scenario requires one or more of the benefits offered by DirectQuery.