Modelling - Financial Advice (Sales)

Hello all,

Looking for a bit of guidance as I am relatively new to dimensional modelling and have a bit of a quandary as to how this would be achieved for the Financial Advice industry.  I am looking to dimension-ally model sales (income) data as I think it would be useful to interrogate via SSAS, so my first point of call is designing a star schema.

In this particular industry, both products and services are offered to Clients. A product can be an investment (of varying types) and a service could be a single, ad hoc fee paid. The aim of this star schema is to report on both of these types of income - the grain of the fact table would therefore be to look at what income has been received for a particular Sales Person, for a particular Client, on a particular day for a particular product OR service. It is the last bit that I am struggling with. I would have the below dimensions for certain:

Client
Financial Adviser (Sales Person)
Date

I was then planning on having a Product dimension that included the various types of investment products and an entry for "Financial Advice", which would allow me to capture the "service" side of income. But a Product can have various types of income; annual management charges, initial investment fees, so I was planning on having a further dimension of Income Measure, which stipulated the type of income. This would only relate to a product however, as a Service is always an income type of Fee.

I wasn't sure if this was best done as a separate dimension, or if my fact table should have multiple columns; Fee, Management Charge, Initial Investment Fee, but then only some of these measures in the fact table would apply to some of the Product dimensions i.e. non-additive.

The fact table would simply be:

FactIncome
AdviserKey (FK)
ClientKey (FK)
DateKey (FK)
ProductKey (FK)
IncomeTypeKey (FK)
IncomeAmount

A summary of the dimension table definitions are below;

DimFinancialAdviser
AdviserKey (PK)(Surrogate)
CRMKey (Natural)
Name
Manager
Location
etc.

DimClient
ClientKey (PK)(Surrogate)
CRMKey (Natural)
Name
Age
Postcode
etc.

DimDate
DateKey (PK)
DateName
MonthName
YearName
DayOfMonth
etc.

DimProduct
ProductKey (PK)
ProductName
The plan would be to have the below stored in here:

• Investment Product 1
  
• Investment Product 2
  
• Investment Product 3
  
• Financial Advice
  

DimIncomeType
IncomeTypeKey (PK)
IncomeMeasureName
The plan would be to have the below stored in here:

• Advice Fee
  
• Annual Management Charge
  
• Initial Investment Fee
  
• Annual Review Fee
  

But the issue is that an Income Type of Advice Fee would only relate to a product of Financial Advice, and all three investment products could have AMC, Initial and Review Fee, but not an Advice Fee.

Looking for any sort of guidance here. Would be GREATLY appreciated.

Many thanks,
montanajr

Are you building these tables based on a business requirements ( it does not sound like it)? Financial advisor (FA) fee is a bit complex. FA fee can be split among multiple advisors, so there is a percentage of fee amount given to a FA based on their level of experience.  

Anyway, you need a lowest level of transaction Fact tables to save daily transactions. First analyze different investment transaction data to find out if their grain is same across different types of transaction or not. In my experience, transactions to buy and sell stock, bond, options…etc. have unique attributes then a “fee” transactions so their data should not be kept together in a single transaction fact table.  
My advice is to create many transaction fact tables based on their uniqueness and after that you can create a Fact table as you purposed, which I think is a daily summary Fact table.

Put two metrics on your fact table, 1 for product revenue, 1 for service revenue. See if that helps clean up the design.

Yes, there is a clear business requirement; to track income (revenue) for Financial Advisers, with the ability to drill down across product/service, time (week/month/quarter/year), and Client.

To confirm; I want to be able to track the following revenue streams:

• Initial Investment Fee - relates to different products
  
• Financial Advice Fee - relates to a service
  
• Annual Management Charge - relates to different products
  
• New Assets - relates to different products
  
• Recurring Service Fees - relates to a service

If I have two metrics in the fact table, as suggested, then the Service Revenue column would be blank when the row in the fact table related to an investment product product, and vice versa. Assuming this is acceptable in terms of design?

If your Business users do not like a blank investment revenue column when product is fee (or vice versa), then you can assignee a default or not applicable amount for example -0.01 . That approach of assigning a N/A value should be used after business users consent.

Another option is to create only one "revenue" amount column in the fact table. Based on the dim product type (investment or Fee) that column is going to have a value.

montanajr wrote:

If I have two metrics in the fact table, as suggested, then the Service Revenue column would be blank when the row in the fact table related to an investment product product, and vice versa. Assuming this is acceptable in terms of design?

Create 2 views. One view pulls rows where service revenue > 0, the other pulls investment revenue > 0.

zoom wrote:If your Business users do not like a blank investment revenue column when product is fee (or  vice versa), then you can assignee a default or not applicable amount for example -0.01 . That approach of assigning a N/A value should be used after business users consent.

Another option is to create only one "revenue" amount column in the fact table. Based on the dim product type (investment or Fee) that column is going to have a value.

I originally had just an IncomeAmount column within the fact table; the issue is that there are multiple income types attributable to different products. Hence why I had IncomeType as a separate dimension.

Sounds like at least two facts to me. One to record client activity and charges (both investments and fees as the client sees it), and a second fact that breaks down fees/income related to the activity.