UCCX with PowerBI
Propose
Microsoft PowerBI is powerful tool that allows user to build reports and dashboards that deliver quick access to data. On other side we have UCCX system which uses CUIC as a base reporting tool. The opinions about CUIC are varied. If you would like to run complex reports and present the results in multiple ways, it will not be the best tool to use.
So, the question is why not to use the features available in PowerBI and use them for UCCX reporting? How to make this connection possible?
Microsoft PowerBI has built-in capability to connect to Informix database. This feature DRDA uses protocol to obtain the data. To configure this type of connection you would need to access to database and Firewall to change the database configuration. Because UCCX is closed solution, this path cannot be used.
To achieve the goal, we need to focus on the standard mechanism that is used also by CUIC – connection via ODBC.
Guide
-
Download the Informix Database driver. If you are working with Cisco products the driver can be downloaded from cisco site:
Informix driver.
-
Unzip the downloaded package and save it on local file.
-
Enter the unzipped package and search for installclientsdk.exe file.
-
Run the installer using the defaults proposed settings proposed by installer.
- Unzip the downloaded package and save it on local file.
- Enter the unzipped package and search for installclientsdk.exe file.
- Run the installer using the defaults proposed settings proposed by installer.
-
Once the client SDK has been installed, click the Windows start button and type in ODBC. You will see the list of tools that are available. Based on
the system that you are running (32 or 64 bit) select the ODBC tool. In the example we used 64-bit version.
-
The new window will be opened. Click the add button to Add new data source.
-
New window will be presented that allows to select the driver that will be used for the connection. On the list search for the IBM INFORMIX DBC DRIVER
(64 bit). If you have 32-bit system, select driver for 32-bit version. Once finished, click Finish button.
-
New window will be presented where you will need to fill in the database details. The easiest way to obtain them is to use CUIC data source configuration
details. Fill in the Data Source Name field with the value that will be recognizable for the server.
-
Open CUIC (Cisco Unified Intelligence Center) and navigate to Configure -> Data Source configuration page.
-
The page will be refreshed, and the system will show list of configured data sources. On the list of available discourses search for the one with the name UCCX.
-
Use Edit option on this data source to see the database details.
-
Return to ODBC data source configuration and go to Connection tab. Using the details fill in the connection details using the following mapping. If
the ODBC field does not have CUIC equivalent, please use the value form Static value column – those are const values that do not change.
CUIC field name
ODBC config field name
Static value
Instance
Server Name
Datasource Host
Host Name
Port
Service
1504
Protocol
onsoctcp
Options
Database Name
Database Name
db_cra
Database User ID
User Id
uccxhruser
Password
Uccx user config doc
Below is the screen that shows the configured data source parameters.
| CUIC field name | ODBC config field name | Static value |
|---|---|---|
| Instance | Server Name | |
| Datasource Host | Host Name | |
| Port | Service | 1504 |
| Protocol | onsoctcp | |
| Options | ||
| Database Name | Database Name | db_cra |
| Database User ID | User Id | uccxhruser |
| Password | Uccx user config doc |
-
Click Apply & Test Connection button. If parameters are valid you should see below message.
-
Navigate to Environment tab and change the following values:
- Client locale – en_US.UTF8
- Use Server Database Locale – checked
- Client locale – en_US.UTF8
- Use Server Database Locale – checked
-
Click Apply and OK buttons and close ODBC configuration window. The data source configuration has been completed.
-
Open PowerBI application. Please remember that if the version of the application (32 or 64 bit) must be compatible with the Informix driver.
-
From the menu select Get data and from the expanded drop down select More… option.
-
In new opened window select Other and from the list choose ODBC. After selection press Connect button.
-
New configuration window will appear. In the Data source name (DSN) drop down list select the DSN name that you created earlier. Expand the Advanced options. If you
have the SQL query past it into SQL statement field. If you do not have the query, you can pull it from CUIC, and the standard stock reports delivered with CUIC.
Finally click the OK button. PowerBI will try to connect to database and pull the data based on the query.
