Joining fact tables in an MDX query

Question

Hello All,

I am building and Anaysis Services project using VS 2005. The goal is to analyse advertising campaigns.

I have a single cube with 2 fact tables

factCampaign: which contains details of what people interviewed thought of an advertising campaign factDemographics: which contains demographic information of the people interviewed

These fact tables have a common dimension dimRespodent which refers to the actual person interviewed

I have 2 other dimensions (I’ve left non relevant dimensions)

dimQuestion: which contains the list of questions asked dimAnswer: which contains the list of possible answers to each question

dimQuestion and dimAnswer are linked to factDemogrpahics but not factCampaign

I want to be able to run queries to return results of what people thought about campaign (from factCampaign) but using demographic criteria (using dimQuestion and dimAnswer)

For example the how many Males, aged 18-25 recalled a particular campaign

I am new to OLAP and Analysis Services (2005) so please excuse me if what I am asking is too basic.

I have tried the following options

1. Linking the to factTables in the datasource view using the common RespondentKey. Queries run and return results but the same result is returned regardless of the demographic criteria chosen, i.e. it is being ignored.
2. Creating a dimension from factDemographics. I have tried to connect dimAnswer to factCampaign in Dimension Usage tabe of the Cube Structure but with out success. Either the project just stalls when I try to deploy it or I get the following error (note the attribute hierarchy enabled is set to true)

Errors in the metadata manager. The 'Answer Key' intermediate granularity attribute of the 'Fact Demographics' measure group dimension does not have an attribute hierarchy enabled.

I would appreciate any help that anyone can offer. Let me know if you require more info and again apologies if this is a basic question

Answer 1

What you probably need is a many-to-many relationship. There is a whitepaper here which goes through a number of scenarios for m2m relationships including one specifically around surveys and questionaires.

Answer 2

For anyone who is interested the solution was to alter the dimRespondent to include the questions and answers. And in the Dimension Usage tab of the Cube design to set dimRespondent to have a Regular relationship to both fact tables.