動態定義維度中的範圍

May 22, 2016

每次我決定建構一個立方體時，我都會遇到一個問題，但我還沒有找到解決它的方法。
問題是如何允許使用者自動定義一系列事物，而無需在維度中對其進行硬編碼。我將在一個例子中解釋我的問題。
我有一張名為Customers的表：
這是表中的數據：
我想以數據透視樣式顯示數據，並將Salary和Age分組在定義的範圍內，如下所示：
我編寫了這個腳本並定義了範圍：
SELECT [CustId]
     ,[CustName]
     ,[Age]
     ,[Salary]
     ,[SalaryRange] = case
       when cast(salary as float) &lt;= 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) &gt; 2000 then
           '2001+'
       end,
       [AgeRange] = case
       when cast(age as float) &lt; 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) &gt;= 40 then
           '40+'
       end
 FROM [Customers]
GO
我的範圍是硬編碼和定義的。當我將數據複製到 Excel 並在數據透視表中查看時，它如下所示：
我的問題是我想通過將Customers表轉換為事實表並創建二維表SalaryDim和AgeDim來創建多維數據集。
SalaryDim表有 2 列（SalaryKey ,SalaryRange），AgeDim表類似（ageKey,AgeRange）。我的客戶事實表有：
Customer
[CustId]
[CustName]
[AgeKey] --&gt; foreign Key to AgeDim
[Salarykey] --&gt; foreign Key to SalaryDim
我仍然必須在這些維度內定義我的範圍。每次我將 Excel 數據透視表連接到我的多維數據集時，我只能看到這些硬編碼定義的範圍。
我的問題是如何直接從數據透視表動態定義範圍，而不創建像AgeDim和SalaryDim這樣的範圍維度。我不想只停留在維度中定義的範圍內。
定義的範圍是 ‘0-25’ 、 ‘26-30’ 、 ‘31- 50’。我可能想把它改成 ‘0-20’, ‘21-31’ , ‘32-42’ 等等，而且使用者每次都要求不同的範圍。
每次我改變它，我都必須改變維度。我該如何改進這個過程？
在多維數據集中實現解決方案會很棒，這樣任何連接到多維數據集的 BI 客戶端工具都可以定義範圍，但我不介意是否有隻使用 Excel 的好方法。

如何使用 T-SQL 執行此操作：
根據要求，這是我之前回答的替代方法，該回答展示瞭如何使用 Excel 為每個使用者執行此操作。這個答案顯示瞭如何使用 T-SQL 來共享/集中地做同樣的事情。我不知道如何為此做 Cubes、MDX 或 SSAS 的東西，所以也許 Benoit 或知道的人可以發布它的等價物……
添加 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查看所有行和範圍資訊。
添加 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]
其他一切，和現在一樣
從這裡開始，一切都和現在一樣。這些範圍都應該像目前一樣顯示在您的數據透視表中。
測試魔法
再次轉到 SSMS 中的 SalaryRangeData 表行編輯器並刪除現有行，然後插入以下值：0、101、201、301、… 2001（同樣，順序對於 T-SQL 解決方案無關緊要） . 返回數據透視表並刷新數據。就像 Excel 解決方案一樣，數據透視表範圍應該自動更改。
添加
如何將其添加到多維數據集：
創建視圖
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]
在 Visual Studio 中創建一個 BI 項目並添加CustomerView
連接到數據庫，並將CustomerView視圖中的視圖添加Data Source Views為事實表
創建一個立方體並定義度量和維度
我們只需要 customerId，作為客戶數量的衡量標準，並且將具有與維度相同的事實表
3.向維度添加屬性
從 Excel 連接到 Cube
5.在Excel中查看立方體的數據
對於 Ranges 中的任何更改，只需重新處理 Dimension & cube
如果您需要更改範圍，請更改中的數據，SalaryRangeData然後AgeRangeData重新處理維度和多維數據集