July 11, 2011 at 12:47 am
Hi All,
I have a quick question,
I appreciate any quick response.
Thanks in advance.
July 11, 2011 at 5:45 am
narain337 (7/11/2011)
Kindly give an example of a real business situation where neither a star nor a snowflake schema can be used to represent that data.
Firstly, a snowflake "schema" is nothing but a variant of a Star schema.
Secondly, since a varying numerical quantity should be either tracked as a Slowly Changing Dimension of the Type 2 or a multi-state fact there is nothing better than a Star schema to do it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 13, 2011 at 3:05 pm
sqlTechie (7/11/2011)
Kindly give an example of a real business situation where neither a star nor a snowflake schema can be used to represent that data.
As far as I'm aware the term "snowflake schema" was invented by Ralph Kimball. It is usually applied to his "dimensional" model. In Kimball terms "star" and "snowflake" are both variations of dimensional design patterns. So I will interpret your question as follows: "Give an example of a real business situation where the dimensional model cannot be used to represent that data." That's a good question!
There are plenty of cases for which a dimensional model is not well suited. Dimensional modelling really just means a set of cookbook examples and design patterns - it isn't a design discipline like normalization that is based on sound principles applicable to any potential database schema.
Dimensional models are most often used for simple data marts with a few entities. In a larger, more complex enterprise data warehouse it may only be feasible to present limited bits of information dimensionally because of the compromises necessary to map normalized data into a dimensional view. Another limiting factor is the potential cost and complexity of generating the redundant data required to support dimensional models - especially problematic if you need to support near real-time reporting.
Dimensional models always introduce "bias" into a design when compared to a 5th Normal Form model of the same data. Therefore they are inherently less satisfactory for some potential queries than to others.
Most if not all dimensional models also assume that the time component of facts has a fixed granularity. That's no good if you have to capture data with irregular, varying or discontinuous granularity.
July 17, 2011 at 6:42 pm
David Portas (7/13/2011)
Dimensional models are most often used for simple data marts with a few entities. In a larger, more complex enterprise data warehouse it may only be feasible to present limited bits of information dimensionally...
A proper, complex, enterprise level Data Warehouse is nothing but a collection of simple Datamarts that share some conformed dimensions therefore there is not limit in regards to the complexity of a Data Warehouse a Star schema modeling a.k.a. dimensional modeling can support.
I agree that ETL processes get progressively more complex as business requirements approach near real time limit but I still have to see a RDBMS based Data Warehouse solution that beats Dimensional Modeling in terms of: elegance, performance, scalability and maintainability.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 17, 2011 at 10:43 pm
PaulB-TheOneAndOnly (7/17/2011)
A proper, complex, enterprise level Data Warehouse is nothing but a collection of simple Datamarts that share some conformed dimensions
Weeeellll... normally an EDW has a whole management process, meta-data/data-lineage, master data management, etc, as well.
PaulB-TheOneAndOnly (7/17/2011)
I still have to see a RDBMS based Data Warehouse solution that beats Dimensional Modeling in terms of: elegance, performance, scalability and maintainability.
Hmm. I think I know what you mean (you mean a 3NF model like Inman as distinct from a dimensional model) although they are both represented in terms of related tables within an RDBMS, and both can act as sources for cubes. I love dimensional models, but they are harder to do for near-real-time.
sqlTechie (7/11/2011)
Kindly give an example of a real business situation where neither a star nor a snowflake schema can be used to represent that data.
Dimensional models track changes via "slowly changing dimensions" with the assumption that the metrics are static once recorded. They start to fall apart when you have "rapidly changing dimensions" or time-variant metrics. In both of those cases, there are some modelling tricks you can do to reduce the issue and better represent them.
So off-hand, I'd say there aren't any "real business situations" which can't be represented, but perhaps there are some that shouldn't.
July 18, 2011 at 3:08 am
PaulB-TheOneAndOnly (7/17/2011)
A proper, complex, enterprise level Data Warehouse is nothing but a collection of simple Datamarts that share some conformed dimensions therefore there is not limit in regards to the complexity of a Data Warehouse a Star schema modeling a.k.a. dimensional modeling can support.
Only if you are following Ralph Kimball's path. For everyone else a data warehouse is a subject-oriented, integrated, time-variant and non-volatile database and typically it is in (at least) 3rd Normal Form.
You describe Kimball's method as a "proper" warehouse as if other things didn't count as a proper warehouse. Being "dimensional" is not a requirement for a data warehouse. Many data warehouses are much, much more than just a "collection of simple Datamarts". I already mentioned some things that don't easily fit the set of Kimball design patterns referred to as "dimensional modelling".
Not sure what your point about being "RDBMS based" is. Almost all data warehouses I have worked on are based on SQL DBMSs.
July 18, 2011 at 6:09 am
David Portas (7/18/2011)
PaulB-TheOneAndOnly (7/17/2011)
A proper, complex, enterprise level Data Warehouse is nothing but a collection of simple Datamarts that share some conformed dimensions therefore there is not limit in regards to the complexity of a Data Warehouse a Star schema modeling a.k.a. dimensional modeling can support.Only if you are following Ralph Kimball's path. For everyone else a data warehouse is a subject-oriented, integrated, time-variant and non-volatile database and typically it is in (at least) 3rd Normal Form.
Well... that's the definition of a Data Warehouse 😎 and I beg to differ they are not "typically (implemented) in at least 3rd Normal Form". 3NF+ is the way to go for OLTP systems but certainly not the path to follow for a Data Warehouse.
David Portas (7/18/2011)You describe Kimball's method as a "proper" warehouse as if other things didn't count as a proper warehouse. Being "dimensional" is not a requirement for a data warehouse. Many data warehouses are much, much more than just a "collection of simple Datamarts". I already mentioned some things that don't easily fit the set of Kimball design patterns referred to as "dimensional modelling".
I know this is going to be a quase-religious confrontation but it is not my fault if Kimballl put together the guidellies of what it is still today the best way to implement a Data Warehouse. By the way, if your core Data Warehouse is not a collection of simple Datamarts then you are in serious troubles, most probably caused for not following dimensional design 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 18, 2011 at 6:16 am
sqlTechie (7/11/2011)
Hi All,I have a quick question,
Kindly give an example of a real business situation where neither a star nor a snowflake schema can be used to represent that data.
I appreciate any quick response.
Thanks in advance.
I think I smell a student looking for help with his/her homework. If you're just asking for your own edification, why the need for a quick response?
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 18, 2011 at 12:06 pm
PaulB-TheOneAndOnly (7/18/2011)
If your core Data Warehouse is not a collection of simple Datamarts then you are in serious troubles, most probably caused for not following dimensional design 😀
Hmm. My understanding is that the Inman method (which is 3NF+) doesn't follow the practice of breaking down the data warehouse into datamarts. I also believe this is still a widely used practice, and not one that leads to "serious troubles".
July 18, 2011 at 1:29 pm
PaulB-TheOneAndOnly (7/18/2011)
Well... that's the definition of a Data Warehouse 😎 and I beg to differ they are not "typically (implemented) in at least 3rd Normal Form". 3NF+ is the way to go for OLTP systems but certainly not the path to follow for a Data Warehouse.I know this is going to be a quase-religious confrontation but it is not my fault if Kimballl put together the guidellies of what it is still today the best way to implement a Data Warehouse. By the way, if your core Data Warehouse is not a collection of simple Datamarts then you are in serious troubles, most probably caused for not following dimensional design 😀
Not everyone drinks the Kimball Kool-Aid, Paul. Even if it isn't within your experience you might be interested to know that there are plenty of data warehouses that get on perfectly well without instructions from RK. The OP specifically wants to know where the dimensional approach fails. I expect he has the good sense to be sceptical about claims that it is "best" for every problem every time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply