I've only got consumer 1/8" (ish) cassettes and I've had a little play with it.
It colours the sound. It can be pleasing, as in it smears transients, smooths and blends things and seems to make overly sterile voices sound a bit 'warmer'. However, I prefer the original digital counterpart, and better still slightly processed version of that with saturation and tape emulation plugins.
Running sound though an analogue medium for effect is a bit blunt. Sure, if you want distortion, then you can adjust gain for more/less, but tape colouration happens in a set amount. A plugin can be varied, and normally, one can dispense with the time based issues and keep things tighter.
Dolby NR seemed to just cause more issues than it solved. Yeah, lower noise floor, but music sounds stranger.
Perhaps, when we didn't have the luxury to AB so easily, or to re-digitise and sync. in the DAW things were more kinder to that medium.
Reel-to-reel tape, different story. I don't have one. I did listen to a bunch of samples where various instruments were recorded to them, both for saturation and as cleanly as possible, and compared to a digital recording. Piano stuck out rather easily.
Vinyl - who has a lathe to cut their own vinyl at home and compare? Probably more bits to fiddle with on vinyl playback. Starts to look less like a bit of equipment and more a work of art.