Section access question

[#1]

I have a situation where my document is reduced by cluster. Each user is assigned a cluster and they get to see only the relevant data. But there is a requirement now to display the total, THE TOTAL SUM regardless of the reduction in one of the graphs for some benchmarking. Is that possible? I believe since the data is already reduced we wouldn't be privy to the entire data hence not be able to calculate the grand sum. Any tricks please?

[alexandros17]

The solution is to create a second cluod of data - completely disconnected from the first and without any reference to the field you use for reductions.

This secon cloud contains Total Sum ...

[SergeyMak]

You can do it in several ways.

The first one was offered by Alexandros17

The second - have same data in separate tables but connected by Date fields. But there is a problem of security. Because if some of users can create or modify charts, he or she have possibility to get access to others users data.

The third - you can have separate table with aggregates (Totals) connected to the Data Model only by Date fields

I think the idea to have connected tables in my opinion is better, because you can simply select Date, Month, Year and see everything connected

[#4]

Thanks for the responses. But if I have a second cloud and if I bring and connect that to the reduced data model would it not pave way for a number of synthetic keys? Because we are talking about 2 models, one which is reduced and the other which is not. May be I don't understand it well. Can you give an example?

My scenario is I have Sector -> Cluster. The document is reduced at a cluster level, but I want to display a reference line in all graphs for the Sector. That is what I want to achieve.

I'm thinking of a rather simple implementation. Store all the sector averages/totals in a single qvd from the base model. (base model is not reduced). then in the reduced model read the sector averages/totals and then use them. The only drawback is if the users click on any filters then the sector averages will be invalidated. The users are fine with that.

[SergeyMak]

Small example with section access and aggregated totals

full access adm:pass

user acess user2:pass2

[#6]

Thanks Sergey. I understand your approach.
I feel my scenario is a little different though. I have a number of Clusters and section access is set for this field 'Cluster'. And there is no requirement for any admin level dashboard which needs to show the data for all the 'Clusters'.

All I need is in the dashboard which is reduced by Cluster, I just need a trendline in the graphs showing the Sector Average or the Sector Total(Sector Average = The Average of all the data regardless of Clusters, Sector total = Grand Total regardless of Clusters).

-Ram

[SergeyMak]

Hi Ramkumar

I think you still need additional table with aggregated (ave or total sum) data by day or week or month. The period depends on your requirements.

Because you can't get the TOTAL data regardless of Clusters etc., if you don't have it in the document. But you don't have it in the main fact table if you applied section access data reduction.

Kind regards,

Sergey