How can i create this metric in rpd

ForSly

I have created a  table based on the view below

I can get a count of  ACT_CAND which is the metric I am interested in by

running this script (

SELECT DISTINCT CAN_NUM,

J_GROUP,

PI_COMP_DT,

ACT_CAND FROM V_DIST_FACT3

order by ACT_CAND,CAN_NUM,

J_GROUP,

PI_COMP_DT;

)in the  database, I wanted to know if there is anyway I can create this metric based on this script in the rpd or in the analysis.

SELECT "CAN_NUM","J_GROUP","PI_COMP_DT","LAST_PI_COMP_DT","DIFF_DATE","J_FAM_N","R_NUM","LST_SM_DT","INTEGRATION_ID","WRKFLW_NM","TRK_STS_NAM","TRK_STEP_NAM","MTVE_NAME","ACT_CAND" FROM

                (

                    SELECT 

                                APP_FACT.PI_CANDIDATE_NUM AS CAN_NUM,

                                JOB_INFO_D.J_GROUP AS J_GROUP,

                                APP_FACT.PI_COMP_DT AS PI_COMP_DT,

                                NULL AS LAST_PI_COMP_DT,

                                NULL AS DIFF_DATE,

                                JOB_INFO_D.J_FAM_N AS J_FAM_N,

                                REQN_D.R_NUM AS R_NUM,

                                APP_FACT.LAST_SBM_DT AS LST_SM_DT,

                                APP_FACT.INTEGRATION_ID AS INTEGRATION_ID,

                                APP_FACT.WRKFLW_NM AS WRKFLW_NM,

                                APP_FACT.TRK_STS_NAM AS TRK_STS_NAM,

                                APP_FACT.TRK_STEP_NAM AS TRK_STEP_NAM,

                                MOTIVES_D.MTVE_NAME AS MTVE_NAME,

                                CASE

                                      WHEN APP_FACT.INITIAL_APP_MEDIUM_ROW_WID IN

                                            (SELECT ROW_WID FROM WC_APPLICATION_MEDIUM_D WHERE CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME'))

                                            OR APP_FACT.APP_MEDIUM_ROW_WID IN

                                           (SELECT ROW_WID FROM WC_APPLICATION_MEDIUM_D WHERE CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME'))

                                      THEN 0

                                      ELSE null END ACT_CAND

                              FROM

                                ABC_EVENT_F APP_FACT,

                                DEF_ROD_D REQN_D,

                                GHI_INFO_D JOB_INFO_D,

                                JKL_APPLE_MTV_D MOTIVES_D

                                WHERE APP_FACT.REQUISITION_ROW_WID = REQN_D.ROW_WID

                                AND APP_FACT.JOB_INFO_ROW_WID = JOB_INFO_D.ROW_WID

                                AND MOTIVES_D.APP_EVENT_WID = APP_FACT.APPL_CSW_MOTIVES_WID

        GROUP BY APP_FACT.PI_CANDIDATE_NUM A,

                                JOB_INFO_D.J_GROUP ,

                                APP_FACT.PI_COMP_DT ,

                                NULL AS LAST_PI_COMP_DT,

                                NULL AS DIFF_DATE,

                                JOB_INFO_D.J_FAM_N ,

                                REQN_D.R_NUM A,

                                APP_FACT.LAST_SBM_DT ,

                                APP_FACT.INTEGRATION_ID ,

                                APP_FACT.WRKFLW_NM,

                                APP_FACT.TRK_STS_NAM ,

                                APP_FACT.TRK_STEP_,

                                MOTIVES_D.MTVE_NAME ,

                                CASE

                                      WHEN APP_FACT.INITIAL_APP_MEDIUM_ROW_WID IN

                                            (SELECT ROW_WID FROM WC_APPLICATION_MEDIUM_D WHERE CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME'))

                                            OR APP_FACT.APP_MEDIUM_ROW_WID IN

                                           (SELECT ROW_WID FROM WC_APPLICATION_MEDIUM_D WHERE CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME'))

                                      THEN 0

                                      ELSE null

                                ORDER BY APP_FACT.PI_CANDIDATE_NUM,APP_FACT.INTEGRATION_ID,JOB_INFO_D.J_GROUP ASC

                        ) IR1 WHERE IR1.ACT_CAND = 0;

Gianni Ceresa

Sorry but how can you get a count of something by running a SELECT DISTINCT ?

To count something you are supposed to have a COUNT() and GROUP BY or use the relative analytical function.

Ignoring the incoherence of the "SQL" not sure what you are looking for: you want to know how to get OBIEE to make a SELECT on your view?

Load the view in the RPD, model it through the 3 layers, make an analysis on top of it and there you go.

ForSly

the reason I am doing distinct count is because there are a lot of duplicates, so that takes care of it. I tried the count and I am not getting correct count. I have modeled my rpd  all I want to know is how can I get a count with a group by without duplicates in either rpd or analysis.

Gianni Ceresa

If you model things as fact and dimension, all the attribute in the dimension will have an implicit distinct and be unique. For the fact you simply need to adjust the fact column to correspond to the combination of columns you need to do a COUNT(DISTINCT  ) on. You set "count distinct" as aggregation and done.

The column itself doesn't need to be a single physical column, could be the combination of columns giving you the combinations you want to count as distinct ignoring duplicates.

ForSly

How do you do this part For
the fact you simply need to adjust the fact column to correspond to the
combination of columns you need to do a COUNT(DISTINCT  ) on. You set
"count distinct" as aggregation and done

Gianni Ceresa

What is your measure? What makes it unique? As you are looking for a COUNT it's often possible to use alternative columns which have the required granularity. If not exists it's up to you to create it: you can concatenate together multiple columns etc.

You need to open up your mind thinking at alternatives giving the same result, a COUNT is way more flexible than a SUM for which you can't change the column at all.

You are the only one having your data and model and knowing it, so can't really tell you exactly what to do ....

ForSly

ACT_CAND is my measure and its a count based on

J_GROUP,

PI_COMP_DT,

CAN_NUM

Gianni Ceresa

You are counting the number of occurrences of ACT_CAND not being null, that's your measure. And because of duplicate you could it in a distinct way based on J_GROUP, PI_COMP_DT and CAN_NUM.

Now look around your dataset and check if there isn't something which already had the right granularity, it's a COUNT, it could work on any other column if the granularity is right.

Globally, the main thing is that you need to stop to write SQL and then ask OBIEE to do the same thing: that's the wrong tool for the wrong job. OBIEE generates queries, so what's the point in you writing the queries first? And you also maybe need to question your dataset: is it the right dataset for the job?

Because sure there are ways to get what you look for out of OBIEE, but it would be easier to use the right tool for the right job with the right data, don't you think?

ForSly

I wrote the sql to test my counts and I wasn't expecting to use same sql on the front end. My question was how I could do something similar to get the correct results. in this case I know  J_GROUP, PI_COMP_DT and CAN_NUM are in right granuality and I can get the right count if I do distinct count and then group by same(J_GROUP, PI_COMP_DT and CAN_NUM ) problem is am mot sure how  I could not distinct count and group by in rdp.