If you're used to Disney fireworks and shows I have a feeling this between an "that's kind of cool" and "alright" experience.
Universal doesn't have a big massive fireworks display due to what
@bchbetha said, although a few times while staying on property I've heard a few pyrotechnics at one particular point (at night) so perhaps they have a little bit towards the end (as I haven't heard it go off for a long duration of time each time I stayed and heard it).
I've watched the show online and (while I know it isn't the same as being there) I wasn't as impressed as I have been with other Disney shows (even watching some only online) which is why I've never tried to go out of my way to see it in person. While it's cool to look at and has great effects, however compared to Disney (since most of their shows has
so many elements) it's not at the same level, or greater in my opinion.
A lot of the entertainment (at least for me) was based on whether I liked the franchise, or film they were focusing on. The lights, projections, and water effects are great, but those are happening for almost the entire duration of the show, so the only difference was moments in which they would focus only on the projections/water effects, or showing a new film. So, for me, when they focused on something in particular I wasn't a huge fan of, or watched for 3-5 minutes I would keep thinking "okay when is the next thing". Also, I might be biased since I do not watch, or like a ton of Dreamworks stuff, but I thought it was strange/didn't enjoy how much they focused on films that don't even have attractions and more so than the actual Dreamworks film that does have an attraction.
Also, have to consider you have to be standing for the duration of the show to really get a good view it seems so depending on if you're tired, or not will probably effect whether you'd enjoy yourself too.