Home > SQL / BI > Combining SSAS’s DefaultMember and Roles

Combining SSAS’s DefaultMember and Roles

I’ve been asked if a cube could be exposed to users so everyone gets ‘their’ view of data by default, but is able to compare with others if they wish. a combination of setting the DefaultMember on a per-role basis seems to be the answer. Take an AdventureWorks example: if I’m a sales manager responsible for the UK, most of the time I only want to report on that. But, come annual review time I probably want to see how I compared to my peers. If there’s a ‘UK Manager’ role set up in the cube, to which I belong, when I build a pivot table I see just values for the UK by default:

image

But, if I choose to include Countries in the query, everything shows up:

image

To set up the role, choose the appropriate MDX statement in BIDS:

image

Make sure you’ve got the corresponding Attribute chosen in the drop-down and specified in the MDX (Country in this example).

Advertisements
Categories: SQL / BI
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: