Unable to create analytics report due to huge data

[Shubhamjain27]

Hi,

I have created analytics query and working fine in data analyser as it returns on 100 rows.

Now when I try to create analytics with the same query and click on create, it just loads and doesnt do anything.

The data we have is around 3-4lakh rows.

Below is the query:

SELECT DISTINCT
sd.riskcode as 'Risk Name',
r.description as 'Risk Description',
CASE
WHEN r.priority = 0 THEN 'Medium'
WHEN r.priority = 1 THEN 'High'
WHEN r.priority = 2 THEN 'Low'
WHEN r.priority = 3 THEN 'Critical'
WHEN r.priority = 4 THEN 'Very Low'
ELSE 'None'
END AS 'Risk Priority',
f.FUNCTION_NAME,
ep.endpointname as 'Application',
u.username as 'User Name',
u.firstname as 'User First Name',
u.lastname as 'User Last Name',
ev1.entitlement_value as saprole,
ev.entitlement_value as Tcode,
acc1.name as SAPUSER,
acc.name as Ref_user,
CASE
WHEN sd.status = 1 THEN 'New'
WHEN sd.status = 2 THEN 'In Progress'
WHEN sd.status = 3 THEN 'Risk Accepted'
WHEN sd.status = 4 THEN 'Closed'
WHEN sd.status = 5 THEN 'Remediated'
ELSE 'None'
END AS 'Violation Status',
bp.busprocname as 'Business Process'
FROM
sodrisks sd
LEFT OUTER JOIN
users u ON (sd.userkey = u.userkey)
LEFT OUTER JOIN
risks r ON (sd.riskkey = r.riskid)
LEFT OUTER JOIN
busprocs bp ON (r.bpkey = bp.busprockey)
LEFT OUTER JOIN
riskowners ro ON (r.riskid = ro.riskid)
LEFT OUTER JOIN
users uu ON (ro.OWNERUSERKEY = uu.userkey)
LEFT OUTER JOIN
user_groups ug ON (ro.OWNERUSERGROUPKEY = ug.USERGROUPKEY)
LEFT OUTER JOIN
sodrisk_entitlement se ON (sd.sodkey = se.sodkey)
LEFT OUTER JOIN
accounts acc on (se.accountkey=acc.accountkey)
LEFT OUTER JOIN
endpoints ep on (acc.endpointkey=ep.endpointkey)
LEFT OUTER JOIN
functions f ON (se.functionkey = f.functionkey)
LEFT OUTER JOIN
entitlement_values ev ON (se.tcodekey = ev.ENTITLEMENT_VALUEKEY)
LEFT OUTER JOIN
entitlement_values ev1 ON (se.ASSOCIATEDSAPROLEKEY = ev1.ENTITLEMENT_VALUEKEY)
LEFT OUTER JOIN
accounts acc1 ON (acc.accountkey=acc1.REFERENCED_ACCOUNTKEY)
union all
SELECT DISTINCT
sd.riskcode as 'Risk Name',
r.description as 'Risk Description',
CASE
WHEN r.priority = 0 THEN 'Medium'
WHEN r.priority = 1 THEN 'High'
WHEN r.priority = 2 THEN 'Low'
WHEN r.priority = 3 THEN 'Critical'
WHEN r.priority = 4 THEN 'Very Low'
ELSE 'None'
END AS 'Risk Priority',
f.FUNCTION_NAME,
ep.endpointname as 'Application',
u.username as 'User Name',
u.firstname as 'User First Name',
u.lastname as 'User Last Name',
ev1.entitlement_value as saprole,
ev.entitlement_value as Tcode,
acc.name as SAPUSER,
'' as Ref_user,
CASE
WHEN sd.status = 1 THEN 'New'
WHEN sd.status = 2 THEN 'In Progress'
WHEN sd.status = 3 THEN 'Risk Accepted'
WHEN sd.status = 4 THEN 'Closed'
WHEN sd.status = 5 THEN 'Remediated'
ELSE 'None'
END AS 'Violation Status',
bp.busprocname as 'Business Process'
FROM
sodrisks sd
LEFT OUTER JOIN
users u ON (sd.userkey = u.userkey)
LEFT OUTER JOIN
risks r ON (sd.riskkey = r.riskid)
LEFT OUTER JOIN
busprocs bp ON (r.bpkey = bp.busprockey)
LEFT OUTER JOIN
riskowners ro ON (r.riskid = ro.riskid)
LEFT OUTER JOIN
users uu ON (ro.OWNERUSERKEY = uu.userkey)
LEFT OUTER JOIN
user_groups ug ON (ro.OWNERUSERGROUPKEY = ug.USERGROUPKEY)
LEFT OUTER JOIN
sodrisk_entitlement se ON (sd.sodkey = se.sodkey)
LEFT OUTER JOIN
accounts acc on (se.accountkey=acc.accountkey)
LEFT OUTER JOIN
endpoints ep on (acc.endpointkey=ep.endpointkey)
LEFT OUTER JOIN
functions f ON (se.functionkey = f.functionkey)
LEFT OUTER JOIN
entitlement_values ev ON (se.tcodekey = ev.ENTITLEMENT_VALUEKEY)
LEFT OUTER JOIN
entitlement_values ev1 ON (se.ASSOCIATEDSAPROLEKEY = ev1.ENTITLEMENT_VALUEKEY)

