My relative is a fairly big academic and works at a university.
Is this correct? or should I have used in instead?
My relative is a fairly big academic and works at a university
is correct.
My relative is a fairly big academic and works in a university
is wrong.
See a similar example at Cambridge Dictionaries Online.
My friend is a fairly big academic and works outside mainstream academia in the mountains of Tibet doing geological research. However, my relative is also a fairly big academic and works in a university.
The quoted sentence is fine.
'In' is usually used when speaking about the general kind of work someone does, e.g.
My father works in telecommunications.
but
My father works at the telephone exchange.
Using 'in' is not just plain wrong - although in that context it does sound it. I would possibly use 'in' if I meant that he works there, but is not directly involved in the university establishment, for example someone who is for whatever reason doing an unrelated job but using the university for accommodation, or a cleaner etc.
AT university
But...
A student studies or a teacher works AT a university while a person not directly involved with studying or teaching for which a university exists, like a carpenter or a painter, works IN a university. At the same time you should use at when the name of the university is mentioned:
Unfortunately, I did not get a chance to study at University Oxford.