Hi Craighb,
I understand that you would like to integrate organization data with
different dimension hierarchies in your sales data and stock data into one
organization dimension.
If I have misunderstood, please let me know.
I am not sure what your original organization looks like. It seems that all
of the names here "Brand", "Branch","Revenue Centre","Cost Centre" and
"Stock Location" are organization units, right? If so, you can simply
create an organization dimension with parent-child hierarchy. For example:
=====================================
[OrganizationKey] [int]
[ParentOrganizationKey] [int] NULL,
[OrganizationName] [nvarchar](50) NULL,
=====================================
Actually this is a common design for organization dimension, you may refer
to this article:
Defining a Parent-Child Hierarchy
http://msdn.microsoft.com/en-us/library/ms174846.aspx