From what I understand, the original plan for the PS3 was to have 2 cells, one of which would have taken the place of the GPU as a fully programmable renderer. They worked on writing the default SPU renderer implementation for a while, but failed to come up with something useful enough, so they gave up on the idea and use the nVidia GPU instead.
Intel still continued research along those lines, though, and the project was called
Larrabee, but they never really got it up to snuff and was eventually abandoned.
The PS3's SPUs were very good for doing vector geometry processing, and eventually a lot of games basically used them to mostly replace the GPU's vertex shaders to let the GPU spend more resources on pixel shaders. However, I don't think this technique was widespread at first. That was more of a thing that was discovered to be effective over the lifetime of the console, and a technique that Sony disseminated to developers later on.
The SPUs were not good at pixel shading (the lack of an effective texture cache is, I believe, the big reason the Cell GPU idea never took off), but they did happen to be very effective for full screen post-processing e.g. bloom/blur effects), or compositing "deferred rendering" passes, etc. stuff that could access the whole screen buffer in linear order. That kind of technique was another thing that Sony started to recommend later in the life of the PS3.
I don't think they really foresaw either of these uses, though. The initial assumption seemed to be that the GPU would be most efficient for rendering tasks. Actually a lot of time at earlier conferences I attended was focused on ways to structure your gameplay code in SPU parallelizable ways. Unfortunately this was also not very helpful advice if you were already deep into your project, most of this advice wasn't effective without up-front planning.
As far as similarity to other vector processing units that preceded it, I think that had been gradually developing over about 10 years already, I don't really know the N64 architecture but around the same time you had MMX being introduced to Intel CPUs, marketed explicitly for "multimedia". It was clear for a long time that there were a lot of media related things that could be effectively parallelized with vectors.
I think Sony was sold on the idea of the Cell because it was demonstrably a very powerful processor, and I don't think they really knew that it was going to be quite as difficult as it turned out to work with for games. I think they assumed developers would get used to it and eventually that power would shine through, but that ended up being very slow going, especially since so much became cross-platform (esp. both being PowerPC architectures and having somewhat similar GPUs it was more viable than ever to develop for both of these platforms at once), I think that stifled research for most and left it mostly in the hands of PS3 exclusive developers. That learning period was probably a whole lot longer than Sony was planning for.
Sony launched a
folding@home client for PS3 at one point, and for a while it was a huge contributor to the project. It was a pretty good PR demonstration of the Cell's power at the time. (Eventually PC GPUs took back the crown on that one.)