I assume you're talking about Windows 98 or ME. It lets you use JPEGs images as wallpaper. (Without it, you can only use BMP files.) That is the only thing that I can think of that you need Active Desktop for.
I thought the idea was that you can have a web page on your desktop. So you could have ActiveX and Java applets running on the desktop, instead of just static wallpaper. Calendars, push content, live TV, etc.
Perhaps it never caught on because (a) it introduced instability with stuff running all the time and (b) lots of times most of the desktop is covered anyway.