The Shinra Mansion is a large house in the northern section of Nibelheim. The inside of the house looks quite dilapidated as it was once used by Shinra to house researchers and a secret lab but has since fallen in to a state of disrepair.
There are several points in the story which involve the Shinra Mansion. The first point is during the flashback sequence at the Kalm Inn. Cloud and Sephiroth will have a few lines of dialogue in the mansion as Sephiroth learns more about his origins. Check out the Kalm walkthrough page for more information.
The second second visit is when Cloud and his team arrive in Nibelheim as they track down Sephiroth on the western continent. You can take the team back in to the Shinra Mansion to obtain some items including:
Twin Viper
Silver M-phone
Enemy Launcher
Destruct Materia
Check out the Nibelheim walkthrough page for details.
The third visit is when you head to the Shinra Mansion to obtain a secret character, Vincent Valentine. This visit can be done at the same time as the second visit but it can be fairly difficult at this point in the game. Check out the Secret Characters section for details:
The map below shows the location of the Shinra Mansion (which can be found in Nibelheim) on the World Map: