The most common use for heroism is during bosses in raids. When it is used varies from boss to boss and guild to guild. When soloing, it can be useful for crunch situations or tougher mobs, such as targets for group quests.
As part of Blizzard's new movement toward "bring a player, not a class" raid compositions, Cataclysm endowed mages with a similar effect, Time Warp.
When Heroism is used in raids, it is usually a good idea to dump all other CDs you have at the same time, because in many cases it will result in higher overall damage than using the cooldowns separately.