nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Using character match to filter records

ForSly

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.

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

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

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

Thomas Dodds

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

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

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

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

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

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

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

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

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.