In this case, it’s helpful to remember that the passé composé il a fini is equivalent not only to the simple past (he finished) but also the present perfect (he has finished). These two meanings also exist in the past subjunctive.

Il faut attendre qu’il ait fini de dormir. = We have to wait until he has finished sleeping.

Despite the subjunctive, the grammar is basically the same in French and English.

Il faut attendre qu’il finisse de dormir. = We have to wait until he finishes sleeping.

Basically the same thing, no?

This is delicate matter.

The moment of the end of the sleeping period in “Il faut attendre qu’il ait fini de dormir” lies indeed in the future.

One way to make sense of the past being used here is to consider that we’ll have to wait for the end of sleep to end before anything can be done, i.e. end of sleep will have to turn into beginning of awakeness before something can actually happen, leaving the end of sleep period in the past of that future moment.

Using the present of the subjunctive instead (“Il faut attendre qu’il finisse de dormir”) may relay the information that he won’t have time to really wake up before he has to deal with whatever will require his attention.

On the other hand, using the verb se réveiller in the present would be similar to using finir de dormir in the past, that is, it would convey less stress on the currently-sleeping person, since by the time he’s awake, the end of his sleep is behind him:

Il faut attendre qu’il se réveille.

Then again, it is perfectly possible to put even the event described by this verb in the past, to extend the waiting period until the transition from sleepiness to awakeness is over :

Il faut attendre qu’il se soit réveillé.