There are two major water parks in Dubai: Aquaventure and Wild Wadi. We visited both. Wild Wadi is located near the Burj Al Arab and Jumeirah Beach hotels.
Honestly, we liked it less than Aquaventure - the slides here are mainly for children and lead up, not down. In addition, the slides constantly stop and accumulate a lot of rubber rings with people.
There is a large wave pool, but it's prohibited to swim in the deep without a life jacket.
There is also a "surfing pool" - you are given a board and put in the water with strong waves. Of course most people cannot hold on their feet and it looks very funny. We didn't try it due to large waiting time.
Timetable and prices at the official website. Entrance fee for adults is 220 dirham ($60).
Getting there: From the Mall of the Emirates metro station go on foot (30-40 minutes) or take a taxi. If you decide to walk, then walk in the direction of the Burj Al Arab hotel. If it's hot, don't go on foot, take a taxi.