January 23, 2012 at 4:40 am
If I understand correctly you are asking why bother with a relational star schema when you can use cubes? (or vice versa)
You've got a whole load of inconsistent dirty unaligned data out of your source systems. Now you need to present it as dimensions and facts. Lets consider OLAP-only (straight to cube) or ROLAP (A star schema in a relational database).
If you go straight to OLAP (cubes) then you have to do all your cleaning, aligning, mapping etc. in your cube load process.
If you go via ROLAP first, you have the luxury of having a few passes at the data using ETL (and other) tools to clean and align it. Then the cube build process can focus on the cube build aspects, not the data cleansing ones.
Out of the box, OLAP will usually perform better than ROLAP. Yes you can have superfast ROLAP if you tune the database but that takes more technical know than the your average wizard-jockey has.
So that is a system that has many Source Systems > DW > Cube
If you're just doing reporting on one (or many identical) system(s) then there is an argument that you can just build a cube directly out of the production system overnight - that's how it was done in the old days.
February 14, 2012 at 9:33 am
nick.mcdermaid (1/23/2012)
... relational star schema
star schemas are not relational, they are dimensional.
_____________________________________
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.February 14, 2012 at 9:27 pm
PaulB-TheOneAndOnly (2/14/2012)
star schemas are not relational, they are dimensional.
Maybe you don't really mean that Paul? Star schema is a design pattern quite commonly seen in relational database design. "Dimensional" models are another set of design patterns commonly implemented as a relational database. When implemented in a relational (or SQL) database I don't think there's much point denying that they are "relational". Denormalized maybe, but that doesn't make them non-relational.
February 15, 2012 at 2:15 am
Of course I agree. Or perhaps there is some disagreeemnt on sematics or some hair splitting going on.
February 16, 2012 at 10:40 am
sqlvogel (2/14/2012)
PaulB-TheOneAndOnly (2/14/2012)
star schemas are not relational, they are dimensional.Maybe you don't really mean that Paul? Star schema is a design pattern quite commonly seen in relational database design. "Dimensional" models are another set of design patterns commonly implemented as a relational database. When implemented in a relational (or SQL) database I don't think there's much point denying that they are "relational". Denormalized maybe, but that doesn't make them non-relational.
Man... you know what I mean, of course SQL Server is a RDBMS. I think we have semantics issue here; let me try again.
Thread was about design and after over 20 years on it never heard before in the same phrase "relational star design", we use "star (schema) design", which is a synomym for "dimensional design" for Kimballistic Data Warehouse databases and we use "relational design" for OLTP databases.
De-normalized is not synonym for star-schema - you can have a de-normalized, reporting oriented section of your database without being a star schema one.
Last but not least, the expression "design patterns" usually applies to OO design which has nothing to do with data modeling and further physical implementation.
Hope that clarifies.
_____________________________________
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.February 16, 2012 at 3:23 pm
PaulB-TheOneAndOnly (2/16/2012)
Thread was about design and after over 20 years on it never heard before in the same phrase "relational star design"
What Nick said was "relational star schema" - a phrase which I'm certain is a very common term in database management. For what it's worth, books.google.com[/url] returns over 100 references for that phrase, including works by Terry Halpin and Ralph Kimball.
In the context of Nick's post the phrase "relational star schema" is entirely appropriate. A dimensional model may or may not be in the form of a relational database schema. Nick was making a point about whether a relational star schema was really needed in SQL when the same data can be accessed in a cube. A very reasonable point to make.
we use "star (schema) design", which is a synomym for "dimensional design" for Kimballistic Data Warehouse databases and we use "relational design" for OLTP databases.
That's seriously inaccurate. The star schema predates the "Kimballistic Data Warehouse [sic]" and is orthogonal to and more fundamental than "dimensional design". Not all star schemas are "dimensional" and not all dimensional models are star schemas. In a similar vein, most data warehouses are probably relational ones but not all of them are "dimensional".
It makes no sense to me to say that a "dimensional" star schema is not relational. If it exists in the form of relational tables with keys and that are accessed by relational queries (or at least SQL queries) then what's the point of saying such a design is not relational?
of course SQL Server is a RDBMS
It is not, but fortunately that's irrelevant here.
February 16, 2012 at 3:27 pm
PaulB-TheOneAndOnly (2/16/2012)Last but not least, the expression "design patterns" usually applies to OO design which has nothing to do with data modeling and further physical implementation.
Design patterns apply wherever design applies. If you haven't read it then check out Vadim Tropashko's fine little book "SQL Design Patterns". I have plenty of other recommendations if you are interested.
February 17, 2012 at 10:51 am
sqlvogel (2/16/2012)
we use "star (schema) design", which is a synomym for "dimensional design" for Kimballistic Data Warehouse databases and we use "relational design" for OLTP databases.
That's seriously inaccurate. The star schema predates the "Kimballistic Data Warehouse [sic]"...
well... if you go back to the network databases like the good-n-old Total DBMS - which I worked with by the way - of course you find a star-schema - but that is not the point and you should know 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.Viewing 8 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply