The cooler seems to be custom made by Sapphire and is massive. Sapphire's card needs two slots to accommodate the bigger cooling solution - but only one fan.
So you thought the 8800 GTX (left) is big? Sapphire's X1950 Pro Dual (middle) is 11" long by 5" wide (27.94 cm x 12.70 cm). This means that it should fit most cases, except for some slim and HTPC variants. The X1950 GT on the right is tiny compared to it.
The back of the card is fairly standard, all hot components are on the other side. The blue heatsink you can see on the upper left of the card is present on both sides of the card to keep the voltage regulation circuitry cool.
Like you would expect from a high-end video card, two Dual-Link DVI outputs are on the card. So you can attach displays running higher than 1600x1200. If you have an older analog display, you can of course use the included DVI adapters on both ports. If you look to the left of the slot metal you can see empty solder pads for two more connectors, these are from the second GPU but are unpopulated.
Even though it is not possible today, it could work in the future to run two of these beasts in a Quad CrossFire configuration. At CeBIT, Sapphire demoed such a setup, but it is unknown when and if ATI will release a driver with support for Quad CrossFire. They are working on it though.