[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1378
  • Last Modified:

Joining fact tables in MDX query

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 (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
  • 2
1 Solution
It comes to me that you need 3 dimensions: dimQuestions, dimAnswers, dimResponder, where dimResponder can hold the demography data. Then you can easily join these dimensions to measure group factCampaign on dimension usage tab.
It is usefull to check the design of dimensions and fact tables in sample Adventure Works database.
NewOLAPUserAuthor Commented:
Hi srnar,

Thanks very much for your reply.  I will try your suggestion and will let you know how I get on.

Thanks again
NewOLAPUserAuthor Commented:
This worked perfectly srnar.  Thank you very much

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now