MDX Query in Excel from Olap Cube?

[largeselection]

Hello,

Not entirely sure if this is possible...

I'd like to somehow get a count of how many locations each player has scored 5 or more points in, details below.

Basically there is an Olap cube set up which I do not have access to change. As a simplified example the data that I have is set up as outlined:

• League - Team - Player# in a nested hierarchy (so you can expand a league and see the teams within, and expand a team and see the players within).
• I also have access to each of those individual fields as individual fields rather than hierarchical.
• I also have a list of locations and corresponding points scored by each player at each location.

So what I tried doing is making a regular pivot table arranged as:
League, Team, Player#, Location, Points

Then I put in a formula to the right of the pivottable that said if points>5, then 1, otherwise 0. Then I did a pivottable based on that pivottable and just summed up the new field that I had created at the Player# level.

The place where I run into problems is that I was able to do that with sample data, but the whole file is too big to do that because I have 10 leagues, 20 teams/league, 30 players/team, 150 locations/player, so by making the pivot table as I described that would be 900,000 rows and I ran out of memory. So I figured I'd ask the question here, because I don't need to see the actual locations/player if I could get a count of how many of those entries scored > 5 points. Then I could just have a file that has 6,000 rows only (10 leagues x 20 teams x 30 players) which shows the count of locations where > 5 points was scored.

Thanks in advance for any help, advice. If you know someplace better to pose this question I'm happy to do so there and read whatever.

 

[largeselection]

Forgot to mention...

I'm happy to accomplish this any way you can see feasible. I've dealt a little bit with Cube functions (cubevalue, etc) as well as creating named sets in the excel olap options a little bit with MDX.

 

[largeselection]

Any thoughts on this?

 

[kwhite100]

Have you found any good sources to do this? I am looking to accomplish something very similar. I also want to count but I want to count the number of players on the team that score points, so to say.

 

[largeselection]

Unfortunately I have not figured out a way to do this yet. If you figure anything out in your searches please let me know.

 

[kwhite100]

I figured out my solution. My solution will not work for yours however.

I might recommend writing a macro that searches through your column that you have returning a 1 or 0. Anytime that the result is a 1, use the offset function to return the game or place where or when that player scored 5 or more points. You can also possible use a formula that says, if=1, return the cell of the location desired. You do this for the whole column next to the 1s and 0s and then you can set a filter to somehow filter where there are places or times.

Just a thought on the run!