Got the solution for the solution for this , we can join these two tables on city_code and get the relevant city_id from city_dimension.
select *, $cityD as city_id from Sfact let cityD = ( select id from cityDim where cityName = $parent.$current.city )
Hope this will help many Newbies users of orientdb.