ssasssas-tabularssas-2012

Issue in Partition in SSAS tabular model with DirectQuery Mode


I am trying to create a sample partition to a tabular model database in DirectQuery mode, and I got the following error after setting the filter and trying to import:

"Failed to save modifications to the server: Error returned: 'A table that has partitions using DirectQuery mode and a Full DataView can have only one partition in DirectQuery mode. In this mode, table 'FactInternetSales' has invalid partition settings. You might need to merge or delete partitions so that there is only one partition in DirectQuery mode with Full Data View."

Would anyone please help me understand the issue. Thank youenter image description here


Solution

  • A DirectQuery model is one which doesn’t cache the data in the model. Instead as the DirectQuery model is queried it in turn generates queries against the backend SQL data source at query time. This is compared to an Import model where the source data is imported ahead of time and compressed in memory for snappy query performance. Import models require periodic refreshes so data won’t get stale. DirectQuery models don’t require refresh since they always reflect what’s in the source system.

    The error you got is self explanatory. DirectQuery models should only have one partition per table and that partition’s query should cover 100% of the date range your model should cover for that particular table. So check FactInternetSales partitions and remove all but one partition and remove the WHERE clause from the partition query.