Data level security using query

Analytics
Enthusiast

Data level security using query

Dear Experts,

I am facing a challenge in setting data level security.

The table structure of my fact table is EMPLOYEE_ID, COST_TO_EMPLOYEE, DIM1, DIM2, etc (Granularity Employee_id)

Now for a given filter (i.e. DIM1) if number of fetched rows is less than 8, then we shouldn't show the cost column in the output (Remaining columns will be displayed). Can we do it in the query level? Also let me know if we can do it in report level. We are using tablo as reporting ttol.

Thanks and Regards,

Arindam




3 REPLIES
Enthusiast

Re: Data level security using query

Hi Arindam,

I dont have Td access. However, I see that you can use something like(just a logic sort)

select dim.d1,dim.d2..,case when dim.cnt>=8 then dim.cnt else null from

(select dim2,dim3,....count(dim1) cnt from tabl group by dim2,dim3..) as dim(d1,d2, cnt..)

Also you can use count(field) over (partition by field rows between unbounded preceding and unbounded following) cnt  and then use qualify.

Also, I always see that at the report level it is pretty easy.

Cheers,

Raja

Enthusiast

Re: Data level security using query

Thanks Raja.. I got the logic. Btw what is way to do it in report? based on that I can choose whether to keep it in db level or move to reports. Thanks again.

Enthusiast

Re: Data level security using query

Hi Arindam,

I look from  many perspectives, infrastructure etc etc. Every reporting tool allows you to write and manipulate code with drag and drop and put the logic you want. However, I feel you may want to finish it in style :) , so I feel it is better to put in query for various reasons-say, when I worked in one project to make change in one report field logic, it takes a lot of doing. I hate it. This is just one of the reasons, but there are many more based on env.

Cheers,

Raja