Here's a demo of some effective indexing strategies to ensure that you have a highly optimized DirectQuery performance and efficiency using SQL database indexing in Power BI:
Consider Using Clustered Indexes for Fact Tables:
The fact table usually has a primary key, so attribute it to a clustered index to reduce space and improve data organization.
Clustered indexes can also be used for date columns if the filter often occurs on those columns.
Create Non-Clustered Indexes for Filtering & Joins:
Foreign keys connecting to dimension tables need non-clustered indexes.
Index those columns commonly filtered (like customer identification number and region) to help reduce scan time.
Don't index columns with high cardinality (i.e., unique possible values like customer email) unless it is necessary.
Composite Indexes for Multi-Column Filters:
When filtering data through different columns, for example, through the Order Date and CustomerID, in most cases, composite indexes that would optimize the WHERE clause are created.
Ensure that the order of columns in a composite index matches the most common query patterns in Power BI.
Using Covering Indexes to Optimize Queries:
When a query, most of the time, retrieves certain columns, create a covering index that is all-inclusive and would eliminate the need to look up.
Example: A query filtering on OrderDate, getting TotalSales would take advantage of an index on (OrderDate) INCLUDE (TotalSales).
Monitor and Tune Indexes:
Use SQL Server's Execution Plan to analyze slow queries and identify which missing indexes are needed.
Don't overdo indexing; it slows down insertion and updates.
