主页 / dba / 问题 / 20099
Accepted
AmmarR
Asked: 2012-06-29 04:09:29 +0800 CST

动态定义维度中的范围

  • 772

每次我决定构建一个立方体时，我都会遇到一个问题，但我还没有找到解决它的方法。

问题是如何允许用户自动定义一系列事物，而无需在维度中对其进行硬编码。我将在一个例子中解释我的问题。

我有一张名为Customers的表：

表结构

这是表中的数据：

数据表

我想以数据透视样式显示数据，并将Salary和Age分组在定义的范围内，如下所示：

具有定义范围的数据表

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

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表转换为事实表并创建二维表SalaryDim和AgeDim来创建多维数据集。

SalaryDim表有 2 列（SalaryKey ,SalaryRange），AgeDim表类似（ageKey,AgeRange）。我的客户事实表有：

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 的好方法。

sql-server sql-server-2008

3 个回答

  1. Best Answer

    如何使用 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，顺序无关紧要，它会为我们创建它）。关闭表行编辑器并执行 aSELECT * FROM SalaryRanges查看所有行和范围信息。

    2. 添加 AgeRanges SQL 表和视图

    执行与上述#1 完全相同的步骤，除了将所有出现的“Salary”替换为“Age”。这应该使表“AgeRangeData”和视图“AgeRanges”。

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

    3. 向数据添加范围

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

    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. 创建一个立方体并定义度量和维度

    我们只需要 customerId，作为客户数量的衡量标准，并且将具有与维度相同的事实表

    措施

    方面

    3.向维度添加属性

    将范围作为属性添加到维度

    4. 从 Excel 连接到 Cube

    将 SSAS 源添加到 Excel

    选择多维数据集

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

    在 Excel 中查看多维数据集

    6. 对于 Ranges 中的任何更改，只需重新处理 Dimension & cube

    如果您需要更改范围，请更改中的数据，SalaryRangeData然后AgeRangeData重新处理维度和多维数据集

    • 12

  2. 如何使用 Excel 执行此操作

    这是我在 Excel 中的操作方式...

    1. 添加 SalaryRanges Excel 表

    插入一个新工作表，将其命名为“工资范围”。在第一行按顺序添加文本标题“Min”、“Max”和“Range”（应分别为单元格 A1、A2、A3）。

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

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

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

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

    在 B 和 C 列中自动填充这两个公式，以获得您可能需要的最大行数（比如说 30）。

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

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

    2.添加AgeRanges Excel表格

    现在制作另一个名为“Age Ranges”的新工作表，并执行与上述 #1 完全相同的步骤，只是将此表称为“AgeRanges”，并在 Min 列中用 0、15、20、30 和40，按顺序。同样，Max 和 Range 值应在您进行时自动填写。

    3. 获取数据

    像以前一样将数据库中的数据获取到 Excel 工作簿中（先不要制作数据透视表，我们会在下面这样做），但您应该删除 AgeRange 和 SalaryRange 案例函数列。

    4. 将 Salary 和 Age Range 列添加到您的数据中

    在数据所在的工作表中，添加“SalaryRange”和“AgeRange”列。在 SalaryRange 列中，自动填充以下公式（假设“D”是 Salary 列）：

    =LOOKUP(D2,SalaryRanges)

    并将此公式自动填充到 AgeRange 列（假设“C”是 Age 列）：

    =LOOKUP(C2,AgeRanges)

    5. 制作你的数据透视表

    像以前一样执行此操作。请注意，年龄和工资范围值/标签与您选择的范围相匹配。

    6. 测试魔法

    现在有趣的部分。转到 SalaryRanges 工作表并重新输入 Min 列，从 0 开始，然后是 101、201、301、... 2001。返回数据透视表并刷新它。沙赞！

    我应该提到，当然你也可以通过将表放在 SQL 中并更改 SELECT 语句以将 LOOKUP(..)s 作为子查询来实现相同的效果（由于范围匹配而有点混乱，但绝对可以 -有能力的）。我这样做的原因（在 Excel 中）是

    1. 对于大多数人来说，更改范围要容易一些。即使对于 DBA 和 SQL 开发人员（如我们）来说，这种方式也更容易一些，因为它更接近 UI/结果。
    2. 这允许您的用户更改他们自己的范围，而不必打扰您。（我生命中的一大优势）
    3. 这也允许每个用户定义自己的范围。

    但是，有时让用户定义自己的范围实际上是不可取的。如果您是这种情况，我将很乐意演示如何在 SQL 中集中执行此操作。

    • 8

  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 中没有用于此的 UI。不过，您可以找到支持MDX语言的 BI 客户端，这些客户端允许在查询中定义您的范围。

    • 5

相关问题