10 votes

Dimensional Modeling and ETL in Redshift

You are definately on the right track with Kimball rather than inmon for Redshift. There are a number of patterns for this, I have used them all in different use cases "ELT" pattern - Load the ...
Jon Scott's user avatar
  • 244
8 votes
Accepted

what is the difference between 'order' and 'order line'?

Generally, an order contains information about the order. Who placed the order, what time they placed it, the shipping address associated, the billing address, payment method, when it was fulfilled, ...
Mark Iannucci'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
6 votes

What is the industry term for superset of "metrics" and "dimensions"?

Dimensions and Measures are the two main components of cubes. The dimensions are basically ways to look at the data while the measures are the data. When writing MDX the measures are a member of the [...
Tom V's user avatar
  • 15.7k
6 votes
Accepted

SSAS Hierachies duplicates when browsing

That behaviour is dependent on your KeyColumns setting. Given a cube with these 2 named queries in the datasource view Cities: SELECT 1 AS id, 'India' AS country, 'Calcutta' AS city UNION ...
Tom V's user avatar
  • 15.7k
4 votes
Accepted

Boolean as measure in fact table or dimension/attribute value

Here is a flexible way to store it that should support changes in requirements in the future. Store the target in a dimension like dim_complaint_type. That way if in the future you have different ...
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
4 votes
Accepted

What is the difference between bridge table and helper table in data warehouse's context?

Bridge tables are the official name according to Dimensional Modeling as defined by the Kimball methodology. Helper tables as far as I know have been used in many (often incorrect) contexts, one of ...
Tom V's user avatar
  • 15.7k
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

Dimensional Modeling of a Loan Approval Process and Fact Grain(s)

Dimensions that are 1-1 with facts are not unheard of. The problem with such dimensions is their size, which won't get any smaller just because you introduce a fact table that's even more detailed. ...
Nickolay's user avatar
  • 172
3 votes

If a DW fact table is not uniquely identified by all dimensions, what problems?

I don't think you will run into a lot of problems when querying your data warehouse except for the one you already stated (cross fact queries) although if you are aware of how your design is done and ...
Tom V's user avatar
  • 15.7k
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
2 votes

Dimensional Modeling and ETL in Redshift

For ETL there's AWS Glue. It is a managed, serverless ETL service that loads to Redshift (among other things). https://aws.amazon.com/glue/
Joshua Guttman's user avatar
2 votes

What is the difference between bridge table and helper table in data warehouse's context?

From the definitions I've seen, helper tables and bridge tables are the same thing. "A bridge table sits between a fact table and a dimension table and is used to resolve many-to-many relationships ...
Nate's user avatar
  • 121
2 votes

Dimensional Modeling and ETL in Redshift

I am currently dealing with a similar task. It is to build ETL process and design dimensional model. I have researched a lot for the best way to deal with it and found an amazing helpful source of ...
Joao Caxias's user avatar
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
Accepted

Implementing a degenerate dimension with an alphanumeric varchar(30) at the transaction level?

Kimball's guidance touching on performance and space use needs to be taken with a grain of salt when using modern, columnar storage. And for SQL Server data warehouses, fact tables should typically be ...
David Browne - Microsoft's user avatar
2 votes
Accepted

What is the meaning of "dimension key"?

A dimension key is the primary key of a dimension table. In the example below, the "Time_key" column is the dimension/primary key of the Time dimension. This column will be used to join the dimension ...
jmng's user avatar
  • 136
2 votes

Database model / design best practice

I would say that if the company locations and non-company locations store a fair amount of different data, then to avoid a bunch of NULLS I would have two tables, and let the application insert data ...
John Herbert's user avatar
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
2 votes
Accepted

How to handle NULL Dates in an "Accumulating fact table"

You say "null should be avoided in a foreign key column," but are these dates really FKs? Most likely, these dates are just attributes of an order, and if the dates are unknown (and the ...
AMtwo's user avatar
  • 16.1k
2 votes
Accepted

What database & design would you use for legos

A standard RDBMS could easily handle this problem. You have a defined schema, a simple one at that, which deals with common industry problems such as Inventory and Production. Having worked at an ...
J.D.'s user avatar
  • 37.5k
2 votes
Accepted

Data marts - to create multiple database or consolidate in one?

Please keep in mind, this example from Google is in regards to a data warehouse / OLAP design implementation, which is ideal for reporting (usually of the aggregative nature), especially to make ad-...
J.D.'s user avatar
  • 37.5k
1 vote
Accepted

Data Warehouse Modeling: Sales Order Charges

Can someone guide me as to which of these options is the most apt design? Funilly enough, that question does have an answer: No. No one can. Dimensional modeling is always about the questions you ...
David Browne - Microsoft's user avatar
1 vote
Accepted

How to normalize CSV invoice data?

You wrote If I were to create this from scratch I would have a "class" table so that the table of items has a many to many relationship This implies that one item can be in many classes. However, ...
youcantryreachingme's user avatar
1 vote
Accepted

Can multiple fact tables have the same structure?

Yes and depending on what you are doing that might be a very good idea. I will use a request table as an example. Request comes in and goes to a submitted request table. After it gets processed it ...
Joe W's user avatar
  • 1,039
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

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

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

Should a Fact table contain a column with a single unique value?

If the intention here is to provide a fixed alternate table name (rather than record a source, which may vary), one option would be to implement a non persisted virtual column (calculated column in ...
Paul Holmes's user avatar

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