User Access Control + Dimensional Model

Hi All,

How can I implement an user access control along with a dimensional model?
Let me take the classic sales star schema: fact_sales, dim_date, dim_store and dim_product.
Also I have 100 end-users of my web application.
Each user is allowed to access a sub-set of products. For example, User 1 must access only Product A and Product B.
The problem is that my access control list may change in the security system on the fly. So if I connect the user access control to the star schema it may be outdated. In other words, I can have a security flaw if I keep a snapshot of user access control.

Any ideas?

Thanks a lot,

LR

Usually stuff like this is a bit easier to do in the BI layer. In a database, a common approach is to set up various schema with views and synonym references to the DW tables. You create views with built in filters to limit content. But it really depends on the database you are using.

In any case, it's a real pain to maintain...

I'll assume your access control list contains the natural/business product identifier (e.g. SKU number).

From a dimensional model perspective, simply use the product identifier from your access list to filter on the product dimension. This will limit any query to the allowed products.

Depending on your BI toolset, there are numerous ways to implement this (database views, BI Metadata etc).