AI is a scorer. He plays point, but he's not really a point. It'll be good for Carmelo to play with an experienced guard like Billups, who while he can score, he definitely fits the point guard role more than AI. He's also a better defender than AI. AI is a good defender for his size, but not near Billups in that regard. McDyess also gives them some toughness in the paint.
For Detroit, it gives them an offensive threat that should ease up the defense on Rip Hamilton. It also gives them more minutes to give to Stuckey at the point. Not to mention AI's contract expires this year, so if it doesn't work out, they can go in another direction with a lot of money next year.