SSAS - 為計量單位應用轉換率

所以我在最後一天左右一直在玩這個，並且無法更接近。

我有一個 SSAS 多維數據集，它有一個度量值組，它以秒為單位顯示事件的總執行時間。我要做的是讓我們的使用者選擇他們想要顯示結果的時間單位（即分鐘、小時、天、周等）。

從表面上看，它似乎類似於進行貨幣轉換。所以使用 AdventureWorks 作為我的模板，我創建了 2 個額外的表。

CREATE TABLE [Dim_DurationUnits]
(
   [DurationUnit_SK] INT NOT NULL, 
   [DurationName] VARCHAR(50) NOT NULL,
   CONSTRAINT [PK_Dim_DurationUnits] PRIMARY KEY CLUSTERED ([DurationUnit_SK] ASC)
)
CREATE TABLE [Fact_DurationConversions]
(
   [DurationConversion_SK] INT NOT NULL, 
   [DurationUnit_SK] INT NOT NULL, 
   [DurationConversionValue] DECIMAL(18, 7) NOT NULL,
   CONSTRAINT [PK_Fact_DurationConversions] PRIMARY KEY CLUSTERED ([DurationConversion_SK] ASC),
   CONSTRAINT [FK_Dim_DurationTypes_Fact_DurationConversions] FOREIGN KEY ([DurationUnit_SK]) REFERENCES [Dim_DurationUnits]([DurationUnit_SK])
)
INSERT INTO [Dim_DurationUnits] ([DurationUnit_SK], [DurationName])
SELECT [DurationUnit_SK], [DurationName]
FROM (
   SELECT 1 [DurationUnit_SK], 'Seconds' [DurationName]
   UNION ALL
   SELECT 2 [DurationUnit_SK], 'Minutes' [DurationName]
   UNION ALL
   SELECT 3 [DurationUnit_SK], 'Hours' [DurationName]
   UNION ALL
   SELECT 4 [DurationUnit_SK], 'Days' [DurationName]
   UNION ALL
   SELECT 5 [DurationUnit_SK], 'Weeks' [DurationName]
   UNION ALL
   SELECT 6 [DurationUnit_SK], 'Months (30 days)' [DurationName]
) A

INSERT INTO [Fact_DurationConversions] ([DurationConversion_SK],[DurationUnit_SK],[DurationConversionValue])
SELECT [DurationConversion_SK],[DurationUnit_SK],[DurationConversionValue]
FROM (
   SELECT 1 [DurationConversion_SK], 1 [DurationUnit_SK], 1 [DurationConversionValue]  -- Seconds
   UNION ALL
   SELECT 2 [DurationConversion_SK], 1 [DurationUnit_SK], 60 [DurationConversionValue]  -- Minutes
   UNION ALL
   SELECT 3 [DurationConversion_SK], 1 [DurationUnit_SK], 60 * 60 [DurationConversionValue]  -- Hours
   UNION ALL
   SELECT 4 [DurationConversion_SK], 1 [DurationUnit_SK], 24 * 60 * 60 [DurationConversionValue]  -- Days
   UNION ALL
   SELECT 5 [DurationConversion_SK], 1 [DurationUnit_SK], 7 * 24 * 60 * 60 [DurationConversionValue]  -- Weeks
   UNION ALL
   SELECT 6 [DurationConversion_SK], 1 [DurationUnit_SK], 30 * 7 * 24 * 60 * 60 [DurationConversionValue]  -- Months
) A

完成後，我將持續時間單位維度和轉化率度量組添加到我的多維數據集中，並使用度量表達式更新我的總持續時間度量 (

$$ total duration $$/$$ conversion rate $$）。就在這一點上，我被困住了。因為在轉換率表和我的事實表（即 Adv.Wrks 中的匯率日期）之間沒有橋樑，所以我無法在維度使用選項卡中定義關係。同樣，由於缺乏時間維度，我無法在轉換率度量上使用冒險作品使用的任何半附加度量（AverageOfChildren、LastNonEmpty）。 有誰知道我怎樣才能讓它工作？我一直在尋找和嘗試，似乎無法為我認為常見的問題找到解決方案。

我沒有足夠的聲譽將我的圖片上傳到網站。我希望描述能有所幫助。如果您想查看此問題的完整文件，可以給我發送電子郵件，我會將帶有圖像的完整文件發回給您。我的電子郵件地址是：alireza.marahemi@gmail.com

假設您有以下數據作為測量單位

Uom     ConvertRate( to smallest unit)
Second  1
Minute  60
Hour    3600
Day     3600* 24

您的事實表包含基於您的最小測量單位（在本例中為秒）的數據。您希望能夠選擇其他度量單位（例如小時），並根據您的選擇顯示您的度量（在我們的範例中為時間）。例如，如果您的數據項總和為 1200（秒），如果您選擇分鐘，則結果應為 20。在這種情況下，我假設您不想為數據的每個測量單位添加額外的度量。這裡是設計方法。首先，您必須以下列方式創建您的測量單位表：

CREATE TABLE [DimUom]
(
   [UomID] INT NOT NULL primary key, 
   [UomName] VARCHAR(50) NOT NULL,
   ParentUomID INT,
   ConvertRate float
)

我使用了 ParentUomID 和 ConvertRate 欄位來辨識基本度量和目前度量的 ConverRate 到基本度量。您可以使用以下腳本填充此表。

INSERT INTO [DimUom] ([UomID], [UomName], [ParentUomID],[ConvertRate])
SELECT [UomID], [UomName], [ParentUomID],[ConvertRate]
FROM (
   SELECT 1 AS [UomID], 'TimeMaster' AS [UomName] , NULL as [ParentUomID], 1 as [ConvertRate]
   UNION ALL
   SELECT 2 AS [UomID], 'Seconds' AS [UomName], 1, 1
   UNION ALL
   SELECT 3 , 'Minutes', 1, 60
   UNION ALL
   SELECT 4 , 'Hours' , 1, 3600
   UNION ALL
   SELECT 5 , 'Days' , 1, 86400
   UNION ALL
   SELECT 6 , 'Weeks' , 1, 86400 * 7
   UNION ALL
   SELECT 7 , 'Months (30 days)' , 1, 86400 * 7 * 30
) A;

您的事實數據可以像以下事實表一樣簡單，只有 3 個欄位 DataID 作為主鍵（實際上不需要）您的 DataItem 和您的測量單位。

CREATE TABLE [FactData] (
[DataID] INT primary key identity, 
dataItem INT NOT NULL,
[UomID] INT NOT NULL);
GO

以下腳本將最少的數據添加到您的表中。你已經註意到我只使用了 TimeMaster 測量單位到桌子上（實際上我可以只使用秒）。無論如何，您的表格應該包含最小的測量單位。

INSERT INTO [FactData]([dataItem],[UomID] )
SELECT [dataItem],[UomID]
FROM (
   SELECT 600 AS [dataItem], 1 AS UomID
   UNION ALL
   SELECT 3600 , 1 AS UomID
   UNION ALL
   SELECT 86400 , 1 AS UomID
   UNION ALL
   SELECT 60 , 1 AS UomID
) A;

現在是時候通過 SSAS 並建構我們的維度模型了。在您的數據源視圖中添加您的事實和維度表。

添加表後您會看到，我已將 dimUom 更改為命名查詢並添加了一個名為 AlternateParentID 的欄位，如下所示：

SELECT        UomID, UomName, ParentUomID, ConvertRate, ParentUomID AS AlternateParentID
FROM            dbo.DimUom;

我這樣做是因為我想通過該欄位將 FactData 與 DimUom 相關聯。我無法通過 ParentUomID 欄位將 dimUom 與 FactData 相關聯，因為我想在 ssas 中將 ParentUomId 用法設置為 Parent，並且您無法通過父欄位將事實表與維度相關聯。添加表並編輯命名查詢後，將以下關係添加到數據源視圖。FactData(UomID)  dimUom(AlternateParentID) dimUom(ParentUomID)  dimUom(UomID) 在解決方案的維度使用選項卡中建構多維數據集後，您將看到以下結構：

創建多維數據集後，轉到 dimUom 屬性選項卡並將 UomId 的 Namecolumn 屬性設置為 UomName 欄位以顯示測量單位名稱欄位而不是 Id 欄位。

現在我們必須將轉換率作為度量添加到多維數據集。因此，在解決方案的多維數據集結構選項卡中，選擇“dimUom”作為源表，ConverRate 作為源列。

添加新度量後，您的多維數據集應如下所示

結構如下

完成上述說明後，您必須在計算選項卡中創建計算度量，如下所示：

正如您在格式字元串部分可能已經註意到的那樣，我使用了腳本而不是預定義的格式字元串。如果計算選項卡的腳本視圖並按以下方式編輯計算公式，則可以執行此操作。

CREATE MEMBER CURRENTCUBE.[Measures].[ConvertedRateQty]
AS [Measures].[Data Item]/[Measures].[Convert Rate], 
FORMAT_STRING = case [Dim Uom].[Parent Uom ID].Currentmember 
WHEN
   [Dim Uom].[Parent Uom ID].&[2] THEN "#,#"
ELSE
   "#,#.0000"
END, 

現在是處理多維數據集並查看結果的時候了。處理多維數據集並轉到瀏覽器選項卡並將 DataItem 和 ConvertedRateQty 度量添加到度量部分。您將看到以下結果。

由於我們沒有指明任何度量單位，因此 ConvertedRateQty 顯示我們的度量 (dataItem = 90660) 除以 uomTable 的所有轉換率的總和 (1 + 60 + 3600 + 86400 + 7 * 86400 + 7 * 30 * 86400 = 18,838,861) 在我們的例如 90,660 / 18,838,861 = .0048 如果您從中選擇任何單獨的測量單位，您將看到轉換後的 DataItem。

或者您可以看到同時轉換的所有測量單位中的所有數量，如下所示：

引用自：https://dba.stackexchange.com/questions/140855