Dynamic Security in SSAS 2005

I am trying to create dynamic security and was hoping to use a named query to link the fact table to a dimension that contains Active Directory users and companies - a user can have access to multiple companies. The problem is that I cannot find out how to get the username of the current user.

Anyone have any ideas?

thanks in advance.

Does the standard MDX keyword: UserName return what you want, like:

>>

With member [Measures].[CurrentUser] as UserName

select {[Measures].[CurrentUser]} on 0

from [Adventure Works]

>>

|||

thanks for the feedback - however, I am relatively new to Analysis Services (and 2005) - and cannot see where this would be created. Are you saying that this is a calculated Dimension (or measure?)

Many thanks

|||

The MDX keyword: UserName will return the name of the current user in an MDX expression, which could be used in many different contexts:

http://msdn2.microsoft.com/en-us/library/ms144884.aspx#expressions_statements_scripts

>>

SQL Server 2005 Books Online

Key Concepts in MDX (MDX)

In MDX, an expression is a combination of identifiers, values, functions, and operators that Analysis Services can evaluate to retrieve an object, such as a set or a member, or a scalar value like a string or a number.

>>

http://msdn2.microsoft.com/en-us/library/ms146016.aspx

>>

SQL Server 2005 Books Online

UserName (MDX)

Returns the domain name and user name of the current connection.

>>

|||Thanks for the feedback.

dynamic cross tab without aggregation? PLEASE help

Ok..we have a db that the company wants to store roles in (ie,
'auditor', 'accountant', etc..) that are active for a specific date
range ...its a wierd paradigm, dont ask... but we need a specific # of
roles filled for each date...
here's the script
Create table tblRoles(RoleID int primary key identity(1,1), RoleName
nvarchar(255))
create table tblDates(DateID int primary key identity(1,1),
ProjectDate DateTime)
create table tblRolesForDate(RoleForDateID int primary key identity
(1,1), RoleID int, DateID int, NumberNeeded)
what I need to see is a table from that...like this.
1/1 1/2 1/3 1/4 1/5
auditor 0 5 5 2 0
account 5 5 5 0 15
etc...
any ideas on how to make that work? the dates across the top are
from the dates table, the names in the left column are from roles, and
the #'s come from RolesForDate...
Grrr
Replied in .programming
Please do NOT multi-post.
David Portas

dynamic cross tab without aggregation? PLEASE help

Ok..we have a db that the company wants to store roles in (ie,
'auditor', 'accountant', etc..) that are active for a specific date
range ...its a wierd paradigm, dont ask... but we need a specific # of
roles filled for each date...
here's the script
Create table tblRoles(RoleID int primary key identity(1,1), RoleName
nvarchar(255))
create table tblDates(DateID int primary key identity(1,1),
ProjectDate DateTime)
create table tblRolesForDate(RoleForDateID int primary key identity
(1,1), RoleID int, DateID int, NumberNeeded)
what I need to see is a table from that...like this.
1/1 1/2 1/3 1/4 1/5
auditor 0 5 5 2 0
account 5 5 5 0 15
etc...
any ideas on how to make that work? the dates across the top are
from the dates table, the names in the left column are from roles, and
the #'s come from RolesForDate...
GrrrReplied in .programming
Please do NOT multi-post.
David Portas

dynamic cross tab without aggregation? PLEASE help

Ok..we have a db that the company wants to store roles in (ie,
'auditor', 'accountant', etc..) that are active for a specific date
range ...its a wierd paradigm, dont ask... but we need a specific # of
roles filled for each date...
here's the script
Create table tblRoles(RoleID int primary key identity(1,1), RoleName
nvarchar(255))
create table tblDates(DateID int primary key identity(1,1),
ProjectDate DateTime)
create table tblRolesForDate(RoleForDateID int primary key identity
(1,1), RoleID int, DateID int, NumberNeeded)
what I need to see is a table from that...like this.
1/1 1/2 1/3 1/4 1/5
auditor 0 5 5 2 0
account 5 5 5 0 15
etc...
any ideas on how to make that work? the dates across the top are
from the dates table, the names in the left column are from roles, and
the #'s come from RolesForDate...
GrrrReplied in .programming
Please do NOT multi-post.
--
David Portas