While there are rain ceremonies elsewhere, such traditions are most commonly associated with the Pueblo of the American Southwest and these provide the inspiration for the pop-culture version of the Rain Dance.

Many, if not all of the pueblos, had a resident Rain Society or a society that maintained the rain ceremonies. These ceremonies took a variety of forms, including races and dances. Such ceremonies are (generally speaking) performed in honor and gratitude for the kachina (to use the English word, derived from Hopi, for a broad class of spiritual entities). With regards to rain, the important kachina are the Cloud people. The Cloud people are the dead who have taken the form of a cloud (depending on the pueblo, those who become Cloud people range from most to only a select few). Through the Rain Dance and other ceremonies, the Cloud people are drawn to the pueblos and, in return for the gift of these ceremonies, bestow their own gift of rain. Traditionally, these ceremonies take place in late August / early September, about mid-way through the rainy season which runs from July through October, so rain following shortly after such a ceremony would not be uncommon. But Cloud people aren't vending machines into which ceremonies are inserted and rain is immediately received. The purpose of the Rain Dance isn't for immediate results, but to show gratitude for rain already received and rain that will, Cloud people willing, continue to be received in the future.

If you want something to read on the topic, I'd recommend Pueblo Indian Religion. It's a hefty two-volumes, but Volume 1, which is the one I linked to, has the information about Rain Societies and Cloud people.