October 9, 2017 at 11:01 am
Ok so I have two stages within a product lifecycle:
1. Application
2. Contract
In the application phase I have a metric, let's call it CoveredAmount which can change throughout the Application phase. Once the Application is agreed and it moves to a Contract this amount doesn't change. In the source database (which has been modelled in 3rd NF from the OLTP system - don't ask!) this metric is stored against both the Application and the Contract equivalent tables.
Until the Application moves to a Contract stage there a ContractID (Business key) does not exist. The business also do not need to sum these amounts so I am not treating it as a fact.
Currently I plan to track CoveredAmount within the Application dimension as a Type 2 SCD but rename it to ApplicationCoveredAmount so the business users can look at changes over time during this phase. I then plan to store the final CoveredAmount in the Contract dimension.
I was handed a draft dim model design put together by a prev employee - who wasn't fully up to speed on the source system and they had CoveredAmount as a Type 2 on the Contract dimension only. I don't believe this is a accurate representation of the business process nor could you track any changes as there would be no business key to join it up to the contract data.
Does this make sense to everyone? I want to ensure I am not missing anything obvious as it seems to easy....any feedback or questions??
Thanks for reading.
October 12, 2017 at 10:37 am
It makes sense if you're trying to give the users the ability to pull in attributes on the contract as well attributes on the application. In meaning, you are capturing the journey from application to contract and you want to ensure when you join the contract records, you can also join the application details with those records. It's just a matter of how you are handling the capture of this journey in the fact table.
I would imagine you start populating the fact with that history as soon as the application comes in. This becomes a record without a ContractID. Then when that user does sign a contract, how do you handle the history from that point on? Does it become a new record where there is a ContractID with the ApplicationID? Do you update the previous application record with the ContractID? Or do you just delete the prior records and leave one record per journey with the ContractID and ApplicationID where the Type 2 Dimensions are the only details on that history? Either way, sounds like the history has to be recorded, just a matter of where.
I think for the most part, people track that history in the fact. In meaning, you are capturing every event, state, log that user makes on their journey in the actual fact table. That way you can see a clear history of steps from 0 to N of that user. You may have a date field, customerID field, applicationID field, contractID field, and other attributes with the metrics combined in that record. Where the application dimension may have details on the app and likewise of details of the contract it's respective dimension. Then Type 2 comes into play when details of that application change on the attribute side, not the metric side. Like a name change, martial status change, contract state change, etc.
While this may result in a lot of logs, that's what most are shooting for so they can then roll that history up any way they see fit to meet the needs of the business and how the business may change down the road. For example, going from a detailed history in the data warehouse to a summarized history in the data mart layer where it's easier to analyze and query into the visualization layer like SSRS, Tableau, PowerBI, etc.
October 12, 2017 at 11:06 am
Additional information would be helpful. First, what is the granularity of the fact table(s)? Is it every change in the application? Part of your discussion makes me think of an accumulating snapshot fact table. In this case, you are tracking the progress of an application from start to finish. But as finish in this case is "contract," which seems to have it's own characteristics not shared by application (I think), I'm not sure.
As for the 2SCD, that is used for preserving history when items shift within the hierarchy. For example, if a store is assigned to a different region, without a 2SCD dimension, all that store's history in the original region would be transferred to the new one. This doesn't seem that case.
October 12, 2017 at 3:02 pm
Ok let me try and answer some of your questions:
1. Amongst other objects there is a Dim application and contract as well as a Fact application (granularity is 1 record per application) and contract (1 record per contract). Fact Application contains the ApplicationID while Fact Contract contains both the ApplicationID and ContractID.
2. The fact application will start being populated as soon as the application comes in
3. When a contract is signed it will be tracked in the fact contract table. So a fact table to represent each business process at the lowest grain available.
4. As the various things change on on the app or the contract such as the status then this will be tracked in the dimension as a type 2 and also a new record added to the fact table
5. There is a bridge table to marry up the application & contracts to the customer dimension to resolve the M2M relationship
6. You are absolutely correct in stating an accumulating snapshot table. That is certainly the plan along with the existing fact tables mentioned above. An application and status moves through it's own stages during the process independent of each other and can overlap and iterate back to previous statuses etc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply