How can I Create Hierarchy from two different dimension?

Question

I have two dimension in my data source DimAddress and DimGeoInforamtion. If i want to use Hierarchy from address table i will get address, city and zip code from DimAddress table but I will get the state from DimGeoInformation table. How can i create Hierarchy between them>?

CREATE TABLE DimAddress
(
    [AddressID] [int]IDENTITY(1,1) NOT NULL,
    [Address] [varchar](MAX) NULL,
    [ZipCode] [Varchar(5)] NULL,
    [AddressSpatialID] [int] NULL,      
    [City] [varchar](50) NULL,
    [CitySpatialID] [int] NULL,
    [CityID] [int] NOT NULL
)

CREATE TABLE DimGeoInformation
(
    [CountyID] [int] IDENTITY (1,1) NOT NULL,
    [County] [varchar](50) NULL,
    [CountySpatialID] [bigint] NULL,    
    [StateID] [tinyint] NOT NULL,
    [State] [varchar](25) NOT NULL,
    [RegionID] int
    [RegionName] varchar(50)    
)

Answer 1

You are trying to normalize a star schema. The temptation exists because you want don't want to duplicate data, but this is the trade-off of a data-warehouse.

If you want to do this, the best method would be to 'snowflake' your star schema. You would have to join your fact table to the Address dimension, and have a standard relational database style join from your address dimension to the GeoInformation table. You would need a Foreign Key for the GeoInformation table in the Address Dimension.

I would recommend an alternative approach: All of these fields would be better in a single dimension table, as well as any other contact details/location related fields. This will cost you some disk space on the low cardinality fields such as 'State', but this should be negligible compared to the size of your fact table.

With less tables, less joins and less keys, this method will almost always be much simpler and faster.