Repeating values across hierarchy & Rollup

Question

Post reply

Repeating values across hierarchy & Rollup

AaronLS

SSC Enthusiast

Points: 119
More actions
July 19, 2016 at 10:38 am

#150494

Let's say I have a hierarchy like this:
TaskDim:
Tenant Name
Team Name
Project Name
Task Name
Task Status
TaskFacts
TaskDimId
HoursToComplete
And I setup attribute relationships corresponding to the hierarchy:
TaskDimId -> Tenant N->Team N->Project N.->Task Name->Task Status
The same TaskName might/often occurs across projects/teams/tenants. So when I setup the above attribute relationships I get the "duplicate" error.
I can solve this error by creating a key for TaskName of Tenant Name,Team Name,Project Name, Task Name.
However, once I do this, rollups no longer work. A report that omits Project Name for example, will repeat the same Task Name multiple times instead of rolling up, since each Task has a unique key now.
Note, the same issue occurs for TaskStatus, since all tasks share the same set of statuses, then you would also get the duplicate error here.
I.e., sometimes users may want to omit Project and Team Name from the report, and see sum HoursToComplete across these.
Desired Report:
Tenant A, Water Plants, Incomplete, 200
Tenant A, Water Plants, Done, 345
Tenant A, Take out Garbage, Incomplete, 100
Tenant A, Take out Garbage, Done, 124
Actual Report, due to TaskName including Team/Project in key:
Tenant A, Water Plants, Done, 125
Tenant A, Water Plants, Done, 110
Tenant A, Water Plants, Done, 110
Tenant A, Water Plants, Incomplete, 50
Tenant A, Water Plants, Incomplete, 150
Tenant A, Take out Garbage, Done, 24
Tenant A, Take out Garbage, Done, 30
Tenant A, Take out Garbage, Done, 50
Tenant A, Take out Garbage, Done, 20
Tenant A, Take out Garbage, Incomplete, 75
Tenant A, Take out Garbage, Incomplete, 25
Is there a way to get the relationships setup for the hierarchy, and still allow tasks to rollup as per desired example?
I know I could just leave relationships default, and setup explicit aggregations to improve performance. However, I would like to do it "properly" and perhaps expand my knowledge a bit on how keys can be set properly but still allow rollups.
Would this be considered an unnatural hierarchy due to the way deeper levels have common/repeating values?
I could of course reorder the hierarchy, but it wouldn't really fit typical usage scenarios. No one is going to start by drilling from Task Status, down to Task Name and get anything useful out of that. They would almost always start with Tenant Name and drilldown. They'd never do analysis across tenants. They may or may not sometimes include Team/Project, depending on what they are trying to accomplish.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

Assuming that your attribute notation was just a typo as it should look like this with Tenant at the far right:

TaskDimId -> Task Status -> Task Name -> Project N -> Team N -> Tenant N

I've done something similar with a date dimension before, because the client did not always want to see the month/quarter/year attributes within the hierarchy. They also wanted the ability to just look at January for instance, instead of January in a particular year.

Two things you need to keep in mind here:

1. The attribute relationships define how these attributes are defined and related to each other. If you build a perceived hierarchy between your attributes in terms of the relationships, your attribute keys have to include the higher levels and it will affect what you see outside of the hierarchy.

2. Hierarchies are merely drill paths that facilitate an end users navigation...nothing more.

With that being said, you should be able to keep your hierarchy as it is to facilitate the drill path from Tenant to Team, from Team to Project etc...but changing your attribute relationships to the following:

TaskDimId -> Task Status

TaskDimId -> Task Name

TaskDimId -> Project N

TaskDimId -> Team N

TaskDimId -> Tenant N

The above should give you the desired effect, and the hierarchy should still show aggregates per attribute instead of the duplication you're seeing now (because of the attribute keys).

Disclaimer: Note that attribute relationships help with aggregation and performance of aggregated queries in your cube. If you do this, you may see slower performance. The designer may also complain about the disconnect between your hierarchy and attribute relationships...but not much you can do about it in this case.

AaronLS SSC Enthusiast Points: 119 More actions · Answer 2

Thanks, yeh, that's what I was saying I do now. I just leave attribute relationships as they are and setup aggregations, but that feels like a hack to avoid figuring out how to properly setup the keys+attribute relationships.

