Seeking suggestions on how to potentially model 0:M scenario
3 posters
Page 1 of 1
Seeking suggestions on how to potentially model 0:M scenario
Greetings,
Perhaps I'm overthinking the situation, but I've come to a mental roadblock on how to sensibly model a scenario within our data warehouse. Simply put, our call center takes in a number of cases each day. Transactionally speaking, the cases are a sort-of "parent-child" relationship in the sense that a case may or may not have case notes. Each new case note ties to an existing case number in the header table.
As expected, due to the nature of the data, this is really large data - a lot of free form text detailing the call/problem being logged in the system - and all information that our reporting end users do want to see.
My question is, how do I model the "zero:many" at the case note level? So far, I have gone about it by having a fact at the case note level but several (if not a majority of) cases may never have notes attached to them. Maintaining the fact at this level would be strange.
I am thinking of creating one fact that is at the CASE NOTE level
Dimensions:
Maybe it's been a long day, but I'm struggling with how to model this with one fact (which may also be my problem). I don't want to repeat the large text fields in a single dimension if I can avoid it. Thanks for your help.
Perhaps I'm overthinking the situation, but I've come to a mental roadblock on how to sensibly model a scenario within our data warehouse. Simply put, our call center takes in a number of cases each day. Transactionally speaking, the cases are a sort-of "parent-child" relationship in the sense that a case may or may not have case notes. Each new case note ties to an existing case number in the header table.
As expected, due to the nature of the data, this is really large data - a lot of free form text detailing the call/problem being logged in the system - and all information that our reporting end users do want to see.
My question is, how do I model the "zero:many" at the case note level? So far, I have gone about it by having a fact at the case note level but several (if not a majority of) cases may never have notes attached to them. Maintaining the fact at this level would be strange.
I am thinking of creating one fact that is at the CASE NOTE level
- CASE_ID
- NOTE_SEQ_NBR
- Other Dim FKs
Dimensions:
- Case Profile Dimension (Junk/Low Cardinality)
- Case Detail Dim (High Cardinality, Free Form Fields)
- Case Note Detail Dim (High Cardinality, Free Form Fields)
Maybe it's been a long day, but I'm struggling with how to model this with one fact (which may also be my problem). I don't want to repeat the large text fields in a single dimension if I can avoid it. Thanks for your help.
cjtravis- Posts : 3
Join date : 2013-10-30
Re: Seeking suggestions on how to potentially model 0:M scenario
Hi - Kimball talks about this in his book (search for "Freeform Text Comments"). To summarise: don't put freeform Notes in Fact tables, either put them in the relevant dimension or create a Notes Dimension that can hold notes created in relation to any activity.
Given that a dimensional model is there to support analytical queries and you can't use freeform text fields in such queries (or not in anyway that makes sense) my personal preference is to put notes in a standalone table. I don't consider it strictly part of the dimensional model but more a standalone table that the the resultset of an analytical query can be joined to
Regards,
Given that a dimensional model is there to support analytical queries and you can't use freeform text fields in such queries (or not in anyway that makes sense) my personal preference is to put notes in a standalone table. I don't consider it strictly part of the dimensional model but more a standalone table that the the resultset of an analytical query can be joined to
Regards,
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Seeking suggestions on how to potentially model 0:M scenario
Kimball or any other relational model doesn't handle unstructured data well. If you want to use a dimensional model, you need to do some preprocessing to make it structured. This is one good use for big data, sentiment analysis, etc.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Need help on a dimensional model scenario
» How would you model this simple scenario ?
» data model for 2 fact tables (Header / Detail scenario)
» How to convert required OLTP schema into dimensional model
» Modeling suggestions
» How would you model this simple scenario ?
» data model for 2 fact tables (Header / Detail scenario)
» How to convert required OLTP schema into dimensional model
» Modeling suggestions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum