Month's end

Hi,

I am working on a cube that contains month standings and month totals. This works fine if I select one month.

It goes wrong when I do a year to date selection because it sums up the month standings.

How can I make a cube that does sum up the month total but only returns the last month standings.

With regards,

Constantijn Enders

Hi Constantijn,

If you configure the "month standings" measure (I don't know its details) with the aggregate function: LastNonEmpty, and you use Aggregate() rather than Sum() in your "year to date" calculation, does that work?

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

>>

SQL Server 2005 Books Online

Configuring Measure Properties

Measures have properties that enable you to define how the measures function and to control how the measures appear to users.

...

Property

Definition

AggregateFunction

Determines how measures are aggregated. For more information, see Aggregation Functions.

LastNonEmpty

Semiadditive

Retrieves the value of the last non-empty child member.

>>

|||Or, depending on your data, you may want to use more optimal LastChild aggregation function.sql

Months appear in alphabetical order in Excel 2003 pivot table

I have a SSAS 2005 cube attached to a pivot table in Excel 2003. When the months are dragged to the column area, they appear in alphabetical order (Apr, Aug, etc) instead of Jan, Feb, Mar.

The same cube displays the months properly in Visual Studio.

I know I can re-arrange the columns manual but that sort of ruins the point of OLAP if you have to do that every time.

Any pointers would be appreciated.

Make sure you have set the OrderBy property to Key (assuming you have a numeric key column for the month) and set the Type property to Months on the attribute and Time on the dimension. Setting the Type properties will also enable date range filters in Excel 2007 on an attribute of type Date.|||

Thanks for the response.

Unfortunately, all of the properties were already set as you ask.

One thing I am unclear on is the meaning of the term "key" in the OrderBy property. How do I know if the attribute has a numeric key?

Also, this is Excel 2003, not 2007.

|||

The answer is to create another attribute containing the values 1 - 12 (representing the various months) and set the Month variable OrderBy property to AttributeKey and OrderByAttribute to contain the name of the new attribute (you will have to relate the new attribute to the month attribute by expanding the month attribute and dragging the new attribute to <new attribute relationship> under the month attribute.

Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.

|||

john shahan wrote:

Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.

It sounds like you are using the string of the month name as the attribute key, as such SSAS is just sorting them alphabetically. If you had set your key column to be an actual date or a number it would have sorted using that. (the name and key don't have to be the same column). SSAS works with multiple languages so it will not automatically true to parse month names and sort them in a date order.

Months appear in alphabetical order in Excel 2003 pivot table

I have a SSAS 2005 cube attached to a pivot table in Excel 2003. When the months are dragged to the column area, they appear in alphabetical order (Apr, Aug, etc) instead of Jan, Feb, Mar.

The same cube displays the months properly in Visual Studio.

I know I can re-arrange the columns manual but that sort of ruins the point of OLAP if you have to do that every time.

Any pointers would be appreciated.

Make sure you have set the OrderBy property to Key (assuming you have a numeric key column for the month) and set the Type property to Months on the attribute and Time on the dimension. Setting the Type properties will also enable date range filters in Excel 2007 on an attribute of type Date.|||

Thanks for the response.

Unfortunately, all of the properties were already set as you ask.

One thing I am unclear on is the meaning of the term "key" in the OrderBy property. How do I know if the attribute has a numeric key?

Also, this is Excel 2003, not 2007.

|||

The answer is to create another attribute containing the values 1 - 12 (representing the various months) and set the Month variable OrderBy property to AttributeKey and OrderByAttribute to contain the name of the new attribute (you will have to relate the new attribute to the month attribute by expanding the month attribute and dragging the new attribute to <new attribute relationship> under the month attribute.

Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.

|||

john shahan wrote:

Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.

It sounds like you are using the string of the month name as the attribute key, as such SSAS is just sorting them alphabetically. If you had set your key column to be an actual date or a number it would have sorted using that. (the name and key don't have to be the same column). SSAS works with multiple languages so it will not automatically true to parse month names and sort them in a date order.

Monthly Average (Expected)

Hi, I want to create a calculation in the cube (thinking that it can be done on that way). Please help me, you'll save a life :P

Problem:

TICKET NR

CUSTOMER

CITY

MONTH

YEAR

PROBLEM COUNT

IM1234499

BEU

ISTANBUL

1

2006

1

IM1234500

CTB

IZMIR

1

2006

1

IM1234501

BEU

ISTANBUL

2

2006

1

IM1234502

CTB

IZMIR

2

2006

1

IM1234503

BEU

ANKARA

3

2006

1

IM1234504

ASE

ANKARA

3

2006

1

IM1234505

BEU

ISTANBUL

4

2006

1

IM1234506

CTB

IZMIR

4

2006

1

IM1234507

BEU

ANKARA

5

2006

1

IM1234508

ASE

ISTANBUL

5

2006

1

IM1234509

BEU

ISTANBUL

5

2006

1

IM1234510

CTB

IZMIR

6

2006

1

IM1234511

BEU

ANKARA

6

2006

1

IM1234512

ASE

ADANA

6

2006

1

IM1234513

BEU

ISTANBUL

6

2006

1

IM1234514

CTB

IZMIR

7

2006

1

IM1234515

BEU

ANKARA

7

2006

1

IM1234516

ASE

ANKARA

7

2006

1

IM1234517

BEU

ANKARA

8

2006

1

IM1234518

ASE

ANKARA

9

2006

1

IM1234519

BEU

ANKARA

9

2006

1

IM1234520

ASE

ANKARA

9

2006

1

IM1234521

BEU

ANKARA

9

2006

1

IM1234522

BEU

ANKARA

10

2006

1

IM1234523

BEU

ANKARA

11

2006

1

IM1234524

BEU

IZMIR

12

2006

1

IM1234525

ASE

ISTANBUL

1

2007

1

IM1234526

ASE

IZMIR

1

2007

1

IM1234527

BEU

ISTANBUL

2

2007

1

IM1234528

ASE

IZMIR

2

2007

1

The table above lists some ticket records in my db. I have two measure;

* Total Count of Ticket
* Average Count of Ticket

First one works fine as expected...

Total PROBLEM COUNT

YEAR

MONTH

2006

Total 2006

2007

Total 2007

Total

CUSTOMER

1

2

3

4

5

6

7

8

9

10

11

12

1

2

ASE

1

1

1

1

2

6

2

1

3

9

BEU

1

1

1

1

2

2

1

1

2

1

1

1

15

1

1

16

CTB

1

1

1

1

1

5

5

Total

2

2

2

2

3

4

3

1

4

1

1

1

26

2

2

4

30

Here is the pivot view yearly and monthly. The total results are produced by Total Count of Ticket.
My second measure's result is as I waited but not my expected one as you see below.

2006

TRANS_MONTH

TOTAL PROB

MONTH AVG OF YEAR

MY EXPECTED AVERAGE

ASE

5

6

1,2

0,5

BEU

12

15

1,25

1,25

CTB

5

5

1

0,4

2007

TRANS_MONTH

TOTAL PROB

MONTH AVG OF YEAR

MY EXPECTED AVERAGE

ASE

2

3

1,5

1,5

BEU

1

1

1

0,5

CTB

0

0

0

0

I think you've got my problem ;-) The average provided by my measure is
total ticket count / count of transaction month

but I want to calculate the average with some other measurement;

If the year ended (past year)
Monthly Average is calculated by total ticket count / 12

If the year not ended (current year)
Monthly Average is calculated by total ticket count / number of last month

Let me show over the tables...

ASE has 6 tickets for 5 months in 2006 and
my expected monthly average of 2006 for ASE is 6/12=0,5 (divided by 12 = end of 2006)

BEU has 1 tickets for 1 months in 2007 (my last recorded month on db is Feb 2007 also) and
my expected monthly average of 2007 for BEU is 1/2=0,5 (divided by 2 = Feb)

Well, I think there is a super-dev would explain how to do it with a calculation (calculated member, right?)
Not good at MDX but waiting for your solution.

Thanks.

I've built a very small solution based on your data.

It consists of 1 fact table and 4 degenerate dimensions (Ticket,Customer,City,Month).

I've created 2 calculated member: the first one count months in the year, the second calculate the average based on the first one

It works fine (the average returns the data you expect) but it could be not exactly what you want (your real business scenario should be more complex!).

If you contact me via e-mail I can send you the SQL db (actually only 1 table!!!) and the AS solution.

francesco.dechirico@.fastwebnet.it(donotspam!)

|||thanks it works fine
sql