Hello everyone, I'm looking for help creating a bottom up
dimension/cube from an existing relational database. Currently the DB
is driven by the 4 main tables Company, Contact, Title, and
CompanyContact. The CompanyContact table has a many-to-many
relationship between Company and Contact. Also each Company-Contact
relation is identified with a TitleID in this same table. A Contact
can belong to many companies and have different titles at each of the
companies.

I'm looking to create a dimension/cube to display the company along
with a count of contacts. Drilling down would give me the titles and
count of contacts holding the different titles. Vice Versa, I want to
be able to start off with titles and count of contacts per title. Then
drill down to Contact info and the company they hold that specific
title for.

I've walked through the tutorials and sort of have a slight grasp on
Analysis Server, but not quite enough. I've attempted to design and
deploy my dimension/cube, but get undesired results.

Any help? Thanks.

CREATE TABLE [dbo].[Company](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyTypeID] [int] NOT NULL,
[CompanyName] [varchar(255)] NOT NULL,
[Address] [varchar(100)] NOT NULL,
[City] [varchar(100)] NOT NULL,
[State] [varchar(2)] NOT NULL,
[Zip] [varchar(10)] NOT NULL
)

INSERT INTO Company
SELECT 'Company A' , 'PO BOX A', 'My City', 'CA', '91110'

INSERT INTO Company
SELECT 'Company B' , 'PO BOX B', 'My City', 'CA', '91110'



CREATE TABLE [dbo].[Contact](
[ContactID] [int] IDENTITY(1,1) NOT NULL,
[FullName] [varchar(255)] NOT NULL,
[Address] [varchar(100)] NOT NULL,
[City] [varchar(100)] NOT NULL,
[State] [varchar(2)] NOT NULL,
[Zip] [varchar(10)] NOT NULL,
[email] [varchar(150)] NOT NULL
)

INSERT INTO CONTACT
SELECT 'Tom Smith', 'PO BOX AA', 'My City', 'CA',
'90065','tom (AT) hootmail (DOT) com'

INSERT INTO CONTACT
SELECT 'Jane Smith', 'PO BOX BB', 'My City', 'CA',
'90065','jane (AT) hootmail (DOT) com'

INSERT INTO CONTACT
SELECT 'Joe Plain', 'PO BOX CC', 'My City', 'CA',
'90065','joep (AT) hootmail (DOT) com'

INSERT INTO CONTACT
SELECT 'Amy lean', 'PO BOX DD', 'My City', 'CA',
'90065','amyl (AT) hootmail (DOT) com'


CREATE TABLE [dbo].[Title](
[TitleID] [int] IDENTITY(1,1) NOT NULL,
[TitleName] [varchar(255)] NOT NULL
)

INSERT INTO TITLE
SELECT 'CEO'

INSERT INTO TITLE
SELECT 'CFO'

INSERT INTO TITLE
SELECT 'VP'

INSERT INTO TITLE
SELECT 'ADMIN'

CREATE TABLE [dbo].[CompanyType](
[CompanyTypeID] [int] IDENTITY(1,1) NOT NULL,
[CompanyTypeName] [varchar(255)] NOT NULL
)

INSERT INTO CompanyType
SELECT 'Clothing'

INSERT INTO CompanyType
SELECT 'Retail'

INSERT INTO CompanyType
SELECT 'Manufacturer'

INSERT INTO CompanyType
SELECT 'Food Chain'


CREATE TABLE [dbo].[CompanyContact](
[CompanyContactID] [int] IDENTITY(1,1) NOT NULL,
[CompanyID] [int] NOT NULL,
[ContactID] [int] NOT NULL
[TitletID] [int] NOT NULL
)

INSERT INTO COMPANYCONTACT
SELECT 1,1,1

INSERT INTO COMPANYCONTACT
SELECT 1,2,2

INSERT INTO COMPANYCONTACT
SELECT 1,3,3

INSERT INTO COMPANYCONTACT
SELECT 2,4,1

INSERT INTO COMPANYCONTACT
SELECT 2,1,2