How to create dimension with attributes from different tables?

Hi, all here,

I am having a question about how to create dimension with attributes from different tables? For example, I am going to create a dimension called Country-region with attibutes from country table and region table. So how can it be done then?

Thanks a lot in advance for any guidance and help.

Hi,

You will need to create a 'named query' in your DSV and use this named query as a source for your dimension.

HTH,

Eric

|||Hi, Aiwa, thanks a lot. But would you please post some sample code about named query for that then? Thanks a lot.|||

Hi,

I don't know your tables structure but you could create a named query that would look like that:

SELECT DISTINCT a.IdCountry, a.CountryCode, a.CountryName, a.OtherCountryAttributes,b.IdRegion,b.RegionCode, b.RegionName,b.OtherRegionAttributes

FROM Country a

JOIN Region b on b.IdCountry = a.IdCountry

Hopefully, your fact table keeps the region id or region code information. You will then create your Dimension based on the named query and btw, the id of the dimension should be the IdRegion column.

HTH,

Eric

|||Hi, Aiwa, thanks a lot. Got it done.