Unable to select dimensional values along with measures in MDX
-
Friday, December 14, 2012 4:25 AM
Hi All,
I'm new to MDX and I'm trying to select some dimensional values along with measures on rows. The dimensional attribute that i want is metro id, but I'm not able to figure a way to do this.
WITH MEMBER [pastyear] AS "[Time].[Date].&[" + Format(NOW(), "yyyy-MM-dd") + "T00:00:00]"
SELECT
{[Measures].[RomanticPicksPCT],[Geography].[Metro ID].[Metro ID]}
ON COLUMNS,
(topcount --to select top x rows only
(filter -- Threshold filters
(order --by CategoryPCT descending
( order --by RestaurantName ascending
(crossjoin -- Tu show both WebRID and Rest Name
([Restaurant].[Restaurant Name].[Restaurant Name],[Restaurant].[Web RID].[Web RID]),
[Restaurant].[Restaurant Name].[Restaurant Name],asc
),[Measures].[RomanticPicksPCT],bdesc
),[Measures].[AvgOverallRating] >= 3 and [Measures].[RomanticPicksPCT] >= .25
),2000
)
)
ON ROWS
FROM [TopTenCube]
WHERE
(
LastPeriods(365,StrToMember([pastyear])) -- for past 365days rolling period
)
It gives an error: "Query (3, 1) Two sets specified in the function have different dimensionality."
Please help
All Replies
-
Friday, December 14, 2012 5:05 AMModerator
You have tried to specify your column set at a member from the measures dimension and a level from Geography, but your level reference is potentially ambiguous so SSAS is not sure if it's a level or a member reference.
so where you have this on the column axis
{[Measures].[RomanticPicksPCT],[Geography].[Metro ID].[Metro ID]}
You should probably have something like this
{ {[Measures].[RomanticPicksPCT]} * [Geography].[Metro ID].[Metro ID].Members }
Note that your nested ORDER() functions are redundant, the outer one will override the inner one. If you want to sort restaurants with the same RomanticPicksPCT score by name you need to come up with some composite measure for sorting.
http://darren.gosbell.com - please mark correct answers
-
Friday, December 14, 2012 5:23 AM
Thanks Darren, that helps a lot, but I still don't quite have what I need. This gives me something like
RomanticPicksPCT RomanticPicksPCT RomanticPicksPCT RomanticPicksPCT
Demoland Seattle / Eastern Washington Chicago / Illinois San Francisco Bay Area
ABC Hotel 123 0.00% 0.00% 0.00% 0.00%So the cross join you suggested jut dumps all the metros. I only want the metro ID relevant to that particular Restaurant.
To rephrase what I need, below is the query that gives me the percent ratings along with the restaurant name and rest ID, now I want a column metro ID which would give me the metro id corresponding to that restaurant only.
WITH
MEMBER [pastyear] AS "[Time].[Date].&[" + Format(NOW(), "yyyy-MM-dd") + "T00:00:00]"
SELECT
[Measures].[RomanticPicksPCT]
ON COLUMNS,
(topcount --to select top x rows only
(filter -- Threshold filters
(order --by CategoryPCT descending
( order --by RestaurantName ascending
(crossjoin -- Tu show both WebRID and Rest Name
([Restaurant].[Restaurant Name].[Restaurant Name],[Restaurant].[Web RID].[Web RID]),
[Restaurant].[Restaurant Name].[Restaurant Name],asc
),[Measures].[RomanticPicksPCT],bdesc
),[Measures].[AvgOverallRating] >= 3 and [Measures].[RomanticPicksPCT] >= .25
),2000
)
)
ON ROWS
FROM [TopTenCube]
WHERE
(
LastPeriods(365,StrToMember([pastyear])) -- for past 365days rolling period
)Thanks
Abhishek
-
Friday, December 14, 2012 5:26 AMCan I use a calculated measure based purely on a dimension and if yes, how do I make it context aware?
-
Friday, December 14, 2012 5:35 AMModerator
So the cross join you suggested jut dumps all the metros. I only want the metro ID relevant to that particular Restaurant.
Then you have to put the Metro ID on the same axis as the restaurant. SSAS always evaluates the different axis completely independantly. If Metro ID is directly related to a given restaurant then it possibly should not even be modelled as an independent dimension.
http://darren.gosbell.com - please mark correct answers
- Marked As Answer by Abhishek_007 Friday, December 14, 2012 5:44 AM
-
Friday, December 14, 2012 6:32 AM
Thanks Darren, that nails it.
The only problem that I have now is now I have 3 attributes on that axis none of which have labels.
I know its is not possible to directly alias attribute names, is there a hack to this?
The Geo is a conformed dimension used by other cubes too, hence used it.
-
Friday, December 14, 2012 8:15 AMModerator
The only problem that I have now is now I have 3 attributes on that axis none of which have labels.
I know its is not possible to directly alias attribute names, is there a hack to this?
I don't understand what you are asking. Are you asking if you can change the attribute names which you say you can't see?
The labels on the query output vary depending on the client tool, so that's a hard one to answer.
One of the primary foundations of SSAS is consistent metadata. So you can't change attribute names on the fly. The only hack I'm aware of is to use translations and hijack another language for alternate names. But then you need to specify the locale ID on the connection string to see the alternate names which is not always easy or convenient.
http://darren.gosbell.com - please mark correct answers
-
Friday, December 14, 2012 1:16 PM
The labels on the query output vary depending on the client tool, so that's a hard one to answer.
One of the primary foundations of SSAS is consistent metadata. So you can't change attribute names on the fly. The only hack I'm aware of is to use translations and hijack another language for alternate names. But then you need to specify the locale ID on the connection string to see the alternate names which is not always easy or convenient.
http://darren.gosbell.com - please mark correct answers
I'm talking about the Labels on the Query output, I referred to them as aliases, sigh of my SQL roots.
Is there a way to change them in SSMS window?
.png)
