When MDX query is issued on SSAS Server it goes through Formula Engine which request data from Storage Engine, and it either retrieves data either from cache or aggregations or from partition/fact data files (see this article for Understanding MDX Query Execution process in detail). When data is not available in cache and aggregations there is only one way for storage engine to provide data requested by formula engine and that is to scan MOLAP partition data files and partition segments. This is the most expensive way to provide data. To speed up this process SSAS provides source url Bitmap Indexes, these are used to eliminate rows from partition files so that very less number of partitions segments are scanned resulting in better performance. Let us see with the help of example how bitmap indexes work.
Let us consider Employee dimension and Sales fact
Now lets say we want to find Sum of sale_amount for Chris, we have to scan entire fact (sales) to get the result, when there are millions of rows rows this become very expensive. To avoid scanning entire dataset we can build http://temeculavalleysymphony.org/about/ Bitmap Index on http://alatusllc.com/2016/04/21/ Employee_id. Bitmap index has structure as shown below:
So now using bitmap index we know that we have to scan only rows having 1 in the bitmap index matrix which reduces rows to be scanned to exact rows which are there for employee_id 4.
Tips for choosing attributes to have bitmap indexes:
- Always chose attribute which has lower cardinality(very few distinct values).
- Multiple attributes can also be used having different bitmap indexes which are logically AND.
- During query time, the bitmap indexes for attributes with high cardinality(for example SSN) are not useful in speeding up retrieval, since the storage engine still must sift through a large number of distinct values to reach the desired values.
- Also during processing, unwanted bitmap indexes increase processing time, increase the cube size as well as they may have a negative impact on query response time. To avoid spending time building unnecessary bitmap indexes during processing set the AttributeHierarchyOptimizedState property to Not Optimized.
- Bitmap Indexes are stored on disk in *.map files in the data directory of the SSAS Cube server.
Bitmap Indexes are created during Process Index phase of partition processing. To create these we can run Process Index for that partition. These indexes get dropped during Process Data/Process Full for the corresponding Dimension and may or may not get dropped during Process Update for the corresponding Dimension and it depends on whether members were deleted or if member relationships changed (e.g., a Customer moved from Redmond to Seattle). But during Process Add for dimension these indexes remains intact. Which means after ProcessUpdate/ProcessFull on Dimension you need to Process Indexes on the Partitions.
Lastly to find out which attributes have bitmap indexes enabled, run below query.
-- Indexes on attributes SELECT * FROM SYSTEMRESTRICTSCHEMA($SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT , CUBE_NAME = 'cube' , DATABASE_NAME = 'md_cube' , MEASURE_GROUP_NAME = 'Fact Sales' , PARTITION_NAME = 'Fact Sales' )