Cannot "test" SAP BW characteristic without key figure (could before!)

[FateAccompli]

Hi all,

I could not find the answer elsewhere, and we are like the blind leading the blind.  My team know Cognos, but not SAP, and are trying to work with a consultancy who knows SAP, but not Cognos!  Hopefully I can explain, and some whizz on here will have an answer for me :)

For a couple of weeks we have been working with a BEX query as a source (from Profitability Analysis data), as we build a suite of reports we ask the consultancy to create some new key figures.  Until recently I could right click a characteristic (dimension) in Framework Manager, choose Test, and would see some data.  Some 224 key values later (!!) all of a sudden this has stopped being the case, and reports with prompts on those characteristics have stopped showing options...

We have tried asking the consultant to back out changes in the BEX query, but that has not worked.  We even tried a cut-down query, but no.
I found that if I clicked the characteristic AND a key figure and then tested, it works!  However I cannot add a key figure to each report prompt and this seems wrong anyway.

I will also note, we have a General Ledger query and that is still working as the other query was before.  I.e. I can right click "Company Code" (which coincidentally appears in both sources) and click test and see 4 or so results.

I think that's enough information to wet your appetites, but there is more for those who need to know.

Please help, PLEASE as I'm losing hair far too rapidly.
Many thanks,
Phill...

[blom0344]

You mention Dimension. I suspect you build a DMR model? Can you ask the source side to run a trace against the DB to capture the SQL fired when you test the dimension? What do you mean by 'creating new key figures' ?
Did you test the BEX query itself. Does it bring back data?

[FateAccompli]

Hi blom0344,

No it's not a DMR model.  I'm just simply bringing a Bex query in to FM and publishing it out as a package at this stage.

When I say creating new key figures (the SAP term for measures) I mean we have been asking the SAP consultant to create new variants (e.g. Billing Value TM, Billing Qty LYTD etc.).  I cannot test the Bex query myself, but the consultant says he has and it works.  If I select dimensional and measure data together it works in FM, the issue is when I select ONLY dimensional data, and I don't believe the consultant can test the Bex query in this way?

Stand by for next post reply as there is an update...

Thanks, Phill..

[FateAccompli]

So, an update...

If I bring in the Bex query, but instead of bringing in all key figures (measures) I choose just one that I know to have values "Billing Qty", then I can right click a characteristic (dimension), e.g. Company Code and it tests OK, returning 8 results as I expect.

If I bring in the full query, it fails.  I note that the first key figure in the full query is an empty one (i.e returns no values), so I am wondering:

a) How does FM deal with a query on one single dimensional item only (SAP/Bex data source), does it pick a default key figure to "map" it to?
b) If yes, how does it decide which key figure to use, is it the first one in the Bex query?

[blom0344]

I know it is semantics, but you seem to mix things up a bit   ;)

'key' moniker  should be used for the unique identifier that normally is used for the _bussinesKey of the dimension level.  A measure dimension consists of one or more measures (facts) from the fact. Each measure in the measure dimension point to a fact , like Billing Qty. If you need to define 20 facts, then you define 20 seperate measures in the measure dimension.

Measures in the measure dimension can be used to perform calculations. You cannot do this with measure of 2 measure dimensions. So , basically you see what you define. If you define a measure against a field that stores nulls instead of values, you simply see nulls.

A default 'key' figure therefore does not exist. You need to tell Cognos which query part (data-item ) to use for which measure