Difference between a Cube and a Star

Hello,

I have a very basic question. Can anyone please, explain the difference between a cube and a star schema?

I studied about Star schema. When I implement it in Oracle Warehouse Builder 11g, I have to create a cube. Am I doing right?

regards,

At a logical level, there is no difference between a cube and a star. It is a matter of physical database implementation. You can implement a star as a cube (multidimensional database) or in a relational database. Cubes are usually queried using MDX as opposed to SQL.

The base data warehouse is usually created in a relational database because of its ability to store large amounts of detailed data. Cubes provide higher performance but do not have the capacity a RDb has.

I am not aware of anything in OWB that forces you to create a cube.

Thanks,
It was really helpful to clarify the concepts.
regards,

@ngalemmo Too good definition. I like your comments.

Just to add that in OWB, you can choose your cube implementation in the second tab as :
* ROLAP (in the Oracle Relational Database with fact table, dimension, hierarchy, materialized view),
* of MOLAP (in the Oracle OLAP Database with a cube)

Cube is a logical term that is more used in the multidimensional database field.
It's a metadata construction.

Cheers
Nico

You guys really don't think there is a difference between a Star Schema and a Cube? That kind of sounds like a Cube is a replacement for a database. IMHO, I think cubes should be built from Star Schema databases.

Some people think that it's OK to build Cubes without having a Star Schema. While I know all of the vendors say you can do this, I always say, "should" it be done this way. Just because it can be done doesn't mean it should be done.

Totally agreed with Jeff. Cube is not interchangeable with relational database (RDBMs), or Star schema stored in RDBMS for that matter, but rather it is an object stored in MDB, instead of RDBMS, which allows end-users to configure, browse, and query against the facts, dimensions and aggregates hierarchically, interactively and instantaneously. Whereases a star is a modelling style that puts normalised fact table in the centre and surround it with a number of denormalised dimension tables by having dimension FKs in the fact table. Tables or views structured in a star format are stored in RDBMS, so do data objects in 3NF snowflake format.

A cube in MDB may source the data stored in RDBMS with facts and dimensions either in Star or Snowflake schema. Someone claims cube sourcing from snowflaked data performs faster but I don’t agree. However the point is that star schema is a lot easier to use, more efficient for data storage, and arguably much faster than snowflake, not just for the cube but for data analysis in general. Star schemas can also leverage Kimball’s SCD process more effectively, to track dimension changes.

Jeff & Hang...

I thought that is what I said. The differences are in physical implementation. The basic concepts of dimensional modeling are identical: measures, attributes and how you can combine mesures across stars/cubes. The though processes to achieve and use a dimensional design are the same.

Snowflakes, keys, bridges etc, are relational issues, and functionality to resolve multivalued dimensions, short of allocation, are not available in a MDDB. But the methods to identify and resolve such issues are common to both implementations.

Don't confuse implementation functionality and capabilities with applying a dimensional model. Such issues determine platform choice, not design methodology.

My only issue with it is when lines of business start calling things Cubes that aren't cubes. Senior execs are easily confused on this topic. Some people think that because we use Cognos then we have cognos use cubes. We don't have any cubes - just aggregate tables.

Instead of asking for a cube, LOBs should focus on explaining their needs.

I have heard many people call their OLTP system a data warehouse system, thanks to Inmon's early contributions blurring the line between the two.

It's true that a Star schema may be formed in RDBMS at physical level or at semantic level in MDB(MS), or even in some reporting tools. However when we are talking about Star (schema) as opposed to Snowflake (schema), we are really referring to dimensional modeling at physical level in RDBMS, nothing to do with cubes implemented in MDB, Cognos, BOJ or any reporting tools that have OLAP cubing capability. In another word, you could have a perfect BI/DW system with good Star schemas without having any cubes. Look, in all of Kimball's book, cube has only be mentioned as a tool that can improve the aggregation performance and reporting capability, the main focus is on dimensional modeling and ETL.

My point is there is nothing to compare between Star and Cube as they are totally different things. However there is a lot to compare between Star and Snowflake, and that should be our focus, as we have seen numerous relevant topics in the forum.

Jeff,

Don't take business terminology to mean the same thing as technical terminology. Vendor marketing has done plenty to muck up any real meaning to a lot of BI terms. When a business person asks for a cube, a data mart, or whatever, he is not (or at least should not) be specifying a technical implementation. He/she is asking for a capability. It is a matter of the architect and the technical staff to figure out the best approach to delivering it... and explain why.

Hang,

Kimball is explaining how to implement a data warehouse using dimensional design. It is quite clear to all of us that the effective way to do that is using a relational database. Multidimensional databases have significant limitations that make using it for an integrated enterprise solution impractical.

But, that does not mean, from an end user point of view, that the two are not the same thing. In fact, almost all BI tools out there have an interface that presents relational sourced data as a cube. One can do identical analysis and manipulation with the same data in either a RDB or a cube.

I think it's funny that a field that strives for clarity and tries to avoid confusion in data has so much confusion in terminology. We strive to make sure the definition of a field stays consistent in the database. We strive to ensure accuracy in the output and do what we can to prevent users from coming up with the wrong answer. Yet ask 4 people what a MART is and you'll get 4 different answers. Same with Cubes, not to mention the "Super Cubes" or the ever popular, "cubes on steroids". And this is from people who have spent a lot of time in the field. Add in the people who recently switched from transactions systems or, heaven forbid, mainframe people and it quickly becomes a volatile arena. And to make matters worse, Senior Execs in Sales and Marketing are asked to make decisions about it.

At least it keeps it interesting.

ngalemmo wrote:You can implement a star as a cube (multidimensional database) or in a relational database

By the same token, you may say there is no difference between a collection of flat files or spreadsheets and a cube.

I think the issue is not so much from people in the field, but from marketing people working for BI vendors.