20

I am involved in a new project where I have to create a data cube from the existing relational database system. I understood that the existing system is not properly designed, and I am not sure where to start.

My question are:

  • What is difference between Star Schema and data cube?
  • Where I have to start? From star schema or directly data cube?
  • Is data cube generated from star schema?
Improve this question
2
  • 3
    Here is my answer, in Stackoverflow, of about 7 years ago. Some of it is still relevant. If you want more opinions from me, I'll provide an answer here. stackoverflow.com/questions/2798595/…
    – Walter Mitty
    Commented May 5, 2017 at 14:25
  • Thanks. Here're some references I found related to this topic. star schema; OLAP cube
    – KFL
    Commented Nov 20, 2018 at 19:59

2 Answers 2

Reset to default
16

After spending some time, reading reference books, I came to the point, where I could define the difference between the star schema and data cubes. I could not comment on this definition but this answer satisfies me and help me to start the task. On the process, I hope I get better understanding (if exists) of these techniques. Here is my findings:

Difference between Star Schema and data cubes:

Star schema is a dimensional modeling technique. It contains, Dimensions and Facts (business measurements). Mostly used in Data warehouse technology.

Data cube is a multi-dimensional table. It means, combination of dimension and fact tables. Mostly used in OLAP analysis tools.

Data cubes are built on the star schema to improve the query performance - performing aggregate and summarizing measurements.

It will pre-calculate the values, instead of calculating on the fly, so it boosts performance.

Example: Total item, Sales Amount, etc

Where I have to start?

I realized I have to start from, star schema and build data cubes on it. Data cubes are not built on the DBMS system, but outside the DBMS system as aggregates and other operations.

I hope this answer will help the people who are new to this technologies. If I am missing something or understanding wrongly, please correct it. Thank you.

Improve this answer
4
  • 5
    Thanks for returning with your answer. A star schema usually refers to a bunch of relational database tables whose relationships form a star. This data lives inside a relational database. These tables are generally 'facts' or 'dimensions'. A 'Data cube' is a very generic term. This same information is stored inside a cube, not a relational database. Just as there are many vendors for relational databases, there are many for cubes as well. A cube is usually built from an external data source. If this data source is arranged in a star schema it usually much easier to develop and load the cube
    – Nick.Mc
    Commented May 8, 2017 at 3:53
  • but the cube has only 3 dimensions? what if i need more than 3 to produce a fact?
    – Dan D.
    Commented Jun 25, 2020 at 9:02
  • Probably you never need to build data cube on top of columnar DB (such as ClickHouse) as its performance rocks holistics.io/blog/the-rise-and-fall-of-the-olap-cube
    – eugene-bright
    Commented Aug 31, 2021 at 15:06
  • 2
    Now that you have 6 years under your belt, please discuss how knowing the difference between "star schema" and "data cube" has helped you in developing databases.
    – Rick James
    Commented Jul 26, 2023 at 18:13
9

Qutoting from Kimball Dimensional Modeling Techniques

Star Schemas and OLAP cubes

Star schemas are dimensional structures deployed in a relational database management system (RDBMS). They characteristically consist of fact tables linked to associated dimension tables via primary/foreign key relationships. An online analytical processing (OLAP) cube is a dimensional structure implemented in a multidimensional database; it can be equivalent in content to, or more often derived from, a relational star schema. An OLAP cube contains dimensional attributes and facts, but it is accessed through languages with more analytic capabilities than SQL, such as XMLA. OLAP cubes are included in this list of basic techniques because an OLAP cube is often the final step in the deployment of a dimensional DW/BI system, or may exist as an aggregate structure based on a more atomic relational star schema.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.