Modelling facts and related state transitions

View previous topic View next topic Go down

Modelling facts and related state transitions

Post  pscorca on Fri Nov 21, 2014 8:14 am

Hi,
I need to model in my dwh the facts from operations table and contracts table inside a relational db. An operation, as a contract, could change status in the time. In the relational source database, I need to read:
a. the operations table and the operation_status table having as the pk the pk of the operations table plus a progressive number, the state value, the related change date and reason;
b. the contracts table and the contract_status table having as the pk the pk of the contracts table plus a progressive number, the state value, the related change date and reason.

I think to flat the operations and the related states in an unique fact table with a surrogate key, the operation id, the status change date plus the other attributes of the operations source table. In an analogue manner I want to handle the contracts and the related states.

Is it the right approach to follow in order to model this scenario?
Many thanks

pscorca

Posts : 4
Join date : 2014-11-21

View user profile

Back to top Go down

Re: Modelling facts and related state transitions

Post  nick_white on Sun Nov 23, 2014 4:20 pm

Please can you clarify what your reporting requirements actually are - what are your measures, etc.?
Thanks

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Modelling facts and related state transitions

Post  pscorca on Mon Nov 24, 2014 4:22 pm

Mainly I need to consider three entities from the source database: contracts, devices and operations.
A customer can rent a device from the company supplier, and so a contract can be subscribed between the customer and the supplier.
A contract can assume different status during his life: created, active, suspended, closed and so on.
In the source database, near the contract entity is present a table to register the assumed states for a contract, with the status change state
and the status reason change. Really this status table contains other info which fe the customer subscriber.
The operations entity contains the different operations accomplished about a device: installation, repair, replacement and so on.
Also in this case, near the operations entity in the source database is present a table to register the assumed states for an operation: planned, canceled,
closed and so on.
The dwh/data mart to create must satisfy some user requirements:
a. which is the number of the contracts in a specified year, quarter, month or week and/or in a specified status?
b. which is the number of the operations about a subscribed device in a specified year, quarter, month or week and/or in a specified status?
c. which is the time interval between the opening date and the closure date for an operation?
No amount is to consider.

Thanks

pscorca

Posts : 4
Join date : 2014-11-21

View user profile

Back to top Go down

Re: Modelling facts and related state transitions

Post  pscorca on Wed Nov 26, 2014 4:18 am

No replies, please? Thanks

pscorca

Posts : 4
Join date : 2014-11-21

View user profile

Back to top Go down

Re: Modelling facts and related state transitions

Post  nick_white on Wed Nov 26, 2014 5:57 am

Hi,
ignoring the status issue for the moment, it sounds like you need two fact tables:
Contracts: Measure = count; Dims = Contract, Date(s), etc.
Operations: Measure = count; Dims = Device, Date(s), etc.
Assuming that you need to create Operation Facts when the Operation is opened, rather than just when it is closed, I would have FKs to Opened and Closed Dates and a duration measure. You would then need to update the Closed Date and duration when the Operation closes.
While it is good practice to avoid updating facts, creating separate records when the Operation is Opened and then Closed and then having to calculate the duration by comparing the two records when you run a report is likely to be more of an issue then updating the fact record.

Status: do you want to be able to report on the Status at any point in time or do you just want the current status of the Contract/Operation?
If you want to be able to report on historic statuses, how do you want to handle statuses if the report is not for a specific date e.g. if you count Contracts for a week, the same Contract could have multiple statuses within that week - so do you not allow Statuses to be included in this type of report or do you have rules defining how the status should be calculated within a specific period?

Regards,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Modelling facts and related state transitions

Post  pscorca on Wed Nov 26, 2014 11:00 am

Hi, thanks for your reply.
In the database source it is present the Operations table and the related operation status table, having a status value,
a status change date and a reason for the status change. So, for each operation it is possible to have more one/two states.
Now, the question is: what is the grain for the fact table about the operations? Single operation + single operation status assumed or single operation?
Obiously, in the first case I will consider also the status change date. The operations fact resembles to a factless fact table.
For the operations is also important to show the current (or last) status assumed.

In an analogue manner I think to handle the contracts fact. In the database source it is present the Contracts table and
the related contract status table, having a status value, a status change date and a reason for the status change date and
a reason, in order to register the status change history.
Is it right to think the contracts as a fact table or is it better to consider as a (SCD) dimension table?
Which is the grain for the contracts fact? Single contract + single status assumed or single contract?
It is important to show also the first and the last contract status assumed.

As a last point to face, I need to model in my dwh the external contracts related 1:1 with the contracts.
The contracts are intended as the contracts as managed internally and the external contracts are intended
as the contracts managed to the external suppliers. Can I merge the contracts and the external contract in
an unique fact table?

Many thanks

pscorca

Posts : 4
Join date : 2014-11-21

View user profile

Back to top Go down

Re: Modelling facts and related state transitions

Post  nick_white on Thu Nov 27, 2014 12:09 pm

pscorca wrote:
Is it right to think the contracts as a fact table or is it better to consider as a (SCD) dimension table?
Which is the grain for the contracts fact? Single contract + single status assumed or single contract?
It all depends on what your reporting requirements are - the fact that information is held in your source, and how it is held, is not really relevant if there is no requirement to report on it.
If you want to report on anything then you need a fact table - whether you call that the Contracts fact table or something else doesn't really matter.
If there are attributes of a contract that you want to filter/aggregate a report by then you will need Contract Dim to hold these attributes.
If you want to know the value of the contract attributes at the point where the fact record occurred then you will need to make the Contract Dim SCD2; if you only want to know the current values then make it SCD1 and if you want to know both historic and current then use the appropriate combined SCD pattern (I think Kimball now goes up to about 7 different SCD patterns).
Fact grain - whether you hold status as an attribute of the Contract Dim or hold it in it's own Dim (and what type of SCD that Dim follows) should again be based on what the reporting requirements are.
If a contract goes through a defined number of statuses and in a set order you might want to consider implementing an accumulating snapshot fact table - but again only if there is a reporting requirement to do this rather than just because you can.

Does this help at all?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Modelling facts and related state transitions

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