MDX Query - set cube security based on business day

Category: sql server analysisservices


Payeez on Mon, 18 Jan 2016 17:21:01

I am new to SQL Server BI and trying to develop code to allow access to the cube to a specific role, only after business day 5.  (i.e. if the first day of month is Friday, then role ABC should not be able to view the content of a cube until next Thursday) 


Simon_Hou on Wed, 20 Jan 2016 06:43:02

Hi Payeez,

In Analysis Services, it's not supported to limit role access based on the current date. We can only allow member set based on UserName() or adding CustomData() in connection string. However, the CustomData() should be static clear text when connecting SSMS. Even in BI solutions, like SSRS, we can assign the value with a dynamic expression so that we can pass a "IsBusinessDay" parameter to Cube. We still need to define different allow set in AMO for each dimension based this passing parameter, which is not efficient at all. For your requirement, it can't be achieved currently.



yger on Wed, 20 Jan 2016 07:35:55


so the users should not be able to see the whole cube? -or do you mean that they shouldnt see just the actual or last month of data loaded?

Kr J

RichardLees on Thu, 21 Jan 2016 03:40:27

Hi P,

You can control which days a user (via an SSAS role) can view.

Create a new Role, and in the Dimension Data tab, select Advanced tab. In the Allowed member set specify the dates the user can see. This could be a literal, eg Null:[Date].[YMD Hierarchy].&[2015-01-21], or it could be soft, such as 

Null:StrToMember("[Date].[YMD Hierarchy].&[" & format(now(),"yyyy-MM-dd") & "]")

Of course, you will need to add some logic to now() to go back to the day you require.

Hope that helps,