Clarity

Where are the risk repsonse strategies stored in datawarehouse? I dont think they are. Why so? These attributes are not even shown on admin side and can not be added to dwh.

You can see those on Risk--> Response Strategy.

Hello Suhas,

I'm attaching a response from Anil Katta who answered a similar query which may help get you the reporting information you need.

- - - -

Question:

How do we report out Risk Response Strategies? Are there any out of the box reports (Jaspersoft), ad-hoc means, or portlet views that display the Response Strategies associated with a risk?

Answer:

The screenshot shows two Reports and few Queries that are available in Jaspersoft Advanced reporting related to the Risk.

You may utilise these queries to create new reports per requirements.

Else you can add the attributes from RISK object to the Data Warehouse and build the Adhoc views based on your requirement.

- - - -

Let us know if that helps.

Thanks, Kyle_R.

Hi Kyle,

None of those have response strategies. We know how to write those queries. The question to CA is about why create custom objects and tables if those cannot be added to datawarehouse? Those attributes of no use if cannot be reported in supposrted manner. Customers get furious when they hear that an attribute cannot be added to DWH, for whatever reason. Especially when you cannot create a subobject under risk to unnecessarily mimic the same OOTB feature that is locked for configuration. We cannot even hide response strategy subpage!.

You can write a materialized views On DWH database to get the risk repsonse strategies

Thanks

Senthil

DWH???

Which table and column?

Regards,

Suhas Kamble

RIM_RISK_MITIGATIONS

There is nothing like that in DWH

Regards,

Suhas

Hi Suhas

You have to create a materialized views in the data warehouse database.

Thanks

Senthil

This is sample materialized view to implement the solution.

1. Login data warehouse database and execute below script one by one.

CREATE MATERIALIZED VIEW Z_RIM_RISK_MITIGATIONS
REFRESH COMPLETE
AS

SELECT RIM.ID, RIM_RISK_ID, MITIGATION STRATEGY, R.FULL_NAME ASSIGNED_TO, DUE_DATE
FROM RIM_RISK_MITIGATIONS@PPMDBLINK RIM JOIN SRM_RESOURCES@PPMDBLINK R ON RIM.ASSIGNED_TO = R.USER_ID

CREATE OR REPLACE PROCEDURE Z_DWH_MV_REFRESH
AS
BEGIN
DBMS_MVIEW.REFRESH('Z_RIM_RISK_MITIGATIONS', 'C');
END;

2. you can execute SP "Z_DWH_MV_REFRESH" on DWH periodical basis or schedule or on-demand

                                                   or

create a process to schedule the process based on your business.

Sample Process:

<gel:script xmlns:core="jelly:core" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:ns1="http://soap.rpc.jira.atlassian.com"
xmlns:ns2="http://beans.soap.rpc.jira.atlassian.com" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:u="jelly:util"
xmlns:xog="http://www.niku.com/xog" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<gel:setDataSource dbId="Datawarehouse"/>
<core:catch var="v_InsertError">
<sql:update escapeText="0">
<![CDATA[
BEGIN
Z_DWH_MV_REFRESH();
END;
]]>
</sql:update>
</core:catch>
</gel:script>

This is great! Thanks a lot.

Regards,

Suhas Kamble

Is there a way this can be done in a SaaS environment where we don't have DB access?

Hi Janet.

You can't implement the solution in Saas environment due to some limitation.  you can create an Idea which product team can review and implement in future version.

Thanks

Senthil