SSAS Dimension attribute as Calculated Measure

3
votes

I am having some issues trying to implement an average of a dimension attribute.

The basic structure is:

  • Booking Header Dimension
  • Fact Table (multiple rows per Booking Header entry)

On the booking header dimension I have a numerical attribute called Booking Window, and I want to be able to create a calculated measure that averages this value.

We are using SQL Server 2012 standard edition.

Any help would be greatly appreciated.

3
sql-server-2012ssasmdxdata-warehouse
Hello, I am not sure this really makes much sense. You say you want to create an average of a dimension attribute. What sort of query do you wish to write? You can create an Average as a Measure (so any column in your Fact table). You can't create an average for a dimension attribute. If you can provide more details about what you are trying to achieve we can hopefully provide more help. Ash – The Dumb Radish
In order to retain appropriate granularity in the fact table, I could not include booking window in the fact table. I am trying to do the average of the booking window that is in the dimension, I have seen quite a few examples of this been done, so I know it is possible. Exactly how to do it is eluding me at the moment – OCDan
Could you give me in plain English the sort of query you want to perform? If you want an average of the Booking Window attribute, that suggests you have a range of Booking Window members over which you wish to perform the average. Is this range fixed (for example the average over every memmber) or is it subject to user selection? – The Dumb Radish

3 Answers

4
votes

The best approach would be to create a measure group from the dimension table (in BIDS, go to cube designer, tab "Cube Structure", right-click the cube object in the Measures list, and select "New Measure Group", select your dimension table). BIDS will generate some measures, and you can remove all but two: the one based on your numeric attribute (I will call it "YourSummedAttrib" to have a name to refer to below), and the count measure. The aggregate function for the measure "YourSummedAttrib" will probably be "sum", leave that as it is.

Then, create a calculated measure which divides "YourSummedAttrib" by the count measure, which gives the average. Finally, if you have tested everything, make the two measures "YourSummedAttrib" and the count measure invisible before you give the cube to the users, as they only need to see the average, which is the calculated measure.

1
votes

You can try this which should give you the average of that attribute across all members.

WITH MEMBER [Measures].[Booking Window Value] AS
[Booking Header].[Booking Window].CURRENTMEMBER.MEMBER_VALUE
MEMBER [Measures].[Avg Booking Window Value] AS
AVG([Booking Header].[Booking Window].[Booking Window].MEMBERS,[Measures].[Booking Window Value])
SELECT
[Measures].[Avg Booking Window Value] ON COLUMNS
FROM
[YourCube]

Hope that helps and apologies for any confusion on my part.

Ash

1
votes

I tried to use the same idea, but without success. The solution I found was create a view with the calculated average and include a new group of measures.