Data Warehousing, BI and Data Science

18 June 2015

Holding History – MDX For “From Date” and “To Date”

Filed under: Analysis Services — Vincent Rainardi @ 5:16 am

Summary
This article shows how to create these calculated measures:
1. How many securities were purchased between date1 and date2
2. How many securities were sold between date1 and date2
3. How many securities were “in holding” between date1 and date2

I will first write the business background, then create the tables and populate them (only 2 tables: FactHoldingHistory and DimDate). Then I’ll create the SSAS BIDS solution, including the calculated measures and deploy the cube to an AS server. And finally I will browse the cube using Excel pivot table, demonstrating the 3 calculated measures above using some test data.
When explaining the business situation, I will try to make it as detailed as possible, so that we can also learn about the investment business, which is the second purpose of this article. The first purpose is to provide the MDX for using the “From Date” and “To Date” technique, which is why I will also take time to explain the concept of technique as clearly as possible.

Business Background

An investment bank, an asset management, a wealth management, or a pension fund would need to maintain their “holdings”, i.e. a collection of securities which they purchase and maintain for investment or risk management. These securities could be bonds, equities, commodities, IRS, CDS, bond futures, FX Forward and cash. Each of these asset classes has different functions in the portfolio/fund.
Holding History is a table which contains the securities that they bought and sold across many years. It contains the securities (e.g. bonds, equities) that they are “holding” / have at that particular moment. There are 2 common design for holding history. One as a daily snapshot table, and the other as a transaction table.
Suppose you have a portfolio which consists of 50-60 equities and every month you make 2-4 trades. In the daily snapshot approach, every day all the 50-60 securities are inserted into the Holding History table. In the transaction approach, we maintain only 50-60 rows in the Holding History table. We updates these 50-60 rows with the trades.
This article is about the second approach (transactional). The Holding History table contain the following information:
1. What security was purchased/sold
2. In which account* (see below) the security was being held in
3. Which broker was used in purchasing/selling the security
4. The date the security was purchased
5. The date the security was sold (null if never sold)
6. How many days in holding (#5 minus #4, null if never sold, 0 on the day of purchase)
7. Quantity purchased
8. Purchase price (to 6 DPs, not just to cent/penny)
9. Purchase Value (this is usually gross, before purchase cost, e.g. broker charges)
10. Selling price (in 6 DPs)
11. Sale Amount (gross, before sales cost, e.g. broker charges)
12. Holding Count (1 if currently held, 0 if not currently held)

*An account could be a client mandate or an open fund or a closed investment trust. A client mandate or a fund could be implemented as several accounts, for example as different asset classes to differentiate the fund charges or to separete the “protection” part from the “growth” part.
If the same security is purchased for the second time, we create a second row for the second purchase.

The Holding History is per account. So if Account A needs 300 shares of Security1 and Account B needs 200 shares Securities1 and they purchased 500 then allocate it, then we create 2 rows, 1 for Account A and 1 for Account B.
The Holding History is different from “Active Holding” table and is different from “Transaction” table (aka Trade table). In a Transaction table we record the status of the trade, i.e. created, approved, executed, confirmed, allocated, settled, cancelled.

Active Holding table contains only the security held, but each security in an account only have 1 row. So if a security is purchased twice, it only has 1 row. It has more detailed information (more columns), such as:
1. Quantity held
2. Date first purchased
3. Average age (this is to calculate the growth. It is weighted on not qty but on value i.e. qty x price)
4. Cost (this is average cost across all purchases, gross)
5. Net cost (after purchase cost / broker’s fees)
6. Market price (this is yesterday’s closed market price)
7. Intraday price (this is today’s price)
8. Current value
9. Potential gain/loss
10. the usual holding information such as security ID, broker ID, account ID
11. Security Analytics* (see below) – at yesterday COB’s prices

Active Holding table enables the portfolio manager to see, very quickly, what this portfolio consist of, including the gain/growth,

*Security Analytics are credit risk analytics which are dependent only on the security. The quantity we hold in the portfolio doesn’t affect these analytics. Examples are: yield to maturity, current yield, effective yield, yield to worst, modified duration, effective duration, spread duration, convexity, effective convexity, option adjusted spread. Security Analytics operates at security level, as opposed to at portfolio level.

In this article we are going to create Holding History table, not Active Holding table.

DDL and Population


Let’s create the tables as follows:

create database Investment
go

use Investment
go

if object_id('dbo.FactHoldingHistory') is not null drop table dbo.FactHoldingHistory
create table dbo.FactHoldingHistory
( HoldingHistoryKey int,
AccountKey int,
SecurityKey int,
BrokerKey int,
DatePurchasedKey int,
DateSoldKey int,
DaysInHolding int,
QtyPurchased decimal(18,8),
PurchaseCost decimal(21,4),
SaleValue decimal(21,4),
AmountGained decimal(21,4),
HoldingCount int
)

insert into dbo.FactHoldingHistory
( HoldingHistoryKey, AccountKey, SecurityKey, BrokerKey, DatePurchasedKey, DateSoldKey, DaysInHolding,
QtyPurchased, PurchaseCost, SaleValue, AmountGained, HoldingCount)
values
(1, 3, 34, 2, 20140506, 20150107, 246, 349.321, 4000, 4300, 300, 1),
(2, 3, 96, 2, 20140709, 20150331, 265, 29.4156625, 2500, 2750, 250, 1),
(3, 3, 72, 2, 20140915, 20150219, 157, 81.0429, 3600, 3650, 50, 1)

select * from dbo.FactHoldingHistory

-- Create and Populate DimDate
create table DimDate
( DateKey int,
[Date] date
)

declare @d date
set @d = '1 Jan 2014'
while DateDiff(day, @d, '31 Dec 2015') >= 0
begin
insert into DimDate values (convert(varchar, @d, 112), @d)
set @d = DateAdd(day, 1, @d)
end

select * from DimDate

-- Create and Populate DimDateInput (for DimDateFrom and DimDateTo)
create table DimDateInput
( DateKey int,
[Date] date
)

declare @d date
set @d = '1 Jan 2014'
while DateDiff(day, @d, '31 Dec 2015') >= 0
begin
insert into DimDateInput values (convert(varchar, @d, 112), @d)
set @d = DateAdd(month, 1, @d)
end

select * from DimDateInput

Create SSAS Cube

Now let’s create the SSAS cube as follows:
DSV:
DSV

FactHoldingHistory named query:
SELECT HoldingHistoryKey, AccountKey, SecurityKey, BrokerKey, DatePurchasedKey, DateSoldKey, DaysInHolding, QtyPurchased, PurchaseCost, SaleValue, AmountGained, HoldingCount FROM FactHoldingHistory

Date From named query:
SELECT DateKey, [Date] AS [From Date] FROM DimDateInput

Date To named query:
SELECT DateKey, [Date] AS [To Date] FROM DimDateInput

Dimension Usage:
Dimension Usage

Cube Structure:
Cube Structure

In the cube structure we create 2 “input dimensions”, i.e. Date From and Date To. These 2 dimensions are for taking input from the users at run time. Using this method, users will be able to specify a date range that they want to use, at run time.
The Date From and Date To dimensions are only populated with the first date of every month, i.e. 1st Jan 2014, 1st Feb 2014, etc until 1st Dec 2015. This is so that the users are not overwhelmed with 730 days from 2 years (to chose from they would have to scroll down a lot of times).

The DimDate dimension on the other hand contains every dates in the two year period. This is so that the Date Purchased and Date Sold can have any value. I limit it from 1st Jan 2014 to 31st Dec 2015 but in practice this would be something like 15 years range (from 2005 to 2020).

In the Dimension Usage, the Date From and the Date To are not connected to any measure group. This way the users would be free too choose any member from these Input Dimensions, without affecting measures (hence the name “Input Dimension”).

And this is how we use the Input Dimensions in the Calculations tab:

Calculations:

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Measures].[Holdings Purchased] AS
Sum
( { StrToMember
( "[Date Purchased].[Date].&" +
Right(MemberToStr([Date From].[From Date].CurrentMember), 21)
):
StrToMember
( "[Date Purchased].[Date].&" +
Right(MemberToStr([Date To].[To Date].CurrentMember), 21)
)
}, [Measures].[Holding Count]
), VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[Holdings Sold] AS
Sum
( { StrToMember
( " [Date Sold].[Date].&" +
Right(MemberToStr([Date From].[From Date].CurrentMember), 21)
):
StrToMember
("[Date Sold].[Date].&" +
Right(MemberToStr([Date To].[To Date].CurrentMember), 21)
)
}, [Measures].[Holding Count]
), VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[Number of Holdings] AS
Sum
( ( { NULL:StrToMember
( "[Date Purchased].[Date].&" +
Right(MemberToStr([Date From].[From Date].CurrentMember), 21)
)
},
{ StrToMember
( "[Date Sold].[Date].&" +
Right(MemberToStr([Date From].[From Date].CurrentMember), 21)
):NULL
}
), [Measures].[Holding Count]
), VISIBLE = 1;

Calculated Measure – Peel By Layer

Here’s the expression used by the Holdings Purchased calculated measure:

CREATE MEMBER CURRENTCUBE.[Measures].[Holdings Purchased] AS
Sum
( {StrToMember
("[Date Purchased].[Date].&" +
Right(MemberToStr([Date From].[From Date].CurrentMember), 21)
):
StrToMember
("[Date Purchased].[Date].&" +
Right(MemberToStr([Date To].[To Date].CurrentMember), 21)
)
}, [Measures].[Holding Count]
)

Let’s peel in the above expression layer by layer.

From the outer most, it is simply: sum( {A:B}, M ).
Where A:B is a range in the Purchased Date dimension, and M is the Holding Count.
So in essense it is something like “sum of Holding Count where the Purchased Date is between A and B.

And this A & B is From Date and To Date. So if the user chooses From Date = 1st May 2014 and To Date = 1st Nov 2014, it will become: sum of Holding Count where the Purchase Date is between 1st May 2014 and 1st Nov 2014.

Let’s dive into a little bit deeper. The “A” in the “A:B” above is this expression:


StrToMember
("[Date Purchased].[Date].&" +
Right(MemberToStr([Date From].[From Date].CurrentMember), 21)
)

This works as follows:
1. Get the current member of the From Date
> [Date From].[From Date].&[2014-05-01T00:00:00]
2. Convert #1 to String
> [Date From].[From Date].&[2014-05-01T00:00:00]
3. Take 21 characters from the right of #2
> [2014-05-01T00:00:00]
4. Add “[Date Purchased].[Date].&” in the front of #3
> [Date Purchased].[Date].&[2014-05-01T00:00:00]
5. Convert #4 to Member
> [Date Purchased].[Date].&[2014-05-01T00:00:00]

In essense, it convert the 1st May 2014 member of Date From dimension to become Date Purchased dimension. Why do we need to do that? We want to constrain the Holding Count by the Purchased Date dimension, but we want to take the range of this constraint from the Date From dimension.

And the B part of the “A:B” works the same way:


StrToMember
("[Date Purchased].[Date].&" +
Right(MemberToStr([Date To].[To Date].CurrentMember), 21)
)

Assuming that the user selects 1st Nov 2014 for the Date To dimension, the above expression becomes: [Date Purchased].[Date].&[2014-11-01T00:00:00]
So the A:B becomes:
[Date Purchased].[Date].&[2014-05-01T00:00:00] : [Date Purchased].[Date].&[2014-11-01T00:00:00]

So the whole thing becomes:
Sum({[Date Purchased].[Date].&[2014-05-01T00:00:00] : [Date Purchased].[Date].&[2014-11-01T00:00:00]}, [Measures].[Holding Count])
Which is roughly: sum of Holding Count where the Purchased Date is between 1st May 2014 and 1st Nov 2014.
Or, making it variable: sum of Holding Count where the Purchased Date is between From Date and To Date.

The same thing applies to the Holdings Sold calculated measures. It’s just using Date Sold instead of Date Purchased, that’s the only difference.
So Holding Sold is roughly something like this: sum of Holding Count where the Sold Date is between 1st May 2014 and 1st Nov 2014.
Or, making it variable: sum of Holding Count where the Sold Date is between From Date and To Date.

Number of Holdings Calculated Measure

Number of Holdings is roughly like this: sum of Holding Count where the From Date is between the Purchased Date and the Sold Date.
Number of Holdings on 1st May is the sum of Holding Count where the Purchased Date is before 1st May and the Sold Date is after 1st May.

So the MDX for the calculated measure is something like this:

Sum
( ( {NULL:A},{B:NULL}),
[Measures].[Holding Count]
)
Where A is the Purchased Date and B is the Sold Date.

Filling in for Purchased Date = 1st May 2014 and Sold Date = 1st Nov 2014, it becomes:
Sum
( ( {NULL:[Date Purchased].[Date].&[2014-05-01T00:00:00]},
{[Date Sold].[Date].&[2014-05-01T00:00:00]:NULL}
), [Measures].[Holding Count]
)

Number of holdings is for a particular date, for example 1st May 2014. That’s why we only use 1 input dimension (From Date). We don’t use To Date.

Browse The Cube

Browse the Cube1

When we browse the cube using Excel Pivot Table, the measures and dimension looks like above. We put From Date and To Date on the Report Filter box, put the Holdings Purchased and Holdings Sold on the Values box. Then we set the From Date to 1st July 2014, the To Date to 1st Feb 2015, The result is below left, i.e. the Holdings Purchased = 2 and Holdings Sold = 1.

Browse the Cube2

The Holdings Purchased = 2 because only Purchase Date 9th July 2014 and 15th Sep 2014 are between 1st July 2014 and 1st Feb 2015:
(1, 3, 34, 2, 20140506, 20150107, 246, 349.321, 4000, 4300, 300, 1),
(2, 3, 96, 2, 20140709, 20150331, 265, 29.4156625, 2500, 2750, 250, 1),
(3, 3, 72, 2, 20140915, 20150219, 157, 81.0429, 3600, 3650, 50, 1)
(these 3 lines are taken from the DDL & Population above)

The Holdings Sold = 1 because only Sold Date 7th Jan 2015 is between 1st July 2014 and 1st Feb 2015:
(1, 3, 34, 2, 20140506, 20150107, 246, 349.321, 4000, 4300, 300, 1),
(2, 3, 96, 2, 20140709, 20150331, 265, 29.4156625, 2500, 2750, 250, 1),
(3, 3, 72, 2, 20140915, 20150219, 157, 81.0429, 3600, 3650, 50, 1)

Number of Holdings

Browse the Cube3

For the Number of Holdings, remove Holdings Purchased and Holding Sold from the Values box and put the Number of Holdings measure into the values box instead. Then remove the To Date from the Filter box, and set the From Date to 1st Aug 2014. The Number of Holdings = 2 as displayed in the above right.
The Number of Holdings = 2 because only the second row below has Purchase Date before 1st Aug 2014 and Sold Date after 1st Aug 2014:
(1, 3, 34, 2, 20140506, 20150107, 246, 349.321, 4000, 4300, 300, 1),
(2, 3, 96, 2, 20140709, 20150331, 265, 29.4156625, 2500, 2750, 250, 1),
(3, 3, 72, 2, 20140915, 20150219, 157, 81.0429, 3600, 3650, 50, 1)

Another Method

Another method is using MDX Filter function to filter the Holdings Purchased like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Holdings Purchased] AS
Sum
( FILTER
( [Purchased Date].[Date],
[Purchase Date].[Date].CURRENTMEMBER.name <= [From Date].[Hierarchy].CURRENTMEMBER.name
), [Measures].[Holding Count]
)

And for the Number in Holdings measure we need to use the Filter function twice using Cross Join, like this:
Sum(FILTER (A) * FILTER (B), M)
where A is a filter on the Purchase Date and B is a filter on the Sold Date, and M is the Holding Count.

Advertisements

1 Comment »

  1. Hey Vincent !

    Thanks a lot for the detailed article, really useful seeing how often this issue is encountered on the field.

    Just a quick note in passing: unless I’m misremembering, with this technic the resulting measures cannot be used in SSMS > browse mode, as the way SSMS generates MDX queries (applying filters via subcubes) breaks the CURRENTMEMBER calls on the FROM/TO dimension. Of course writing an actual MDX query solve the issue 😉

    Comment by Fleid — 17 July 2015 @ 1:32 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: