Very Tricky Hierarchy For Dimesion From Relational Database

I need to create a dimension with a hierchy derived from 3 other dimenions where one of the dimensions contains a recursive hierarchy.

eg:

In my RDBMS I have; a table "Entities" which contextualises geographic locations and (typical columns include and id and a description with values for: ID1=United Kingdon, ID2=France, ID3=Norway, ID4=Finland, ID5=Europe, ID6=Scandinavia, etc.), another table "Entity_Type" which contextualises the values held in "Entities" (Typical olumns; ID1=Continent, ID2=Country) and a table, "Geo_Code" which codifies the values from both "Entities" and "Entity_type". They act more or less like 2 dimensions ("Entities", Entity_Type) and a fact table ("GEO_Code").

The third table has a recursive hierarchy in that it references it's own ID again in column "Entity Reference".

The first table is Entities, the second is Entity_Type and the third is the one that references both.







id
description

01
England

02
Norway

03
Europe

04
Scandinavia




id
description

01
Continent

02
Country




id
entity
Entity_Type
entity Reference

01
03
01
NULL

02
04
01
NULL

03
02
02
02

04
01
02
01
From the last table "GEO_CODE" we can determine the following:
01. Europe is a continent
02. Scandinavia is a continent
03. Norway is a Country that is in Scandinavia (Entity Reference recursive referencing ID 02 of the same table where the Entity is 04).
04. England is a Country that is Europe (Entity Reference recursive referencing ID 01 of the same table where the Entity is 03).

How do I create a new dimension using Visual Studio in Analysis Services where the Hierarchy is reflected?

So far, I have managed to depict 2 Hierarchies: where a dimenion depicts Entity (Countinent/Country) as level 1 and the value of the corresponding entity (Europe, ENgland, Norway, Scandinavia) in level 2 but how do I get to the last bit where the Entity Reference references the location of the Country or Continent???