Portfolio Holdings Data: Analytics Content Retrieval

In a previous post I had stated that one of the reasons I wanted to try Couchbase was the built-in analytics service. It's accessible from the menu on the left of the UI, just below the the "Search" item:

The analytics console looks very much like the query console with the exception of the panels on the right. This is where you can map data structures from the local or remote Couchbase collections as sources. The analytics service makes a copy of the original data, and provides the ability to index it separately from the original source.

Couchbase Analytics Views

As you can see on the left, the "Local" location has a 1-1 mapping back to the source. It does offer the ability to subset, but I decided to start with the entire collection.

The "Analytics Views" consist of SQL++ queries, much like relational databases support views. Below are examples of view definitions, and what result sets look like in the expandable lists.

Unfortunately, the indexing and data storage capabilities in the analytics service do not allow partitioning of either data or indexes.

It does not provide a "where" clause on indexes so you can mimic partitioning, so I encountered very long query runtimes for metrics on a Fund Series level even if only over one period date. There were other views impacted as well.

Couchbase has tried to rectify this by introducing its Couchbase Capella Columnar with it's cloud offerings.

Fund Series Collateral View

SELECT PeriodEndDate, CIK, SeriesName, ROUND(TONUMBER(aggregateCondition.aggregateInfos.aggregateInfo.amt), 2) AS Amount, ROUND(TONUMBER(aggregateCondition.aggregateInfos.aggregateInfo.collatrl), 2) AS Collateral_Amount, aggregateCondition.aggregateInfos.aggregateInfo.invstCat AS Investment_Category, ROUND(TONUMBER(aggregateCondition.aggregateInfos.aggregateInfo.amt) - TONUMBER(aggregateCondition.aggregateInfos.aggregateInfo.collatrl),2) AS Collateral_Gap FROM FundParents WHERE aggregateCondition.isNonCashCollateral = 'Y'

Fund Series Collateral Results

FundSeriesCollateral for 2023-12-31

Money Flows View

SELECT i.PeriodEndDate, i.SeriesName, ROUND(TONUMBER(i.netAssets), 2) AS netAssets, ROUND(SUM(TONUMBER(i.mon1Flow.sales)+TONUMBER(i.mon1Flow.reinvestment)-TONUMBER(i.mon1Flow.redemption)), 2) AS month1Flows, ROUND(SUM(TONUMBER(i.mon2Flow.sales)+TONUMBER(i.mon2Flow.reinvestment)-TONUMBER(i.mon2Flow.redemption)), 2) AS month2Flows, ROUND(SUM(TONUMBER(i.mon3Flow.sales)+TONUMBER(i.mon3Flow.reinvestment)-TONUMBER(i.mon3Flow.redemption)), 2) AS month3Flows FROM FundParents i GROUP BY i.PeriodEndDate, i.SeriesName, i.netAssets ORDER BY i.PeriodEndDate DESC

Money Flows Results

MoneyFlows Result Set

Fund Trailing 3Month Flows (uses MoneyFlows)

SELECT PeriodEndDate, SeriesName, ROUND((TONUMBER(month1Flows)+TONUMBER(month2Flows)+TONUMBER(month3Flows)),2) AS net3MoFlows, netAssets FROM MoneyFlows ORDER BY net3MoFlows DESC

Fund Trailing 3Month Flows Results

Results for 3-Month Flows

Analytics Graph(s) in Couchbase; downloads to PNG

Analytics Graph over three periods for Fund Trailing 3 Month Flows

The analytics service includes various other views, such as Asset Category by Period, Invested Instruments, and Total Invested Per Asset and by Period, among others. While they provide valuable insights, they are hindered by slow query times, making them unsuitable for interactive use. These views are better suited for "overnight" reporting, where data can be reviewed the following day due to lengthy runtimes.

Therefore, certain usage patterns with the analytics service may not be ideal for very large datasets, such as those exceeding 100GB, although the service does offer some capabilities. Those in need of on-demand or real-time functionality might consider alternatives, such as a columnar store or an in-memory store, to enhance the usability of a data warehouse.