动态定义维度范围

Question

我每次决定建立一个立方体时都会遇到一个问题，而且我还没有找到解决办法。 问题是如何让用户自动定义一系列事物，而不需要在维度中硬编码它们。我将用一个例子来解释我的问题。 我有一个名为“Customers”的表：

这是表中的数据：

我想以透视样式显示数据，并将“薪水”和“年龄”按照定义的范围进行分组，如下所示：

我写了这个脚本并定义了范围：

SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = case
        when cast(salary as float) <= 500 then
            '0 - 500'
        when cast(salary as float) between 501 and 1000 then
            '501 - 1000'
        when cast(salary as float) between 1001 and 2000 then
            '1001 - 2000'
        when cast(salary as float) > 2000 then
            '2001+'
        end,
        [AgeRange] = case
        when cast(age as float) < 15 then
            'below 15'
        when cast(age as float) between 15 and 19 then
            '15 - 19'
        when cast(age as float) between 20 and 29 then
            '20 - 29'               
        when cast(age as float) between 30 and 39 then
            '30 - 39'
        when cast(age as float) >= 40 then
            '40+'
        end
  FROM [Customers]
GO

我的范围是硬编码和定义的。当我将数据复制到Excel并在数据透视表中查看时，它显示如下：

我的问题是我想通过将“Customers”表转换为事实表来创建一个立方体，并创建2个维度表“SalaryDim”和“AgeDim”。 “SalaryDim”表有2列（“SalaryKey，SalaryRange”），而“AgeDim”表类似（“ageKey，AgeRange”）。我的“Customer”事实表有：

Customer
[CustId]
[CustName]
[AgeKey] --> foreign Key to AgeDim
[Salarykey] --> foreign Key to SalaryDim

我仍然需要在这些维度内定义我的范围。每次我将Excel数据透视表连接到我的立方体时，我只能看到这些硬编码定义的范围。 我的问题是如何直接从数据透视表动态定义范围，而不创建像AgeDim和SalaryDim这样的范围维度。我不想仅仅局限于维度中定义的范围。

定义的范围是'0-25'，'26-30'，'31-50'。我可能想要将其更改为'0-20'，'21-31'，'32-42'等等，并且用户每次请求不同的范围。 每次更改时，我都必须更改维度。如何改进这个过程？ 如果能在立方体中实现一个解决方案，那将非常好，这样无论连接到立方体的BI客户端工具如何定义范围，都可以使用。但如果只使用Excel有一个好方法，我也不介意。

- AmmarR

Answer 1

如何使用T-SQL完成此操作：

根据要求，这是我之前回答的另一种方法，展示了如何使用Excel按用户进行操作。这个答案展示了如何使用T-SQL来实现相同的共享/集中操作。对于Cubes、MDX或者SSAS方面的操作，我不太清楚，也许Benoit或者其他熟悉这方面知识的人可以提供等效的解决方案...

1. 添加SalaryRanges SQL表和视图

使用以下命令创建一个名为"SalaryRangeData"的新表：

Create Table SalaryRangeData(MinVal INT Primary Key)

使用以下命令将计算列包装在视图中以添加：

CREATE VIEW SalaryRanges As
WITH
  cteSequence As
(
    Select  MinVal,
            ROW_NUMBER() OVER(Order By MinVal ASC) As Sequence
    From    SalaryRangeData
)
SELECT 
    D.Sequence,
    D.MinVal,
    COALESCE(N.MinVal - 1, 2147483645)  As MaxVal,
    CAST(D.MinVal As Varchar(32))
    + COALESCE(' - ' + CAST(N.MinVal - 1 As Varchar(32)), '+')
                        As RangeVals
FROM        cteSequence As D 
LEFT JOIN   cteSequence As N ON N.Sequence = D.Sequence + 1

在SSMS中，右键单击表格并选择“编辑前200行”。然后，在MinVal单元格中输入以下值：0、501、1001和2001（对于SQL Server来说，顺序无关紧要，它会为我们创建）。关闭表格行编辑器，执行SELECT * FROM SalaryRanges以查看所有行和范围信息。

2. 添加AgeRanges SQL表和视图

执行与上述步骤1完全相同的步骤，只是将所有出现的“Salary”替换为“Age”。这将创建表“AgeRangeData”和视图“AgeRanges”。

在AgeRangeData的[MinVal]列中输入以下值：0、15、20、30和40。

3. 向数据添加范围

用以下CASE表达式替换您的SELECT语句以检索数据和范围：

SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = (
            Select RangeVals From SalaryRanges
            Where [Salary] Between MinVal And MaxVal)
      ,[AgeRange] = (
            Select RangeVals From AgeRanges
            Where [Age] Between MinVal And MaxVal)
  FROM [Customers]

4. 其他一切，与现在一样 从这里开始，只需按照您目前的方式进行操作。所有范围应该像当前一样显示在您的数据透视表中。 5. 测试神奇之处 再次进入SSMS中的SalaryRangeData表行编辑器，删除现有行，然后插入以下值：0、101、201、301、... 2001（对于T-SQL解决方案，顺序无关紧要）。返回到您的数据透视表并刷新数据。就像Excel解决方案一样，数据透视表的范围应该会自动更改。

---

添加

如何将其添加到立方体中：

1. 创建一个视图

CREATE VIEW CustomerView As
SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = (
            Select RangeVals From SalaryRanges
            Where [Salary] Between MinVal And MaxVal)
      ,[AgeRange] = (
            Select RangeVals From AgeRanges
            Where [Age] Between MinVal And MaxVal)
  FROM [Customers]

1. 在 Visual Studio 中创建一个 BI 项目，并添加 CustomerView

