Using a PowerCube with Report Studio

[rjhankey]

Curious if anyone has tackled a similar problem ...

I have data that contains financial loss occurrences by company, year, type of loss, and amount. The users need to generate a report that involves aggregating the amounts for each type of loss and company, for a year that has been selected at report generation time. To add to the challenge, a score is generated for each company/loss type combination using the ln function and aggregate amount data for each type for only the year selected.

My current thinking is to leverage a PowerCube to store the aggregate amounts by year, company, and loss type, and then have Report Studio lookup the values for the year selected at report generation time.

Is this feasible, or is there a better way to approach the solution that I'm not seeing yet?

[cognostechie]

The aggregation is exactly what the cube will do for you so you don't have to do it yourself.

Type Of Loss, Company and Year should all be dimensions or levels within a dimension. Year shoulod be a Time dimension by itself and the Date should be the source for the Time dimension. Amount should be the measure and then Transformer would aggregate the data.

Not sure what exactly you do to assign the score so can't say.

[rjhankey]

Thanks cognostechie ... there lies my problem though.  Not only do I want to "aggregate" loss amounts by the company, type, and year ... I would be in great shape if the cube could calculate the scores as well.

A score applies to each company / loss type combination.  For a given company and loss type, the score is the ...   - ln (aggregate loss amount / precalculated factor).  The aggregate loss amount can change at report run time as the user chooses to select different years worth of loss data, and other slicing criteria.

The cube isn't going to be able to provide the ln function, or at least I'm guessing that's the case.  What I figured I could do is have Report Studio return the applicable aggregates from the cube, and then do the labor of calculating the scores in the report?

[cognostechie]

I didn't see this post earlier so didn't see your reply. If you haven't found the solution already -

1> Yes, you can definately calculate the score in Report Studio but whatever function sare available in Report Studio are also available in the Data Source that you use to create the cube. Ex: If you are using a package or IQD to provide the data to the cube, those have the ability to use same functions so basically, you can create that calculation in the query that feeds the data to the cube so Transformer will receive the result of the calculation as a data item. It won't have to calculate by itself. If you do that, you will have to be careful about the fact that scores should not be aggregated because it may not be additive. So if you have a company dimension with Company as the 1st level and Loss Type as the 2nd level, you will need to stop agrregating the scores from Loss Type to Company. So either you can use an externally rolled up measure for this or you could have Company and Loss Type (BOTH) as the same level and assign a score for each Loss Type of each Company. That way, when you make a report to see score, you can use this dimension which would say something like -

Company - Loss Type                           Score
-------------------------------------------       -------
ABC Corporation - Loss Type One        6.5
ABC Corporation - Loss Type Two        3.0

Basically, stop the rollup for measures that are not additive. I think there are properties of the measure too in Transformer that can let you do this.