OBIEE 11g need help with formula merging two columns into a true or false

KevinjLayton

Hey as the title says my company is currently running oracle bi ee 11g and I'm working in analysis. This is the first time/company where they're forcing me to work exclusively in OTBI. Anyway, what I'm trying to do is create a formula that flags if a manager has direct reports or not (because during this current database transfer the pre-made column never got populated) So essentially I need the formula to select managers and if the manager has an employee tied to him then the measurement is true else it is not. And for the life of me I can not visualize how I'm going to do this. Does anyone have any pointers for me? Or anywhere that has a good information about a similar situation. I've been working all day on other implementations and my brain is currently mush so any help would be much appreciated. I hope this made since to someone like I said, my brain is mush.

Robert Angel

Hello Kevin J,

mush or no, make your problem clear and provide full detail and you are more likely to get a solution...

We don't know what your employee / manager structure looks like, so my answer is based on assumptions / guesses - not the best situation...

I suspect your requirement is better fulfilled in the ETL process as it sounds like you need a self join; to check if the employee is used in the manager field for another employee; this kind of analysis you could do in OBIEE using a filter based on another query, or you could do in the rpd modelling the employee table as an aliased version of itself to use as managers, but either way it will be resource hungry and would not perform as well as a pre-baked function result populated courtesy of the ETL.

So my advice is two-fold; -

1. Push this problem to your ETL developers, it is not a good idea to try this in OBIEE (unless your management hierarchy has already been flattened?!)

2. Next time you ask a question give more detail and clearly state and structure your question - I took one look at your question and thought - "he made no effort, why should I"

KevinjLayton

Hey,

     Sorry about that I wrote this at the end of the day when I was frustrated and I didn't really take the time I should have. I'm getting access to the backend for a while after I'm done with more pressing work, the ETL developers are basically non-existent here, we are a multi-national corporation and it would take months to push the ticket through to them as they are dealing with more than a handful of more critical issues, but I'm being granted temporary access when I'm ready to get this done for my department.

     There is a Employee ID that returns with one employee per row and then Manager ID is split up into 10 separate fields (Manager 1-15). 1 being the direct manager that the employee reports to and then as it moves down the columns it post the next manager in the line until it reaches the CEO and then any remaining columns are null (also only posting 1 manager each time). But I'm only concerned with Employee ID and Manager 01 because I want to create a direct report flag and 01 contains every manager in the system anyway.

As you said how would I go about creating a column that told me if a employee ID is in the Manager ID of another Employee ID? and where could I write something up so I could pull it into my analysis?

Jerry Casey

If you have to do this in an analysis, one way is with a filter query, as Robert Angel said.  Create a simple filter query that lists all Manager 01 IDs.  Then build your main analysis and add a column that simply contains 'Yes'.  Filter it on Employee ID = Manager 01 ID from your filter query (using the "is based on the results of another analysis" option).  Now create a union with the exact same query, but filter on Employee ID NOT IN Mgr ID from the simple query.  Change the 'Yes' column in this second analysis to 'No'.

Thomas Dodds

There is a Employee ID that returns with one employee per row and then Manager ID is split up into 10 separate fields (Manager 1-15). 1 being the direct manager that the employee reports to and then as it moves down the columns it post the next manager in the line until it reaches the CEO and then any remaining columns are null (also only posting 1 manager each time). But I'm only concerned with Employee ID and Manager 01 because I want to create a direct report flag and 01 contains every manager in the system anyway.

^ looks like it's flattened ... model it that way and count distinct employee IDs by level 1 Mngr ...

take a look here:  https://www.rittmanmead.com/blog/2008/08/ragged-hierarchy-handling-in-obiee/

Thomas Dodds

Jerry ... and then have to repeat that for any/every report he wants the hierarchy in ...

Jerry Casey

Robert,
I do understand that, but doing it more than once reinforces the need to get it done the right way.

Jerry

Thomas Dodds

"Nothing more permanent than a temporary solution" is often the result ... if there's no time to do it once right, when we the time be had to do it again?