using determinants/correctly summing denormalized data

[griftr1]

I've set up a consolidated report in framework manager with several levels of data. The data is about requests, each request can have multiple items, each item is sent to multiple vendors and each vendor could have come back with 0 or more candidates. The data has one row per candidate, or in the case of no candidates submitted, one row for the supplier.

There are facts/measures at various levels in the dataset, like the average number of days for a vendor to respond or the number of candidates requested on each item. I set up proper determinants for each piece of data, req/item/supplier are set to group by, candidates are set as unique.

I need to set up an Analysis Studio report for people to use to be able to answer all their questions (What is the average # of candidates requested in area A? what is the total number of positions requested in January? etc). I've set up Transformer with various measures and dimensions at multiple levels, built the cube, but Analysis Studio doesn't calculate the averages and counts properly (it counts, for example, the item quantity multiple times for each supplier/candidate).

Here are my questions:

1. Is a report like this possible with one Analysis Studio report? Or do I need to split it up into multiple for each level?

2. Do the FM determinants only work if all the items I select are from one level? So it will correctly calculate the total item quantity if all I select are item level, but if I add candidate into the mix it'll fail?

3. Should I set the candidate level as a "Group By" as well and remove all the non-candidate items from the properties? Or am I off with that solution? (I'm going to try that next)

Thanks for any help you can offer.

 

[griftr1]

I tried turning off the Unique for the Candidate level and turned on Group By, adding to the candidate properties only those items that specifically applied to the candidate, but that didn't work either. The item level measures are still being overcounted.

Does anyone have any idea of how to get around this problem? I've thought about creating a weight temporary measure, like [1 / Number of Candidates], so that if an item has 5 candidates, the temp measure would be 1/5, and I could weight the other measures by that (so the quantity total would be counted 5 times, but multiplied by 1/5 each time to equal a total of 1). But this would only work if you were totalling at the item level, below that and the quantity totals would be all fractions. So that's not a very elegant solution.

Any ideas would be greatly appreciated.

 

[blom0344]

I do not think that determinants are going to correct for measures at 4 levels. Determinants basically work in 2 cases:

1. 2 facts with different grain converge on to 1 - time -dimension , like daily income and monthly budget. Determinants can then automatically aggregate the income to - at least - the month level.

2. In case of orderheader - orderdetail (1:n) determinants can ensure that facts from the 1 side are not overcounted.

I must admit my experience deals with relational/DMR models, not with generating transformer cube models.

The easiest perhaps is to recalculate the needed facts to the lowest level, as facts from the lowest level can always be rolled up.

Is ' average number of days for a vendor to respond ' a measure?
What type of calculation would you want to perform with it?

Ties Blom