Modelling situation with Task, Person and Document in unpredictable business processes

View previous topic View next topic Go down

Modelling situation with Task, Person and Document in unpredictable business processes

Post  lecro on Tue Aug 30, 2011 2:29 pm

Hi,

Could you please help me with a datamodel? Case is quite complicated.

Imagine several departments. Each department has many persons.
Each person does some task related or not related to document.
Each task has a duration and result.
Some tasks lead to change of the status of a document (verified, approved, changed).
Each document may change it's status (verified) under several tasks (do_verify) from several persons from several departments.

Business needs are:
1. Find problematic documents (longest durations, some errors during tasks).
2. Understand who have made an error.
2. Understand how each person spends time during the day depending on his tasks and regarding/regardless documents (characterized by idle time, etc).
3. Assess each person on his work for each document.
4. Provide OLTP part with visibility for each task and actions taken.

Number of steps (tasks) is unpredictable.
We've designed it as
DIMPersons - 1:M - FTask (transactional) - M:1 - DIMDocument;
FTask - M:1 - DIMStatus.


Day 1 Pers 1 Verifies, changes fields Doc 1 50 sec sampling factor 1
Day 1 Pers 1 Verifies, changes fields Doc 3 150 sec sampling factor 1
Day 1 Pers 1 Edits, changes fields Doc 1 50 sec sampling factor 1
Day 1 Pers 2 Verifies, changes fields Doc 3 1150 sec sampling factor 1
Day 2 Pers 1 Verifies, changes fields Doc 3 150 sec sampling factor 1

Why we didn't introduce accumulating snapshot?.. We thought that the bridge between Person and Document will be too wide and heavy as it will contain a lot of non-weightable measures like idle time.

Transactional quite obvious, but we have real troubles with several things.

1. At current moment DocID is degenerate dimension due to high cardinality. However, document has document-level attributes (Category, dates, etc) and now we understand that the document may change it's natural ID during each task. How to deal with a high cardinality dimension here?

More questions to follow.




lecro

Posts : 2
Join date : 2011-08-29

View user profile

Back to top Go down

Re: Modelling situation with Task, Person and Document in unpredictable business processes

Post  ngalemmo on Tue Aug 30, 2011 2:49 pm

At current moment DocID is degenerate dimension due to high cardinality. However, document has document-level attributes (Category, dates, etc) and now we understand that the document may change it's natural ID during each task. How to deal with a high cardinality dimension here?

Keep the ID degenerate and make document category (and possibly other document attributes) dimension(s) off the fact. Dates relating to the document should be dimensions as well.

Why would changing document ID's matter in this case?
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 situation with Task, Person and Document in unpredictable business processes

Post  lecro on Tue Aug 30, 2011 9:10 pm

Thank you for prompt and clear answer!
Concern about natural ID change is because we need to track this change. Should we go in a way similar to SCD1 and update all DD entries?..


lecro

Posts : 2
Join date : 2011-08-29

View user profile

Back to top Go down

Re: Modelling situation with Task, Person and Document in unpredictable business processes

Post  BoxesAndLines on Wed Aug 31, 2011 9:15 am

Dump the document dimension. You need to create additional dimensions from the document table, Doc_Status_Dim, Date_dim, etc. If the source changes the doc_id (which is reason to fire the OLTP modeler for picking such a bad PK), and you cannot link the old doc to the new doc, then there's not a whole lot that you can do. If you can, I would create a new attribute that links all of the doc id's from the source and manage it through ETL. And transaction fact is the way to go here. The editing time is nicely additive.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Modelling situation with Task, Person and Document in unpredictable business processes

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