Context
The fee based reporting we had in Analytics was based on the fee model in Core. However the Core team were looking at a new process which would make fee and commission administration easier in Core.
The plan was that this new approach would replace the existing method and so the reports in Analytics would need to be addressed to allow for this transition so that they did not break.
Background
From a user interface point of view the commission information in Core is currently stored as fields in the Fee table.
So at the moment, if we have a commission which is split between consultants we have to complete different fields in Core. Each consultant has their own fields which need completing:
What this results in from a back-end perspective is a Fees table with different columns for the different amounts:
In analytics in general, aggregation is done by specifying a column. However with the above tabular structure, we cannot get the total commission for the fee by aggregating a single column.
We also cannot easily get simple analysis such as the total commission by consultant because the consultant name information is also being stored in multiple columns rather than a single 'Name' column.
To accommodate this, we have a step in the SQL ETL process which unpivots this data so the names and amounts are in the more usual analytical tabular format:
In the new world with the new process in Core, this additional wrangling in the back-end will no longer need to be done.
Instead, the new commission lines grid allows the user to enter the data in this format in Core so none of the transformation work has to be done in the ETL anymore.
This new method to record commission is to be the method going forward. At some point the old method of using fields in the Fee entity will be deprecated. How can we accommodate these Core changes in our Analytics processes?
Investigation
In broad terms, there are three options for how we deal with this change in Source:
- Option 1: Build a parallel ETL process
- Option 2: Extend the existing infrastructure to accommodate data from either Source entity
- Option 3: Employ a dynamic approach
Option 1
Overview
The idea here is that we could build a whole new extraction, transformation and load process in the "background" keeping the existing pipeline running as normal. And then switch over to the new one when the time is right and then retire the old way.
Implementation Thoughts
Add another table to the Data Dictionary.
Create new staging table to house the source data extracted from the new source entity.
Create new transformation stored procedure and fact table.
Add new view to report schema.
Pros
No need to break anything current.
Can easily be tested / validated before going "live".
If anything is broken or needs to be reworked can be done in the background.
Cons
Extra effort to create a brand new process.
More resource usage and storage while both pipelines are still in use. Would have a cost implication.
Power BI would also need re-pointing to the new view in the report schema on go-live.
Option 2
Overview
Similar to option 1, this time however we amend the current extraction to the staging layer to also include the new source table.
Implementation Thoughts
Either modify the existing FetchXML using joins or add an additional FetchXML query and then append / merge the data in staging.
Or keep the extraction separate and create a new stored procedure to transform the data and the merge / append the data later downstream during the load process into the Fact table.
Once the old way has been deprecated, could then remove the logic which exclusively deals with this extraction and transformation.
Pros
No requirement to maintain two separate pipelines as in Option 1.
Cons
Could be complex to re-write the FetchXML, stored procedures etc. to "merge" the two sources of data into 1 table.
What would testing look like?
If there are any issues with this, it would be a lot harder than Option 1 to revert back.
Option 3
Overview
This would involve a config setting (in the data dictionary) which confirms which is the current source table for extraction.
Implementation Thoughts
As part of the deployment, the extraction would only run the FetchXML query based on the row which confirms which source tables have the isCurrent flag set to Yes.
Then the stored procedure which creates the staging table would create this staging table based on the IsCurrent flag and the associated FetchXML query and target mapping.
Pros
No requirement to change the downstream processes.
Scalable for the case if/when another source table changes.
Cons
Complex to accomplish.
Is this over-engineering for this one-off situation?
Outstanding Questions
Do we need to consider the history records in the three options above? We don't do any reporting on history currently but how easy would analytical reporting be based on historical records in these three cases?
Future Enrichments
The new source data in core has a number of fields which were not in the original source table. This new data opens up possibilities we didn't have previously. These are areas we could now explore, if the business has the need.
The commission role
The start date and end date for which any split / commission is applicable.
With these new fields we could potentially provide analysis in the following areas:
1. Commission cash flow forecasting
The ability to predict when and how much commission amounts will be paid out.
2. Commission Date vs Fee Date
Take a look at what the gap is between when the fee is earned and when the related commission is paid.
3. Commission by role
Which roles are driving the most value?
4. Role behaviour based on commission type
Do consultants (or other roles) behave differently depending on whether the commission is a flat amount or percentage based? Does the client involved affect behaviour?
5. Commission trends
Which periods see more commission?
6. Commission splits
Where multiple roles are involved, can we derive any insights? Is there a pattern to the splits?
7. Experience
Does experience play a part in the amount of commission a consultant will earn? Do more experienced roles earn larger commissions?
Additional Information
For options 2 and 3, the current Power BI report would be unaffected because the proposed changes would happen upstream. The data being ingested into Power BI would remain in the same structure as now.
What Happened Next?
The old way of doing things was turned off in core with the customers being informed well ahead of time. They were also trained on the new way of submitting commissions. The data team chose to do option 2 with a new extraction and stored procedure relating to the new table.
As the old way was deprecated, there was no requirement to append both tables in the warehouse. The new data flow simply replaced the old. Power BI was unaffected.