A tool for aggregation design XMLA file viewing/editing ?

Is the best way to specify the most useful aggregations to actually edit the XMLA for measure group and hand pick the aggregations I need?

In that case has anyone seen a basic tool for viewing the attributes in each aggregation and perh
In taht case has aaps even deleting them/editing the XMLA file a little bit? The reason I am looking for a tool is to make sense out of 2000 aggregations created by the wizard.

A new sample app for viewing and editing aggregations should become available as part of SP2...

Thanks,

Akshai

|||what is the name of that sample? I have SP2 final, but only samples under Microsoft SQL Server\90\Sample\Analysis Services are:

\Administrator\ActivityViewer
\DataMining\Data Mining Web Controls
\DataMining\SQL Server Data Mining Plug-In Algorithms
\Programmability\AMO
\Tutorials\Lesson4
\Tutorials\Lesson7

I checked the Programmability\AMO sample, but all it does is provide drilldown into properties. I can drill into aggregations but it's very laborious to use to find see which attributes each aggregation aggregates, because you would need to click 2 times on each dimention in each aggregation to see which attributes it affects. I did not see an ability to create a new aggregation or change an aggregation.

Did I miss something?
Where is the sample that Edward and you mention ?

|||

It's called AggregationManager and is under Administrator/AggregationManager.

I don't think the samples are included in the main SP2 download. I think you have to download them separately. The SQL Server sample are now all available from codeplex. The download page for the SSAS samples is here http://www.codeplex.com/MSFTASProdSamples/Release/ProjectReleases.aspx?ReleaseId=2461

The AggManager sample only works with a deployed database. I am looking to add support for this tool directly in BIDS through a project I am working on at www.codeplex.com/BIDSHelper. I hope to get an initial beta release out in the next week or two if you are interested.

A tool for aggregation design XMLA file viewing/editing ?

Is the best way to specify the most useful aggregations to actually edit the XMLA for measure group and hand pick the aggregations I need?

In that case has anyone seen a basic tool for viewing the attributes in each aggregation and perh
In taht case has aaps even deleting them/editing the XMLA file a little bit? The reason I am looking for a tool is to make sense out of 2000 aggregations created by the wizard.

A new sample app for viewing and editing aggregations should become available as part of SP2...

Thanks,

Akshai

|||what is the name of that sample? I have SP2 final, but only samples under Microsoft SQL Server\90\Sample\Analysis Services are:

\Administrator\ActivityViewer
\DataMining\Data Mining Web Controls
\DataMining\SQL Server Data Mining Plug-In Algorithms
\Programmability\AMO
\Tutorials\Lesson4
\Tutorials\Lesson7

I checked the Programmability\AMO sample, but all it does is provide drilldown into properties. I can drill into aggregations but it's very laborious to use to find see which attributes each aggregation aggregates, because you would need to click 2 times on each dimention in each aggregation to see which attributes it affects. I did not see an ability to create a new aggregation or change an aggregation.

Did I miss something?
Where is the sample that Edward and you mention ?|||

It's called AggregationManager and is under Administrator/AggregationManager.

I don't think the samples are included in the main SP2 download. I think you have to download them separately. The SQL Server sample are now all available from codeplex. The download page for the SSAS samples is here http://www.codeplex.com/MSFTASProdSamples/Release/ProjectReleases.aspx?ReleaseId=2461

The AggManager sample only works with a deployed database. I am looking to add support for this tool directly in BIDS through a project I am working on at www.codeplex.com/BIDSHelper. I hope to get an initial beta release out in the next week or two if you are interested.

A Sample of an MDX query?

Hello,

I have a cube that includes a measure called student count that is pivoted as data below, row called student statuses and column being a time dimension. The one thing left that I would like to accomplish is to create two extra calculated measures called Difference and %Changed using MDX or any other method? Does anyone has some MDX samples or ideas how I could calculate those two columns in blue in my SQL Server Analysis 2005 cube?

Any help is greatly appreciated!

Spring Semester 2006Spring Semester 2005Difference%ChangeUndergraduates Count490045004008.89%Graudates Count100090010011.11%

You can use Business Intelligence Development Studio to add your calculated measures. Start BIDS in Program Files->Microsoft Sql Server 2005->Sql Server Business Intelligence Development Studio and open your database. Double click on the cube and after the cube is opened, click on to the 3rd tab which says "Calculations" to add new calculated members. For more information, check http://msdn2.microsoft.com/en-us/library/ms169748.aspx.

HTH,

Chu

A little MDX problem

Hi,

I need some support for a MDX problem.

We have a cube with a measure, are region (with hierachy, but this doesn't matter at all) and two time dimensions (with the usual hierachies). The business problem is now:

You select a date (on any level) from Time1. If you would place the region on rows and Time2 on colums (the days) with nonempty enabled, you would get a number of members back (about 1000 numbers in Time2 per day in Time1). I don't what to see that 1000 numbers, I need a measure which is calulated like this:

- Sort these numbers by value

- Find the number which is as position 99%, so i.e. if you have 1000 numbers (but this number can change), you need the 990th member. So you have to count the members, multiply by .99 and you have the ordinal of the member you need

So: how to do this in MDX? I'm quite struggeling around with counts and sorts, the 1000 are returned quite quickly but when I start to sort them it's getting veeeerrrrryyyy slow...

I would be very happy if someone can help me with that...

Hi Thomas,

There are some problem parameters that I'm not sure of, so I'll assume that:

- Ordering is along a pre-determined level of a hierarchy (not based on query axes)

- Ordering is by a pre-determined measure (again, not dynamically determined)

- Desired position is 99th percentile ascending (ie. 1st percentile descending)

Based on these assumptions, here is an Adventure Works query which returns the [Date] name, value and ordinal of the 99th percentile [Order Count], by Product Category on rows:

>>

With

Member [Measures].[Orders99thMember] as

MemberToStr(Tail(TopCount(NonEmpty([Date].[Calendar].[Date].Members,

{[Measures].[Order Quantity]}) as DS,

Int(DS.Count/100)+1, [Measures].[Order Quantity])).Item(0).Item(0))

Member [Measures].[Orders99thName] as

StrToMember([Measures].[Orders99thMember]).Name

Member [Measures].[Orders99thValue] as

(StrToMember([Measures].[Orders99thMember]),

[Measures].[Order Quantity])

Member [Measures].[Orders99thOrdinal] as

CInt(99 * (NonEmpty([Date].[Calendar].[Date].Members,

{[Measures].[Order Quantity]}).Count)/ 100)

select {[Measures].[Order Quantity],

[Measures].[Orders99thName], [Measures].[Orders99thValue],

[Measures].[Orders99thOrdinal]} on 0,

NonEmpty([Product].[Product Categories].[Category].Members,

{[Measures].[Order Quantity]}) on 1

from [Adventure Works]

Order Qty Orders99thName Orders99thValue Orders99thOrdinal
Accessories 61,931 November 1, 2003 1663 417
Bikes 90,220 February 1, 2003 2654 1082
Clothing 73,598 July 1, 2003 3200 417
Components 49,027 September 1, 2003 4365 38

>>

|||

Deepak,

thanks for you excellent support... But one question: Where do you do the "sort" by order quantity? I do only see that you return the 99th procentile of the set... I don't think your assuptions will help since you can't define that a set is always sorted by a measure... I understand your second assumption that you mean that it's always the order quatity and not sometimes the quantity and sometimes the amount...

Thanks,

|||Sorry, I guess the topcount does the job... Thanks, it's not very fast but it does the job...