Hi,
I have been trying to write an MDX query which restricts the data based on a date range on the time dimension.
I am able to frame this query on the GUI interface of the Business Intelligence Studio (Using the cube browser) But I want the actual MDX for it.
To be specific, I am trying to view how the "Range (Inclusive") operator is implemented in the "Browser" Tab of the Cube Editor in the Business Intelligence Studio.
I have a Time dimension which has Year, Quarter, Month, Day Hierachy and I need to form the query such that I have a couple of pre-defined CALCULATED measures on the Column axis and Another dimension on the Row axis. I want to limit the records to a Range (Say Jan 1, 2007 to Aug 5, 2007).
My MDX works when it is for a single value of the Time Dimension.But for a range, I get nulls in the calculated measures.
My MDX is :
WITH
MEMBER [Time-By Calendar].[Time-By Calendar].[Selected Time] AS '
AGGREGATE({{[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1].parent.parent.lastChild.lastChild}
+ {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1].parent.parent.nextMember:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5].parent.parent.prevMember}
+ {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5].parent.parent.firstChild.firstChild:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}})
', SOLVE_ORDER = 5001, SCOPE_ISOLATION=CUBE
SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,
DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)
FROM [RESOURCE SUMMARY]
WHERE [Time-By Calendar].[Time-By Calendar].[Selected Time]
=====================================
Where the calculated MEASURE.Agency is pre-defined as:
CREATE MEMBER CURRENTCUBE.Measures.[Agency] AS
'IIF(NOT([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL) AND IsLeaf([Resource-By Pool].[Resource-By Pool].CurrentMember),
IIF(NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Pool].&[].&[0]},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).count > 0,
"",NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Name].members},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).item(0).item(0).name),"")', SOLVE_ORDER = 5000;
Any help is greatly appreciated.
Regards,
Mehernosh
I'm not entirely sure, but if I read your statement correctly all you are trying to achieve is to get the aggregate of the dates from Jan 1 to Aug 5. In which case you should be able to do the following.
SELECT {[Measures].[Available Hours], [Measures].[Agency] } ON AXIS(0) ,
{[Resource-By Pool].[Resource Name].members} on AXIS(1)
FROM [RESOURCE SUMMARY]
WHERE {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}
And if you are after a YTD amount it could be expressed even more concisely as:
SELECT {[Measures].[Available Hours], [Measures].[Agency] } ON AXIS(0) ,
{[Resource-By Pool].[Resource Name].members} on AXIS(1)
FROM [RESOURCE SUMMARY]
WHERE {YTD([Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5])}
I'm not sure why you are using a measure for the agency name and not just crossjoining in the Agency dimension:
SELECT {[Measures].[Available Hours] } ON AXIS(0) ,
NON EMPTY {[Resource-By Pool].[Resource Name].members} *
{Agency-By Pool].[Agency Name].members} on AXIS(1)
FROM [RESOURCE SUMMARY]
WHERE {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}
There are a couple of issues with your calculated measure. This statement "NOT ([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL)" will always return true as the IS operator does a member comparison not a value comparison and CurrentMember will always return a valid member. I think what you probably meant was "NOT IsEmpty(Resource-By Pool].[Resource-By Pool].CurrentMember)". But I would have thought including the Agency dimension in the row axis would have done a similar thing (unless agency &[0] has some other label)
|||
Hello Darren,
Thank you for your response. You are correct that I want the aggregate of the Hours for a Resource, but also want the value of the Agency dimension to be displayed along, BUT as a measure.
The MDX you provided, (with the cross join on Row Axis), certainly works. But alas I need the AGENCY to be a measure on the Column axis.
The reason is that our enterprise application has a customizable Portfolio management component (that uses Analysis Server) wherein Users can add/remove a set of predefined measures to their view. Very much like how the Cube browser does, but with limited functionality such that only predefined measures can be dropped on to the Column axis. And the ROWS are limited by Time dimension. The application generates the MDX based on the set of measures provided. Hence we need each measure to be declared separately as 'CREATE MEMBER CURRENTCUBE.Measures'.
Also the switch to using "NOT IsEmpty" instead of "NOT null" check in the definition of the Agency measure did not make any difference. The measure value still returns as null for each row.
The funny thing is that this query WORKs in AS2000. But as we were porting our application to use AS2005 we ran into this issue.
But I see that when I do a similar thing with the "Cube Browser", we get the correct result. (Agency is declared as a calculated measure on the cube)
Hence I wanted to see the MDX query that is generated by the Cube browser. Should I listen on any particular port to get the MDX that is sent to the Analysis Server. Is the traffic encoded
Any help will be highly appreciated as the porting of the application to AS2005 has stalled because of this issue
Regards,
Mehernosh
|||
 Mehernosh Vadiwala wrote: |
