SSAS/MDX Calculated member - Excel Mode function

Question

SSAS/MDX Calculated member - Excel Mode function

hutanu.mihai

Right there with Babe

Points: 741
More actions
January 31, 2014 at 1:25 am

#303853

Hello,
I need some help. I am quite new to MDX; I've created a cube (SQL Server 2012)and the measure needs to behave same as the MODE function in Excel (the following link describes what the function does). Apparently the only way of achieving this is by MDX (calling the MODE function directly from Excel is not an option for me). Practically the member should return a single value, that represents the price that appears most times. There are 4 dimensions in my cube: dates, stores, products and client products. I need the following calculations:
1. MODE(price) for specific Date, Product and Client Product. There will be multiple lines returned for this set, as there are multiple stores.
2. MODE(price) for specific Store, Product and Client Product. There will be multiple lines returned for this set, as there are multiple dates.
I found the below code online, that I've adapted to work against my cube, currently the query returns correct results for the first part, Mode(price) for multiple stores. I can change it to do the same for date.
What I need is to create 2 calculated measures using the last select and I am unsure how this can be achieved. Any help will be much appreciated or any other approach in achieving this.
WITH
--Count how often each value appears
MEMBER [Measures].[ValueCount] AS
SUM( Union([Dim Stores].Store].CurrentMember.Level.Members, {[Dim Stores].[Store].CurrentMember} AS CurrentStore),
IIF(([Dim Stores].[Store].CurrentMember, [Measures].[Price]) =
(CurrentStore.Item(0).Item(0), [Measures].[Price]), 1, null))
--Only get the items that appear the most
SET [MaxModes] AS
ORDER(FILTER(NONEMPTY([Dim Stores].[Store].Members, {[Measures].[Price]}),
[Measures].[ValueCount] = MAX(NONEMPTY([Dim Stores].[Store].[Store].Members, [Measures].[Price]),
[Measures].[ValueCount])), [Measures].[Price], ASC)
SELECT {[Measures].[Price]} on 0,
[MaxModes]
--Filter out the duplicates
HAVING [MaxModes].CurrentOrdinal = 0 OR [Measures].[Price] <> ([Measures].[Price], [MaxModes].Item([MaxModes].CurrentOrdinal - 2)) ON 1
FROM [old_Prices_v2]
WHERE {[Dim Date].[Date].&[2013-06-23T00:00:00]}*{[Dim Client Products].[Client Product].&[13]}*{[Dim Products].[Product].&[551]}

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

hutanu.mihai Right there with Babe Points: 741 More actions · Answer 1

Hi guys,

I would much appreciate an answer on the above. I need to know how I can change the last select to be able to create a calculated member.

Thanks

Mihai

PB_BI SSCoach Points: 17461 More actions · Answer 2

Can you not just install the Excel assembly on the server and then you can call the Mode function using Excel!Mode ?

I'm on LinkedIn

hutanu.mihai Right there with Babe Points: 741 More actions · Answer 3

Thank you for answering. Unfortunately due to license issues we cannot install MS Excel on the server.

Thanks,

Mihai

hutanu.mihai Right there with Babe Points: 741 More actions · Answer 4

I'm describing below what exactly is required:

The cube has 4 dimensions (Date, Store, Product, Client Product) and one measure (Price). I need a measure to behave same as the MODE function in Excel (value that occurs most often). Practically the member should return a single value, that represents the price that appears most times.

While browsing the cube I need, for any combination of dimensions/hierarchies, to get the MODE of the price at its lowest grain (that is Date, Store, Product, Client Product). No need to aggregate the measure in any way.

For example if someone looks at a specific combination of Product & Client Product, the calculated measure would show the price that appears most times against that Product+Client Product, in any of the stores and in any of the dates.

Example:

DateStore ProductClient ProductPrice

05-Feb-2013Store1Prod1ClProd150

05-Feb-2013Store1Prod1ClProd160

06-Feb-2013Store2Prod1ClProd160

06-Feb-2013Store2Prod1ClProd170

05-Feb-2013Store1Prod1ClProd280

05-Feb-2013Store1Prod1ClProd250

06-Feb-2013Store2Prod1ClProd250

06-Feb-2013Store2Prod1ClProd270

Case1: If one looks at Prod1 & ClProd1 then the MODE(Price) is 60 as there are 4 rows to be aggregated and the value that repeats most is 60

Case2: If one looks at Prod1 & ClProd1 & Store1 then the MODE(Price) is either 50 or 60 as there are 2 rows to be aggregated and both 50 and 60 repeat 1 time. I this case any of them can be returned (act like TOP 1 in TSQL)

Case3: If one looks at Prod1 & Store1 then the MODE(Price) is 50 as there are 4 rows to be aggregated and the value that repeats most is 50

Case3: If one looks at Prod1 & 06-Feb-2013 then the MODE(Price) is 70 as there are 4 rows to be aggregated and the value that repeats most is 70.

Any guidance will be much appreciated!

Thank you in advance.

Mihai

PB_BI SSCoach Points: 17461 More actions · Answer 5

I might be wrong here, but do you just need to create a set from what is in your select statement that can be used at any time with the [Demo] Member?

