Customize dimension attributes in SSAS

I have a time dimension table with an integer column "Quarter". In the SSAS Dimension I would like to display this as "Q 1" etc. instead of just "1".

I could easily add a column in the root table, but would prefer to format the column in the SSAS dimension. Can't find a "Format" property for an attribute. Is there a way to do this?

The most simple way to do this is to add a named calculation(a new column) in the data source view for the dimension table.

Add this attribute to the dimension or use is as the name column for the quarter attribute/column.

Add: 'Q' + ' ' + Cast(Quarter as Char(1))

HTH

Thomas Ivarsson

|||if u have choosen "typical" time dimension in analysis services, u would get "Quarter" in the display.

Customising Dimension Security

HI, I'm having problems specifying customised security in the Advanced tab (allowed members). I'm fairly new to mdx script and am trying to restrict access to a STAFF dimension which contains a loginname member which I want to restrict access to based on username matching the loginname DIM. Have tried the following which didn't work:
[STAFF_DIM].[Login Name].members(MID( USERNAME, INSTR(1,USERNAME, "\") + 1, 128 ) )

I read somewhere that this can only be used on leaf layer members (this is the first of 3 layers), also should I be using the filter function? Any help with trying to setup would be great help.
Before using this expression for dimension security, you may want to do some experimentation first, by creating a simple MDX query including this expression, and verifying that it evaluates to the expected member...

Hope this helps

Customer Sales based on YTM dimension

Hello,

We have a cube that has customer sales data for last 5 years. Time Dimension displays the YTM hierarchy.

=> Selecting "month" is a parameter for the user on a Reporting Service report. Once he selects a month from YTM hierarchy - how to get list of "only those customers" whose sales have been continuously below say 80,000 dollars, beginning the month he selects as a "start month" until "next 6 months".

Multiple selection - not allowed. Only one month can be selected by user at any time.

Any help highly appreciated.

Thanks,

RajShri

Here's an Adventure Works example, which lists all customers with < $1000 in sales for each of 6 months, starting with the selected month (here, Jan. 2004). Note that this includes customers with no sales as well:

>>

With

Member [Measures].[Max6MonthSales] as

Max(LastPeriods(-6,

OpeningPeriod([Date].[Calendar].[Month])),

[Measures].[Internet Sales Amount])

Set [LowSalesCustomers] as

Filter([Customer].[Customer Geography].[Full Name].Members,

[Measures].[Max6MonthSales] < 1000)

select {[Measures].[Internet Sales Amount],

[Measures].[Max6MonthSales]} on 0,

[LowSalesCustomers] on 1

from [Adventure Works]

where [Date].[Calendar].[Month].&[2004]&[1]

>>

sql

Customer Count

Hello,

I have a Fact table containing sales information, and is linked to a customer dimension through a CustomerKey and to a product dimension. The Customer Dimension is a slowly changing dimension having CustomerKey as primary key and CustomerPIN as BusinessKey. A customer can have historical records in the Customer Dimension.

I want to count the number of clients per product. If I create a measure Customer Count as Distinct Count on the CustomerKey, my results are not quite correct because 2 different Customer Keys might belong to the same Customer since the latter is an SCD. So what I want is actually Distinct Count on CustomerPIN, but I am not able to create this measure correctly. It is being created as a measure group under Customer but is not linked to the sales.

I am not sure if I'm doing the right way or if I'm missing something.

Appreciate your help,

Grace

Hi Grace,

One possibility is to use a Named Query for the fact table, where the original fact table is joined to the Customer table, so as to add a Customer PIN field on which a distinct count measure can be created.

|||

Thanks Deepak,

I was able to create a Named Query having SalesKey as Logical Key with Person PIN according to the Person Key of each Sales transaction. And I added a relation between SalesKey from the New Named Query and the Fact table. Then, I was able to create a measure as Distinct Count on Person PIN and use it correctly.

However I was wondering if maybe since I can modify my underlying fact table to originally include this Customer PIN in addition to the Customer Key, and fill both from the SSIS, wouldn't that be faster and less complex? What do you suggest?

Thanks again,

Grace

|||

Grace,

My own inclination would be avoid loading redundant data, unless the cube processing performance with the Named Query is a problem. But I can't say that I've dealt frequently with this scenario, so maybe others on this Forum could comment as well?

Custom time period - 26th of month to 25 of next month

How do I create a server time dimension which has custom defined month periods.

For example we use month that starts on 26 and ends on 25 next month.

So our January is actualy starting from 26.Dec.2006 until 25.Jan.2007,

and so on for Feb, Mar...

Any idea how I can do it?

Thank you,

Mitja

Hello! Create a new column in your time dimension called CustomMonth.

Update TimeDim

Set CustomMonth = 'Jan'

Where CalendarDate between '2006-12-26' and '2007-12-25'

--

Continue with the next month.

This is a very simple solution. It possible to make it generic but that will require more thinking.

HTH

Thomas Ivarsson

|||

Thank you Thomas!

I have tried the proposed solution,

but I get syntax error at "where" critieria.

I was looking for some pre-made solution in business intelligence wizards,

strangely enough to find there isn't one.

Any ideas?

|||

This is an example that works with the AdventureWorksDW sample database:

Alter Table DimTime

Add SpecialMonth Char(3)

Select * from DimTime

where FulldateAlternateKey Between '2002-12-26' and '2003-12-25' --365 Check the no of records that will be updated

-

Update DimTime

Set SpecialMonth = 'Jan'

where FulldateAlternateKey Between '2002-12-26' and '2003-12-25'

-

It is possible to build a full generic solution but it will take some time. You can use TSQL CASE for that.

This will help you to get started.

Regards

Thomas Ivarsson

custom sorting dimension values

Hi,

This one seems like a weird problem to me.

Lets say I have a dimension which has only 5 values: High,Low,Medium,VeryHigh,VeryLow.

When i browse this dimension, I want it to be custom sorted so that I always get them in the below order: VeryLow, Low,Medium,High,VeryHigh

Is there any way to accomplish this? I can arrange them in this order in the VS 2005 browser ide, but can i persist my custom arrangement?

Thanks

If you're using AS 2005, you could create an attribute to order the 5 members by. If the dimension source table is in SQL Server, you could add a named calculation for the attribute, like:

case dimvalue

when 'VeryLow' then 1

when 'Low' then 2

when 'Medium' then 3

when 'High' then 4

when 'VeryHigh' then 5 end as dimorder

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

>>

SQL Server 2005 Books Online

Sorting Attribute Members Based on a Secondary Attribute

However, sometimes you may have to order attribute members based on a secondary attribute to achieve the desired sort order, for example if neither the name of the attribute nor the key of the attribute provide the sort order that you want. In order to sort an attribute by a secondary attribute name or key, you must use a secondary attribute that is related to the attribute.

>>

Custom sort the dimension members

Hello users of SSAS2005!

I am in the funny position of developing a cube for (at least) three different user groups, and each need "their" special treatment. My problem is in sort order of one particular dimension. The dimension has 3 fields: ID (=person number), Firstname and Lastname - as simple as straightforward as it can be.

Now Finance wants it sorted by ID to relate it to their documents. Human resources needs it by last name, so it can be copy-pasted directly into their structures.

It gets really hilarious when IT needs it sorted by first name (not joking here) because an external application delivers correlating date sorted by first name (again, I am not making that up).

Since I cannot convience anyone onto one common sort I need to make all three possible sort orders available, and I might end up with having 3 identical dimensions or one dimension with 3 identical attributes (except for sorting)

We use Excel as our frontend, I know I can sort a cube there as well. This is, of course, if the field to base the sorting upon is dragged onto the same axis, but that is a bit clumsy too.

What is the "best practices" approach to such a funny situation? (Except for changing the company workflow, let's take that as ... sigh ... static.)

Hello Ralf! I am not sure that this is possible in SSAS/MDX.

One way to solve this is to make the SSAS 2005 reports in Reporting Services 2005.

Check Books On Line for dynamic sorting in reporting services. It is also possible to sort with parameters.

If you can only use Excel pivot tables you can make three different reports, each sorted according to each groups preference.

Publish them i Sharepoint(or any other portal).

HTH

Thomas Ivarsson

HTH

Thomas Ivarsson

Custom Rollup Dimension for AVG

I am trying to build a dimension and a cube that contains a measure of Avg Family Size and a dimension that group different types of families into different categories.

The dimension is a parent-child type but the parent should be the AVG of all children instead of SUM.

I tried to use Custom Rollup feature in dimension (there are 2 places, one during the dimension setup in pop-up window, there is check box then to define with MDX or use a column with unitary operator.Another place is in dimension Properties after dimension is setup).I cannot get it to work no matter what I do in dimension with MDX.The cube display #ERR for each cell.

Also, on cube setup, measure setup has a property of Aggregation.It has SUM, MAX, MIN, COUNT, DISTINCT COUNT but just does not have AVG.

So far, the only way is to define a calc measure with such MDX:

IIF(IsLeaf([Test1].CurrentMemeber), [Measure].size, AVG([Test1].CurrentMember.Children, [Measuer].size) )

This is sort of awkward because it renders the original Measure useless.

Any ideas to do this directly in Dimension or in Mesaure? This is for AS 2000.

One of the standard way of doing an average in AS is to set up 2 measures, a SUM and a COUNT and then create a calculated measure for the average and set it up as the SUM / COUNT. If you don't need the original SUM and COUNT measures you can set their visible properties to false. This would create an average for a single measure.

If you had a lot of measures and they all have to be averaged you could try something like one the following which I think should work across all measures (but I have not tested it)

AVG([Test1].CurrentMember,1))

OR

SUM(descendants([Test1].CurrentMember,1))/COUNT(descendants([Test1].CurrentMember,1))

The descendants(<member>,1) will go down 1 level if there is one, otherwise it will return the member itself, which helps avoid the IIF(IsLeaf(...)) test. And not explicitly stating a measure *should* mean that the query will use the current measure context.

Custom aggregation value for all hierarchies'' levels above a specific level name?

I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.

The behavior that I'd want is:

If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.

I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.

Is this definitely possible and does it have minimal impact on query times and performance?

There might be a better design - if so, please comment and set me straight. Thanks!

I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)

But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.

How would one go about overriding the cube behavior?

If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.

|||

Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?

I've included syntax of 'currenthierarchy', but i don't know if that's really possible.

SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])

this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)

END SCOPE

Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?

Custom aggregation value for all hierarchies'' levels above a specific level name?

I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.

The behavior that I'd want is:

If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.

I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.

Is this definitely possible and does it have minimal impact on query times and performance?

There might be a better design - if so, please comment and set me straight. Thanks!

I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)

But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.

How would one go about overriding the cube behavior?

If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.

|||

Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?

I've included syntax of 'currenthierarchy', but i don't know if that's really possible.

SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])

this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)

END SCOPE

Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?

Custom aggregation value for all hierarchies'' levels above a specific level name?

I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.

The behavior that I'd want is:

If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.

I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.

Is this definitely possible and does it have minimal impact on query times and performance?

There might be a better design - if so, please comment and set me straight. Thanks!

I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)

But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.

How would one go about overriding the cube behavior?

If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.

|||

Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?

I've included syntax of 'currenthierarchy', but i don't know if that's really possible.

SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])

this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)

END SCOPE

Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?

Custom "ALL" Aggregation in Dimensions

Hallo

I have a dimension "Company scenarios" which shows me the "to market" sellings and the Intercompany sellings (Branches in other Cities). This dimension looks like:

All scenarios

Vienna

Vienna to market

Vienna to Amsterdam

Vienna to Paris

Amsterdam

Amsterdam to market

