Skip to main content
Share Your Experience: Take the 2024 Developer Survey
16 votes
Accepted

Difference between star schema and data cube?

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 ...
Rathish Kumar B's user avatar
12 votes
Accepted

When are dimension tables still needed using clustered columnstore indexing?

I don't think that your question is applicable to any RDBMS that supports columnar storage. I'm writing my answer from the point of view of SQL Server and most of the reasons depend on implementation ...
Joe Obbish's user avatar
  • 32.2k
10 votes
Accepted

Benefit of having time dimension in a star schema?

Let me answer this question with a scenario starting with a simple Transaction table. When our business started, management wanted to know the 'name' of the month, so I've included that information ...
Scott Hodgin - Retired's user avatar
9 votes

Difference between star schema and data cube?

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 ...
Galal Ouda's user avatar
7 votes

Design star schema for many-many relationship

Many-to-many tables are typically handled with a "bridge table." This is really just another name for a many-to-many table like you'd use in a transactional database: one column for an FK ...
Jon of All Trades's user avatar
7 votes
Accepted

What are the difference between a "dimension" table in a star-schema and a "lookup" table in a relational database?

If you only have those tables there will be little difference between your datawarehouse star schema and your actual schema. You probably have a much more complex relational schema though, where you ...
Tom V's user avatar
  • 15.7k
5 votes

FK Issue with Fact Table

I see a tweet as an event happening, so I would model it as a new fact table FactTweet. More specifically as a factless fact. The dimensions for FactTweet would be DimDate, DimCar (if you can relate a ...
JeromeFr's user avatar
  • 289
4 votes

Dealing with different types in a fact table

I would steer clear of the 3 option. It's called a snowflake instead of a star schema. It's a rather advanced solution which can be used when necessary but has it's own drawbacks. As usual refer to ...
Tom V's user avatar
  • 15.7k
3 votes
Accepted

SQL All Procs have WITH RECOMPLILE so how to maintain a bloated plan cache

The plans generated for queries with forced recompile are not cached. See RECOMPILE Hints and Execution Plan Caching for details.
David Browne - Microsoft's user avatar
3 votes
Accepted

Summarizing over Outrigger Dimensions Directly (e.g. Total Customers by Demographic by Signup Date)

Conceptually, CustomerSignup is a fact. But if every Customer has exactly one Signup, you only care about the date, and there no measures associated with it, it might as well just be stored on the ...
David Browne - Microsoft's user avatar
3 votes

Cons of using a star schema in a columnar database

You are mixing up three orthogonal concepts: data model (star schema), workload characteristics (OLTP vs. OLAP), and physical data organisation (columnar). Your data model has no bearing on whether ...
mustaccio's user avatar
  • 26.1k
3 votes
Accepted

Star Schema from Relational Database

Ok, based on your very limited documentation, I would do the following: Fact Tables - Your fact table is your measurement table. It is the thing that happened. It is the meeting together of dimension ...
Anthony Genovese's user avatar
3 votes
Accepted

Should I use separate address dimensions?

You should add the address to the user/store dimension as the address is a property of the dimension member, not the fact record. Adding the address to the dimension allows you to define attribute ...
Tom V's user avatar
  • 15.7k
3 votes
Accepted

Building slowly changing dimension on a Fact/Dimension Star Schema

Usually, dimension tables contain a single valid time (start and end date) for all fields and SCD2 would apply to the complete record. It is good practice to use an non-null end value ahead in time to ...
Grimaldi's user avatar
  • 420
2 votes

Data into Star Schema's fact or dimension table?

This is a very interesting, but perhaps also a bit too general as a question. My general opinion is that if the cardinality of a dimension starts to approach that of the fact table, then there's no ...
nsousa's user avatar
  • 176
2 votes
Accepted

Star Schema - One dimension or split

There is something to say for both approaches. I usually prefer to have this situation in a single dimension so you can define attribute relationships that allow the engine to calculate bitmap indexes....
Tom V's user avatar
  • 15.7k
2 votes

Star schema dimension table design

When dimensions have references to other dimensions, we call that a Snow Flake instead of a Star schema. Depending on how you query your model later on, one approach might be more efficient than the ...
JeromeFr's user avatar
  • 289
2 votes

Is there a recommended order to create a DV model and star schema?

Seeing as you are asking about models, I assume you are in the requirements and design phase. If you know what your target dimensional model looks like then you can start there, assuming this is the ...
Russell Searle's user avatar
2 votes

designing a star schema

A very basic star-schema for your situation might look like this: The T-SQL statements to create the dimension tables for this schema look like: CREATE TABLE dbo.problems ( problem_id int NOT ...
Hannah Vernon's user avatar
  • 70.1k
2 votes

Connecting dimension tables in a multi-fact star schema database warehouse?

Having multiple fact tables in a model is pretty common and is sometimes referred as a Fact Constellation. The shared dimensions are referred as Conformed Dimensions. As @bbaird said in the comment ...
JeromeFr's user avatar
  • 289
1 vote
Accepted

Efficient dimension and fact joining

Here's a sample to play with: --1. CREATE TABLE dbo.Dim ( Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED, CustomerName VARCHAR(2000) ) --index CREATE UNIQUE NONCLUSTERED INDEX uniqueindex1 ON Dim(...
David Browne - Microsoft's user avatar
1 vote
Accepted

Is Star/Snowflake database design good for applications? What is the alternative?

In Option 2 the Status_Code appears as non-key in two tables: Study, Contract. But which of those entities does it belong to? And what are the relative cardinalities of those entities? Looking at ...
AntC's user avatar
  • 218
1 vote
Accepted

Design star schema for relatonal database

You could put a many-to-many relationship within a data warehouse, but many people consider it bad practice to do so - even so far as some data warehousing tools do not permit it to be created at all. ...
Mr.Brownstone's user avatar
1 vote

Cons of using a star schema in a columnar database

Mustaccio's answer is good. In addition, every star schema I have ever seen was implemented on top of a relational DBMS, often Oracle. Also, the data in it was downstream from some kind or ...
Walter Mitty's user avatar
  • 4,336
1 vote

Is there a recommended order to create a DV model and star schema?

Data Vault is an architecture and methodology to store data historically, sometimes holding hundreds of entities which can, but don't need to be interconnected. Star Schema is an architecture to ...
tobi6's user avatar
  • 111
1 vote

Should I use separate address dimensions?

This would be an opinion, but I agree with this answer which is along the lines of the question you are asking. I'm copying the answer from the link here in case the link goes cold. It sounds like ...
Scott Hodgin - Retired's user avatar
1 vote

Dealing with different types in a fact table

@TomV Already answered this well, essentially if you want to split each case into separate dimensions you create a dummy record with a surrogate key. Can't say if it's right for your business case but ...
Max xaM's user avatar
  • 141
1 vote

Cleanup / Prune Unreferenced Data in Dimension Tables

How about partitioning the dimension tables by a new "last_referenced_date" column (monthly)? The column would be set to current date on record INSERT. First run of cleanup process would have to ...
Shaun's user avatar
  • 31
1 vote

Star Schema from Relational Database

CREATE TABLE atudent ( student_id_ INT NOT NULL, address INT NOT NULL, Gender INT NOT NULL, Birthday INT NOT NULL, Program INT NOT NULL, Class INT NOT NULL, Name INT NOT NULL, Phone ...
Mohammed Rjoub's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible