Computing on a cube?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » Computing on a cube?

Computing on a cube?

Rate Topic

Display Mode

Topic Options

Author

Message

raffaelet

Posted Monday, January 28, 2013 3:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:41 AM
Points: 7, Visits: 91

Hallo,
I'm totally new on computing on SSAS, and quite new to SSAS in general.
I'd like to get some index like
"tell me the total of contracts started in that year, having status different than '101', divided by the total of contracts ended in the previous year, for each agency (row) and for years from 2010 to 2012 (columns)".

The cube features dimensions StartDate, EndDate, Agencies, and Status.
Facts are contracts each one with datestart, dateend, amount, agent ID, and status.

I'm totally blank: where should I start from? KPIs? Aggregations? Computations?

Any help (even the minimal one) will be greatly appreciated.
Thanks in advance

Post #1412293

Mackers

Posted Monday, January 28, 2013 3:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:25 AM
Points: 117, Visits: 473

Not sure there is a great amount of detail to go off but I would create two measures - # of contracts started this year & # of contracts finished this year.
You may need to do this by creating distinct count measures or by creating MDX calculations (its difficult from the details to work out how best to do this)

You can then create MDX such as

WITH MEMBER [MEASURES].[CALCULATED MEMBER] AS

Sum({[Status].[All].Children - [Status].[101]},[MEASURES].[# contracts started this year])
/[MEASURES].[# of contracts finished this year]

SELECT {[YEARS].[2011],[YEARS].[2012]} ON COLUMNS,
[AGENCY] ON ROWS
FROM CUBE

Mack

Post #1412301

raffaelet

Posted Monday, January 28, 2013 3:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:41 AM
Points: 7, Visits: 91

Thank you for your reply, I updated my post with some details - but you pointed me in the right direction.

Post #1412304

mmartin1

Posted Thursday, April 11, 2013 5:11 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 13, 2013 2:02 PM
Points: 198, Visits: 656

Hi,

the total of contracts started in that year, having status different than '101', divided by the total of contracts ended in the previous year

Look into the function "ParallelPeriod" in MDX. You can create a calculated measure for the previous year then divide it by your current measure in your select. Hope this helps if this is still an open issue.

Post #1441525

lordzoster

Posted Monday, April 15, 2013 1:22 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, June 10, 2013 2:01 PM
Points: 11, Visits: 56

thank you

Post #1442182

« Prev Topic | Next Topic »

Permissions