A table for historical Score status.

[steelblu]

My customer wants to see kpi status for a year together. User view is based on Xcelsius consuming Bex query(BW). So I have to build a BW for a year data. And I have checked below but still no clear solution yet.

CPMS_SCORE_STATUS is Including current status of KPIs. And is updated by 'calculate all scorecard' scheduler, which  does not allow any enty point by external program. Webservice gives status without updating CPMS_SCORE_STATUS. So I have not yet found any way to use the table as a source of full historical source. Then I should have not made a trigger for cummulative data table.

While the table does not include historical status, Scorecard for a KPI displays three months, which does not  exists in any CPMS table. Runtime calculation without any table update?

My question is simple. Is there an easy method to build a historical table which includes status for 12 months?

Any hint would be helpful.

Thanks,

[Former Member]

One solution might be to have PAS carry a new measure which is the KPI status. It would require you to duplcate the logic in the SSM front end but this would be simple to do, There are two options for how to do the calculation; do it in batch at load time, or do it as as a virtual variable.

Then it would be a matter of getting Xelsius to interogate PAS rather than the relational data. PAS / SSM has a cubeservices service that shoul dbe able to supply this data to Xcelsius.

[steelblu]

Thanks for good information. But I have no knowledge for SSM yet. So if you could give more detail example, it would be more helpful.

But I might miss a important requirement from customer. The status is served from BW since all data should be there in the customer's architecture. So it would be helpful to put data into a table.

The view is simmar with result of getAllKPIStatusByTime. Below is a example which can be supplyed to Xcelsius directly.

                    201201               201202               201203 ..............  201212

                    score status          score status  ................

KPI1         30   well below      31     well below .........

KPI2          .....................

.............................................

do it in batch at load time, or do it as as a virtual variable

I guess you might have something for solution. But I have not closed to that.... Please would give more detail! Thanks,

[Former Member]

I am somewhat confused by your reply. You say the status is served from BW. BW is not part of SSM although sometimes BW is used as a data source. In this regard BW is no different to any relational database or flat file from which SSM gets data.

SSM has a PAS database which contains the measure data and a relational database that usually contains user info and other non-numeric data. PAS holds Actuals, Target and Score. The colours are caclulated in the web front end based on the PAS data.

I am suggesting you use PAS code to caclulate a new measure representing the colour, (e.g. 1 = well below, 2 = below, 3 = meets, 4 = above and 5 = well above). Whereas the front end does not write back a time series of its caculations to the relational database, PAS can write a full time serries to its internal database.

Xcelsius could then use Web Services to interogate PAS for the colour time series.

[steelblu]

I meant the customer wanted the colour information to be loaded into BW via Oracle table.

Anyway, do you mean PAS code should include status calulation logic for? Is there any chance to write colour time series into Oracle table?

If possible, would you please show me PAS code example to calculate colour?

Thanks,

[Former Member]

I'm still confused. If you want Xcelsius to run over SSM why is BW in the mix? Xcelius can talk directly to SSM.

If you are going to use BW can you calculate the colour directly there? I get the impression that you are new to PAS and so unless I tested any code I posted it is likley to cause you further effort.

Do any of the CPMS tables give you actual, target and score for 12 months? If so BW caould read these and do the colour calculation.

If you are going to use BW, you need to consider how to get the PAS data to BW. PAS can consume BW data but BW can't consume PAS data. You'd need to export a CSV from PAS (easy) or use PAS to write to a relatinal table (difficult).

[steelblu]

I know Xcelsius can consume SSM web service. Actually, I have already tested it. Problem is from that the customer is requiring the data must go into BW and then Xcelsius read it.

I have checked CPMS_SCORE_STATUS which includes just one month actual, target, score and colorr status. And I thought I could make a historical table with whole time series if it is possible to run 'Calculate all scorecard' schedule for 12 times. But I have not found a way to run it with changing 'doc LASTDATE' automatically. It's all that I have done.

Colour should be calculated in SSM system. And I know there is a way to export PAS data but I don't think PAS includes colour info since the colour can be calculated with KPI index, which is not in PAS.

In my thought, you have an example for the colour calculation into PAS. If it is possible, I'm going to export PAS data to a CSV and load into BW.  Thanks,

[Former Member]

You are correct in saying PAS does not calculate colours. You would have to replicate the SSM colour calc in PAS using numbers to represent the 5 colours.

For index KPIs you would also have to replicate the SSM calculation of the index KPI. I don't use these since they can't give a full history. I prefer to use PAS to create a KPI that is based on other KPIs since that provides all the function of an index KPI and also gives the full 12 months (or more) history.

Replicating the colour calculations and the index KPI calculations can be done in either procedures or in the definition of the measure if it is created as a virtual measure. Given that you will need index KPIs and then over the top of that you will need the colour calculations, I recommend you use real variables calculated in procedures rather tahn virtual measures. Vitual measures can be tricky if you start building one on top of another,

[steelblu]

Thanks for your detailed answer. But I am shocked due to the difficulty. I would be better someday. Thanks,