Background of the issue:
After the successful restoration of production data into the sandbox environment (tier 2) tried to reset DataMart mart through PowerShell, but it failed with below error.
” Table ‘Datamart.DimensionValue’ uses a clustered columnstore index. Columnstore indexes are not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database
Root cause analysis:
After going through a couple of blogs understand that one of the tables in Financial DB uses a clustered index. In your D365 FO environments, if Azure SQL database (FinanicalReportingdb) is below <S3 tier it won’t support clustered indexes. In my case, this caused to DataMart failing.
How to check Azure SQL database tier/performance level?
Executing below query gives you details of your Azure SQL database.
FROM sys.databases d
JOIN sys.database_service_objectives slo
ON d.database_id = slo.database_id;
How we resolve the data mart reset error?
After executing above SQL query, FinancialReportingdb is in standard(S0) tier. So, requested Microsoft support to increase database tier to premium. After a successful upgrade, the DataMart reset issue got resolved.