So I've been watching some YouTube vids (Quinn's Ideas),
does Paul's son live on like centuries?
Leto II lives 3500 years.
Is he a tyrant? He merged with a worm?
That is correct. He is absolute ruler of the empire, and merged with sandfish (baby sandworms).
Did Paul end up a tyrant?
No, he's a messianic figure that leads the Fremen and the empire, but Leto II rules, where Paul leads. Leto II has the mental strength to live that long and force the evolution of mankind to be immune to superhuman prescience, and see humanity through to a safe future (ie the Golden Path), where Paul does not.
Does Paul's daughter play an important role?
Not really. As I explained upstream, she's unknowingly part of Leto II's plans in Book 3, but her main function is carry forward the Atredies bloodline where Leto II cannot because he's merged with the sandfish.
Her last appearance is in Book 3, and the wikis say she lives to 102 years old. She outlives Paul and Alia, but not Leto II.
Did the BG want Paul's sister killed because she was an abomination?
Yes. To go through the waters of life and become a Reverend Mother in the womb is totally abhorrent to the Bene Gesserit, and they would never take the waters of life pregnant (as Jessica was). The unborn baby's mind would be destroyed by their ancestral memories, their bodies by being unable to convert the waters of life (babies having a distinct and different body chemistry, despite being joined to their mother's). Jessica does this in desperation to consolidate her power among the Fremen to safeguard Paul. The fact that Jessica is able to protect the unborn Alia and that she survives the process is enough for the Bene Gesserit to consider her an abomination to be destroyed.
What role does Paul's wife play after his death? Not chani.
Not a huge one. Irulan basically becomes the imperial biographer, and in the first book every chapter starts with a quote from her history of Paul's life. After the failed attempt to prevent Chiani conceiving heirs, she's pretty much a spent force.
Did the whole story end at book 6?
No. Paul's story ends at Book 2, and again at Book 3. Alia, Ghenna, Jessica, Irulan end at Book 3. Leto II story ends at Book 4.
Book 6 is Chapter House Dune, and takes place far in the future. Although it has Bene Gesserit, Honoured Matres, and old empire, sandworms, etc, none of the characters from the first four books are there. I think it is the last Frank Herbert book and it does become a possible end point for the reader, although there are other stories out there. Book 6 is basically a rebirth of a new planet taking the place of a destroyed Arakkis, and the machinations of the Bene Gesserit (and those who oppose them).
At this point it's not the same story as the first books, it's another story set in the same universe but with some new themes and characters. It still has a lot of the original tropes (like the Kwizats Haderach).