Data Type to use for Exchange Rate in SSAS Multidimensional

Depending on what type/volume of data you are reporting on, SSAS can possibly make it easier. I would not take that as a blanket statement. The decision to generate reports off of a relational or dimensional database should be made on a case by case basis.

Keep in mind that the development required to build an OLAP database around a subject area is far more time consuming than building a single report (or even several).

The benefits of building out an OLAP database are huge in return. In many cases, your users can connect directly to it with excel and generate their own reports. This eases the SSRS development requirements on IT.

At the end of the day, there are many factors that would contribute to answering your question. There is simply not a one size fits all solution.

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: