Search code examples
ssas

Alias Dimension member value in SSAS

Is it possible to display or return a different value for an attribute in SSAS?

For example, in my date dimension I derive the business day within the month. Something like this:

    DateId          Date            BusinessDay         WeekDay
    20120101        2012-01-01      01                  Mon
    20120102        2012-01-02      02                  Tue
    20120103        2012-01-03      03                  Wed
    20120104        2012-01-04      04                  Thu
    20120105        2012-01-05      05                  Fri
    20120106        2012-01-06      05                  Sat
    20120107        2012-01-07      05                  Sun
    02120108        2012-01-08      06                  Mon

But the problem is every month has a 01 BusinessDay in it, so when I am creating a hierarchy for this, I get an error for duplicates. Also on the weekends I keep the business day constant.

So I need a way to have a unique BusinessDay value, but show a user friendly value. I was thinking I could concatenate the DateId + BusinessDay, but with an expression only show the Right 2 characters.

Making 2012010101 display as 01

Is this even possible? Maybe in the attributes properties somewhere?

Solution

  • I was able to accomplish this by doing the following:

    I added two columns to the DSV. One for the value to be displayed, and a second for the true value. Next, I opened the Design view of the date dimension and added the attribute relationship just like adding any new attribute.

    Now to make this work you have to open the attribute properties. and scroll all the way to the bottom and under the Source options update the KeyColumns to the actual value attribute. Next, in the same Source Options, update the NameColumn to the Display value you want.

    It's actually pretty easy.