Amsterdam to Vienna

Amsterdam to Paris

Paris

Paris to market

Paris to Vienna

Paris to Amsterdam

The normal "All scenarios" with an aggregate-function of "Sum" has no business value, instead a value for us would be, if the "All scenarios" as an aggregate function of ([Vienna to market]+[Amsterdam to market]+[Paris to market]) - ([Vienna to Amsterdam]+[Vienna to Paris]+[Amsterdam to Vienna]+[Amsterdam to Paris]+[Paris to Amsterdam]+[Paris to Vienna])

Is it possible to do this in the cube?

Thanks

Hans

Hi Hans,

you can do it in a lot of ways, but probably the easiest way is to redefine the value of the member "All scenarios" using an assignment operator in the cube calculation.

Try to do this:

1) open your cube in BIDS

2) open Calculations Tab

3) open the Script View

4) Now , in the line after the "Calculate;" command, you have to add a code like this one :

[Company scenarios].[All scenarios] = ([Vienna to market]+[Amsterdam to market]+[Paris to market]) - ([Vienna to Amsterdam]+[Vienna to Paris]+[Amsterdam to Vienna]+[Amsterdam to Paris]+[Paris to Amsterdam]+[Paris to Vienna]);

Obviously I don't know the correct syntax for your cube but you can easily drag and drop every single member to obtain the correct one.

Francesco

|||

You could probably also make this calculation a bit more "data driven" by either filtering the set of members based on another attribute or possibly even looking for the "to market" string in the member name.Although in terms of pure performance the "hard coded" list of members may be faster.