Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Computing on a cube? Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 3:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, January 28, 2013 3:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
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
Posted Monday, January 28, 2013 3:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, April 11, 2013 5:11 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:05 PM
Points: 355, Visits: 870
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
Posted Monday, April 15, 2013 1:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, August 9, 2014 9:49 AM
Points: 12, Visits: 87
thank you


Post #1442182
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse