That piano has a busted key, so instead of playing a note it plays a cancel noise (this is intended; it was originally part of the Safe combination key puzzle). It makes playing the song by ear a bit trickier, but if you manage it then it unlocks the new thing. You just have to use the busted key where it would be in the song.
Maybe I'm losing it, but played it five times on Tifa's one and worked properly (got the "I heard this melody before) but seems on the mansion one nothing happens, I'm 99% sure I'm playing it correctly, should be cancel,switch,menu,pgup+menu,pgup+switch,cancel,switch,menu,pgup+cancel,ok,cancel,switch,cancel.
I may just be trying it far too early in the game? Just beat the safe boss and it's first visit to the mansion.
Apologies for the bother on this, last thing I want is to take more of your time, specially since I think you invest far too much of it helping everyone who posts :S