Organisation dimension - trouble making it conformed
  Home FAQ Contact Sign in
microsoft.public.sqlserver.olap only
 
Advanced search
POPULAR GROUPS

more...

microsoft ... sqlserver.olap Profile…
 Up
Organisation dimension - trouble making it conformed         


Author: CraigHB
Date: Jun 16, 2008 09:40

I'm creating a OLAP cube that holds both sales and Stock (inventory) data for
restaurants, and I'm looking for help building an Organisation dimension.

For sales data, the Organisation dimension hierachy would look like this:
Brand (e.g. Macdonalds)
Branch (e.g. Central Park branch)
Revenue Centre (e.g. Upstairs section, Pool bar)

For Stock data, the Organisation dimension hierachy would look like this:
Brand
Branch
Cost Centre (e.g. Kitchen, Bar)
Stock Location (e.g. Kitchen storeroom #1, Kitchen storeroom #2)

The finest grain is Revenue Centre for sales data and Stock Location for
Stock data. I want to have a conformed dimension, but that does seem possible
as they have different grains and I'm not sure what I would use as the
Organisation dimension key.

Any ideas?
1 Comment
RE: Organisation dimension - trouble making it conformed         


Author: Charles Wang [MSFT]
Date: Jun 16, 2008 22:53

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
Show full article (2.58Kb)
no comments