Paul Douglas

Build your org chart

Blog Post created by Paul Douglas Employee on Jul 31, 2016


As a small flight of fancy, I thought that I would piece together an organisation chart using the information collected directly from Identity Governance and Lifecycle.

The reason I wanted to use the information G&L gathered instead of the direct HR source, is that it allows for contractors etc. not located in HR to be included.

 

I'll do my best to explain this process, but depending on the versions of Windows and Office installed, your experience may vary.

My environment is as follows:

Operating System: Windows 7 x64

Visio Version: Visio 2013 x32

 

For this exercise, you'll first need to setup an ODBC data source in Windows.

As I have a 32 bit Office installation, the 32 bit Oracle InstantClient Basic and ODBC versions were needed. These can be obtained from Oracle's website.

 

With these packages installed, you'll need to modify Windows' Path variable to include the InstantClient folder and add a new System Variable ORACLE_HOME that also points to the InstantClient folder.

My installation location was C:\Program Files\Oracle\instantclient_12_1_32bit

Next step is to create a tnsnames.ora file.

You'll need to create this file within a network\admin folder in your InstantClient folder. For me this was C:\Program Files\Oracle\instantclient_12_1_32bit\network\admin

The contents of my tnsnames.ora file are below:

GandL=

(description=

   (address_list=

     (address = (protocol = TCP)(host = 192.168.1.200)(port = 1555))

   )

(connect_data =

   (service_name=avdb)

)

)

 

Now that Oracle InstantClient is installed and all dependencies set, it's time to create the ODBC data source.

On a Windows x64 OS, using the normal ODBC data source creation won't work for the x32 InstantClient version.

Running C:\Windows\SysWoW64\odbcad32.exe opens the 32 bit version.

Click Add

Choose the InstantClient and click Finish

You'll then get the configuration prompt

You should see the service name configured in the tnsnames.ora file. I strongly suggest making the connection Read-Only to avoid any damage being done to the database.

Click Test Connection, and you'll be prompted for the username and password to the database.

 

If you've got this far, you can now do the easy part of creating your Visio org chart. The ODBC connection you've just setup can be used by any other application that utilises ODBC i.e. Archer, Excel etc.

 

Open Visio and choose to create an Organisation Chart

Choose Information that's already stored in a file or database

Choose An ODBC-compliant data source

Select the ODBC source that you created in the beginning

Enter the username and password

Select the V_MASTER_ENTERPRISE_USERS table

I've used the following configuration

After a little bit of waiting for the information to build and process

You'll now have an org chart

I have used colouring of users by department value using Data Graphics

 

I hope this is of value to you, and it's a great exercise to go through to further use the information you've spent so much time gathering and validating.


Have fun!

Paul

Outcomes