I like the OP's suggestion pretty much as is. Sure the actual effects should be tweaked but at this stage, I'd much rather have a dwarf wizard running around doing crazy random stuff rather then not have any magic in the game because people get obsessed about stating and micro-managing everything.
I like wizards described as random FUN. They don't have to be useful. I mean hell, what benefit does the hammerer have? Or even a king? Saying that making a wizard not helpful means he shouldn't be in the game because people will magma pipe him is a terrible arguement.
There are two kinds of people who play this. Those for fun and those for FUN. The "fun" ones will magma pipe any and all nobles as quickly as possible because of the pain they cause. They demand stuff, they issue mandates and forbid random stuff, the hammerer kills/eats people. They lock them in a room and are done with it. Wizards would be just another noble to them, except perhaps they can teleport away from danger?
Wouldn't that be a b****, you have a wizard who loves to set fire to your booze stocks and you can't kill him because the b****** keeps teleporting out of danger.
Anyway, the wizard in the OP could be randomly useful as well. He just made it rain indoors? Great, you have an impromptu tower-cap farm.
He created a golem? Awesome watch as it rampages through the goblin invaders.
He turned your plants into hostile planet-men? More experience for my Axdwarf Squad.
He set fire to your base? F***...oh well.
edit: read up on the whole thread, lotta different ideas being thrown out.
Organic Spells: Dwarf encounters a problem. Dwarf casts a spell. Problem solved.
For example. Urist McWizard wants to cross a river but there's no bridge. Urist McDwarf casts a spell to create a bridge. Urist McWizard accidentally vaporizes the river. Urist McWizard crosses the now bone dry riverbed.
That would be an awesome ideal to strive for, but dwarves are very simple creatures. The game can't say "this is a problem." The game won't try and pathfind a dwarf across impassible terrain so at best the only things that could come up as problems are the error announcements:
-Interrupted by creature
-Object missing
-Too injured
-Dangerous terrain (why isn't fire considered dangerous terrain? /shrug).
So you're only looking at 4 "problems" and while it might still be interesting, it'd also be very limiting and old. For the short term, just having him randomly do stuff is fine. The OP had a lot of great ideas for stuff ranging from horribly FUN to harmless mischief to very useful.
In the longer term though, having wizards identify problems and try to fix it would be awesome. On a back end you'd have a list of problems, solutions, and outcomes:
Problem: Not enough booze.
Solutions: Create booze, improve crop production.
Outcomes: Create booze - clones existing booze, summons booze out of air, turns something else into booze.
Crop Production - increase harvest size, turn something into plump helmets, create seeds, turn something into seeds, create a farm plot.
The actual result (positive or negative) would be solely up to the player's opinion. Let's say wizard decides to solve your booze problem by turning stone into seeds. Player A, who's seeds were all accidentally used up in cooking and is desperately trying to keep things together until a merchant comes along would think this guy is a godsend and praise him on the forums. Player B, though, who's fortress is low on booze because almost everyone was slaughtered by a rampaging megabeast might find that a little less helpful. Finally, player C might think the wizard is a demon for transforming his valauble obsidian/bauxite/iron ore into seeds.
I don't think there should ever be a hard coded: "Good result/Bad Result" It should all be situational. Sure doubling your crop output might generally be seen as good, then again that draws a bunch of hauling jobs and if you're busy or low on dwarves, coudl mean a lot of rotting crops creating clouds of miasma.
Spells: There should not be pre-built spells. they should be generated and stored on a wizard by wizard basis. The end development ideal would mean that a wizard could name his spell and teach it to other wizards for solving problems. So building on the above system, Urist McWizard discovers the spell "Evaporate Water." He bumps into Urist McImaWizardToo. McWizard teaches the evaporate wate spell and so when McIma runs into a "crossing the river" problem, instead of coming up with a new way, she just evaporates the river.
This has an interesting effect of spell migration. So if one powerful wizard comes up with a dangerous way to cross a river (like shooting fireballs at it until it evaporates) then suddenly magic becomes dangerous as wizards use fireballs to solve everything. Compare that to a world where wizards instead tend to just summon stones to create a bridge, or better yet, summon an actual bridge. Then magic becomes peaceful and reliable, a tool to be used.
Plus you get the fun-factor of randomly generated names. So you'd have "Urist McWizard's Mighty River Draining Explosion"
As a player, you wouldn't be able to tell what a spell does until you see it in use (and even then you might not know exactly what it does, just what the aftermath is." but you can get a guess at it by the name. So descriptors like "fiery" would clue you in to how dangerous a spell might be, even if it's "Urist McWizard's Spectacular Fiery Table Summoner". Sure it summons tables, but it also sets off a fireball in the process.
Biomes: Definitely should have an impact. Definitely. Be wary of wizards you encounter in scary places. Perhaps also magic level could be a trait on a map, like vulcanism or savagry. So you could mod the parameters and create a high/low/none magic setting, and wtihin the map itself there would be deviation. So you could find a low- or no-magic zone in an otherwise high-magic setting. Perhaps this would block magical effects completely, so no walking undead or megabeasts, no goblin wizards throwing fireballs, and no magical artifacts

On the flipside, a low- magic setting woudl probably be mostly low- or no-magic zones, but there could be a few places where faries and pixies are everywhere, and seemingly everything is magical.
Randomness: Magic should not be controllable. Period. No setting up magic guilds and training wizards and researching spells based on user input. User shouldn't be able to tell a wizard to do something any mroe then any other noble. They should wander around and be helpful/harmful on their own time. Perhaps at best you could tell your wizard to charge into combat, but you better hope he has some kind of combat spell or else he might just start whacking gobbos with his staff (and probably die in the process). ON the flipside, you'd better be careful about sending in a wizard who likes to summon volcanoes to solve bad guy problems.
Now, the downside is what happens when you have a guy who's answer to everything is to cause a huge explosion. Maybe you can tell a wizard not to use certain spells. You can open up his spellbook and disable spells. If you keep him happy, he'll listen to you and not whisper the words that kills every dwarf in a mile radius. But if you piss him off, give him an inferior bedroom or study area, or not listen to him when he demands 15 glass windows then he might just cast a spell out of spite.
Artifacts: I like the idea that wizards are capable of creating more artifacts, but also how about not having a set preference on artifact type. So normally a dwarf goes by their highest skill or does a craft artificat by default. Your wizard, despite being an expert carpenter, gets hit by a mood and creates an artifact weapon instead. Add to that the much higher chance of an artifact created by a wizard being magical and I think there'd be quite a few people willing to let Wizard McBlowsStuffUp live in case he creates the DF version of a chainsaw (i.e. adamantine artifact axe).
Frequency: 1 wizard per fort. They should be treated like a noble that gets triggered when certain factors occur. These factors should be at least partially controllable by the player, so maybe something like:
50+ dwarves + 5+ artifacts + low-level of magic or better in region + have an unnassigned royal quality level study.
So if you really don't want wizards, just don't build top quality study and leave it unassigned. This is just a thought on the PROCESS, not the actual system. Don't really care if you think there should be more/less dwarves or more/less artifacts etc.