sql - 数据立方体设计 : hard-to-aggregate measure

Question

我正在为数据立方体设计事实表，我有一个我真的不知道如何正确聚合的度量。以下 SQL 代码将创建一个小型示例事实表和维度表:

create table FactTable (
    ID      int,
    Color   int,
    Flag    int)

insert into FactTable (ID, Color, Flag) values (1, 'RED',   1)
insert into FactTable (ID, Color, Flag) values (1, 'WHITE', 0)
insert into FactTable (ID, Color, Flag) values (1, 'BLUE',  1)
insert into FactTable (ID, Color, Flag) values (2, 'RED',   0)
insert into FactTable (ID, Color, Flag) values (2, 'WHITE', 0)
insert into FactTable (ID, Color, Flag) values (2, 'BLUE',  1)
insert into FactTable (ID, Color, Flag) values (3, 'RED',   1)
insert into FactTable (ID, Color, Flag) values (3, 'WHITE', 1)
insert into FactTable (ID, Color, Flag) values (3, 'BLUE',  1)

create table ColorDim (
    CID     int, 
    Color  int)

insert into ColorDim (CID, Color) values (1, 'RED')
insert into ColorDim (CID, Color) values (2, 'WHITE')
insert into ColorDim (CID, Color) values (3, 'BLUE')

FactTable 和 ColorDim 在 FactTable.Color = ColorDim.Color 上连接。在立方体中，应该有一个名为“爱国”的度量，它计算对象 ID，包括红色、白色或蓝色(至少一种颜色)。期望的输出如下:

• 浏览立方体时，如果用户拉入爱国措施(不拉动维度)，则显示的总数应为 2，因为有 2 个 ID(即 1 和 3)至少包含三种颜色中的一种.请注意，ID 1 应该为总爱国值贡献 1，即使它有两种颜色。

• 如果用户按颜色维度浏览爱国措施，他们应该会看到如下表格。请注意，ID 1 对红色计数贡献 1，对蓝色计数贡献 1。

  +--------+------------+
  |颜色 |爱国|
  +--------+------------+
  |红色 | 2 |
  |白色 | 1 |
  |蓝色 | 2 |
  +--------+------------+

(我尝试使用 this web app 创建表格，但间距似乎不正确。希望它具有足够的可读性以便理解。)

我确定这是一个非常基本的多维数据集设计情况，但我之前并没有使用多维数据集，而且我使用的度量通常是简单的列 SUM，或列 SUM 的乘积，等等。任何帮助将不胜感激。

(如果相关，我正在运行在 MS SQL Server 2008 中构建事实/维度表的 SQL 查询，并且我将在 MS Visual Studio 2008 中设计多维数据集本身。)

Answer 1

我会试一试，虽然我不是 100% 确定我理解这些问题。此外，我不想在评论中发布查询以验证它们是否有效。如果我偏离了方向并且这没有帮助，我会删除答案。

When browsing the cube, if the user pulls in the Patriotic measure (pulling no dimensions), the total shown should be 2, since there are 2 IDs (namely, 1 and 3) which include at least one of the three colors. Notice that ID 1 should contribute 1 to the total Patriotic value, even though it has two of the colors.

WITH MyCTE (id, Count)
AS
(
select id, count(flag) as count
from FactTable
where Flag=1
group by id
having COUNT(flag) >=2
)
select COUNT(*) from MyCTE

If the user browses the Patriotic measure by the Color dimension, they should see a table like the following. Note that the the ID 1 contributes 1 to the RED count and 1 to the BLUE count.

select a.Color, COUNT(*)
from FactTable a
    join ColorDim b
    on a.Color = b.Color
where Flag = 1
group by a.Color