连接到数据库，并将 CustomerView 视图添加到 Data Source Views 中作为事实表

2. 创建一个立方体并定义度量和维度 我们只需要顾客ID作为顾客数量的度量，并将相同的事实表作为维度。

3. 给维度添加属性

4. 从Excel连接到Cube

5. 在Excel中查看立方体的数据

6. 如果需要更改范围，只需重新处理维度和立方体中的数据。 如果您需要更改范围，请更改SalaryRangeData和AgeRangeData中的数据，然后只需重新处理维度和立方体。

- RBarryYoung

Answer 2

如何在Excel中完成此操作

以下是我在Excel中的做法...

1. 添加SalaryRanges Excel表格

插入一个新工作表，将其命名为"薪资范围"。在第一行分别按顺序添加文本标题"最小值"、"最大值"和"范围"（应位于单元格A1、A2、A3）。

在单元格B2中添加以下公式：

=IF(A2="","",IF(A3="","+",A3-1))

在C2单元格中添加以下公式：

=IF(B2="","",A2 & IF(B2="+",""," - ") & B2)

将这两个公式自动填充到B列和C列，以满足您可能需要的最大行数（假设为30行）。

接下来，选择整个范围（A1..C31）。转到“插入”选项卡，点击“表格”按钮，将此范围转换为Excel表格（以前称为“列表”）。在“表格工具设计”选项卡中，将此表格的名称更改为“SalaryRanges”。

现在，在Min列的单元格A2中输入“0”，在A3中输入“501”，在A4中输入“1001”，最后在A5中输入“2001”。注意，当您执行此操作时，Max和Range列会自动填充。

2. 添加AgeRanges Excel表格

现在创建另一个名为“年龄范围”的新工作表，并按照上述第1步的完全相同步骤进行操作，只是将此表格命名为“AgeRanges”，并在Min列中按顺序填充A2到A6的单元格，分别为0、15、20、30和40。同样，随着您的操作，Max和Range值应该会自动填充。

3. 获取数据

将数据从数据库导入到Excel工作簿中，就像之前所做的那样（还不要制作透视表，我们在下面会做），只是这次应该删除AgeRange和SalaryRange的case函数列。

4. 在数据中添加薪资和年龄范围列

在包含数据的工作表中，添加一个"SalaryRange"和"AgeRange"列。在SalaryRange列中，使用以下公式进行自动填充（假设"D"是薪资列）：

=LOOKUP(D2,SalaryRanges)

并将此公式填充到年龄范围列（假设“C”是年龄列）：

=LOOKUP(C2,AgeRanges)

5. 制作您的数据透视表

按照之前的步骤进行。请注意，年龄和薪水范围的值/标签应与您选择的范围相匹配。

6. 测试神奇效果

现在是有趣的部分。转到“SalaryRanges”工作表，重新输入最小列，从0开始，然后是101、201、301... 2001。返回到您的数据透视表，只需刷新它。如法炮制！

---

我应该提到，当然你也可以通过将表格放在SQL中，并将SELECT语句更改为使用LOOKUP(..)作为子查询来实现相同的效果（由于范围匹配的原因有点混乱，但肯定可行）。我之所以选择这种方式（在Excel中）是因为：

1. 对大多数人来说，更改范围会更容易一些。即使对于DBA和SQL开发人员（像我们一样），这种方式也稍微容易一些，因为它更接近用户界面/结果。
2. 这样可以让用户自己更改范围，而不必打扰你（对我来说是个很大的优势）。
3. 这还允许每个用户定义自己的范围。

然而，有时候让用户定义自己的范围实际上是不可取的。如果你的情况是如此，我很乐意演示如何在SQL中集中进行操作。

- RBarryYoung

---

+1 和非常感谢，解决方案非常出色，通过Excel将表格与数据范围连接起来。是否有办法将这些定义的范围与连接到 SSAS 中的 Cube 的数据透视表连接起来？我的数据透视表直接连接到 SSAS 中的 Cube，如果您可以展示“如何在中心位置执行”，那将非常棒。 - AmmarR

我可以向你展示如何使用SQL表达式来集中处理，我会将其作为另一种答案发布。不幸的是，我无法解决Cube/SSAS问题，因为我对它们不了解。是的，我应该知道它们，我也希望我知道，但事实上我并不知道，所以其他人将不得不解决这个问题。 - RBarryYoung

Answer 3

使用MDX语言，您可以创建自定义成员来定义范围。以下表达式定义了一个计算成员，表示所有501到1000之间的薪水：

MEMBER [Salary].[between_500_and_1000] AS Aggregate(Filter([Salary].Members, [Salary].CurrentMember.MemberValue > 500 AND [Salary].CurrentMember.MemberValue <= 1000))

你可以用年龄维度做同样的事情。

MEMBER [Age].[between_0_and_25] AS Aggregate(Filter([Age].Members, [Age].CurrentMember.MemberValue <= 25))

这篇文章解释了如何在Excel中添加这些计算成员（请参阅“在Excel 2007 OLAP数据透视表中创建计算成员/度量和集合”部分）。 可惜的是，Excel中没有用户界面来实现这一点。不过，你可以找到支持MDX语言的BI客户端，它们允许在查询中定义你的范围。

- Benoit

---

谢谢@Benoit，我正在尝试在立方体中添加计算字段，使用你提出的相同概念，但似乎还没有成功，这个过程有点冗长，而且我对它不太熟悉，我也会尝试在Excel中实现。 - AmmarR

谢谢 @RBarryYoung。 @MarkStorey-Smith：如果你给我“薪资”和“年龄”维度中的级别列表，我可以提高公式的效率。 - Benoit