Hi New To Here Need Help With Ssas And Mdx Query

[Gowtham0714]

Hello,

my name is Gowtham,

I need help with SSAS to implement Sales Security,

I am a student and can pay you no more than 25$/hour for implementing sales security on a cube.

Any one who knows how to implement Sales security on the cube using MDX expressions.

Req: [color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
1. "Based on username, limit the sales representatives to see their own customers".[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
2. "Based on Territory, limit the sales representatives to see their own customers".[/background][/size][/font][/color]
[color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
Explanation:[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
I have the following tables[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
1. "Customer" = Has all customer information. PK= Customer_SK[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
2. "Employee"= Has all the employees information. PK= Employee_SK[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
3. "Salesrep Territory"= Has all the Salesrep Information. PK= Salesrep_SK[/background][/size][/font][/color]
[color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
Need to implement Sales Security based on Username from Employee and Territory from Salesrep Territory. (Using MDX Queries)[/background][/size][/font][/color]
[color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
Progress:[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
The Bridge Employee Salesrep Employee is the bridge b/w employee, salesrep and customer dimensions as suggested in the link in the attached file.[/background][/size][/font][/color]
[color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
I put and an MDX Query on customer_sk on the Cube Dimension.[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
nonempty([/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
[Customer].[Customer_sk].members,[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
([/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
strtomember("[Employee].[username].&["+username()+"]"),[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
[Measures].[Bridge Customer Salesrep Employee][/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
)[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
)[/background][/size][/font][/color]
[color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
Now i tried using similar technique to work with the territory,[/background][/size][/font][/color]
[color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]
I put the following MDX expression on customer_sk on the Cube Dimension.[/background][/size][/font][/color][color=#222222][font=arial, sans-serif][size=3][background=rgb(255, 255, 255)]

{
EXISTS(
[End User].[Customer Key].members,
IIF(
([Bridge Salesrep Employee].[User Id]. CurrentMember.Properties("User Id") = Username() and
[Bridge Salesrep Employee].[TERRITORY].currentmember.uniquename = "[Bridge Salesrep Employee].[TERRITORY].&[WW]"),
{[End User].[Customer Key].members},
{NULL}
)
}

Gowtham.[/background][/size][/font][/color]

[simha2727]

help

[micxas]

[Tadika]