Joining fact tables in MDX query

Posted on 2009-02-10
Last Modified: 2016-02-13
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
Question by:NewOLAPUser
    LVL 8

    Accepted 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.

    Author Comment

    Hi srnar,

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

    Thanks again

    Author Closing Comment

    This worked perfectly srnar.  Thank you very much

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Read about achieving the basic levels of HRIS security in the workplace.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how the fundamental information of how to create a table.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now