Joining fact tables in MDX query
Posted on 2009-02-10
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 (Ive 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