According to the manual, measures may be attached at any level of a dimension. What I don't understand is how the dimesion would have to be setup for this to work.

If the dimesion is three levels deep:
region
branch
business

and the only key is for the business level - how do you add anything at the branch level?

There is no key for the branch level - just the name of the branch - which is not a key. And If I were to use the branch key as the field in the dimension hierarchy - it would display to the user as "354^#$^$8" - not "Boston".

So, how do you make this work?

Hi Chris.

Hope I've got your question right - you want to be able to attach measures to a higher level, so their lowest level is above the lowest level of the dimension. Have we got this right?

What you do depends whether these higher levels already have distinct keys in the data source for the dimension. If they do, follow A below. If not, follow B.

A. If there IS a distinct key for the higher levels in the source data for the dimension, you can create a merged view to load from as follows (uses WebFOCUS universal concatentation AKA MORE):

1. Run a report on first pass printing only L1 key (with as name of KEY) and level 1 display field and HOLD it to a file.
2. Run a report on second pass printing only L2 key (with as name of KEY) and level 1 & level 2 display fields and HOLD it to the same file doing a MORE.
3. Run a report on third pass printing only L3 key (with as name of KEY) and level 1 & level 2 & level 3 display fields and HOLD it to the same file doing a MORE.
4. Load your dimension from the merged file, not the source data. You'll have a key at each level to which you can attach measures.

B. If there is NO distinct key field in your source for these higher levels you'll need to invent one. You can create a new table taken from your source data, printing the level fields and the key field for the business level and export it to a database or to excel using WebFOCUS.

Steps to produce that table (uses WebFOCUS universal concatentation AKA MORE):

1. Run a report on first pass printing only key and level 1 field and HOLD it to a file.
2. Run a report on second pass printing key and level 1 & level 2 field and HOLD it to the same file doing a MORE.
3. Run a report on third pass printing key and level 1 & level 2 & level 3 field and HOLD it to the same file doing a MORE.

4. Run a TABLE against that file and sort BY level 1 title, then level 2 title, then level 3 title.

If this is your current source data:

SEA,USA,WEST,SEATTLE
CHI,USA,MIDWEST,CHICAGO
BOS,USA,EAST,BOSTON
NYC,USA,EAST,NYC

You would end up with a file that looked like this (KEY, L1, L2, L3):

,USA
,USA,WEST
STL,USA,WEST,SEATTLE
,USA,MIDWEST
CHI,USA,MIDWEST,CHICAGO
,USA,EAST
BOS,USA,EAST,BOSTON
NYC,USA,EAST,NYC

Note the blanks (the lines that begin with comma). These are the ones to fill in. You can then manually (or automatically) enrich the resultant file. What we usually do is come up with a three character abbreviation of the titles at each level.

>>USA<<,USA
>>USAWES<<,USA,WEST
STL,USA,WEST,SEATTLE
>>USAMID<<,USA,MIDWEST
CHI,USA,MIDWEST,CHICAGO
>>USAEAS<<,USA,EAST
BOS,USA,EAST,BOSTON
NYC,USA,EAST,NYC

The chevrons >><< above point out the synthesized keys. They are not literal - they're only there to emphasize the date. You can use the EDIT[) function in WebFOCUS to do this.

5. HOLD the final result to FOCUS or another indexed format, indexing on your key field.

---
For either situation, you would then load the Dimension from this intermediate table instead of your source. If you wanted this intermediate table to be re-created on a regular basis to reflect changes in the dimensions, you would schedule it to run overnight or on a regular basis with Caster or with your ETL scheduler.

Because all the rows are populated, PMF would not have to load any rows with null keys. Your measures that needed to attach at a higher level could use these keys (e.g., USA for country-wide, USAWES for West Coast of USA, etc).

Not sure the degree of chops onsite, but either of these is a pretty easy job, shouldn't take more than 1-2 hours to build it. Feel free to share with your dev team or onsite consultant.

Hope this helps.

This message has been edited. Last edited by: Bob Jude Ferrante,