Bitmap Indexes (*.map files) play an important role within Analysis Services. They provide a mechanism for the storage engine sub cube event to efficiently locate the relevant segments within the fact file without having to scan the whole thing. This is how you can have a 20 GB *.fact file in the partition but still get storage engine events of only 50ms or so.
However, it can be a best practise to turn off Bitmap Indexes in a few scenarios:
a) Where the attribute is not used much.
b) Where the attribute is really just a related property of an existing attribute
c) where the cardinality of the attribute is pretty much the same as the key
Further reading on this is below
One word of warning is when you turn off/on the bitmap indexes by adjusting the AttributeHierarchyOptimized property this may not actually have the desired effect.
The key thing we need to be aware of is that this AttributeHierarchyOptimized Attribute can be set in TWO places and just changing it in the shared dimension may not actually change it for your cube:
- Once in the shared dimension
- Once in the cube dimension
1) Changing AttributeHierarchyOptimized on the shared dimension
2) Changing AttributeHierarchyOptimized on the a cube dimension
This caused havoc on a recent tuning project where we turned off too many bitmap indexes in the normal dimension editor and then didn’t realise that when we turned them back on in the same place they were now stuck in the cube in the “off” state.
Now this behaviour is pretty much by design. The intention is that turning off bitmap indexes in the shared dimension will turn them off for all cubes, but if you leave them on you are free to turn them off on specific cubes. however it can lead to some confusion!
