The Huge Materia is stated to be used as an explosive on the Rocket. I can't imagine if was used as fuel for the Rocket cause Shin-Ra doesn't even know if the normal rocket with its own fuel source would work. It was meant to be a bomb and I wouldn't use the cutscene of its impact onto Meteor as proof one way or the other cause the developers didn't bother to make variations (most likely due to running out of time, money, and other resources) so they may have built it based on one variation. My guess, is the scenario they chose is if the player failed to obtain all of them cause frankly, trying to get the materia in the Rocket itself is a pain in the ass and I'm sure most people failed the first time.

Also, fighting a magical Meteor with normal explosives doesn't sound like it would work even on paper, fighting them a bomb made of pure concentrated magic on the other hand does. So I can't back up the notion its just to power the Rocket. Especially since the rocket still uses classic rocket propulsion devices and the Rocket is still capable of moving even after the materia has been rescued.