Aggregating member properties or not aggregating distinct data

I have an employee, who received an initial starting bonus of $50k.
This value will be static from day 1 to day n and will never change. I
want to see the intial starting bonus, but any sets higher than the
employee dimension will need to aggregate the starting bonus. Is there
an easy way to do this?

If I just look at the data from an employee's perspective, I can do
this by making the measure a Min, Max, or Avg Aggregate function. But,
if for instance I want to view the data from the perspective of
departments, it would need to sum() the data instead (which min/max/avg
don't do).

If I make the starting bonus a member property of the employee, and a calculated measure off the member property, it aggregates the data when it shouldn't.

Hi, Alex:

Thanks for posting this the Database Journal forums, where many can benefit from a central location.

I can think of numerous approaches to this, and will list three, beginning with the "easiest." None of these involve elaborate MDX, as you seem to expect, but only require a knowledge of some of the options within MSAS components. Keep in mind that performance issues may dictate the "order of preference" in the below, as every situation is unique:

1. First, an easy approach can be seen in my article at the following URL:

http://www.databasejournal.com/featu...le.php/3107081

The rather obvious difference here is that we are working, in the article, with Square Feet in a member property, but that is so that the reader can follow along using the sample cubes that ship with MSAS. The same concepts apply if you take the member properties route (to which you seem pre-disposed in your description of the requirement) with your own need, if I understand what you are trying to accomplish.

Obvious drawbacks with this approach include the need to convert the property to values you can aggregate, etc., but the article offers guidance here, too.

2. A cube containing identical (relevant) dims, with a measure for the initial salary only, present a tempting consideration. This would give you optimal rollups, and has the added benefit of offering the opportunity to "pull in" the initial salary figure for infrequent uses into other cubes via a virtual cube, yet where you don't make it a permanent feature when it is not important in the standard cube. I discuss using virual cubes for similar purposes in many articles, a couple of which are at these urls:

http://www.databasejournal.com/featu...le.php/1555281

http://databasejournal.com/features/...le.php/3482536

The first article deals with virtual cubes in general; the second is much akin to the potential use I am suggesting, but uses a DISTINCT COUNT in the isolated cube, where you might consider supplying the initial salary measure. I even show a quick way to replicate the dimensions component of the cube to save time.

3. You could always add the initial salary as a measure in the primary cube, and simply hide it, or otherwise isolate it, so as not to confuse users - unless you've got information consumers in the cubes, this could be easly accomplished by simply not showing the measure in reports to avoid any such issues.

There are other approaches - perhaps a calculated member / lookup function (maybe to the isolated cube from the cube with which you wish to interact it), custom rollups in several possible perspectives, complex MDX in various ways, etc.. But I think these will be overcomplications, unless I seriously misunderstand what seems to be a fairly simple requirement.

I hope this helps. Let us know, if not, by posting back to this thread. In addition, consider posting back news of your successes, as well: the result will be a contained, problem-solution set posting here on Database Journal that many others can access and use!

Best,

Bill

This gets me further than I was, but I'm still running into problems. Unfortunately I'm wanting to look at things at levels that extend beyond just the scope [dimension] of the store and it's location:


I looked at it some more. If you add:

SUM(Descendants([Store].CurrentMember, [Store Name]) , Val(Store.CurrentMember.Properties("Store Sqft")))

as a calculated value into the Warehouse and Sales cube - and use any dimension other than, or in addition to, the Store dimension, the Total SqFt is inaccurately aggregated into the totals. Even if you don't pivot the data, it doesn't get filtered out properly. If you set a Filter of Time to 1997, none of the canada stores didn't do anything during 1997, yet their square footage shows up in that report. Just drag the time dimension to the left side of the store dimension while browsing the data in analysis manager.

In otherwords, anywhere which there is no fact entries for the parent of the member property, the member property is still included in a rollup. This is what I'm trying to figure out how to prevent. The grand total of square footage of all the stores with activity in 1997 is 271020, not 571596.

Let's migrate to FoodMart instead of my employee example.

Typo:
"none of the canada stores didn't do anything during 1997"

should be:

"none of the canada stores did anything during 1997"

The square footages I get not only appear correct at the rollup levels of the Store dim, but drill down correctly, as well, as you can see in this compressed image:

http://msas-architect.com/images/sqft.jpg

Moreover, your conclusion that Canada square footage, a member property, should not appear based upon a measure, in combination with another dimension entirely (time), is incorrect. Member properties belong to members, and are not time- (or any other dimension) "relative". Did you read up on the nature of member properties before you decided this was the direction to take? I tried to offer three approaches, specifically, but assume you didn't get to the others.

Frankly I can't see why you feel the member property approach above is not giving you correct numbers, either.

Try it again - and maybe some of the other ideas - then get back to us ...

Good Luck,

Bill