| |
| The funny thing is that this query WORKs in AS2000. But as we were porting our application to use AS2005 we ran into this issue. |
You did not mention that this used to run under AS2000, on double checking the calc I noticed that you are referencing the [Resource-By Pool].[Resource-By Pool] attribute hierarchy. At the attribute hierarchy level there is usually a default "All" member so the count will always be 1. If you add an extra [Resouce-By Pool] reference to specify just the level in the attribute hierarchy with the actual members.
CREATE MEMBER CURRENTCUBE.Measures.[Agency] AS
'IIF(NOT([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL) AND IsLeaf([Resource-By Pool].[Resource-By Pool].CurrentMember),
IIF(NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Pool].&[].&[0]},{[Resource-By Pool].[Resource-By Pool].[Resource-By Pool].CurrentMember}).count > 0,
"",NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Name].members},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).item(0).item(0).name),"")', SOLVE_ORDER = 5000;
| Mehernosh Vadiwala wrote: |
| |
| Hence I wanted to see the MDX query that is generated by the Cube browser. Should I listen on any particular port to get the MDX that is sent to the Analysis Server. Is the traffic encoded |
Yes, the traffic is encoded, but you can use SQL Profiler to connect to SSAS in 2005 and see the begin and end query events which have most of the MDX, some of the cube browser controls create named sets within the session, so you might need to assemble the MDX from a couple of trace statements.
|||
Darren,
This query used to work in AS2000, but I had already ported it for AS2005. i.e. I had already added the additional hierarchy attribute. (If you notice, I already have [Resource-By Pool].[Resource-By Pool].currentMember in the query I gave above. The SQL2000 version had only [Resource-By Pool].currentMember )
But that was the only change I made to the Query.
Anyway I found the solution. All I had to do was not declare the slicer axis (WHERE clause) in place, instead of with a separate Member.
======= Not Working =================================
WITH
MEMBER [Time-By Calendar].[Time-By Calendar].[Selected Time] AS '
AGGREGATE({[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5})
', SOLVE_ORDER = 5000, SCOPE_ISOLATION=CUBE
SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,
DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)
FROM [RESOURCE SUMMARY]
WHERE [Time-By Calendar].[Time-By Calendar].[Selected Time]
======= WORKING =================================
SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,
DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)
FROM [RESOURCE SUMMARY]
WHERE ({[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5})
=================================================
Any insight on why this works and not the other ?
Also I have a bigger issue coming up with "LookupCube" function. None of our calculated Measures that have the LookupCube function in them return any results now. In the documentation, there is just one line, stating that the implementation of LookupCube has changed. There is no additional information.
Could this be the reason? Is there some documentation on this ?
Mehernosh
|||
| Mehernosh Vadiwala wrote: |
| |
| This query used to work in AS2000, but I had already ported it for AS2005. i.e. I had already added the additional hierarchy attribute. (If you notice, I already have [Resource-By Pool].[Resource-By Pool].currentMember in the query I gave above. The SQL2000 version had only [Resource-By Pool].currentMember ) |
Sorry, you're right the formula has already been adjusted correctly.
| Mehernosh Vadiwala wrote: |
| |
| Anyway I found the solution. All I had to do was not declare the slicer axis (WHERE clause) in place, instead of with a separate Member. |
Yeah, that was what I suggested in my first response.
| Mehernosh Vadiwala wrote: |
| |
| Any insight on why this works and not the other ? |
The only reason I could think that this might have issues would be if you [available hours] measure is calculated and relies on having a [Time-by Calendar] context. A cacluated aggregate in the query will not supply such a context and the formula would calculate as if it were at the all level. The Aggregate function is meant to calculate early in the solve order, but your setting of the solve order may be overriding this.
| Mehernosh Vadiwala wrote: |
| |
| Also I have a bigger issue coming up with "LookupCube" function. None of our calculated Measures that have the LookupCube function in them return any results now. In the documentation, there is just one line, stating that the implementation of LookupCube has changed. There is no additional information. |
I don't use LookupCube any more I use multiple measure groups, possibly linking them in if they are shared across more than one cube.
