Geographical realignment dimension

I have a fact table filled with clients including their address information as well as their original geographical alignment info (RegionID, DistrictID and TerritoryID). In order to forecast as well as perform an “as if” analysis, I need to be able to realign the accounts based on previous or future alignments. I have an alignment table (dimension) that maintains alignment information by year and quarter and I can join to this table using the ClientAddressID field as well as the Year and Quarter fields. I’d like to be able to make the Year and Quarter fields/join be variable so that I can perform realignments simply by changing the year/quarter.

Is this possible in SSAS or am I going to have to find another solution? Any help will be greatly appreciated...

David

I resolved this using a many to many relationship. This write-up was very useful: http://msdn2.microsoft.com/en-us/library/ms170463.aspx

David

Geographical realignment dimension

I have a fact table filled with clients including their address information as well as their original geographical alignment info (RegionID, DistrictID and TerritoryID). In order to forecast as well as perform an “as if” analysis, I need to be able to realign the accounts based on previous or future alignments. I have an alignment table (dimension) that maintains alignment information by year and quarter and I can join to this table using the ClientAddressID field as well as the Year and Quarter fields. I’d like to be able to make the Year and Quarter fields/join be variable so that I can perform realignments simply by changing the year/quarter.

Is this possible in SSAS or am I going to have to find another solution? Any help will be greatly appreciated...

David

I resolved this using a many to many relationship. This write-up was very useful: http://msdn2.microsoft.com/en-us/library/ms170463.aspx

David

sql

Geographic Locations in a database

Hi

Has anyone ever tried to store international address information in to a single data

example

UK Address
Nildram Ltd
Ardenham Court
Oxford Road
AYLESBURY
BUCKINGHAMSHIRE
HP19 3EQ
GREAT BRITAIN

US Address
CHRIS NISWANDEE
BITBOOST SYSTEMS
421 E DRACHMAN
TUCSON AZ 85705
USA

Spainish Address
Organismo Autnomio Correos y Telgraphos
Area de Asuntos Internacionales
Calle Aduana, 29
28070 MADRID
SPAIN

Has anyone ever normalised this information into a database, because Im not sure if I should create separate tables or US States etc. Are there any other countries that contain data such as states?

Im just wondering how large organisation such as Yahoo or Microsoft would store this information.

Regards
Bennettas you have posted a question in the articles section it is being moved to SQL Server Forum.

MODERATOR.