Anyone who queries the data in a way that can't leverage the aggregation will see slow performance without the attributes. It seems it would be better to setup the attributes "properly", thus getting better general on-the-fly performance across the board, and save setting up aggregations only for the most common query scenarios.

It feels like a workaround.. Maybe there just isn't any other way in this scenario though.

gwfenton1974 SSC Veteran Points: 209 More actions · Answer 3

Is there any way to make this work in your scenario across multiple ID's eg Tenant A and B.

I have a scenario where I have a different product ranges each with 2 seater chairs. I want to use the hierarchy, as much as it appears more user friendly in the browser.

When looking at the attribute (Item) covering 2 seater chairs in isolation I want to see the total across all ranges and not just its on range.

Martin Schoombee SSCoach Points: 19128 More actions · Answer 4

gwfenton1974 (9/16/2016)
Is there any way to make this work in your scenario across multiple ID's eg Tenant A and B.
I have a scenario where I have a different product ranges each with 2 seater chairs. I want to use the hierarchy, as much as it appears more user friendly in the browser.
When looking at the attribute (Item) covering 2 seater chairs in isolation I want to see the total across all ranges and not just its on range.

Creating the attribute relationships as described above will still allow you to create a hierarchy in any way you want. As stated above, hierarchies are merely drill-paths and function independently from attribute relationships. If you create a hierarchy with a higher level (combining the items into product ranges), it will still roll up correctly.

Creating the attribute relationships in this way allows you to get around assigning a composite key consisting of product range and item.

gwfenton1974 SSC Veteran Points: 209 More actions · Answer 5

I think I am missing something as when I use the attributes outside of a hierarchy they roll up perfectly. When I drop them in a Hierarchy leaving the attribute relations unchanged it stops rolling them up. When include both options in the cube one rolls up and the other doesn't. I am at a bit of a loss on this one.

(Thanks for the help)

Martin Schoombee SSCoach Points: 19128 More actions · Answer 6

gwfenton1974 (9/19/2016)
I think I am missing something as when I use the attributes outside of a hierarchy they roll up perfectly. When I drop them in a Hierarchy leaving the attribute relations unchanged it stops rolling them up. When include both options in the cube one rolls up and the other doesn't. I am at a bit of a loss on this one.
(Thanks for the help)

Could you please share an example? (screenshots will help)

gwfenton1974 SSC Veteran Points: 209 More actions · Answer 7

I have a customer dimension where the PK is an int called ID with a customer ID and Delivery Postcode

The attribute relation ship is set to;

ID to Customer ID

ID to Delivery Post code

The full hierarchy displays like this;

CustomerID Delivery PostCode Order Quantity

1 FY5 3TG 1

2 CH2 1NF 1

3 SK22 4DT 1

4 L20 6LT 1

5 FY6 8BX 1

6 WA8 7XP 1

7 L18 3JN 1

8 L12 3HB 1

9 M28 0SX 1

10 BB7 9AZ 1

10 ZZ99 9ZZ 1

9 ZZ99 9ZZ 1

If you just pull the delivery post code, from the hierarchy, through it returns;

Delivery PostCode Order Quantity

FY5 3TG 1

CH2 1NF 1

SK22 4DT 1

L20 6LT 1

FY6 8BX 1

WA8 7XP 1

L18 3JN 1

L12 3HB 1

M28 0SX 1

BB7 9AZ 1

ZZ99 9ZZ 1

I would expect the post codes at the bottom to roll up like the below. It is also worth noting that I have left the attributes visible outside the hierarchy. If use that delivery Postcode attribute it also returns the below;

Delivery PostCode Order Quantity

FY5 3TG 1

CH2 1NF 1

SK22 4DT 1

L20 6LT 1

FY6 8BX 1

WA8 7XP 1

L18 3JN 1

L12 3HB 1

M28 0SX 1

BB7 9AZ 1

ZZ99 9ZZ 2

I don't understand why the same attribute is acting differently inside and outside the hierarchy. My only conclusion, which is probably wrong(!), is that the hierarchy itself is forcing the Delivery Postcode attribute to act differently.

gwfenton1974 SSC Veteran Points: 209 More actions · Answer 8

I have continued working on this and then used Excel as the Front end tool for the cube. I have come to the conclusion that I am misusing/misunderstanding how the Hierarchy works outside the cube's own browser.

While the browser lets you pull out individual levels of a hierarchy this isn't possible within Excel. With this in mind I have to reconsider where and how I use Hierarchies.

Thanks for the help