To homepage To homepage
Solved

SSL Models

  • 4 January 2024
  • 5 replies
  • 97 views
J
Rank

Hi,

Need help on the following : 

  1. I have built the entire SSL model is built on Analysis server & want to understand what is the best practise around this?
  2. We have one base model built & users can access all the tables & can be confusing to the user which table & column to pick from. We have tables from Finance, Operations, production, Purchasing everything. How do you segregate & manage scenario like this?
  3. We did explore Row level security, but it is no good & we need to use Object level security which is not supported by TX.
icon

Best answer by bas.hopstaken 5 January 2024, 14:34

View original

5 replies

rory.smith
Rank
Userlevel 5
Badge +7

Hi @jyothi.dosi ,

  1. typically, a Semantic Model will be a compact model geared towards one use-case or department. Meaning that you aim for one Fact table (for example SalesOrder) with any Dimension tables connected to it. I.e. you prefer many small tailored models over one large model
  2. building a model containing “everything” will always be confusing and difficult to maintain. Performance in Analysis Services / Azure Analysis Services will also be problematic as such models tend to be very large.
  3. TimeXtender does not directly generated OLS, you would need to implement that yourself. Tabular Editor will allow you to do that kind of thing. I suspect you want this because you have one model with all tables and want to filter what objects users get to see depending on their department or something similar. I would simply make separate models for different sets of users
R
Rank
Userlevel 2

One disadvantage of having multiple smaller models is that you can't re-use dimensions across your models. Creating the same date dimension for the umpteenth time gets a bit tedious :) It also makes it impossible to combine data from different sources, e.g. finance & HR which could be useful to have inside one model.

Regarding 2: you can define perspectives inside your tabular model to help your end users find what they need (depends on your AS license though).

bas.hopstaken
Rank
Userlevel 3
Badge +4

Hi @jyothi.dosi,

I agree on the points mentioned by @rory.smith. I would advise creating multiple Semantic Models. It is hard to add Row Level Security when you have multiple fact tables because you need to take into account the different filter directions on relations.

Creating multiple smaller Semantic Models is faster and easier to maintain.

One thing to add: It is possible to create perspectives in the Semantic Model. This enables hiding tables. Users can connect to a specific perspective that only contains the tables needed. Unfortunately it is not possible to add security based on perspectives.

Good to mention: Perspectives are only available in SQL Server Enterprise or Azure Analysis Services Standard pricing tier.

Bas Hopstaken (2Foqus)
Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @jyothi.dosi can you please help us by marking a best answer above? If you have any follow up questions please let us know

J
Rank

Thanks for the reply. I am going ahead with smaller semantic models.

Reply


Terms and ConditionsCookie settings