HSBC - Brokerage - Tableau Reporting

 

Tableau System

Tableau is a visual analytics platform using to display data generated by Merit system. Whatever the data is being processed, tableau is showing the data in forms of charts and reports.

What tableau system used for in Merit is to show various reports of trades from the point of broker-trades uploaded in Merit system, whether it is matched or unmatched. The generated data includes bank side trades as well.

What Merit system do?

Merit system generates data and Tableau system consumes the data to generate reports as configured in the Tableau itself.

To generate data in Merit system, we were using an SQL View. After implementing the logic for calculate Sum of the Brokerage for FXE Zero-brokerage trades, the view became slow and it took above 5 hours to generate the data. Because of this latency issue, user cannot view reports in tableau system.

How did we improve performance?

Unlike the existing view we proposed to write a stored procedure and run the stored procedure using a SQL Job. The job has scheduled to run every hour. The job runs the stored procedure and the generated data will be inserted to a table in merit database. This improved the performance by around 4-5 minutes to generate all data.

Also, the Tableau system can directly query the data instead of scheduling a job inside tableau system as implemented earlier.

How the Tableau system works?

In Merit data base we have below SQL Job scheduled for every 1 hour.

• Job_Tableau_Report

The job executes the below stored procedure.

• MRT_TABLEAUREPORT_STORED_JOB

The stored procedure inserts the data to below table in Merit database.

• MRT_TABLEAUSPEND_REPORT

How to create the SQL Job

• Go to the database
• Expand the SQL Server Agent at the bottom of the Object Explorer
• Right click > select New Job

• Select General tab from the left pane (it would be the default selection). Provide the Name as Job_Tableau_Report and a proper Description.

• Then select Steps tab from the left pane and click New button at the bottom.
• Give the Step name as ‘Execute Stored Procedure’, select the Database from the list where the stored procedure exists and give the below command in the Command section and click OK.

EXEC MRT_TABLEAUREPORT_STORED_JOB

This is where we specify the stored procedure to run when the job runs.

• Now you will be able to see a new step added to the Job select list.

• Now we need to create a schedule to run the job. For that, select Schedules from the left pane and click New at the bottom.
• Provide a Name for the schedule and below details:

Schedule Type as Recurring.

Under Frequency section provide below details:

Occurs Daily.

Recurs every 1 day

Under Daily frequency provide below details:

Occurs every 1 hour

Starting at 12:00:00 AM

Ending at 11:59:59 PM

Start date don’t need to change and select No End date and click OK

                You will be able to see the schedule description in the Description section.

• Now click OK to complete the job creation.

How to deploy tableau stored procedure change to UAT and Prod?

Note: Before do any changes in the stored procedure, make sure you have a back up of the existing one. I would suggest to rename the existing one with the date you took backup.

• Save the stored procedure as .sql or .txt in your local folder after done with the changes.
• Request the existing stored procedure from where we are going to update (UAT or Prod).
• Compare that with the new one where we done the changes.
• Share the file with the utility team as attachment in the mail. And provide below information as well.
  1. Ask them to rename/ backup existing stored procedure in the database before executing the shared sql.
  2. List out the changes made.
• Restart the SQL job, if required.

Sample mail below:

We have Vijay from Utility team to support tableau deployment related queries. And Charusmitha will help with any requirement clarifications.

--- End of the document ---