In my opinion, not so much. I haven't got much experience with the US justice system, but everything the detective learns is hearsay, so (s)he can't tell the jury what witness so-and-so says. (S)he sends everything to the crine lab for testing, but only the scientist can testify about the results. So really, all (s)he can ever say is, 'I was the lead detective. I talked to some people. I sent some stuff to the lab. I wrote some reports.' Things like that can be cross-examined quite easily.
How useful a lead detective will be as a murder witness in a trial inside of the courtroom?
Very useful. As a murder witness, the detective's testimony can be more useful than the average witness's because the detective's training renders him capable of paying attention to important elements that to the average witness tend to go unnoticed.
Outside the murder witness capacity, your description oversimplifies the detective's role in an investigation. The detective is more than a clerk who takes notes and delivers items to a facility. His scrutiny of the evidence can highlight to the fact-finder a number of inconsistencies and nuances that are far-reaching, more relevant than "I talked to some people" and "I wrote some reports".
... everything the detective learns is hearsay ...
Hearsay doesn't mean what you think it means.
Under the Federal Rule of Evidence (which is representative of all common law jurisdictions): "Hearsay is a statement, other than one made by the declarant while testifying at the trial or hearing, offered in evidence to prove the truth of the matter asserted."
So the lead detective (or anyone else) can testify about their own experiences: what they saw, what they heard, what they felt (physically and emotionally) and that is evidence of the truth of those experiences. This includes testimony about things that were said to them as proof that those things were said but not that the things that were said are themselves true.
An example may help. I'm the detective and I interview you. During that interview you say to me "I heard a gunshot." I can testify that you said you heard a gunshot - that is evidence that you said it, not evidence that a gunshot was actually heard. This might be relevant if, for some reason, your testimony in court is now that you didn't hear a gunshot.
Naturally, all the physical evidence that the detective sees or collects is not hearsay because it was the detective who did the seeing and collecting. Similarly, the detective's evidence on who was where, when, what they were wearing, whether they were covered in blood etc. is not hearsay.
Furthermore, there are a bunch of exceptions to the hearsay rule. The most significant is that inculpatory statements by a defendant are not hearsay. If a suspect says "I hit him with a brick" to a witness and that suspect is on trial in a murder where the physical evidence indicates the victim died after being hit with a brick, that statement is admissible as evidence that the defendant "hit him with a brick".