Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to completely hide SSAS tabular tables?

Hi!

 

So this is my scenario. In my SSAS tabular cube model I have a few fact tables (views) imported.

In these fact tables I create some hidden measures. These hidden measures are then used to create all the cube measures I need to a seperate calulated table. All the fact tables and their columns, are then hidden to prevent them from showing up in the model, along with the hidden measures.

 

When browsing the cube in PBI Desktop, the fact tables hidden. All good so far.

However, if I create a new measure in PBI, I can still browse the whole model and see/reference even the hidden tables/columns/measures, which is a serious issue.

In most cases this is probably fine, but I have a fact table with sensitive data in it, that end users should not be able to query. They should only be allowed to see the results of the measures I have created, and that I can restrict access to with security roles.

 

I have tried to set row level security to =FALSE() just for the underlying fact tables, but then access to the measures referencing them are removed as well.

 

Is there really no way of completely hiding tables in an SSAS tabular model? I think this is a serious flaw if that's the case. Or maybe there's a simple solution for it that I have missed?

 

/Z

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

If I understand your scenario correctly that you want the end user could see the measure which is created with the hidden fact table, but cannot see the hidden table?

If so, normally, if we hide the tables in power bi desktop, we cannot see the hidden table in power bi service. If you set the RLS, the end user cannot download the the pbix in power bi service, they cannot open it with power bi desktop so that they cannot see the data model.

In addition, if you want to share the pbix directly with the end user, the end user can see the whole data model even the hidden table in Model view in power bi desktop.

Hope my explanation is clear.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-piga-msft 

 

Thanks for the reply! You almost understood my scenario correct 🙂

 

My data model is not built in PBI, it's an SSAS tabular cube model, that users will connect to with live connection. 

Users will be able to download the pbix-file from the Service and we have some advanced users that will start building their own measures from the underlying data model. But I can't allow them to create any measures from the sensitive, hidden, tables/columns in the model. In PBI the hidden option in the SSAS model, only seems to affects what's visible in the Fields-window.

As soon as you start DAX-coding new measures the whole model is exposed, even the hidden tables where there's sensitive data in some columns.

 

The RLS is built into the security roles in the cube, swhich are then connected to AD-groups. No matter if they browse the report/cube in the service or from desktop, acess is still restricted.

 

So you may wonder why we don't set object level security on sensitive columns/tables? Well, then you can't combine security roles, meaning haveing users being member of more than one role....

 

Anyway, I have found a work around for how to deal with it. By importing all the sensitive as seperate tables in the model, I can configure security for those tables seperatly. Tables will probably still show up in DAX-editor, but no actual data should be exposed, which is good enough I think.

 

/Z 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.