Modelling - Financial Advice (Sales)

View previous topic View next topic Go down

Modelling - Financial Advice (Sales)

Post  montanajr on Tue Jan 05, 2016 11:53 am

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

montanajr

Posts : 3
Join date : 2016-01-05

View user profile

Back to top Go down

RE: Modelling - Financial Advice (Sales)

Post  zoom on Tue Jan 05, 2016 2:49 pm

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Modelling - Financial Advice (Sales)

Post  BoxesAndLines on Tue Jan 05, 2016 3:49 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modelling - Financial Advice (Sales)

Post  montanajr on Wed Jan 06, 2016 5:19 am

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?

montanajr

Posts : 3
Join date : 2016-01-05

View user profile

Back to top Go down

Re: Modelling - Financial Advice (Sales)

Post  zoom on Wed Jan 06, 2016 8:20 am

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Modelling - Financial Advice (Sales)

Post  BoxesAndLines on Wed Jan 06, 2016 9:03 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modelling - Financial Advice (Sales)

Post  montanajr on Wed Jan 06, 2016 11:45 am

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.

montanajr

Posts : 3
Join date : 2016-01-05

View user profile

Back to top Go down

Re: Modelling - Financial Advice (Sales)

Post  ngalemmo on Wed Jan 06, 2016 12:52 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Modelling - Financial Advice (Sales)

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum