April 5, 2012 at 6:48 am
hi everyone
i have values in 100 different columns (Value01 -> Value99) in one table and now i have to find out which of them are in Top 5.
i made following t-sql statement
SELECT TOP 5 (Agg.Col)
FROM (SELECT
Value01 AS Col FROM dbo.FaktIntervall UNION SELECT
Value02 AS Col FROM dbo.FaktIntervall UNION SELECT
Value03 AS Col FROM dbo.FaktIntervall UNION SELECT
Value04 AS Col FROM dbo.FaktIntervall UNION SELECT
Value05 AS Col FROM dbo.FaktIntervall UNION SELECT
Value06 AS Col FROM dbo.FaktIntervall UNION SELECT
Value07 AS Col FROM dbo.FaktIntervall UNION SELECT
Value08 AS Col FROM dbo.FaktIntervall UNION SELECT
Value09 AS Col FROM dbo.FaktIntervall UNION SELECT
Value10 AS Col FROM dbo.FaktIntervall UNION SELECT
Value11 AS Col FROM dbo.FaktIntervall UNION SELECT
Value12 AS Col FROM dbo.FaktIntervall UNION SELECT
Value13 AS Col FROM dbo.FaktIntervall UNION SELECT
Value14 AS Col FROM dbo.FaktIntervall UNION SELECT
Value15 AS Col FROM dbo.FaktIntervall UNION SELECT
Value16 AS Col FROM dbo.FaktIntervall UNION SELECT
Value17 AS Col FROM dbo.FaktIntervall UNION SELECT
Value18 AS Col FROM dbo.FaktIntervall UNION SELECT
Value19 AS Col FROM dbo.FaktIntervall UNION SELECT
Value20 AS Col FROM dbo.FaktIntervall .....) AS Agg
ORDER BY Agg.Col DESC
this T-SQL query is really slow and that's why i would like to rewrite as a MDX to use it in a CUBE. is it possible ? if yes, how ?
April 5, 2012 at 2:47 pm
I think you mean MDX. You cannot directly translate SQL to MDX. Even though there are some superficial similarities, they are completely different languages, because they query completely different structures.
You haven't specified how your Value columns were implemented in your cube, so we can't tell you the corresponding and we don't know what dimensions you have defined, so we can't give the exact command, but look up TOPCOUNT() which requires a set and a measure.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 7, 2012 at 5:18 am
Hi Drew
thanks for your message. yes i'm talking about MDX. I have to show the Top 5 Values from all the ValueXX-Columns (ValueXX 01 - 99).
is it possible to do that with TOPCOUNT ? can i use 99 different Columns ?
April 12, 2012 at 1:58 am
The Tablestructure of my Fact-Table was totally wrong. I changed it and now its working.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply