What is the simplest MDX request?
I want to test whether my MDX endpoint answers correctly to a very simple request.
In SQL, I would say SELECT 1;
views:
51answers:
4
+2
A:
I'm not sure that you can bring back constants in an MDX query - you've piqued my interest. I would just fire off a query against a base measure in your cube, if you don't specify any additional axis you'll get a single value result set.
SELECT [MeasureGroup].[Measure]
FROM [Cube]
Pretty basic - here's MS guide to the basic syntax for an MDX statement.
Bobby B
2010-07-20 10:42:36
That's quite simple, but I will wait a bit before accepting, to see if any constant-based answer comes up. Not requiring knowledge about the existing data's structure would be ideal.
Nicolas Raoul
2010-07-21 01:17:52
I expect you can bring back a constant in MDX, with something like WITH MEMBER [Measures].[Forty Two] AS '42' perhaps?
Magnus Smith
2010-07-21 11:25:46
+4
A:
This query will display your first measure against the top level of the first dimension it finds. All you need to specify is the name of your cube. It will return one number.
SELECT {Dimensions(0).defaultMember} ON ROWS,
{[Measures].defaultMember} ON COLUMNS
FROM [NameOfCube]
Magnus Smith
2010-07-21 11:23:52
Perfect! This request only requires to know a cube's name, which is not much to ask.
Nicolas Raoul
2010-07-22 01:17:52
A:
WITH MEMBER Test AS 1
SELECT Test ON COLUMNS FROM [Cube]
Constants all the way down.
Meff
2010-07-22 10:23:51
I get errors when running this... Is this an MDX request, or just a fragment?
Nicolas Raoul
2010-07-22 11:03:25
@Nicolas, this works fine in MS SSAS 2008, you will need to swap [Cube] for your own cube name. What error do you get?
Meff
2010-07-22 13:54:29
@Meff: Mondrian Error:Failed to parse query 'WITH MEMBER Test AS 1SELECT Test ON COLUMNS FROM [SalesAnalysis]'Mondrian Error:Error while parsing MDX statement 'WITH MEMBER Test AS 1SELECT Test ON COLUMNS FROM [SalesAnalysis]'Mondrian Error:Hierarchy for calculated member '[Test]' not found
Nicolas Raoul
2010-07-23 01:33:37
@Nicolas, I'm not familiar with Mondrian, so this could be a difference in provider syntax. Try enclosing the 1 in single quotes? If that fails, then perhaps this will only work in Microsoft's version of MDX :s
Meff
2010-07-23 09:33:27
@Nicolas, unless it requires the MDX to be "WITH MEMBER [Measures].[Test] AS '1'SELECT {[Measures].[Test]} ON COLUMNS FROM [Cube]" then I think it may be an MS MDX extension :(
Meff
2010-07-23 11:50:38
A:
I guess the simplest MDX request would be : SELECT FROM cubeName
Marc Polizzi
2010-08-25 11:58:01