Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Computing on a cube?


Computing on a cube?

Author
Message
raffaelet
raffaelet
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
Mackers
Mackers
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
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
raffaelet
raffaelet
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 91
Thank you for your reply, I updated my post with some details - but you pointed me in the right direction.
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1112 Visits: 2015
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.

----------------------------------------------------
How to post forum questions to get the best help
lordzoster
lordzoster
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 101
thank you



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search