Interesting input here from various people. I create both stills and video ("film") productions professionally. I work with HD, 2.7K footage and beyond, and 22 and up MP RAW stills.
I'm coming from a loaded 2011 iMac with a 256GB SSD and a 1TB hard drive for things like my iTunes library.
On the 2011 iMac, I kept all my apps and their support files and caches on the SSD. Everything else was on the HDD.
However I can tell you now even that explicitly split (SSD/HDD) setup is too slow for serious video editing. I run through more video than could even fit on that 256 SSD in a session usually, considering caches and so forth. And iPhoto and Lightroom are kind of slow running off an HDD as they have so many smallish files to load.
What I do is run all my video and photo editing off of a Promise R6 12TB Thunderbolt RAID array with 6 drives. Running in RAID 5 it runs over 700MB/sec reading and writing at the same time, which is far faster than even the built in SSD on this latest iMAC. I run in RAID 6 for dual parity (8TB free with 3 drives that can fail before the raid dies), and even with that overhead I am seeing faster performance than the built in SSD.
So the irony here is an array of spinning discs via thunderbolt still beats (or matches) any one SSD in speed - with far more space.
One could potentially edit video off of a large SSD, and that same array loaded with SSDs would probably max out the TB link at 1,000/MB a sec. But you're also going to wear those SSDs out faster doing that (marginally) at a far higher cost.
[For comparisons, the less drives in the array the slower it goes. The Pegasus R4 with 4 drives maxes out around 500MB/sec - still the throughput of a fast-ish modern SSD. Spinning raid TB arrays with less drives are even slower]
Bottom line is no video professional would ever choose to edit off the Fusion drive setup (or a standlone HDD) vs a fast RAID, or split and edit off the internal HDD. They will, if they can afford it, use a RAID of some form (the faster the better) to speed up their work. Or they'll get a huge SSD and work off that. The internal HDD is fast enough to video completed video of course and to look through photos, but if you're actively editing either you need a huge SSD or a fast RAID - which on a price/performance basis is still far cheaper than that big SSD.
As for stills, editing RAW is a very data intensive task. These are huge files that need to be loaded quickly to work on them, and/or the thumbnail caches need a quick load. Running off a HDD is not fun for these programs.
So what the heck is the Fusion drive good for then? It's speeding up the OS. It's keeping the most often little cache files and programatic bits super fast and ready to load off of those chips of the SSD. It's keeping your pro apps ready to launch at a momen't notice. I have a ton of programs and all of them and their supporting libraries just fit into 120GB or so. They all fit on the SSD, and then when I launch them all their intensive data lives off the RAID - no discernable loss in speed.
The downfall of Fusion is you can't tell the OS explicitly where you want what. On my first boot of the new 2012 27"er after I transferred all my data, many (oddly not all) the non-apple apps I migrated like Photoshop loaded slowly off the HDD.
I left it on overnight then rebooted. The next pass, all the apps I ran that loaded slowly off the HDD before now loaded super fast off the SSD as they had with my older iMac. All was righted again.
Now on the surface, this isn't a bad thing. I have my super fast large raid for performing the actual work, and my normal apps run quick. I don't use all those 120 GB of apps every day, so it won't hurt me if some are moved off to the HDD over time (as I don't touch them) to free up space for the things I do work on. And if I load them in the future? A few relaunches and they'll move back to the SSD. It guarantees I can have one non-split drive for all my slow and fast data and the system is relatively good about keeping what should be where.
The bad thing is it's semi uncontrollable. You can't force something onto the SSD, though if you run it off the HDD a few times it seems to get moved there. And large files will all eventually go to the HDD. That one time you want to show a client your fancy new project and it happens After Effects for whatever reason moved back to the HDD because you hadn't touched the comp in a month will be annoying, having to wait when you didn't before. But for the most part, it seems it will "just work" (at least day one is showing me just that with the system!)
The other bad is as some have said, its like running a RAID 0 in your computer. Gotta make sure you back up as there are now two points of failure. Nothing to defend that one - it is what it is. I tend to move critical files to the RAID for some safety and of course double backup. (RAID 6 in a 6 drive array only fails when 3 physical drives fail at once - a rare event - and it can survive a 2 drive failure and run whereas Fusion will die when one drive goes) Everyone seems to think SSDs are crash-proof but they fail like spinning drives do, just less often. Backups are necessary in all cases.
Sure you can edit all this stuff and view it off of an HDD, but once you use a SSD or a fast spinning RAID, you never want to go back. It saves so much time and makes the system so much more responsive. And for a pro time is money as they say.
In conclusion - Fusion is a set of compromises to make it so you can "set it and forget it" - have one drive without having to think about what goes where and still have a lot of data storage. You can get your OS to boot quick, apps to (usually) load in a second, and still plow more "big" stuff onto the drive like your itunes library. No symlinks, no messing around - it all works. And it's far cheaper than 3 TB or 1 TB of flash storage.
But it isn't explicit - for that you need split storage. Apple doesnt seem to offer that now (a 256 or 512GB SSD + HDD option would be nice). Your only for explicit storage is to go 768GB SSD at a huge cost, or split your Fusion into a kind of small 128GB SSD and a HDD. But with fast external storage for doing real work, and a HDD fast enough to simply consume content, I think the ease of use of Fusion outweighs the downsides.
In the end time will tell... after I use it for a month I'll revisit my opinion.