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
