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.
I'm attaching a response from Anil Katta who answered a similar query which may help get you the reporting information you need.
- - - -
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?
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.
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
Which table and column?
There is nothing like that in DWH
You have to create a materialized views in the data warehouse database.
This is sample materialized view to implement the solution.
CREATE MATERIALIZED VIEW Z_RIM_RISK_MITIGATIONS REFRESH COMPLETEAS
SELECT RIM.ID, RIM_RISK_ID, MITIGATION STRATEGY, R.FULL_NAME ASSIGNED_TO, DUE_DATEFROM RIM_RISK_MITIGATIONS@PPMDBLINK RIM JOIN SRM_RESOURCES@PPMDBLINK R ON RIM.ASSIGNED_TO = R.USER_ID
CREATE OR REPLACE PROCEDURE Z_DWH_MV_REFRESHASBEGIN 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
create a process to schedule the process based on your business.
<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.
Is there a way this can be done in a SaaS environment where we don't have DB access?
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.