Overview

Build a MicroStrategy dossier to showcase outliers for Model validation team. Business head wants to see an overview of backtesting outliers that occur every day.

Team also wanted to analyze positional intraday margin calls using live version and exact timestamp date based on snap short & batch data

Client:

Large European Central Counter Party (CCP)

Industry: Risk Management
Services:

Data analytics, Data Visualization

Challenge

1. Dossier runs on big data hive, oracle and .csv files. Gathering and merging data from a variety of sources was a complex task as data is stored in different format with heterogeneous names. Using MicroStrategy wrangling features, we extract, transform and load clean data.

2. Running a dossier on large data sets within seconds was a nightmare. But we met the user
expectation by loading data in memory cubes for faster performance.

3. There was no provision of extracting older version of positions which are already expired

Solution

Provided business analysis services in key areas such as:

We built a dynamic, interactive and self-service MicroStrategy dossier to report Margin, P&L, Lossto-Margin ratio at portfolio, Liquidation group, account and CM level. This can further be drilled
down at position level which present valuable insights using bar graph, line charts, pie chart and
heat map. The dossier can automatically be scheduled to refresh every day and pdf export and dossier link can be send to user’s email.

Currently, current business day data is stored in a snap shot table and all previous day
positions are stored in a different table. The requirement is to fetch current or previous day data
based on user’s prompt answer and this should happen dynamically. We implemented this with
warehouse partition mapping where we sliced the data based on date attribute to point to its
respective PMT tables