Is there anyway to fix this?

[Manu269]

Any specific error you see in logs?

The analytics anyhow should get created. I can understand the problem can be while executing the analytics.

But you can schedule and add an email template to notify.

[Shubhamjain27]

It is not even creating the analytics. and yeah no errors in the logs 

[rushikeshvartak]

SELECT 
    sd.riskcode AS 'Risk Name',
    r.description AS 'Risk Description',
    CASE
        WHEN r.priority = 0 THEN 'Medium'
        WHEN r.priority = 1 THEN 'High'
        WHEN r.priority = 2 THEN 'Low'
        WHEN r.priority = 3 THEN 'Critical'
        WHEN r.priority = 4 THEN 'Very Low'
        ELSE 'None'
    END AS 'Risk Priority',
    f.FUNCTION_NAME,
    ep.endpointname AS 'Application',
    u.username AS 'User Name',
    u.firstname AS 'User First Name',
    u.lastname AS 'User Last Name',
    ev1.entitlement_value AS saprole,
    ev.entitlement_value AS Tcode,
    acc1.name AS SAPUSER,
    acc.name AS Ref_user,
    CASE
        WHEN sd.status = 1 THEN 'New'
        WHEN sd.status = 2 THEN 'In Progress'
        WHEN sd.status = 3 THEN 'Risk Accepted'
        WHEN sd.status = 4 THEN 'Closed'
        WHEN sd.status = 5 THEN 'Remediated'
        ELSE 'None'
    END AS 'Violation Status',
    bp.busprocname AS 'Business Process'
FROM
    sodrisks sd
LEFT JOIN users u ON sd.userkey = u.userkey
LEFT JOIN risks r ON sd.riskkey = r.riskid
LEFT JOIN busprocs bp ON r.bpkey = bp.busprockey
LEFT JOIN riskowners ro ON r.riskid = ro.riskid
LEFT JOIN accounts acc ON sd.sodkey = acc.accountkey
LEFT JOIN endpoints ep ON acc.endpointkey = ep.endpointkey
LEFT JOIN functions f ON sd.sodkey = f.functionkey
LEFT JOIN entitlement_values ev ON sd.sodkey = ev.ENTITLEMENT_VALUEKEY
LEFT JOIN entitlement_values ev1 ON sd.sodkey = ev1.ENTITLEMENT_VALUEKEY
LEFT JOIN accounts acc1 ON acc.accountkey = acc1.REFERENCED_ACCOUNTKEY

UNION ALL

SELECT 
    sd.riskcode AS 'Risk Name',
    r.description AS 'Risk Description',
    CASE
        WHEN r.priority = 0 THEN 'Medium'
        WHEN r.priority = 1 THEN 'High'
        WHEN r.priority = 2 THEN 'Low'
        WHEN r.priority = 3 THEN 'Critical'
        WHEN r.priority = 4 THEN 'Very Low'
        ELSE 'None'
    END AS 'Risk Priority',
    f.FUNCTION_NAME,
    ep.endpointname AS 'Application',
    u.username AS 'User Name',
    u.firstname AS 'User First Name',
    u.lastname AS 'User Last Name',
    ev1.entitlement_value AS saprole,
    ev.entitlement_value AS Tcode,
    acc.name AS SAPUSER,
    '' AS Ref_user,
    CASE
        WHEN sd.status = 1 THEN 'New'
        WHEN sd.status = 2 THEN 'In Progress'
        WHEN sd.status = 3 THEN 'Risk Accepted'
        WHEN sd.status = 4 THEN 'Closed'
        WHEN sd.status = 5 THEN 'Remediated'
        ELSE 'None'
    END AS 'Violation Status',
    bp.busprocname AS 'Business Process'
FROM
    sodrisks sd
LEFT JOIN users u ON sd.userkey = u.userkey
LEFT JOIN risks r ON sd.riskkey = r.riskid
LEFT JOIN busprocs bp ON r.bpkey = bp.busprockey
LEFT JOIN riskowners ro ON r.riskid = ro.riskid
LEFT JOIN accounts acc ON sd.sodkey = acc.accountkey
LEFT JOIN endpoints ep ON acc.endpointkey = ep.endpointkey
LEFT JOIN functions f ON sd.sodkey = f.functionkey
LEFT JOIN entitlement_values ev ON sd.sodkey = ev.ENTITLEMENT_VALUEKEY
LEFT JOIN entitlement_values ev1 ON sd.sodkey = ev1.ENTITLEMENT_VALUEKEY;

[Shubhamjain27]

Not getting the correct result.

[rushikeshvartak]

• Are you able to save analytics ?
• What is wrong data ?

[Shubhamjain27]

1. Able to save the analytics

2. Giving 4k results rather than 4l and also not related to SOD also coming.

[rushikeshvartak]

Run query in parts and find which union selection have issue