Hi Team,

I need to make a second copy of all my measures, so that I can aggregate one of the dimensions at a lower level than the first set of measures. I already know the answer ('No.'), but I'm also not looking forward to manually typing in 60 measures by hand.

I don't suppose there is some FOCUS code I can use to extract all the Measures from the datamart and then feed them right back in with a slightly different measure name using MODIFY?

Cheers,

Joey

Before we go into how this could be done, a question.

Why do you need to load a different set of the same measures aggregated at a lower level? Why not load the base 60 at a lower level and let PMF handle the higher level aggregation?

Hi Bob,

The reason for this is that my client's requirement works backwards to the PMF security model. They have a group of users that is only allowed to see measure values at the highest level of dimension aggregation. They have a second group of users that is allowed to drill-down on that same dimension, although they are likely to be restricted to just one of the highest level values.

My plan is to load two versions of the same measure, and use scorecard security to give access to each version to each user type. One measure will not allow drill-down and the other one will.

Cheers,

Joey

There's probably a way to alter the security model (using a customization) to allow these user IDs to only see a single level of the dimension.

I'll run it by the team and see if there are any ideas. Expect to hear more from us.

Worse comes to worst, we'll figure out a way to do the hack of copying the measures over for you.

thanks

Joey,

With some work, you can set up PMF security so that designated users will get the "No Data" report if they try to report at or below a particular level for a particular dimension. These users will still see the lower level dimension values on the dimension trees, but any attempts to report against these levels will return the 'No Data' output. To implement this you will have to do a few things (this is simpler than it seems on first glance):

1) Create a new Access Role
This technique relies on establishing a dedicated Access Role. I'd recommend giving this a meaningful name. For example, if you are limiting users to not seeing level 3 of LOCATION dimension, you could call this "No third level Location". Do *not* apply any actual dimensional filters to this Access Role, it is simply a shell for applying custom code.

2) Get the number of the new Access Role
You will need to hard code this Access Role. Do a TABLE FILE ROW_LVL_SEC_ROLES and get the ROW_LVL_SEC_ID of your new Access Role. For purposes of this discussion, I will use ROW_LVL_SEC_ID=6.

3) Bypass this role in row_lvl_sec.fex
As a security measure, PMF normally disables any data access if you are using an Access Role with no filters. To bypass this normal mechanism, open up row_lvl_sec.fex. You will see this on or about line 18:

-IF ((&ROW_LVL_SEC EQ 'O') OR (&ROW_LVL_SEC_ID EQ 1)) THEN GOTO :NOUSERFILT ; 

Change this to

 -IF ((&ROW_LVL_SEC EQ 'O') OR (&ROW_LVL_SEC_ID EQ 1 OR 6)) THEN GOTO :NOUSERFILT ; 

Notice that I added in the new ROW_LVL_SEC_ID.

4) Add custom code in pmf_booklet_data

Add this sample code into pmf_booklet_data after the -INCLUDE PMF_ANALYSIS_PARMS.

-IF (&ROW_LVL_SEC_ID NE 6) THEN GOTO SKIPCUSTFILT ;

-SET &F_CNT = 0 ;
-REPEAT CUST_FILT_LP FOR &F_CNT FROM 1 TO &NUM_DIM_WHERES
-SET &F_SUB = IF (&F_CNT LE 10) THEN '0' | &F_CNT ELSE &F_CNT ;
-IF (&WHERE_DIM_TIME_FLAG.&F_SUB EQ 'Y') THEN GOTO CUST_FILT_LP ;

-IF (&WHERE_DIM_DIMNAME.&F_SUB NE 'LOCATION') OR (&WHERE_DIM_LEVEL.&F_SUB LT 3) THEN GOTO CUST_FILT_LP ;

-SET &PMF_BOOKLET_DATA_DIM_WH01 = 'WHERE LOCATION_LEVEL03_VALUE EQ ''blah blah blah''' ;
-GOTO SKIPCUSTFILT

-CUST_FILT_LP

-SET &PMF_BOOKLET_DATA_DIM_WH01 =  IF 
-                                  (
-                                    ((&SORTBY1_IS_DIM EQ 'Y')    AND (&SORTBY1_DIM EQ 'LOCATION')    AND (&SORTBY1_DIM_LVL    GE 3))
-                                 OR ((&SORTBY2_IS_DIM EQ 'Y')    AND (&SORTBY2_DIM EQ 'LOCATION')    AND (&SORTBY2_DIM_LVL    GE 3))
-                                 OR ((&SORTACROSS_IS_DIM EQ 'Y') AND (&SORTACROSS_DIM EQ 'LOCATION') AND (&SORTACROSS_DIM_LVL GE 3))
-                                  )  THEN 'WHERE LOCATION_LEVEL03_VALUE EQ ''blah blah blah'''  ELSE ' ' ; 

-SKIPCUSTFILT
  

This sample code is using LOCATION dimension and level 3. Adjust this code for your dimension, level, and the new ROW_LVL_SEC_ID. The way this works is that if you are viewing data at Location Level 3, it inserts a rogue WHERE clause which will return 0 rows. I think it's a reasonable assumption that you do not have a Location named 'blah blah blah'

5) Link your users to this Access Role
From the Manage tab, select the users who are not allowed to see the lower dimension level and link them to the new Access Security Role.

EricH

I'm not sure if it's clear why Eric posted this... but it's to help you avoid having to make 60 duplicate measures and reload everything for that group of users that isn't allowed to see data *below* a certain level.

Anybody else in this situation that Joey is in?

thanks

Hi Team,

I'm working on implementing this right now.

My concern is that we have a gadget that compares measures by region (location level 1 as an across). As soon as I drill on a value, the Measure Detail report shows the second level of the location dimension. With this technique, it will fail with a no-data error anytime my access role user is trying to drill-down. My default dimension is NOT location; I think it is being chosen because I'm explicitly drilling on a Location level 1 value.

How do I make this gadget display a different dimension grouping besides Location Level 2?

Cheers,

Joey

We understand.

By design, a drilldown will drill *down*. If it's not allowed to drill down because a security has been implemented to block the user from seeing the next level down, can you tell us what that drill should do when it's drilled down?

Possibly you need to disable the drilldown? Or something else... if so, please share.

thanks

Hi again,

I'm having some trouble implementing this technique. For starters, this variable is not found:

&WHERE_DIM_TIME_FLAG.&F_SUB (&F_SUB equals 01 on the first loop).

Should I comment that line out, it tells me:

A VALUE IS MISSING FOR: &WHERE_DIM_DIMNAME01

Please advise.

Cheers,

Joey

Hi Bob,

To reply to your most recent response, I would like to group by a different dimension, at the first level of that dimension, upon drilling on the first level of the location dimension.

Another way of saying this is that I would drill on the gadget on the first level of my location dimension and the measure detail report would maintain that as a filter but group by, say, my default dimension instead of Location.

Possible?

Cheers,

Joey

You could rewrite the drilldown in PMF, either for the report that gadget's based on (which we're not sure which one it is since you didn't specify) or generally.

But is this how the customer wants the drilldown to work across the site, even for those who are not limited to just seeing one level of this dimension? If not, you probably don't want to rewrite the drilldown.

As for the other issue you're reporting, it looks as if you might have replaced an existing INCLUDE. Do a comparison against the original version of PMF_BOOKLET_DATA. Those parms are set in provided INCLUDEs.

thanks