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

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 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 fields in SSAS 2005 KPI's

Hi there,

What is the best way to get a custom field in a SSAS 2005 Cube KPI?
For example, what if the user wanted two target fields, or a budget field, or a contact person field for a KPI? (mostly static data, hard coded by the user).

Thanks.

You can use the Annotations property of KPIs (and other AS 2005 objects) to store custom information. The annotations is a property bag of name-value pairs and can contain most anything you like (including complex XML). If you set Visibility=SchemaRowset on an annotation, then the annotation will be available in the schema rowset and can be used by client applications. The down side to this approach is that off-the-shelf clients will generally ignore annotations and you'll probably need a custom client application to look for and use the annotations.

Custom Assembly generates error in Aggregation Wizard

I have a fairly simple custom assembly for adding some functions to a cube. Having a reference to the assembly in the cube project causes the following error when I try and run the aggregation wizard for the cube:

Message: Errors in the metadata manager. The system assembly cannot be modified by a user request. To restart the process, resolve the problem and then click start.

I get the message after the "Specify Object Counts" step, when it starts the process of determining the aggregations.

Is this a bug or is there something I need to do in the custom assembly differently? I started with the code framework from the AdventureWorksAMO sample project, and added/removed functions.

After applying SP1, I now get the following error when running the aggregation wizard:

"clr assembly must have main file specified"

The workaround is to remove the assembly reference and then run the aggregation wizard, and then re-add the assembly reference.

Although this works, it bring into question why the error in the first place? Any CLR assembly I've tested causes the same problem.

|||

This error will be fixed in the next SP, meanwhile the work-around is indeed to remove the assemblies and add them back after designing aggregations.

About why the error happens in the first place:

- when ran from BI development studio, the aggregation design wizard can have unsaved changes in the background (in the cube and/or dimension editors). or the entire BI project database might not have been deployed

- the wizard requires the database to be saved to the server in order to use the aggregations designing algorithm, so it does temporarily deploy the database (in a transaction that is being rolled back at the end); this is where the error about the assemblies happens

Adrian Dumitrascu

Custom Assembly generates error in Aggregation Wizard

I have a fairly simple custom assembly for adding some functions to a cube. Having a reference to the assembly in the cube project causes the following error when I try and run the aggregation wizard for the cube:

Message: Errors in the metadata manager. The system assembly cannot be modified by a user request. To restart the process, resolve the problem and then click start.

I get the message after the "Specify Object Counts" step, when it starts the process of determining the aggregations.

Is this a bug or is there something I need to do in the custom assembly differently? I started with the code framework from the AdventureWorksAMO sample project, and added/removed functions.

After applying SP1, I now get the following error when running the aggregation wizard:

"clr assembly must have main file specified"

The workaround is to remove the assembly reference and then run the aggregation wizard, and then re-add the assembly reference.

Although this works, it bring into question why the error in the first place? Any CLR assembly I've tested causes the same problem.

|||

This error will be fixed in the next SP, meanwhile the work-around is indeed to remove the assemblies and add them back after designing aggregations.

About why the error happens in the first place:

- when ran from BI development studio, the aggregation design wizard can have unsaved changes in the background (in the cube and/or dimension editors). or the entire BI project database might not have been deployed

- the wizard requires the database to be saved to the server in order to use the aggregations designing algorithm, so it does temporarily deploy the database (in a transaction that is being rolled back at the end); this is where the error about the assemblies happens

Adrian Dumitrascu