Neither sentence is correct. Both sentences are in the present perfect tense. This verb is formed with the present tense of the verb 'to have,' and the perfect tense of the verb 'to want.' You should use the simple past tense: "When he was a puppy he wanted to be the most famous dog in the world."

I agree with Andrew's reply.

'Since he was a puppy, he has wanted to be the most famous dog in the world' does make sense, but it has a different meaning to 'When he was a puppy he wanted to be the most famous dog in the world'. Instead it means that he wants to be the most famous dog in the world now, and this has been the case throughout his life, starting from when he was a puppy. The second sentence 'When he was a puppy he wanted to be the most famous dog in the world' implies that he no longer wants to be the most famous dog in the world now that he is an adult, he only wanted this when he was a puppy.