FM calculation on parent with data security

[psten]

Hello,
I have a bit of a challenge that i have not been able to solve, hopefully i can get some assistance from this knowledgable community :)

I have two tables, Sales (fact) and Organisation (dimension). The Organisation table contains Country, Chain and Store (Each country have several chains, each chain have several stores). The Organisation table is joined to the Sales table on Store key. We restrict the users access to the sales data, using a 'security string'. This is put on every users ldap record, and is picked up by cognos as a session parameter. The format of this string is GAABBCC, where AA is country, BB is chain and CC is store. So if my security string is GAABB%, then i can see all stores under chain BB. If my security string is G%, then i have access to all countries, chains and stores. This restriction is modelled into Framework manager on the Organisation table (ORGANISATION.SECURITYCODE like security.parameter).

The FM modell is a DMR modell, using DQM. This all works perfectly, the user runs the reports, and only sees the data he is allowed to see. But here is my challenge : I want to have a measure that gives me the Sales Revenue for one level of the Organisation dimension, as a percent of its parent. For instance one stores revenue in percent of chain revenue, or one chains revenue as a percent of country revenue. One challenge is to create the actual calculated measure. Another is to keep the restrictions on Organisation access, but to calculate on data from one level above what you are allowed to see. Long explanation there, hopefully you understand what i am trying to do.

My environment is Cognos 10.2.

Any assistance would be much appreciated.

Thanks,
P..

[cognostechie]

The security is set on the Organisation Query subject which is filtering the record depending on the access of the user. This means that the Organization table has an inner join with the Sales Fact.

If that is the case, then you can create another query subject pointing it to the same Organisation table without setting the security on it. Join this to the Sales Fact. Now you have access to all the Stores of all the Chains of all Countries. Create calculated measures using this new dimension and publish these measures without publishing this new Organisation dimension.

So basically, you can add the calculated measures in your Business/Presentation Layer which will give the users the percentage or whatever you need and also keep the security working because you will not be publishing the new Organisation dimension which will not have the security.

For creating the measures, I presume you will have to do some modelling.

[psten]

Thanks for you reply. Your description of our Query subjects, joins and security is correct.

But i dont see how this will work. Because i will still need to filter on the. In my simple package i will have one dimension (Country > Chain > Store) and Two Facts (Revenue and Revenue Index). So if i pick (or have access to) just one store, it will filter the fact on this store. But at the same time, i need to filter the fact on this stores parent, the chain. As the existing dimension (based on the secured table) and the new (unpublished) dimension are not connected, the filtering done on the existing dimension will have no impact on the new dimension. Of course, it may be that i just misunderstand your solution.

[cognostechie]

Is this what you need? See attachment.

[psten]

Yes, that is the result i am looking for. Which would be easy without the security.

[cognostechie]

In that case, you can just do it in the report. Do a total so that the report gives you the total for the higher level (Product Category in this case). Create a calculated column and insert the expression shown in the attachment.

My first reply was based on my understanding that you want to calculate the % based on the value at the lower level (with the security) with the total of the higher level (without the security).

In this case, the user will get the values based on security at both the levels. If you need to calculate the percentage based on the total of the higher levels (without the security) then it would require modelling the way I described earlier but that would not make much sense because then it would allow the backtrack and get the value of the higher level and that way they can
breach the security.

[psten]

Thanks. But the reason that we want this as a precalculated measure from FM, is that this is part of a BI Self Service initiative. For our standard reports, we have done something similar to what you describe. However, in our Self Service solution, users should have access to these indexes, but without having access to the actual revenues/quantities for stores or chains other than their own. Its a tricky one, so thanks for spending time on it :)

[cognostechie]

There is always something new to learn. I just tried it in FM and it worked ! The only limitation is that the higher level has to be part of the report.  If this works, then send me a PM with your e-mail and I can send you the FM model.

In this example, the City is the higher level and Customer is the lower level and the % shows the percentage of customer's measure for the City's total.

[psten]

Yes, if this works with the security, then i should be able to use it. But i cannot see any email-icon on your profile ?

[MFGF]

Yes, if this works with the security, then i should be able to use it. But i cannot see any email-icon on your profile ?

I sent your email address to cognostechie. You may not have enough posts yet on the forum to be able to send a personal message? You can try it by clicking "My Messages" at the top of the page.

MF.

[psten]

Thank you :)

[cognostechie]

I sent you the model. Since this does not require any change in the Dimension query subject it should work with the security because the security is set on the Organization QS in your case.

Thanks MFGF for sending OP's e-mail address.