turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Proc OLAP: MDX Order of Operations for Weighted Av...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-15-2010 01:24 PM

I'm trying to compute a weighted average in an OLAP cube using a calculated member with some MDX logic but the order of operations is throwing me off and basically forcing a simple average calculation rather than using the weights. This is because the cube is aggregating the measures used in the MDX calc FIRST, then doing the math for the weighted average. I'm looking for a way to force the MDX calculation to do the weighted avg multiplication first, then do the sum aggregation.

Example:

In my dataset I've got mortgage loan-level information with things like current balance, LTV, DTI, etc. I need to create a weighted average of LTV, DTI, or any other variable using the Current Balance as the weight.

So logically the math would be this for Weighted Avg LTV:

1) at the loan level, multiply current balance by LTV to get the weighted LTV

2) sum the weighted LTV across all of the loans (or in the case of OLAP, whatever dimensions are being crossed)

3) sum the current balance (in the case of OLAP, whatever slice)

4) divide step 2 by step 3

5) Viola, weighted average

Any insight is appreciated. The problem is simple, its just figuring out how to control how the calculated member does its math. I could easily do the multiplication in the base dataset and calculate the weighted values but I'd rather do them in the cube.

This is the example define code in the proc OLAP statement along with the measure definitions. The first member define is the problem. I need to force it to NOT do the measure aggregation first. I think I need some function outside of the multiplication.

MEASURE CURR_LOAN_BAL_AMTSUM

STAT = SUM

COLUMN = CURR_LOAN_BAL_AMT

CAPTION = 'Current Loan Balance'

FORMAT = dollar22.2;

MEASURE ORIG_COMB_LTVMAX

STAT = MAX

COLUMN = ORIG_COMB_LTV

CAPTION = 'Combined LTV'

FORMAT = comma14.4;

/* Numerator for weighted average */

DEFINE Member '[Levels_Loan_Detail].[MEASURES].[Wtd CLTV]' AS

'([MEASURES].[CURR_LOAN_BAL_AMTSUM] * [MEASURES].[ORIG_COMB_LTVMAX]), FORMAT_STRING="COMMA15."';

/* Weighted average calculation */

DEFINE Member '[Levels_Loan_Detail].[MEASURES].[Wtd Avg CLTV]' AS

'([MEASURES].[Wtd CLTV] / [MEASURES].[CURR_LOAN_BAL_AMTSUM]), FORMAT_STRING="COMMA15.4"';

Example:

In my dataset I've got mortgage loan-level information with things like current balance, LTV, DTI, etc. I need to create a weighted average of LTV, DTI, or any other variable using the Current Balance as the weight.

So logically the math would be this for Weighted Avg LTV:

1) at the loan level, multiply current balance by LTV to get the weighted LTV

2) sum the weighted LTV across all of the loans (or in the case of OLAP, whatever dimensions are being crossed)

3) sum the current balance (in the case of OLAP, whatever slice)

4) divide step 2 by step 3

5) Viola, weighted average

Any insight is appreciated. The problem is simple, its just figuring out how to control how the calculated member does its math. I could easily do the multiplication in the base dataset and calculate the weighted values but I'd rather do them in the cube.

This is the example define code in the proc OLAP statement along with the measure definitions. The first member define is the problem. I need to force it to NOT do the measure aggregation first. I think I need some function outside of the multiplication.

MEASURE CURR_LOAN_BAL_AMTSUM

STAT = SUM

COLUMN = CURR_LOAN_BAL_AMT

CAPTION = 'Current Loan Balance'

FORMAT = dollar22.2;

MEASURE ORIG_COMB_LTVMAX

STAT = MAX

COLUMN = ORIG_COMB_LTV

CAPTION = 'Combined LTV'

FORMAT = comma14.4;

/* Numerator for weighted average */

DEFINE Member '[Levels_Loan_Detail].[MEASURES].[Wtd CLTV]' AS

'([MEASURES].[CURR_LOAN_BAL_AMTSUM] * [MEASURES].[ORIG_COMB_LTVMAX]), FORMAT_STRING="COMMA15."';

/* Weighted average calculation */

DEFINE Member '[Levels_Loan_Detail].[MEASURES].[Wtd Avg CLTV]' AS

'([MEASURES].[Wtd CLTV] / [MEASURES].[CURR_LOAN_BAL_AMTSUM]), FORMAT_STRING="COMMA15.4"';

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to StephenOverton

07-16-2010 07:15 AM

Hi

In my experience MDX skills are a rather rare good in the SAS world.

Do you have the "ORION" cubes at hand? And would it be possible to simulate your problem there? That would help a lot in searching for a solution.

Thanks

Patrick

P.S: What Olap Cube Studio version are you using? Message was edited by: Patrick

In my experience MDX skills are a rather rare good in the SAS world.

Do you have the "ORION" cubes at hand? And would it be possible to simulate your problem there? That would help a lot in searching for a solution.

Thanks

Patrick

P.S: What Olap Cube Studio version are you using? Message was edited by: Patrick

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Patrick

08-02-2010 04:57 PM

I figured out the solution using IIF logic in an MDX statement. BUT the hard part with this is you have to detect every possible dimension you want to compute the weighted average for. So I took the easy route and simply put the weights in the source dataset. Worked like a charm after that!