My First Technical Solution

Background

It was year 2008, about a year in the IT career. I was doing a proof-of-concept for modelling dimension hierarchy table in OBIEE. I had created a table with sample data and next step was to create the hierarchy table for it. The available method to achieve this was using a complex Informatica mapping and I hated Informatica that time. The idea of using Informatica mapping for such a simple task(IMO that time) of converting dimension table to dimension hierarchy didn't make sense to me. I was looking for a simple SQL that would do the job. I soon realized that there is no SQL method for doing this, a fact that my mind refused to accept. So I decided to build it myself.


The Problem

To display all hierarchial reporting manager details on a single record.

Convert this information-

Unique_ID	Emp_Name	Position	Parent_ID
1032	Sandhya Shataram	President	NULL
1033	Jayshree Gadkar	Vice President	1032
1034	Varsha Usgaonkar	Senior Manager	1033
1035	Sonalee Kulkarni	Manager	1034
1036	Ketaki Mategaonkar	Sales Representative	1035

Into hierarchial format on one line like this- 

Level5_Emp_Name	Level_5_Position	Level4_Emp_Name	Level4_Position	Level3_Emp_Name	Level3_Position	Level2_Emp_Name	Level2_Position	Level1_Emp_Name	Level1_Position
Sandhya Shataram	President	Sandhya Shataram	President	Sandhya Shataram	President	Sandhya Shataram	President	Sandhya Shataram	President
Jayshree Gadkar	Vice President	Jayshree Gadkar	Vice President	Jayshree Gadkar	Vice President	Jayshree Gadkar	Vice President	Sandhya Shataram	President
Varsha Usgaonkar	Senior Manager	Varsha Usgaonkar	Senior Manager	Varsha Usgaonkar	Senior Manager	Jayshree Gadkar	Vice President	Sandhya Shataram	President
Sonalee Kulkarni	Manager	Sonalee Kulkarni	Manager	Varsha Usgaonkar	Senior Manager	Jayshree Gadkar	Vice President	Sandhya Shataram	President
Ketaki Mategaonkar	Sales Representative	Sonalee Kulkarni	Manager	Varsha Usgaonkar	Senior Manager	Jayshree Gadkar	Vice President	Sandhya Shataram	President

Sample Data

Click here for the code  -->  CREATE TABLE and INSERT INTO Scripts


PL-SQL Solution

Click here for the code  --> PL-SQL Script

This solution involves inserting records in the dimension hierarchy table for each level in dimension table one-by-one. The logical steps in the PL-SQL procedure are as follows -
1. Identify the records with highest level in the dimension table and load these into a dummy table.
2. For each record in the dummy table, insert a record in the dimension hierarchy table.
3. Next, identify the records with second highest level in the dimension table and load these into another dummy table.
4. For each record in this new dummy table, insert a record in the dimension hierarchy table.
5. Repeat steps 3 and 4 for all lower levels in the dimension table.


Wow!!!!! I did it, found a solution for the problem and it feels great. But this euphoria didn't last long because the SQL solution is not simple. There are too many lines, loops, conditions in the code. THE SOLUTION MUST BE SIMPLE! Couple of weeks passed thinking about alternate solution, is it possible to do it another way? One day I realized that the PL-SQL solution, at the design level, does the job on a row-by-row basis. If I have to do it another way, it should be, at the design level, on a column-by-column basis. I got the direction for my second solution and so started working on it.


SQL Solution

Click here for the code  --> SQL Script

This solution involves preparing an SQL statement with multiple self-joins to dimension table and decode statements to select correct values for each column.


Afterthoughts

So I found the solution for the problem, twice! However, it does not replace Informatica mapping as it's a proven ETL tool with many large scale implementations. But these solutions provides alternative ways to achieve the same objective. This gave me a sense of satisfaction and confidence to solve the problems.

Thanks for spending your time to read my blog!