inconsistent customer hierarchies

[slaforce]

Hi everyone -

I have a situation where we have multiple customers that each have there own organizational hierarchies. For instance - one customer might be organized by organization, region, district, location - and another customer might only have organization and location.

Any suggestions or ideas about how I can model this in a cube?

Thanks for the help.

 

[JGirl]

Just to clarify your question...

Are you saying that each of your customers has an entirely different set of fields for their structure, or that each customer record has the same set of fields but they are not always all populated in the same way?

J

 

[slaforce]

Same set of fields but not always all populatd the same way. So one customer might have organization, region, district, location all populated - while another customer would only have organization and location populated. Location would always be populated since that is the lowest level.

 

[Draoued]

if you bring all the fields in your hierarchy , then you ll get lots of (blank) when the field is Null.
One way is in the IQD to defined , ifnull(District, Location) for district .
For Region ,ifnull(region, district)
for Organisation , ifnull( organisation , Region)
Which of course means , that you ll have in some cases a duplicated categories along the dimension.

My Organisation
[tab] my Organisation
[tab] [tab] My district
[tab] [tab] [tab] My location

 

[slaforce]

In this situation - I would then by drilling into the same category - right? If I populate district with the location - then I would drill from location to location. I think this would be confusing to the users.

 

[mayo04]

Why not just allocate them as unknown where a level in the dimension is not populated eg in the sql: case when .... is mising then 'Unknown' else .....
or in the blank substitution of the respective dimension level properties instead of blank. If you aint got the data there is noting you can do.



Mayoman

 

[JGirl]

Yep, you would be drilling into the same category for some of the customers. Its certainly not ideal, but really, your options are limited when your dataset is inconsistent

The only other alternatives I could think of would be to:
- Manually supress any of the blanks in the cube model (time consuming and inefficient), or
- Clean up your data and populate the missing fields
- Forget about including the region, location categories and just include your customer names

Probably not very helpful suggestions though

J