April 25, 2017 at 1:09 pm
So we are developing a single OLTP database with multiple companies, meaning the data will be segregated at the highest level by company. I want to create only one SSAS project, with one set of dimensions but with mutiple cubes so each cube is for a specific company and they only see their data. Can I do this? I thought I could do it by creating views in the datawarehouse with the company id hard coded in it, but that doesn't really work. Is there any way to dynamically change your datasource view in SSAS when building a cube? Or pass a variable somehow? I could do it with one large cube and make sure that all dimension tables and fact tables have companyids, but I would rather not if I can avoid it.
Thanks!
April 25, 2017 at 1:39 pm
It doesn't sound like you're creating an OLAP solution, but rather creating the SSAS database on top of the OLTP solution. If this is the case, I would strongly recommend that you don't do this.
Given what you've said, my first inclination is to create an OLAP database. Create a schema for each company that you need and put the tables with only their data in that schema. Then create each company cube on top of that schema. You could even do that within the same solution. But given that I think I understand that the companies will have the same dimension but different underlying data, that would get messy as there's no good way to separate out dimensions in a single solution. Probably better to have a separate SSAS solution for each company.
April 28, 2017 at 12:15 pm
In your scenario I would create a single cube that includes all companies, but use perspectives to create "cube views" for each separate company. The drawback here would be that it will not be dynamic, i.e. you would have to create a new perspective each time a new company is added.
April 28, 2017 at 12:20 pm
use perspectives
I would not use perspectives in this case. Perspectives are not a security measure, and from what I gather from the initial description companies cannot be able to view the data from other companies.
April 28, 2017 at 12:30 pm
RonKyle - Friday, April 28, 2017 12:20 PMuse perspectives
I would not use perspectives in this case. Perspectives are not a security measure, and from what I gather from the initial description companies cannot be able to view the data from other companies.
Perspectives may not technically be a security measure, but it is way to ensure that only certain data is visible to certain users/groups. From the OP's description, this is what is required, unless I am misunderstanding it.
April 28, 2017 at 12:33 pm
Use one cube and apply dimension permissions to each company for their data only.
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/grant-custom-access-to-dimension-data-analysis-services
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
April 28, 2017 at 12:35 pm
Martin Schoombee - Friday, April 28, 2017 12:30 PMPerspectives may not technically be a security measure, but it is way to ensure that only certain data is visible to certain users/groups. From the OP's description, this is what is required, unless I am misunderstanding it.
Perspectives do not stop the user from being able to connect to the cube directly.
April 28, 2017 at 12:40 pm
Use one cube and apply dimension permissions to each company for their data only.
Given that the initial description has some ambiguity and certain details would need to be fleshed out, the risk here is that the companies don't have identical dimensions/products etc. When the original poster said "and only see their data", I assumed that referred not only to fact table entries but also dimension information. If it only refers to fact table entries, this is a good recommendation. But if not, then securing the dimension information becomes complicated.
April 28, 2017 at 12:42 pm
RonKyle - Friday, April 28, 2017 12:40 PMUse one cube and apply dimension permissions to each company for their data only.
Given that the initial description has some ambiguity and certain details would need to be fleshed out, the risk here is that the companies don't have identical dimensions/products etc. When the original poster said "and only see their data", I assumed that referred not only to fact table entries but also dimension information. If it only refers to fact table entries, this is a good recommendation. But if not, then securing the dimension information becomes complicated.
Went on the assumption that OP will have one fact for all his companies, and all related dimensions will be the same.
Like you say, if it's not all structured the same, one cube might not be the best approach.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply