So when you attach a card like Song of Kings do you get and extra resource during the refresh phase??

I am thinking no, but just wanted to double check.

You are correct. You do not get additional resources.

I"m assuming it's the same if you use Celebrian's Stone. So is the purpose of cards like "song of kings" simply to have the card be active in multiple spheres?

RIght - you can use that hero's resources for more than one sphere of cards.

You also can count the hero as belonging to the new sphere, which is useful for cards like Burning Brand.