How to prepare star schema for multi-tenancy
4 posters
Page 1 of 1
How to prepare star schema for multi-tenancy
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
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
Re: How to prepare star schema for multi-tenancy
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
Re: How to prepare star schema for multi-tenancy
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
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
Re: How to prepare star schema for multi-tenancy
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.
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.
Re: How to prepare star schema for multi-tenancy
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.
"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
Re: How to prepare star schema for multi-tenancy
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: How to prepare star schema for multi-tenancy
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
Similar topics
» Star Schema for Surgeries
» Snowflake or Star Schema?
» Star Schema vs All in one table
» Star Schema for MPP databases
» star schema designing
» Snowflake or Star Schema?
» Star Schema vs All in one table
» Star Schema for MPP databases
» star schema designing
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|