Extending your CTRM with BI Tools

๏€‡

torym.barber

๏€—

October 20, 2020

๏€‰

In the following article, we will be covering how companies can extend their CTRM systems using BI toolsets.  Whether it is short-term solution to provide added functionality, or a long-term program to establish a better reporting structure, these tools offer the flexibility and data aggregation that most CTRMs cannot. 

This article is driven by some of the most common questions and requests encountered over 10 years demoing and implementing CTRM systems to both large and small organizations throughout the Energy and Agricultural sectors. 

  • โ€œCan I see a report this (Insert specific request) wayโ€ย ย ย 
  • โ€œCan I add this (Insert specific) fieldโ€ย 
  • โ€œHow will I know when (Insert specific) occursโ€ย 
  • โ€œBut where can I keep (Insert specific) dataโ€ย 

The general theme throughout these questions being that no traditional CTRM will be able to capture every field that a business requires, and nor should that be the goal. Also, it emphasizes that data modelling and reporting requirements are unique to each individual company, even in the same industry.  

BI tools are one of a variety of approaches that can offer a solution that may not require customizing your current CTRMโ€™s version, or any detailed additional development. The key component to the success of this implementation is having an individual or team that understands: 

  1. The business and user requests in the right contextย 
  2. The CTRM data and process modelย 
  3. The BI toolset that is to be utilizedย 

Once this has been established you can embark on solving some of the common requests for improvement around CTRM systems and enhance the experience of your users.  

Table of contents 

  1. Limitations of a traditional CTRMย 
  2. BI Tools: Approach and Challengesย 
  3. BI Tool Benefits and Examplesย 
  4. BI Use Case: Adding Limits
  5. Other Considerationsย 
  6. Conclusionย ย 

Limitations of a Traditional CTRM 

Some of the most popular CTRMS in use today are still considered โ€œLegacyโ€ products, and they tend to come with reporting capabilities in four broad categories, each with their own set of limitations.  

  1. โ€œOut-of-the-Boxโ€ or โ€œCannedโ€ Reportsย ย 
  2. Grid extracts to Excelย 
  3. Ad hoc queriesย 
  4. Custom developmentย 

Out-of-the-Box or โ€œCannedโ€ reports 

Depending on the system, a few out-of-the-box reports are usually offered in each major functional area (Trade capture, Risk, Settlement, etc.). The potential issues with these reports are that they will be limited in their scope of coverage, and inflexible to adjustment. It will also require your organization to change its reporting needs to fit the system and establish a new set of terminology to be used in understanding their output. Terminology issues are central to most CTRM implementations, and the difficulty in training increases as the users move from day-to-day operations, to report-only individuals in the organization. 

Grid extracts to Excel 

When a canned report does not meet the user’s needs, the next step would usually be to export the information to Excel and manipulate it further. This requires the user to spend additional time pulling together data together from other sources, and repeat the task each time the report is generated. With outside data, and repeated user interaction there is an increased possibility of human error in the process.  

Ad hoc queries 

The next step, after repeated efforts to generate a report, is to request or draft an Ad Hoc query to pull together the information requested on an on-going basis. This may be able to be completed through the UI or must be done directly through the back end of the CTRM. In both scenarios, systems may only be able to provide one of the options, or possibly neither, and questions on access and security will need to be discussed. If successful, this approach will likely create another Excel report which will require storage method, and possible further manipulation to visualize the data.  

Custom development 

The last step would be to consider custom development by the vendor, internally, or through a third-party consultant. This step would require the time and effort of requirements gathering, design, development, and deployment of a new โ€œCannedโ€ report into your system. It would need to be managed on a version to version basis going forward, and any additional changes would also need to go through a development cycle. Ultimately, this may lead to another company specific report finding its way into the vendors reporting offering.  

While these methods may succeed in handling single one-off requests from the business users, it is common that frequent adjustments will be required to satisfy an evolving trading environment.


BI tools: Approach and Challenges  

There are multiple BI tools on the market today, including free offerings, desktop, and cloud interfaces as well as advanced features such as machine learning and predictive analytics. However, the common use cases are generally: 

  • Data aggregation and combination from multiple sourcesย 
  • Data modelling and managementย 
  • Data visualization ย 

These three areas can be used in combination with the CTRM software ecosystem to provide short- and long-term functional extensions to your system. However, to take advantage of these features there are some skill sets to focus on developing internally or procure from third party providers.  

