Perhaps we should first make a list of the different types of magic that could be implemented. There are many different styles of magic that exist, so we need to break them down into their general themes. This would allow us to discuss the realtive merits of each, and their feasabilities. The start of such a list is as folows (post anything I have missed):
-Chanelling the dead. If a particularly notable figure has died, such as your legendary swordsdwarf, then the wizard could channel his/her spirit in order to gain some temporary stat boost. Alternatively, if the dwarf channels the spirit of a dragon, then for a short time that dwarf might gain the ability to breathe fire. If the dwarf channels a titan, the dwarf could gain a big strength boost.
-Chanelling the living. Let's imagine that your champion hammerdwarf, defender of your fort, is injured during sparring. The wizard could form a soul link between the hammerdwarf and himself, allowing the wizard the gain great hammering skill. This would also open up new possibilities. Let's imagine that you catch a lot of goblins in your cage traps. By building a fancy restraint, you can place the goblin in the restraint and use his soul to augment your finest warriors
-Affecting minds. This allows the wizard to pit his/her willpower against other's. For example, a goblin squad is sitting outside your fort. The wizard might attempt to dominate the mind of one of the goblins, causing him to turn against the others. Alternatively, One of your soapmakers has had a tragic fall into the magma pit. His wife, a legendary weaponsmith, is now very unhappy. To avoid a fell mood, you get one of your wizards to cheer her up.
-Affecting bodies. This can be broken up into two subcategories - Hurting and Healing. Hurting is simple - try to break a goblin's legs using magic. Healing is also simple: try to fix a broken leg. Disease can be incorporated into this.
-Transporation. This can involve teleportation. A dwarf might teleport to your otherwise inaccessible artifact stash to stash the latest artifact. Alternatively, your champion macedwarf may have been knocked unconscious on the battlefield, and the wizard can teleport him to safety. This would also allow for accessing remote parts of the map. For example, your river is outside your fort. Your booze supplies have run dry, and the siegers are preventing you from getting any water. Your wizard then opens up a temporary gate to the river, allowing for access to water. This could be used offensively - the wizard could teleport a goblin 20 Z-levels straight up.
-Animation. This is bringing things to life. It can be either things that were alive or were never alive. For example, your dwarves have just lost their champion marksdwarf. The wizard starts a ritual and sacrifices a newborn child to bring the champion back to life. Alternatively, your dwarves need food. By sacrificing several rats (who give little meat), and by using some elephant bones, they can bring to life a full-grown elephant (who would give lots of meat). This can also create golems. If you have a masterwork iron statue, your wizard can embed the soul of a dozen lesser beings to bring it to life (it would act like fortress guard)
-Create Item. This is simple - it would create an item. It would require the input of items which are unrelated to the product (e.g. input three bauxite blocks to get a crystal glass block)
-Energy. The wizard attempts to manipulate energy. For example, the wizard could operate your magma pumps which are submerged in magma. Alternatively, your wizard could increase the temperature at a certain point by a certain amount, burning the enemy.
-Terraforming. Manipulating the earth. For example, the wizard could create a great chasm in the earth, or conjure a bridge that spans the chasm.
-Summoning. This differs from animation in that animation is giving life to something that already exists, whereas summoning is giving life from nothing. It could involve creating a horde of rhesus macaques to swarm and exhaust your enemies, or bringing back your dead dwarves even if their remains are lost (it would involve a much higher cost, however).
-Enchanting items. This is straightforward - the wizard would attempt to give any given item a magical property. For example, it might increase the critical boost of your giant corkscrew, or make your axe do fire damage.
-Enchanting areas. To use an example, a dwarf might enchant an area of land to make it especially fertile for planting. Alternatively, the dwarf might enchant your dining hall to make it light and avoid cave adaptation.
-Weather. Makes it rain, makes it snow, etc
-Time. Makes the growing seaon a little longer, for example.
-Self-preservation. I couldn't think of a better term for this. Basically, it gives your wizard more staying power after death. For example, the wizard, if killed, might inhabit the body of a child in the fortress. Alternatively, the king of the fort might require the wizard to cast spells so he will not die of old age.
There is also the source of magical energy to be discussed. Care must be taken with the source of magical energy so it is not too easily exploitable. There is a common problem with magic systems that wizards tend to be exponential in their power.
-Worship of others. If a dragon is worshipped by a town of goblins, he will be faster, stronger and tougher. Similarly, the goblins will be weakened by their worship whenever the dragon calls on their powers. Your wizard might set himself up as a god - if he does, then as the population grows and more people worship him, he will get more powerful
-Chanelling of power. Let us consider the dragon example further. The head priest of the goblin town converts several goblins into worshipping the dragon. As a result, the dragon allows the priest to channel some of his power, so the priest gains some of the abilities and attributes of the dragon
-Mechanical energy. This is where the power for the spell is proved by mechanical energy. A given spell might have an upkeep of 40 units of power. As long as those units of power are supplied, the spell continues.
Material wealth. If one wants to cast a simple spell, then mabye 100 dorfbucks are needed. The items used are consumed in the casting of the spell. If you want to cast a more powerful spell, it may need 2000 dorfbucks. If you want to cast a truly epic spell, then you may need to empty your entire artifact stockpile for it.
-Living souls. As described before, a soul generation plant could be set up - a goblin is captured and placed in such a plant. As long as the goblin is alive, they allow your wizard to capture spells. In order to prevent abuse, it should cause harm to the goblin, so it dies. This would not be restricted to enemies. As a punishment for breaking breaking dwarven law, your miner could be sentenced to a month in a soul harvester. Alternatively, you could manually assign dwarves with the "soul extraction" labour. This would put them into the soul harvester for a period of time, then replace them. It would give a very bad thought, though.
-Sacrificing souls. The dwarf must sacrifice souls to cast a spell. The complexity of the spell would dictate the number and quality of the souls. For example, to make it rain you might need to sacrifice a few vermin, but to raise your deceased champion might require a newborn child. Ideally, this should always cost you more souls than you could get out of it.
-Force of will. This is one that is perhaps the most well known. By exerting enough mental effort, the wizard can make things happen. For example, by standing still, concentrating and chanting magic words the wizard can conjure a fireball. Great care must be taken with this, as it essentially gives something for nothing. This could be conteracted if it has bad mental effects on the caster
-Group magic. Essentially, this is the force of will method but spread out over more people, so they experience lesser strain. This is different from the worship of others method in that is requires the participants to actively arrange themselves and canel other tasts during the casting of the spell.
-Natural forces. For example, a wizard might fuel himself from the thermal energy of a volcano. Once again, care must be taken to ensure that this is not abusable.
-Spiritual creatures. Thsi can be broken down into two sections: those that serve the wizard and those that the wizard serves. A wizard with a strong mind might dominate an imp. After dominating about 20 imps, the wizard might be able to cast a potent spell. Alternatively, the wizard might offer a greater demon something in return for power. For example, the wizard might sacrifice an eye but be able to call upon the power of the demon, to an extent. A weak wizard will only be able to dominate weak creatures, but a stronger wizard will be able to dominate stronger creatures. If the wizard dies, then the minions that they have summoned will either disappear or become hostile, which can be a problem if the wizard has a dozen powerful beasties.