Is there a way to calculate the MODE in SSAS? I see there are Microsoft provided functions for Median, AVG, Max, and Min but nothing for Mode. Would I need to code a user defined function in .net to make this happen or is there an easier alternative?
views:
311answers:
3
A:
This should do it:
create table #temp (value int)
insert into #temp (value) values (1)
insert into #temp (value) values (1)
insert into #temp (value) values (1)
insert into #temp (value) values (2)
insert into #temp (value) values (2)
insert into #temp (value) values (3)
insert into #temp (value) values (3)
insert into #temp (value) values (3)
insert into #temp (value) values (3)
insert into #temp (value) values (3)
insert into #temp (value) values (3)
insert into #temp (value) values (4)
select value from (select top 1 count(*) as counts, value from #temp group by value order by count(*) desc) as myTemp
paperino
2010-01-12 18:44:03
This was tagged as an MDX question, not a SQL question. TSQL is not used in Sql Server Analysis Services so this won't solve my question. Mode is easy enough to calculate in Sql Server, or even SSIS, but I need it done in SSAS for my needs.
ShaneD
2010-01-14 18:17:54
+1
A:
I can see two possible alternatives. Creating a .Net stored proc is one, but there is a bit of overhead in calling those, so you would not want to run a Mode function over too many cells.
The other option, if there is not too large a number of distinct value that you need to operate across, is to create a dimension with an attribute based off the number. Then you could create a row count measure and get the mode by doing TOPCOUNT(... ,1) over the dimension with the numeric values.
Darren Gosbell
2010-02-23 04:15:08
A:
How about creating a fact dimension (Degenerate dimension) of the numbers you are interested in the mode of, then using that against a count of rows, take the Top Count of 1?
Or are you after a more general MDX calculation that you could use for may scenarios?
Meff
2010-02-25 16:17:27
I was after a more general calculation. I came up with this but it is so slow that it is unusable. It turned out that in my specific scenario the MODE was more than 50% of the values making it the same as the MEDIAN so I went ahead and used MEDIAN since it was built in. MEDIAN( [Billable Item].[Billable Item].[All].Children,[Measures].[Contractual Adjustment - Billable Item] )So far I have been unable to make it fast enough for production use though, it takes minutes on very small datasets. I think I need to use EXISTS or some other sort of filter to speed it up.
ShaneD
2010-05-04 18:20:59
@Shane - If this is a calculated member, setting the non empty behaviour is worth a go: http://msdn.microsoft.com/en-us/library/ms166568.aspx It should make it faster if it's not set, but be careful as setting it wrong can produce wrong results.
Meff
2010-05-04 20:06:05