How to prepare star schema for multi-tenancy

View previous topic View next topic Go down

How to prepare star schema for multi-tenancy

Post  ds on Thu Oct 24, 2013 7:50 am

Hi,
I'd like to hear your ideas on preparing a star schema for multi-tenancy.
In my example, the star schema already exists and we have a client dimension which we could use for assigning the access rights (option 1). The other option (2) we were thinking about is adding the client name to every dimension in the star schema (considering we query the dimensions individually to drive parameter selection in reports). I'd like to hear your take in this ... what is the best solution?
Please note: We are not intending to have one star schema per client. Everything has to be in one star schema.
Thanks,
Diddy

ds

Posts : 8
Join date : 2011-05-15

View user profile

Back to top Go down

Re: How to prepare star schema for multi-tenancy

Post  Jeff Smith on Thu Oct 24, 2013 5:05 pm

I have a System ID (an integer field) on all relevant fact and dimension tables. I use the BI reporting tool to limit access to rows based on the value of the integer. There are a couple of different ways to limit access using BI tools.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: How to prepare star schema for multi-tenancy

Post  ds on Thu Oct 24, 2013 5:21 pm

Hi Jeff,
Many thanks for your reply. My question was not really concerned about enforcing access rights. Our BI tool supports columns and row level security.
I don't very much like the thought of putting the field we would use for row level access (client) into every dim table. As mentioned we have a client dim and could theoretically enforce row level security by using this dim. The many problem then is though, that whenever a report / analysis view just wants to show values of another dim, the query will have to have a join to the fact table and client dim to figure out which values are the right ones to show, which is very performance intense. This is just theoretically speaking, I haven't tested this yet. Right now I'd like to understand what the best way to model this would be.
Thanks,
Diddy

ds

Posts : 8
Join date : 2011-05-15

View user profile

Back to top Go down

Re: How to prepare star schema for multi-tenancy

Post  ngalemmo on Thu Oct 24, 2013 6:17 pm

If you have a client dim and all your tables have a FK to that dim, you have basically solved your problem. You need to keep the reference on all tables should a client wish to report from a dimension only. The FK is essentially the system key Jeff is referring to.

You then enforce row level security on the value of the FK, this way no one can access someone else's data... or you can expose views of every table that explicitly join to the client dim and enforce security there.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: How to prepare star schema for multi-tenancy

Post  ds on Fri Oct 25, 2013 4:06 am

Many thanks for your reply!
"If you have a client dim and all your tables have a FK to that dim, you have basically solved your problem. You need to keep the reference on all tables should a client wish to report from a dimension only."

-> Yes, my fact tables have a FK to the client dimension. I have several other dimensions as well, like product. So your suggestion is to add the client dim key as well to all these dimensions, so a layout similar to this: product_tk, client_tk (FK), product_name, ...
Did I understand this correctly?

As pointed out initially, it seems to be the best solution in regards to keeping the query times down in case values need to be retrieved from the dimensions only (report parameters), but it has the additional overhead adding the client_tk to every dimension.

I like your idea of introducing a view additionally as well - thanks for pointing this out.

ds

Posts : 8
Join date : 2011-05-15

View user profile

Back to top Go down

Re: How to prepare star schema for multi-tenancy

Post  BoxesAndLines on Fri Oct 25, 2013 10:58 am

Personally, I would create a schema (or database) for each tenant. You can still keep a common model for all tenants. The possibility of delivering someone else's data always exists when the data is intermixed.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to prepare star schema for multi-tenancy

Post  ds on Fri Oct 25, 2013 11:05 am

Thanks a lot for your advice! Yes, to be on the safe side, this certainly a very good idea.

ds

Posts : 8
Join date : 2011-05-15

View user profile

Back to top Go down

Re: How to prepare star schema for multi-tenancy

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum