How can I make this MDX work with OWC PivotTable (Using CommandText):
SELECT [Measures].[Project Count] ON COLUMNS,
NONEMPTYCROSSJOIN([Selection Factor].[Factor].Children,
[Selection Factor].[Factor Value].Children) ON ROWS
FROM PROJECTCUBE
WHERE NONEMPTY(INTERSECT(NONEMPTY([Project Profile].[Project ID].Children,
([Measures].[Selection Project Count],[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues])),
NONEMPTY([Project Profile].[Project ID].Children,([Measures].[Selection Project Count],[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]))))
There seems to be an issue with the types of WHERE clauses accepted by OWC. This query works perfectly (with good performance) using SQL Server 2005 Management Studio.
Thank you.
It's possible that having a set in the WHERE clause is the issue as OWC was writting before it was possible to do this. Although the query does not look valid to me as the [Selection Factor].[Factor] hierarchy is in the rows and the where clause and you cannot specify a hierarchy on more than one axis. And on top of that you are intersecting two sets which do not overlap, which would result in an empty set.
For example, take the intersection of two sets of months January to June and November to February, this would return a set of January - February as these are the only months in common. In your example the revenue and concurrent users members are independant and the intersection would be an empty set.
If you are just wanting to see the particular concurrent users and reveue members, maybe you were after something like the following.
SELECT [Measures].[Project Count] ON COLUMNS,
NONEMPTY(
{Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users],
, [Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]} *
[Selection Factor].[Factor Value].Children) ON ROWS
FROM PROJECTCUBE
Thank you for your response.
The WHERE clause in the MDX finds the intersection of all projects that have both the "selection factors". Project->Selection Factors (1:M). As a result, the column axis gives me the project counts that are specific to these specific projects. The row axis gives me all the selection factors that are part of the specific projects.
1. The WHERE clause is returning the Projects hierarchy and not the Selection Factors, although it looks for an intersection in the Selection Factors. Could that be the reason why the query does not fail because of the same hierarchy being used on two axes?
2. Why do you say that the intersecting sets do not overlap? Are you assuming that there is a 1:1 relation between projects and Selection Factors?
Thank you.
|||Sorry, you are right, I missed seeing a comma in the NonEmpty() function, which makes the difference between including the selection factors in the set that is being filtering and just using it as a criteria. Sorry about the confusion.
OWC is probably trying to parse the query to try and lay it out in the UI and as I said earlier, my guess is that it does not understand the set in the WHERE clause. You could try doing the following, which is kind of what OWC does with a multi member filter. Basically creating a calculated member that is the aggregation of the specified set:
WITH MEMBER [Project Profile].[Project ID].[Selected Projects]
AS
AGGREGATE(
NONEMPTY(INTERSECT(NONEMPTY([Project Profile].[Project ID].Children,
([Measures].[Selection Project Count],[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues])),
NONEMPTY([Project Profile].[Project ID].Children,([Measures].[Selection Project Count],[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]))))
)
SELECT [Measures].[Project Count] ON COLUMNS,
NONEMPTYCROSSJOIN([Selection Factor].[Factor].Children,
[Selection Factor].[Factor Value].Children) ON ROWS
FROM PROJECTCUBE
WHERE [Project Profile].[Project ID].[Selected Projects]
|||
Thanks again for your response.
The MDX that you suggest does not return any results (when I use the SQL Server 2005 Management Studio). I tried the Member without the Aggregate and with only one set (no intersection); it still does not return any results. Furthermore, OWC throws an error that the query could not be processed.
Since OWC is not going to be supported any longer, I suppose a better option is to use Reporting Services or Office 2007.
1. Will there be a web component in Office 2007 that can be embedded within a web page?
2. What is the natural migration for OWC applications with Sql Server 2005/Office 2007?
Thank you.
|||Unfortunately there is not web component in Office 2007 that can be embedded within a web page and in my opinion, there is no really natural migration path for OWC applications. I know some people state that Excel Services in Microsoft Office Sharepoint Server (MOSS) offers web based Excel rendering, but it does not have the same functionality that OWC does and it requires that you purchase MOSS licenses.
I'm not sure why creating a calculated aggregate member dif not work. Is either of the [Selection Project Count] or [Project Count] measures a calculated measure? The aggregate function gets calculated early in the solve order and this can cause issues with calculated measures. You could try switching to use the SUM() function.
WITH MEMBER [Project Profile].[Project ID].[Selected Projects]
AS
SUM(
NONEMPTY(INTERSECT(NONEMPTY([Project Profile].[Project ID].Children,
([Measures].[Selection Project Count],[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues])),
NONEMPTY([Project Profile].[Project ID].Children,([Measures].[Selection Project Count],[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]))))
)
SELECT [Measures].[Project Count] ON COLUMNS,
NONEMPTYCROSSJOIN([Selection Factor].[Factor].Children,
[Selection Factor].[Factor Value].Children) ON ROWS
FROM PROJECTCUBE
WHERE [Project Profile].[Project ID].[Selected Projects]
|||
Actually, there is only one measure [Measures].[Project Count]. There was a mistake in the query when I entered it in this thread first. My apologies! However, it is a regular measure (not calculated). In any case, I have tried SUM and it does not return any results as well.
WITH MEMBER [Project Profile].[Project ID].[Selected Projects]
AS
SUM(
NONEMPTY(INTERSECT(NONEMPTY([Project Profile].[Project ID].Children,
([Measures].[Project Count],[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues])),
NONEMPTY([Project Profile].[Project ID].Children,([Measures].[Project Count],[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]))))
)
SELECT [Measures].[Project Count] ON COLUMNS,
NONEMPTYCROSSJOIN([Selection Factor].[Factor].Children,
[Selection Factor].[Factor Value].Children) ON ROWS
FROM PROJECTCUBE
WHERE [Project Profile].[Project ID].[Selected Projects]
Creating an aggregated member is the standard way that OWC normally deals with a set in the filter, so I can't think why that would not be working.
But if we are only dealing with the [Project Count] measure, wouldn't putting the selection factor list straight in the axis definition work too? (if I am understanding what you are trying to do)
SELECT NON EMPTY [Measures].[Project Count] ON COLUMNS,
NON EMPTY
{
[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]
,[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]
} *
[Selection Factor].[Factor Value].Children ON ROWS
FROM PROJECTCUBE
WHERE [Project Profile].[Project ID].[Selected Projects]
1. I agree with you that OWC deals with sets in the filter. But what I have observed is that all the sets are explicit members. For instance,
WITH MEMBER [Project Profile].[Model].[XL_QZX] AS
'AGGREGATE({[Project Profile].[Model].&[116],[Project Profile].[Model].&[80]})'
I'm not sure how it deals with more complex expressions like the one I have.
2. Are you suggesting removing the Selected Projects member from the WHERE clause? I'm not certain I understand your suggestion since the [Project Profile].[Project ID].[Selected Projects] is still in the WHERE clause, but the Member definition is missing.
3. In any case, I'm not convinced that is the solution since what I need is to a) Get the projects that have the intersection of the selection factors and b) Get all the factors for this subset of projects.
Thanks, once more.
|||
Anna Mallikarjunan wrote:
1. I agree with you that OWC deals with sets in the filter. But what I have observed is that all the sets are explicit members. For instance,
WITH MEMBER [Project Profile].[Model].[XL_QZX] AS
'AGGREGATE({[Project Profile].[Model].&[116],[Project Profile].[Model].&[80]})'
I'm not sure how it deals with more complex expressions like the one I have.
Aggregate() just takes a set, Interset() returns a set which is why that was my first suggestion.
2. Are you suggesting removing the Selected Projects member from the WHERE clause? I'm not certain I understand your suggestion since the [Project Profile].[Project ID].[Selected Projects] is still in the WHERE clause, but the Member definition is missing.
Sorry, I should have taken that off. Since you don't appear to be referencing the set of projects anywhere else in the query, I'm not sure that it is required. It's hard to be sure, just by looking at one query, but breaking down to a set of projects only to add them all together, specifying the selection factors might be enough to get the same result and it should perform better.
SELECT NON EMPTY [Measures].[Project Count] ON COLUMNS,
NON EMPTY
{
[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]
,[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]
} *
[Selection Factor].[Factor Value].Children ON ROWS
FROM PROJECTCUBE
This query returns only the counts for the two factors in the set. This does not meet my requirements.
My original query:
SELECT [Measures].[Project Count] ON COLUMNS,
NONEMPTYCROSSJOIN([Selection Factor].[Factor].Children,
[Selection Factor].[Factor Value].Children) ON ROWS
FROM PROJECTCUBE
WHERE NONEMPTY(INTERSECT(NONEMPTY([Project Profile].[Project ID].Children,
([Measures].[Selection Project Count],[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues])),
NONEMPTY([Project Profile].[Project ID].Children,([Measures].[Selection Project Count],[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]))))
1. Finds the projects for which the two selection factors intersect,
2. Then returns all the factors that belong to the projects from the subset above.
|||
Sorry, I understand now. I was not considering that projects could have multiple factors.
The aggregate approach should work. Maybe the multiple dimensions in the set are the issue. If we follow the two aims in your last post and start by getting a set of project members that have both factors (I am using the extract() function to just pull out the project dimension). Then use try using that calculated member in the WHERE clause to achieve the second point.
WITH MEMBER [Project Profile].[Project ID].[Selected Projects]
AS
AGGREGATE(
EXTRACT(
NONEMPTY(INTERSECT(NONEMPTY([Project Profile].[Project ID].Children,
([Measures].[Selection Project Count],[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues])),
NONEMPTY([Project Profile].[Project ID].Children,([Measures].[Selection Project Count],[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]))))
,[Project Profile])
SELECT [Measures].[Project Count] ON COLUMNS,
NONEMPTYCROSSJOIN([Selection Factor].[Factor].Children,
[Selection Factor].[Factor Value].Children) ON ROWS
FROM PROJECTCUBE
WHERE [Project Profile].[Project ID].[Selected Projects]
|||
Darren- The Aggregate does not seem to work in this context. The Extract does return the set of projects but the Aggregate restricts the result to an empty set.
Perhaps there is something in the cube design that causes this? I will look into your suggestion further and see if I can narrow in on the problem.
Thank you,
Anna.
sql
No comments:
Post a Comment