Custom sorting

Hi,

I am having a cube with 6 dimensions. One of the columns is of

leave type which has the following:

Business
Vacation
Sick
Casual
Paid
Unpaid
Miscellaneous
Time Off

When I display this on the cube it displays in alphabetically sorted

format. Is there any way I can display it in a customized manner? For e.g. as

displayed above?

Would appreciate if anybody can guide me on how it can be

done.

Thanks and regards

PMNJYou could add an Annotation into Dimension.Annotations property for your dimension display ordinal. Use this property for sorting when you display the dimensions.

custom Discretization patterns

I have a problem with setting a reasonable Discretization pattern on some dimensions.

One of my dimensions has int numbers scattered from 400 to 900. another dimension has float numbers from 0 to 100, however Automatic discretization does not create even ranges for example 0-100,100-200,etc nor 0-10, 10-20, etc. I tried setting DiscretizationBucketCounts to 10,20,100 etc, however, AS does not create rounded bounds for the buckets.

Since the analisys applications are mostly for use by humans, it would make sense to add support for human-readable discretization, like is done in Excel Charts, etc.

Is there a way to setup my own discretization pattern (other than changing the data view to Query with a bunch of Case statements?) ?

From the ASSL documentation in SQL Server 2005 BOL, it looks like an "EqualRanges" discretization method was envisaged, which might have met your needs. Unfortunately, it doesn't seem to have been implemented (as of SP1), at least for DimensionAttribute:

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

>>

SQL Server 2005 Books Online

DiscretizationMethod Element (ASSL)

Defines the method to be used for discretization.

...

Relationship	Element
Parent elements	DimensionAttribute, ScalarMiningStructureColumn

...

EqualRanges

Equivalent to the EQUAL_RANGES discretization method for mining structure columns.

>>

|||

I tried other discretization methods, - Automatic, Clusters, Equal Ranges.

they all seem to group members on odd intervals, like 12.3-13.5, 13.5-14.6, etc.

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.