Complex dimension question

I have two dimension; 1 is a 'Users' dimension which has information about users of our system, the other is a self-referencing dimension containing industry hierarchies.

The Users dimension is linked to the Industry dimension by Industry ID.

I tried adding the Industry hierarchy to the Users dimension but when I go to browse the hierarchy all I see is IDs of the DimUser table, so I think that I did something wrong.

My goal is to add the industry hierarchy to the User dimension.
Can I do this with my current architecture?

Here are some examples of the two dimension:

DimUsers
- ID
- Name
- IndustryID (FK -> DimIndustry.ID)

DimIndustry
- ID
- IndustryName
- ParentID (FK -> DimIndustry.ID)

Thanks in advance.

Preston,

You should be able to do this using the following steps:

1.) Edit the User dimension

2.) In the "Data Source View" pane of the dimension editor, and select "Show Tables..."

3.) Add the DimIndustry table to the data source view pane

4.) Drag the "IndustryID" from your DimUsers table as a new attribute.

5.) Change the "NameColumn" property to pull the "IndustryName" column from the DimIndustry table.

6.) Create your hierarchy using the attributes you want and process.

HTH,

- Steve

complex and or query

Hi,
Anyone able to help? XTab wont work.
Have a table containing names of people.
Mark
John
Mary
They answer up to 10 questions each. The answers are
unique id numbers kept in the same column
ResultsTable
QuestionID AnswerID Answer Name
1 001 Apple John
2 005 Holden John
3 010 20 John
1 002 Orange Mary
2 006 VW Mary
3 011 30 Mary
Need to be able to have a stored procedure that allows me
to select (for example) everyone who chose fruit = apple
or orange and cartype = Holden and age > 25
The problem is, doing a simple and returns no results
because of the single column for the AnswerID. Doing an
or will return anyone answering either of these answers.
Any ideas?I'm not sure why you have both answerid and answer columns in this table.
Since answerid appears to be redundant I've ignored it.
CREATE TABLE Answers (questionid INTEGER NOT NULL, answer VARCHAR(10) NOT
NULL, firstname VARCHAR(10) NOT NULL, PRIMARY KEY (firstname,questionid))
INSERT INTO Answers VALUES (1, 'Apple', 'John')
INSERT INTO Answers VALUES (2, 'Holden', 'John')
INSERT INTO Answers VALUES (3, '20', 'John')
INSERT INTO Answers VALUES (1, 'Orange', 'Mary')
INSERT INTO Answers VALUES (2, 'VW', 'Mary')
INSERT INTO Answers VALUES (3, '30', 'Mary')
CREATE TABLE Criteria (questionid INTEGER NOT NULL, min_answer VARCHAR(10)
NOT NULL, max_answer VARCHAR(10) NULL, PRIMARY KEY (questionid, min_answer))
INSERT INTO Criteria VALUES (1,'Apple',NULL)
INSERT INTO Criteria VALUES (1,'Orange',NULL)
INSERT INTO Criteria VALUES (2,'Holden',NULL)
INSERT INTO Criteria VALUES (3,'20','999')
Here's the query that gives people who match at least one answer in the
Criteria table for each of the questions in the Criteria table.
SELECT firstname
FROM Answers AS A
JOIN Criteria AS C
ON A.questionid = C.questionid
AND A.answer BETWEEN C.min_answer AND COALESCE(C.max_answer,
C.min_answer)
GROUP BY firstname
HAVING COUNT(DISTINCT A.questionid)= (SELECT COUNT(DISTINCT questionid)
FROM Criteria)
--
David Portas
--
Please reply only to the newsgroup
--