There are a number of different uses for dynamic connections.
First and foremost is to solve the issue of maintaining a local and remote checkout file when doing local development. Dynamic connections allows you to set up one connection file with both local and remote database details, and it will use the correct ones based on URL.
It also offers the ability to set the connection character set which will help solve issues with storing and retrieving data that contains accented characters.
Another user would be a multi lingual site, you could crate two separate databases, one for English, another for Spanish content. have the user select their language and store that in a session variable. using the session variable, you could determine which database to connect to.