在MDX查询中连接事实表

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

我正在使用VS 2005构建和Anaysis Services项目。目标是分析广告活动。

I have a single cube with 2 fact tables

我有一个包含2个事实表的多维数据集

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

factCampaign:其中包含受访者对广告活动事件的看法的详细信息.Demographics:其中包含受访者的人口统计信息

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

这些事实表有一个共同的维度dimRespodent,它指的是受访的实际人

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

我有2个其他尺寸(我已经离开了非相关尺寸)

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

dimQuestion:其中包含问题列表dimAnswer:其中包含每个问题的可能答案列表

dimQuestion and dimAnswer are linked to factDemogrpahics but not factCampaign

dimQuestion和dimAnswer与factDemogrpahics相关联,但不与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)

我希望能够运行查询以返回人们对广告系列的看法(来自factCampaign)但使用人口统计标准(使用dimQuestion和dimAnswer)

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

例如,18-25岁的男性中有多少人召回了特定的活动

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

我是OLAP和Analysis Services(2005)的新手,所以请原谅我,如果我的要求太基础了。

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.

使用公共RespondentKey将数据源视图中的factTable链接起来。查询运行并返回结果,但无论选择的人口统计标准如何,都会返回相同的结果,即它被忽略。

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)

从factDemographics创建维度。我试图将DimAnswer连接到Cube Structure的Dimension Usage选项卡中的factCampaign,但没有成功。当我尝试部署它时,项目只是停止,或者我得到以下错误(注意启用的属性层次结构设置为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.

元数据管理器中的错误。 “Fact Demographics”度量值组维度的“Answer Key”中间粒度属性未启用属性层次结构。

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

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.

您可能需要的是多对多关系。这里有一份白皮书,介绍了m2m关系的一些场景,包括一个专门围绕调查和问卷调查的场景。

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

对于任何有兴趣的人来说,解决方案是改变dimRespondent以包含问题和答案。在Cube设计的Dimension Usage选项卡中,将dimRespondent设置为与两个事实表都有Regular关系。