As far as I understand it, Realflow is pretty industry standard. However, Houdini has some pretty awesome fluid tools in it as well. The learning curve may be steeper with Houdini, but it can do much much more than just fluids. There's also a learning edition available for free so you can test drive it.
If you already have DPIT Effex then I would start with that. It is the most complete liquid simulator for Cinema (not counting Realflow, which is more of an import plugin). The only other option is X-Particles which is easy to use but pretty basic.
Another option would be to check out Blender, which is free. It has a liquid simulator but I've never tried it.