Business knowledge  

Requirements from business users cannot exist in a vacuum. It is imperative that an individual or group managing the BI tool have an understanding of the industry for which they are trying to assist. From upstream to downstream, front to back office and across all major commodities, understanding the nuances that exist in commodity markets are key drivers of success.  

The main focus for this resource is to understand โ€œWhyโ€ a segment of data is being requested, just as much as the โ€œWhatโ€ that data represents. This helps in the design, development and construction of a meaningful report or extension. 

The CTRM data model 

CTRMs generally consist of a common set of functional areas including Trade Capture, Operations, Risk Management and Settlement, with a varying level of capabilities offered by each vendor. The vast array of data stored by each CTRM, calculation engines and functional offerings lead to a set of distinct underlying components to handle the operation of the system.  

For the most part, these structures follow similar patterns across vendors, but it will require an experienced individual to understand the flow of data through the system. When considering the use of a BI tool, the knowledge of these CTRM data models is key to building the most efficient and flexible datasets for reporting.   

The BI tool 

The last step is developing the understanding of the BI Tool that your organization is planning to utilize. In comparison to the other two areas mentioned above, this is likely the easiest to learn and build internally. You could also reach out to resources from other departments, or trusted consulting partners, that already have the skillsets required to achieve your goals. 

From personal experience, I have found that Power BI from Microsoft is a very complete toolset, and it is available to users as part of the Office 365 subscription. Learning the basics of the tool can be relatively easy, and there is a large amount of support online.  

It is a challenge to bring together a team with industry and CTRM knowledge, but the effort will be rewarded with shorter implementation times frames, lower cost, and better user adoption. 

BI Tool Benefits and Examples 

Below are some of the relatively quick and easy improvements that can be made to your CTRM with the help of a BI Tool.  

  1. Data Aggregation ย 
  2. Adding Attributesย 
  3. Adding Notificationsย 
  4. Management Dashboards and Reportingย 

Data Aggregation  

One of the most common features that can be used to augment your CTRM reporting is the ability to combine data from multiple sources into one reporting platform. It is common that data from production or generation, shipping and operations, or ERP systems are kept outside the CTRM. With the help of a BI Tool, this information can be viewed side by side without having to develop extensive cross references within the software itself.  

Examples: 

  • Production system information to provide a combined view with marketing dataย 
  • ERP cash information combined with CTRM invoice informationย 

Adding Attributes 

Another frequent request is adding a field or trade attribute to the CTRM that may not exist natively. This is generally addressed by CTRM’s with custom development or with the use of User Defined Fields (UDFs). A benefit with adding it through a BI tool can limit an impact to the underlying system and any custom code that has a knock-on effect to upgrades or other routines. This may be a short-term solution until the appropriate changes can be implemented either in the CTRM itself or in another appropriate system. 

Examples: 

  • Daily trading reportsย with company specific attributesย 
  • Additional credit and collateral information not typically stored in a CTRMย 

Adding Notifications  

Once reports have been established, it then useful to receive feedback from the system when certain data is either approaching or breaching pre-established thresholds. These notifications can be established against any area in which data is updated or changed. With web-based functionality, notifications can be sent directly through a BI tool to mobile apps, or email inboxes.   

Examples: 

  • Credit threshold of 80% with a CP has been breachedย 
  • Trader position exceeds threshold against a specific curveย ย 

Management Dashboards and Reporting  

CTRM canned reporting was already discussed in the previous section, but one of the key components is that is also focused mainly on frequent users of the system and providing a significant of detail. BI Tools are extremely useful in preparing reports to be easily digested by management or steering committees. These can be established directly from the system, so they need not be created from scratch every quarter or reporting cycle. 

BI tools can also assist with improving detailed reporting as the drag and drop nature allows for ease in filtering and digging through large amounts of data.  

Examples: 

  • Daily total position and MTM across major business unitsย 
  • Cash position against monthly obligationsย 

 Trader Limit Dashboard in Power BI
Trader Limit Dashboard in Power BI

BI Tool Use Case: Adding Limits 

If an organization has an established set of limits for their trading program, then it is beneficial that their CTRM system can represent and assist in managing the risk policy. While some CTRMโ€™s have some limit management included as standard functionality, it can be constrained in scope and not offer the full coverage of an organizations policy. Other systems will have none of this functionality, which requires custom development and possibly introducing a host of other issues.  

The addition of limit monitoring is one of the more useful scenarios I have seen from the addition of a BI Tool, and act as an interim solution or long-term answer to manage a company’s limit policy. It also takes advantage of the majority of features offered by the BI Tool.  

Data Aggregation  

The limits themselves may be stored in another database, or an outside Excel sheet, that needs to be combined with the CTRM data. The BI tool can be setup and managed to pull from these sources and allow for easy adjustments as the limit policy changes.  

Adding Attributes 

With the capabilities offered by BI Tools, it is easy to add additional attributes to the underlying data tables of a CTRM system that may not be natively captured. Having these company specific fields reflected in your reporting will help in understanding of the information being conveyed by the limit data.  

Adding Notifications 

When a limit is approaching a threshold, or has breached a floor or ceiling, then it is imperative the right individuals are notified. The capabilities in BI tools allow for Email, dashboard or mobile notifications to be sent so the appropriate actions can be taken.  

Dashboards 

Combining all the risk policy information into one easy to understand dashboard helps in digesting the large amount of data being generated by the companies trading operations. With visual representation and the ability to highlight the most concerning areas, these tools allow for faster reaction and decision making.  


Other Considerations 

Integration with shared document storage 

In several of the case above, it is likely you will need to use Excel sheets or other stored data to maximize the use of the BI Tool. Most BI tools allow for the connection directly to SharePoint, or other data storage services allowing the level of security to be extended to this supplementary data. It also allows central storage for viewing and editing without stranding files on individuals’ hard drives.  

Mobile and Web capabilities  

Most BI Tools today offer both Web and Mobile capabilities to extend your ability to view data while remote, at home or on the road. Given the nature of most Legacy CTRMS, this provides access to near real time data for both daily users and management.  


Conclusion  

Overall, if you are looking to extend your system a BI Tool can be a powerful way to add features and enhance your organizations reporting capabilities with relative ease. Whether you are looking to cover some short terms gaps, or establish long term reporting structures, BI tools in combination with Excel and other data sources, can provide the flexibility and ease of implementation to enhance your CTRM system. 

It will require expertise in both the industry and CTRM data model, as well as with the BI tool in question, but developing in-house knowledge will provide business continuity and ownership of your data even if the CTRM system is changed.  

The path to system enhancement can take many forms and there are a lot of options for improvement to explore. At TruPoint Advisory we look forward to discussing these options further and assist you in getting the most out of your CTRM system. 

Regards, 

Chris Lesperance 

Director | TruPoint Advisory Inc.

TruPoint Advisory provides experienced CTRM Selection, Implementation and Consultancy services across all energy markets” 

Recent Posts

TruPoint Welcomes Kajal Singh to its’ CTRM Team

TruPoint Welcomes Kajal Singh to its’ CTRM Team

TruPoint welcomes Kajal Singh to its team of CTRM consultants.   Kajal brings over 12 years of experience in the ETRM industry, with expertise spanning both technical development and functional consulting roles. Prior to joining TruPoint, Kajal worked as a...

TruPoint Welcomes Mike Kozlov to its’ CTRM Team

TruPoint Welcomes Mike Kozlov to its’ CTRM Team

TruPoint welcomes Mike Kozlov to its team of CTRM consultants.   Mike joins TruPoint with over 17 years of combined business and technical CTRM experience.  Mike began his career analyzing and trading liquids before transitioning into energy trading systems....

TruPoint Welcomes Dharshan Abraham to its’ CTRM Team

TruPoint Welcomes Dharshan Abraham to its’ CTRM Team

TruPoint welcomes Dharshan Abraham to its growing team of ETRM Business Analysts. Dharshan joins TruPoint with a background in accounting and auditing for the Oil and Gas market. He will be starting his career as a Business Analyst with our team of CTRM experts and...

TruPoint Welcomes Ahmad Mekkawi to its’ CTRM Team

TruPoint Welcomes Ahmad Mekkawi to its’ CTRM Team

TruPoint welcomes Ahmad Mekkawi to its growing team of ETRM developers.   Ahmad joins TruPoint with over 6 years of experience working as a software developer and team lead. Ahmad has a strong background in .Net and C#, with experience in both SQL Server and...

Power BI Use Cases for CTRM

Power BI Use Cases for CTRM

When considering an analytics tool to use with your CTRM, there are a number of options available. However, as the majority of companies work within the Microsoft 365 eco-system, most will have easy access to one of the most powerful tools on the market...

torym.barber