SSAS – Applying Conversion Rates for Units of Measure

ssas

So I've been playing with this for the last day or so, and haven't been able to get an closer.

I have a SSAS cube that has a measure group which displays total execution times for events in seconds. What I'm looking to do is give our users the option to select which time unit they want to display their results in (ie. minutes, hours, days, weeks, etc.).

On the surface, it seems similar to performing currency conversion. So using AdventureWorks as my template, I created 2 additional tables.

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

With that complete, I added the duration unit dimension and the conversion rate measure group to my cube, and updated my total duration measure with a measure expression ([total duration] / [conversion rate]). And it's at this point I'm stuck. Because there isn't a bridge between the conversion rate table and my fact table (ie. the exchange rate date like in Adv.Wrks), I can't define the relationship in the dimension usage tab. As well, the lack of a time dimension is preventing me from using any of the semi-additive measures adventure works uses (AverageOfChildren, LastNonEmpty) on the conversion rate measure.

Anyone know how I can get this to work? I've been searching and trying things, and can't seem to come up with a solution for what I thought would be a common problem.

Best Answer

I didn't have the enough reputation to be able to upload my images to the site. I hope the descriptions could help. If you want to see the complete document to this problem you can send me an email and I will send you back the complete doc with images. My Email Address is: alireza.marahemi@gmail.com

Suppose you have the following data as measurement unit 

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

Your fact table contains the data based on your smallest unit of measurement (in this case seconds). you want to be able to select other measurement units (e.g Hour) and your measure (Time in our example) displayed according to your selection. For instance if sum of your data items is 1200(sec) if you select Minute the result should be 20. In this case I assume you do not want to add extra measures for each unit of measurement to your data. Here is the design method. First you have to create your Measurement unit table in following manner:

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

I have used the fields ParentUomID and ConvertRate to identify the base measure and ConverRate of current measure to base measure. you can fill this table with the following script.

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;

Your fact data can be as simple as the following fact table with only 3 fileds DataID as Primary key( not needed actually) your DataItem and your measurement unit.

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

The following script adds a minimal data to your table. You have noticed that I used only the TimeMaster measurement unit to the table( Actually I could have used only seconds). Anyhow your table should contain the smallest measurement unit.

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;

Now it is time to go through SSAS and build our dimensional model. In your data source view add your fact and dimension table.

You see after adding the tables I've changed the dimUom to a Named Query and added a field named AlternateParentID as following:

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

I've done this because I want to relate FactData to DimUom via this field. I couldn't relate dimUom to FactData by ParentUomID field as I want to set ParentUomId usage as Parent in ssas and you can not relate a fact table to a dimension by a parent field. After adding tables and editing the named query add the following relations to data source view. FactData(UomID)  dimUom(AlternateParentID) dimUom(ParentUomID)  dimUom(UomID) After building the cube in the dimension usage tab of the solution you will see this structure:

After creating the cube go to dimUom attribute tab and set Namecolumn attribute of UomId to UomName field to display measurement unit name field instead of Id field.

Now we have to add the convert rate as a measure to the cube. So in the cube structure tab of the solution and select the "dimUom" as source table and ConverRate as the source column.

After adding the new measure your cube should look like this

and the structure as follows

After doing above instructions you have to create a calculated measure in the calculation tab as follows:

As you may have noticed in format string section I used a script instead of predefined format string. You can do this if script view of calculation tab and editing the calculation formula in following way.

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,

It is time to process the cube and view the results. Process the cube and go to the browser tab and add DataItem and ConvertedRateQty measures to the measure section. You will see the following results.

As we did not indicate any measurement unit the ConvertedRateQty shows our measure ( dataItem = 90660) divided by sum of all conversion rate of uomTable ( 1 + 60 + 3600 + 86400 + 7 * 86400 + 7 * 30 * 86400 = 18,838,861) In our example 90,660 / 18,838,861 = .0048 If you select any individual measurement unit from you will see the converted DataItem.

Or you can see all the quantity in all measurement units converted simultaneously as follows:

Related Question