A great start would be to read "Man From The Margin" by Dr. Rafe de Crespigny. He's the leading Western sanguo zhi scholar and translator, and this work focuses solely on Cao Mengde. You might be able to find a pdf copy to read online, and I'd highly recommend it to give you a better understanding of Cao Mengde, as Sanguo Yanyi and other historic dramas are pretty heavy on fiction, and not reliable as sources.

"Romance of the Three Kingdoms" was by far my most favorite book when I was growing up -- and yet over the years I've grown to despise it so so so much because people keep mistaking it for history.

Romance of the Three Kingdoms is NOT history. It's never been and never will be. What became the novel came through a long transformation process of passing compelling historical stories through popular performing arts, such as story telling during Song and Chinese plays and opera during Yuan, Ming, and even Qing. RotTK reflects the Confucian values and morality, as well as social construct of Song, Ming rather than Late Han. In other words, RotTK was at best an anachronitized popular history.

Historical records almost uniformly portraited Cao Cao positively. In facts, those that preferred Shu Han to Cao Wei were the odd ones out and were not part of the mainstream.

As for Cao Cao's motivation, wasn't it obvious -- power? What exactly were you asking here?

If this is a question about whether Cao Cao was good or bad, that can be a debate that lasts for generations (and in fact has lasted for about 1800 years). I personally admire Cao Cao intensely and have a very sympathetic opinion of him, but can fully understand people who see him as a villain. Even Cao Cao admirers would be hard-pressed to argue he did not commit atrocities (the massacres of the Xu province and the massacre of Yuan Shao's surrendering troops being the two big ones, but then they would argue that he has been unfairly treated by history as contemporary warlords and generals who were treated sympathetically did just as bad. For example Sun Ce & Sun Quan waged what is arguably a genocidal campaign against the Yue people and committed massacres and atrocities in the taking of the city of Xiakou). I'll talk on this in a follow up comment if I have time.

If this is a question about the views and opinions of Cao Cao then that's a bit more answerable. I'll give my answer in broad strokes.

In terms of contemporary accounts of Cao Cao they basically split down political lines. Records from the Wei/Jin perspective painted him positively. Records from the Shu/Wu perspective painted him negatively. Note though that on balance most histories of the time viewed him positively because:

a) the Wei/Jin dynasties ruled around 70% of the population of then-China

b) the Wei/Jin dynasties actually had historical records and a developed court system with a large amount of scholars and literati and recieved diplomats from other kingdoms/vassals. The Shu Dynasty meanwhile was in most essence a less developed self contained warlord state and kept no official histories. (Wu did a lot better but it was still aeons less developed then Wei).

The Jin Dynasty still recorded him quite positively. A large part of their legitimacy came from being the successors to the Wei Dynasty who abdicated to them.

From then on however views on him started becoming quite divergent. In retrospect the political system he set up was not stable or successful (how much of this can be blamed on him is arguable, I would argue very little) and Confucianist rhetoric demands that the failures of the dynasties be explained as the lack of virtue of its founders. The fact that he was very hostile to the Confucianist gentry who wrote a lot of history certainly did not help his legacy. The second thrust here was that Northern China fell out of Han-Chinese control with the breakdown of the Jin Dynasty, and suddenly the narrative of the loyal virtuous South versus the villainous invading North was all the more appealing.

By the Tang Dynasty we already had records of people listening to heavily romanticized Three Kingdom stories told in the street (in a kind of oral story-telling tradition), and people laughed when Cao Cao would lose a battle, and children would cry when Cao Cao won. In the Song Dynasty literati would condemn Cao Cao as a villain.

Views of Cao Cao continued to deteriorate, but I would argue not to the level that we associate with the mainstream view today. For example: in the Yuan Dynasty traditional oral stories started being collated in a manner that we can almost recognize as a kind of proto-Romance of the Three Kingdoms. This collection was called "Sanguozhi PingHua" or "Stories of the Records of the Three Kingdoms". And this was already an incredibly anti-Cao/Wei novel, but even here, they explained Cao Cao's rise as a karmic justice story, in that Cao Cao was the reincarnation of the great general Han Xin, who helped found the Han Dynasty but was unfairly betrayed and killed by Imperial Liu clan. This seems to me a more sympathetic narrative than the one laid out in Romance of the Three Kingdoms, where Cao Cao was just a cunning villain with power on his mind.

Sometime in the 1300's the first Romance of the Three Kingdoms arose, and as we know this work is very anti-Cao, setting up Liu Bei as the protagonist and Cao Cao as the antagonist quite clearly. But even this work was less anti-Cao than the one we know and love. Because in the Qing Dynasty two writers named Mao Lun/Mao Zonggang (father/son) revised and filled out the story, to the standardized version we're largely familiar with today. And the Mao father/son duo was even more anti-Cao, removing most passages which praised members of the Cao camp or showed them in a heroic light. Shortly after Qianlong Emperor wrote that Cao Cao was a villain and....well, you don't disagree with the Emperor. That was just official history by that point.

The source of the above is from a run-down on Cao Cao historiography from "Pin Sanguo" by Professor Yi Zhongtian (Yi has been widely accused of being pro-Cao).

However after the fall of Imperial China there has been a very strong revisionist trend in favour of Cao Cao. The leftist writer Lu Xun claimed that Cao Cao was a hero, and Mao Zedong also had some good things to say. There is also an added factor that the Marxist reading of Three Kingdoms history (as opposed to the traditional Great Man reading) sees Cao Cao as a progressive force representing the first stage of the overthrow of large land owning gentry families. Newly emerged western historiography on Cao Cao, driven by people like Dr Rafe de Crespigny from ANU, also views him in a drastically more sympathetic light.