Sql-server – Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube

data-warehouseperformanceperformance-tuningsql serversql-server-2016ssas

Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube?
I am reading that applying indexes, will transfer into SSAS. I thought SQL and SSAS are totally different environments.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/acda0b00-b40c-47cf-b9ca-334ccf2e4212/how-indexing-affect-performance-on-analyses-service-cube?forum=sqlanalysisservices

So the point is, create appropriate indexes on source tables to
improve the performance of the query which SSAS fires while processing
the cube or while retrieving data from the source. If you have access
to the source data, you can use this DMV to identify missing indexes
or you can use the Index Tuning Advisor for identifying and creating
missing indexes on the source.

Additionally, do columnstore indexes translate into SSAS Cubes?

Best Answer

The answer you quoted is worded a bit confusing but it says that it improves the performance of processing the cube, not querying it.

So the point is, create appropriate indexes on source tables to improve the performance of the query which SSAS fires while processing the cube or while retrieving data from the source. If you have access to the source data, you can use this DMV to identify missing indexes or you can use the Index Tuning Advisor for identifying and creating missing indexes on the source.

Querying the cube doesn't retrieve data from the source, it retrieves data from the SSAS storage engine.

What can happen however is that your cube has a drillthrough action that action may read from the actual source database and filter your fact table on the dimension keys which were filtering the measure in the cube, in which case it may benefit from indexes in your data warehouse.

Related Question