Using character match to filter records

ForSly · Jul 16, 2018 7:11AM

I am working on OBIEE project that requires me to get a count of all candidates that were hired as interns, the problem I am facing is I cant use %intern% in the metric or filter because there are jobs with words such as internal or internet which end up getting counted and thereby skewing my numbers. so my question is how do capture only interns and nothing else.

Thomas Dodds · Jul 16, 2018 7:28AM

Use of information drives data design ... if the employee currently is NOT an intern, then you should have a slowly changing dimension that has the history of when they were. This means you need a history of the employee ID, type/status, and effective date. Furthermore the status/type of an employee is not the same data as the job description or role they fulfill. Again, seems like the use of information wasn't considered in the design of your data store. I'd had have a table (type-2 SCD)) for both employees and positions/roles/job and a bridge table that relates an employee to a position across an effective period of time. Now you have a history of who, what and when. This issue is not an OBIEE issue - it's a data modeling (physical) issue.

ForSly · Jul 16, 2018 8:52AM

Thanks very much, let me tinker with it , I think the type -2 SCD seems more plausible.

Thomas Dodds · Jul 16, 2018 12:49PM

with SCD-2 you can have 'views' of the historical data ... DW_EMPLOYEE physical table can be represented with

DW_EMPLOYEE_LST (where current_flg = 'Y') <-- current representation
DW_EMPLOYEE_FST (where rank() on eff_dt = 1) <-- how the employee looked on day 1

You'll need a surrogate SCD id (this then is the ID that is represented in the fact ...

Current 'eyes' joins are then fact.Employee_id = employee.scd_id AND employee.current_flg = 'Y'
Historical 'eyes' joins are then the last reord in employee where fact.Employee_id = employee.scd_id AND employee.effdt <= fact.date

^ given these two it's pretty easy to build the First 'eyes' ...

ForSly · Jul 16, 2018 1:45PM

Forgive my lack of warehouse knowledge but the current flag, will that be something I define during view creation or its something expected to be in the original table?

Thomas Dodds · Jul 16, 2018 3:54PM

in the type-2 slowly changing dimension ... do it at ETL time ... then you have it set once (even if the current record is now 10-yrs old)

SonPat99 · Jul 17, 2018 2:59AM

Hello,

I am assuming you have all the data in your DB table (which is without SCD).

Why cant you use <JOINED_AS> = "INTERN" (or whatever your "intern" is defined as) instead of using a "%intern%" on OBIEE Side?

Do you have a fixed job title defined for Interns? If yes, you can use that directly with equate operator.

Hope this helps.

ForSly · Jul 17, 2018 10:30AM

I came up with the following code to extract and create an Interns table, its capturing the Interns as expected and now the hard part is to create a table that will have a column showing a count of those interns.

create table INTERNS (APPLICATION_DT,CANDIDATE_NUM,COMPLETED_DT,STATUS_NAME)

AS SELECT
APPLICATION_DT,CANDIDATE_NUM,COMPLETED_DT,STATUS_NAME
from EVENT_F
where STATUS_NAME IN ('Hired - External')
    and exists (select row_wid
                  from JOB_D
                  where job_info_row_wid = row_wid
                    AND (   TITLE LIKE '%Intern %'
                          OR TITLE LIKE '%Internship%' ));

I thought at the end of the code I could as ( AS INTERNS_F;) but that's throwing a syntax error. any suggestions?

ForSly · Jul 17, 2018 10:31AM

I came up with the following code to extract and create an Interns table, its capturing the Interns as expected and now the hard part is to create a table that will have a column showing a count of those interns.

create table INTERNS (APPLICATION_DT,CANDIDATE_NUM,COMPLETED_DT,STATUS_NAME)

AS SELECT
APPLICATION_DT,CANDIDATE_NUM,COMPLETED_DT,STATUS_NAME
from EVENT_F
where STATUS_NAME IN ('Hired - External')
    and exists (select row_wid
                  from JOB_D
                  where job_info_row_wid = row_wid
                    AND (   TITLE LIKE '%Intern %'
                          OR TITLE LIKE '%Internship%' ));

I thought at the end of the code I could as ( AS INTERNS_F;) but that's throwing a syntax error. any suggestions?

Thomas Dodds · Jul 17, 2018 10:37AM

I've counseled you on a type-2 SCD ... what you've got there is nowhere near that. Where are you executing that SQL? DB? RPD (o-view)?

The 'ask' is: Which employees were initially hired as interns?

The SQL you've got doesn't do that - which OBIA are you running? the _F and _WID naming is giving it away ... if so, and you are indeed using OBIA, then you really need to do this properly and according to the extension and customization guidlelines - else updates will render your solution useless.

ForSly · Jul 17, 2018 10:58AM

I am executing SQL in the DB and using DbaaS. I didn't use the type-2 SCD because I wasn't so sure how to come up with that script.

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

Using character match to filter records

Answers