If so it would be (in AdventureWorks and adopted from the code here:http://blogs.adatis.co.uk/blogs/jeremykashel/archive/2012/03/19/mean-median-and-mode-in-mdx.aspx)

WITH

--Produce a result set that will guarantee bi modal results

MEMBER [Measures].[Demo] AS

CASE WHEN [Product].[Subcategory].CurrentMember IS [Product].[Subcategory].&[31] THEN 65.91

ELSE Cdbl([Measures].[Internet Sales Amount]) END

--Count how often each value appears

MEMBER [Measures].[ValueCount] AS

SUM(

Union([Product].[Subcategory].CurrentMember.Level.Members,

{[Product].[Subcategory].CurrentMember} AS Currentsub)

, IIF(([Product].[Subcategory].CurrentMember, [Measures].[Demo]) =

(Currentsub.Item(0).Item(0), [Measures].[Demo]), 1, null)

)

--Only get the items that appear the most

SET [MaxModes] AS

ORDER(

FILTER(

NONEMPTY([Product].[Subcategory].[Subcategory].Members, {[Measures].[Demo]}),

[Measures].[ValueCount] = MAX(NONEMPTY([Product].[Subcategory].[Subcategory].Members, [Measures].[Demo]),

[Measures].[ValueCount])), [Measures].[Demo], ASC)

SET Mode AS

FILTER([MaxModes]

--Filter out the duplicates

, [MaxModes].CurrentOrdinal = 0

OR [Measures].[Demo] <>

([Measures].[Demo], [MaxModes].Item([MaxModes].CurrentOrdinal - 2)))

SELECT {[Measures].[Demo], [Measures].[ValueCount]} on 0,

[Mode] ON 1

FROM [Adventure Works]

WHERE ([Date].[Date].&[20070727])

So when browsing the cube you can drop in the [Mode] set in conjunction with the [Demo] calculated member and then filter it by whatever you need and it should work.

sorry if I'm misunderstanding you :blink:

I'm on LinkedIn

hutanu.mihai Right there with Babe Points: 741 More actions · Answer 6

Thanks yayomayn however I decided to go a different path. I managed to get this fixed as follows: I've created a dimension that holds all price combinations and then a measure that would count how many times a price repeats. Then using a named set with TOPCOUNT I seem to get what I want. Everything seems to work fine in Management Studio but when I create the same Named Set in client application (Dundas Dashboard) I seem to get different result. I am convinced that the MDX query I created is not the best therefore I would like someone to have a look at it and advise if it needs changing and how. Also the performance seems quite poor.

WITH SET Mode AS
GENERATE(
filter(
[Dim Date].[Week].[Week].members
*[Dim Products].[Product].[Product].members
*[Dim Products Pivote].[Product Pivote].currentmember
, [Measures].[Price])
,
TOPCOUNT(
[Dim Price].[Price].[Price]*
[Dim Date].[Week].currentmember*
[Dim Products].[Product].currentmember
, 1, [Measures].[Price Count]))

Now to explain the code above: TOPCOUNT returns the price that appears most times and I use GENERATE to get this price against a specific Product Pivote, Product and Week.

SELECT
[Measures].[Price] on 0,
Mode on 1
from [cubename]
where [Dim Products Pivote].[Group - Family - Product Pivote].[Product Pivote].&[13]

The above select works fine in SSMS and returns exactly what I expect. That is a result set containing Weeks & Products, the Price that appears most and how many times it appears; this result set runs against a parameter restricting the Product Pivote on the dashboard level (the one appearing in WHERE clause) and counts the number of Shops that have a specific price (Shop is the only dimension that is left - not appearing in my query at all). For some reason the dashboard returns strange results, quite close to the correct results but still are incorrect.

Let me know if you need anything else.

Thanks in advance,

Mihai

PB_BI SSCoach Points: 17461 More actions · Answer 7

I have had some (quite brief) dealings with Dundas and I know that if you write manual mdx then you lose some functionality in regards to drilldown but I'm not sure that this would affect what you are doing. It will probably be something to do with your parameter in Dundas, can you supply the exact query as written in Dundas including parameterisation?

I'm on LinkedIn

hutanu.mihai Right there with Babe Points: 741 More actions · Answer 8

I've lost the whole day trying to find out what goes wrong. Still couldn't figure it out. I will do my best to as many details as possible, maybe you can figure it out.

The below image shows the Named Set in Management Studio and results it returns for a specific Producto Pivote. These are correct results (tested)

(in case the image does not work, follow this link)

The below image show the Named Set as it was created in Dundas (Administration - Token screen). The columns are missing from TOPCOUNT so that I can use Parameters without getting the error (That the dimension is used more than once). I have tried with a query identical to the one in the image above (SSMS image) and use other hierarchies with the same grain in Dundas screen, makes no difference.

(in case the image does not work, follow this link)

The below image shows Dundas KPI with parameters and generated results. Parameters are as follows:

- Precio - this is the actual measure used as dimension, the one that is being counted. As you see, the default value points to the Named Set described above.

- Semana, Producto are used as Axis

- Producto Pivote is a single value parameter

(in case the image does not work, follow this link)

A strange thing that is happening is that, if I select a specific Producto instead of showing all available Productos, it sometimes show the correct information. For other Productos it doesn't. This makes me think that the Named Set is wrong. But again, why does it work in SSMS then?

I think I'm going crazy :hehe:

Any guidance is much appreciated!

Regards,

Mihai

PB_BI SSCoach Points: 17461 More actions · Answer 9

Hi,

Sorry for the late reply I was busy yesterday!

I honestly can't see anything obvious but I'm still pretty sure it has something to do with the use of currentmember in the set and your parameter. Can you run a profiler trace on the ssas instance and then run your query on Dundas (the one showing incorrect results) and post the query from the profiler here?

Also you might want to try defining your set on the cube itself rather than in Dundas. It shouldn't make a difference but you never know with third party things.

I'm on LinkedIn