SSAS Tabular Analysis Service Indexing

Question

I'm currently working on top of a Tabular SSAS database with transactional data. The data model contains a lot of tables (with hundreds of millions of rows) and relationships.
This database is connected to many PowerBI dashboards, and sadly performance of visualizations has dropped critically.
Currently, the solution has partitions and paging. Moreover, its storage space is 60% full.
Because many of the visualizations in PowerBI have many filters based on columns, I was thinking about creating some indexes based on these columns.
Is this way a possible solution? Is there any other way to optimize the performance?

Answer 1

Creating indexes in the source database can help improve performance, but SSAS Tabular model doesn't use indexes directly. Instead, it uses a columnstore-based in-memory storage (VertiPaq) for its data. However, improving the query performance on the source database can indirectly help the processing time of the Tabular model.

To optimize the performance of your SSAS Tabular model and Power BI dashboards, consider the following strategies:

1. Optimize the data model:
   • Remove unnecessary columns and tables to reduce the model size.
   • Use the "Hide from client tools" option for columns that are not needed in the reports but are required for relationships or calculations.
2. Optimize relationships and cardinality:
   • Ensure relationships between tables are properly set up.
   • Reduce high cardinality columns if possible, by removing or grouping data.
3. Optimize measures and calculated columns:
   • Review your DAX calculations for inefficiencies and optimize them.
   • Use measures instead of calculated columns wherever possible, as measures are calculated on-the-fly and do not consume additional storage.
4. Use aggregations:
   • Create aggregations for frequently used measures and dimensions to improve query performance.
5. Incremental refresh in Power BI:
   • If you are using Power BI Premium or Power BI Premium per User, leverage incremental refresh to load only new or changed data instead of refreshing the entire dataset.
6. Optimize Power BI visuals and filters:
   • Limit the number of visuals on a single report page.
   • Use slicers and filters efficiently.
   • Avoid using complex custom visuals that may negatively impact performance.
7. Monitor and analyze performance:
   • Use tools like SQL Server Profiler, DAX Studio, or Performance Analyzer in Power BI to identify and analyze performance bottlenecks.
8. Optimize the hardware and configuration of your SSAS instance:
   • Ensure your SSAS server has enough memory and processing power.
   • Adjust the SSAS instance settings, such as memory and processing settings, to better suit your workload.

Remember that optimizing performance is an iterative process, and it's essential to monitor and analyze the effects of any changes you make.