Computing on a cube?

  • 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

  • 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

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

  • 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.

    ----------------------------------------------------

  • thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply