MDX Total Question - Multiple Hierarchies

Question

Hi,

What I thought was an easy question has turned out to be much more difficult than I thought.

I have a dimension which has 2 levels

--Fund

---- Book.

I then have a Measure - Value.

I have then have multiple other hierarchies such as Instrument and Account.

I want to create a Calculated Member that returns the Total Value for the associated fund regardless of what other hierarchies are in the query. I call this "Fund Value"

So Far I have come up with

This = (Ancestor([Fund].[Funds].CurrentMember,[Fund].[Funds].[Fund]),[Measures].[Market Value-Closing]);

This works fine for a query which contains only the fund hierarchy. However, once I add in another hierarchy I get. As you can see at the total level it works fine but at the level of the other hierarchy I get the sum at that level. I for the life of me cannot get the calculated member to continue returning the total at fund level.

All help welcomed

Answer 1

You need to set the reference for the other dimensions to the all member for each and every other dimension if you don't want it to be applicable to that dimension.

Have a look at this post, which seems like a similar problem (if not a little simpler) than yours: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/2f0b0960-702f-4f95-9baa-7fef69b6a89b

Answer 2

Hi Michael,

Thanks for your reply. I can kinda see what you mean but can't quite work out how it would lookin the script

Are you able to give me an example? I have read that article and am not quite sure how it relates. I am new to MDX and my head is really starting to hurt now!

Geoff

Answer 3

Is your Market Value-Closing connected to Account Dimension if so why do you need to show at Fund Level ?

---

Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Answer 4

Hi,

Thanks for looking at my problem.

A lot of my measures will be sliced by quite a number of different dimensions (one of them being account). The result at each cell will then be expressed as a % of the Fund Market Value.

What I was trying to create was a way of easily referring to the Fund Market Value without having to rewrite the logic everytime I was writing a % of market value calculation

Thanks

Answer 5

Have actually decided to create another fact table that is just associated with date and book so the problem just goes away. I guess this is what Rakesh you were referring to.

As the Market Value of a Fund is made up by all its individual positions (that are associated with multiple different hierachies) I was hoping to be able to just use a sum at the fund level when doing % calculations.

However, this became way too complicated once other hierarchies are used in queries so it was simpler jus to create a second fact table with a relationship at the book level

Answer 6

Hi

I think what you will want is something like the following:

This = (Ancestor([Fund].[Funds].CurrentMember,[Fund].[Funds].[Fund])

          ,[Account].[Account].[All] <<Place any other dimension [all] members here too>>

          ,[Measures].[Market Value-Closing]);

So what you are saying here is, get the Ancestor of the fund current member and add any other dimensions All member for the Market Value-Closing. It is probably easier doing it this way than, trying to build a whole new measure group for just the book level, not to mention processing time.

Hope this helps

Mike

Answer 7

you can go to  your cube design and set the [Measures].[Market Value-Closing] and account dimension to be unrelated and set IgnoreUnrelatedDimensions=TRUE for [Measures].[Market Value-Closing] measure group.

---

Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Answer 8

Hi

I think what you will want is something like the following:

This = (Ancestor([Fund].[Funds].CurrentMember,[Fund].[Funds].[Fund])

          ,[Account].[Account].[All] <<Place any other dimension [all] members here too>>

          ,[Measures].[Market Value-Closing]);

So what you are saying here is, get the Ancestor of the fund current member and add any other dimensions All member for the Market Value-Closing. It is probably easier doing it this way than, trying to build a whole new measure group for just the book level, not to mention processing time.

Hope this helps

Mike

Hi Mike,

Thanks for the suggestion. Sorry I have not got back to this sooner but I have only just had the 5 minutes to come back to  the problem. So I have tried  your solution and it works some of the time.

I have one hierarchy with about 15 different hierarchies on it. It is the Instrument hierarchy which gives all the different characteristics of the shares we hold in a fund. When I add [Instrument].[All] where you say add <<other dimensions  [All] members here too>>  it wors for some of the hierarchies on the dimesnions but not others.

Do you have any suggestions why this would be

Many thanks ofr your help

Answer 9

and the hierarchies that seem to work all have the word instrument in front of them

Answer 10

I'll just interject in this thread with an example from AdventureWorks to help illustrate. Say that I have a requirement to report the Category Amount regardless of other dimensions referenced in the query or the other levels used in the Product dimension. To overwrite the context of the other dimensions, I can use the Root function on those Dimensions to overwrite the context of those dimensions with the All member of all attribute hierarchies in that dimension (if the All member exists, otherwise the default member is returned). The Category Amount measure would then be defined as the following:

WITH MEMBER [Measures].[Category Amount] AS
	(
		[Measures].[Reseller Sales Amount],
		Ancestor(
			[Product].[Product Categories].CurrentMember,
			[Product].[Product Categories].[Category]
		),
		Root ( [Product].[Standard Cost] ),
		Root( [Date] ),
		Root( [Geography] ),
		Root( [Delivery Date] )
	)

HTH, Martin

---

http://martinmason.wordpress.com

Answer 11

Thanks Martin and Mike - using the Root of each dimension in the Calculated member works a treat