March 24, 2008 at 10:21 am
Products and Categories is actually slightly over-simplified for what I'm looking at, but it's an example everyone can wrap their heads around.
Here's the situation:
I have 2 products with the same Description. Let's say Adult Bike. These products show up in 2 different Categories: Mountain Bike and Road Bike. In SSAS I have a dimension called Product. It has attributes for Product Description and Product Category. There is an Attribute Relationship between Product Category and Product Description. There is also a hierarchy with the same structure.
The problem:
I seem to have to choose between 2 problem situations. I can either leave Product Description as the Key Column, without a NameColumn, in which case I get all the Adult Bikes lumped into 1 of the Product Categories, seemingly arbitrarily chosen at Processing. Or I can add Product Category to the KeyColumns list, in which case I get Adult Bike in both of it's Product Categories, but I also see Adult Bike twice in the members list since there are sort of 2 instances of it. This is most prevalent in Excel 2007.
In my actual example, there aren't just 2 instances in the problem scenario, there are dozens. So I would end up with a filter list with 1000s of extra entries.
Basically what I'm hoping for is a best of both worlds solution: single instances in the members list (select distinct Product Description, basically), but the correct key in the background.
I'm sure I'm not the first person to encounter this, so hopefully someone else has a clever solution!
Thanks in advance,
Rick Todd
March 24, 2008 at 11:12 am
Rick,
Are they actually two different products, or the same product you want to see under two different parents in the hierarchy?
If they truly are two different products then I'm not sure why you're trying to do what you're trying to do?? Showing them as a single member under the Product Description attribute is technically incorrect.
One way you could try to achieve this (no guarantees 🙂 )is to create a new field (either in your view or perhaps a calc field in the DSV), make this the distinct member names. Add this field as a attribute hierarchy, then make sure it's attribute rlationship is set correctly. Make sure you mod your current Product Desacription to create nique instances (e.g. Adult bike A and Adult Bike B for the multiple instances). You can then create a user hierarchy that goes Product Description. You then have to mod your current user hierarchy to esre it uses the Product Description field.
So using new hierarchy, you might see 1st level members of Adult Bike, Kids Bike, My Old Bike, and the children of Adult Bike might be Adult Bike Cat1 and Adult Bike Cat2, but it will let you see the combined total of all Adult Bike sub cats.
Your current Category -> Product Description hierarchy will work as normal, but when drilling from Cat1 to the next level, instead of Adult Bike as a child, you'll see AdultBikeCat1.
HTH,
Steve.
March 24, 2008 at 11:14 am
Arggh, smileys gone wild... just imagien the wink is a closing bracket and it reads sorta ok...
Steve.
March 24, 2008 at 12:04 pm
I realize they're not technically the same product, I just want a distinct list of the descriptions.
The actual scenario is Discounts, and Discount Reasons. For example we have a AAA (American Automobile Association) Discount. This can be used with a multitude of different discounts. I want the DW users to be able to choose AAA Discount from the filter list, and see totals for that Discount Reason across all the Discounts it's been used for. But I also want them to be able to use a hierarchy with Discount and Discount Reason to drill from 10 % Admission Discount into AAA Discount.
I also ran into this same problem with our product model, but worked around it by using the business key for the Product. There are now 2 instances of the Product in the filter, but since there are only a handful of repeated products, it's not a huge issue.
I'll tinker with the solution you proposed, but it seems like a lot of work. What I was hoping for was a checkbox somewhere in SSAS/BIDS that would tell SSAS to only show a distinct list of the NameColumn entries.
Rick Todd
March 24, 2008 at 6:44 pm
Hey Rick,
I was going to write and suggest that perhaps these really belong in two separate dims which reminded me of the many-to-many paper that Marco Russo wrote. It's been a while since I read through it but I think you may have a calssic case of many-to-many here. The paper is here. It's reasonably long but i think even just going through the first section (Classic M-2-M) you might see how you can achieve the outcome you've described.
Cheers,
Steve.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply