问题是如何允许用户自动定义一系列事物而无需在维度中对其进行硬编码.我将在一个例子中解释我的问题.
我有一个名为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 [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的好方法.
解决方法
根据要求,这是我之前的答案的替代方案,该答案显示了如何使用Excel为每个用户执行此操作.这个答案显示了如何使用T-sql共享/集中进行相同的操作.我不知道如何为此做Cubes,MDX或SSAS的东西,所以也许Benoit或知道可以发布其等效的人…
使用以下命令创建一个名为“SalaryRangeData”的新表：
Create Table SalaryRangeData(MinVal INT Primary Key)
通过使用此命令将其包装在View中来添加计算列：
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以查看所有行和范围信息.
执行与上面#1完全相同的步骤,除了将所有出现的“Salary”替换为“Age”.这应该使表“AgeRangeData”和视图“AgeRanges”.
在AgeRangeData [MinVal]列中输入以下值：0,15,20,30和40.
3.向数据添加范围
将SELECT语句替换为CASE表达式,以使用以下方法检索数据和范围：
SELECT [CustId],[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],[AgeRange] = (
Select RangeVals From AgeRanges
Where [Age] Between MinVal And MaxVal)
FROM [Customers]
1.在Visual Studio中创建BI项目并添加CustomerView
连接到数据库,并将数据源视图中的CustomerView视图添加为Fact表
2.创建一个多维数据集并定义Measure&尺寸
我们只需要customerId作为客户计数的衡量标准,并且将具有与维度相同的事实表
4.从Excel连接到多维数据集
5.在Excel中查看多维数据集的数据
6.对于Ranges中的任何更改,只需重新处理Dimension&立方体
如果您需要更改范围,请更改SalaryRangeData和AgeRangeData中的数据,然后重新处理维度和多维数据集
