9 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 ...
user avatar
  • 219
7 votes

Is my understanding of Fact table granularity correct?

You are correct: "different grains must not be mixed in the same fact table". But reserve balance at the end of the month and sum of payments at the end of the month are at the same grain. It just ...
user avatar
7 votes
Accepted

Cube with 2 measure groups (with different relationships to dimensions) returns too many dimension members in reports

As you correctly noted this is what happens when you try to display measures across a dimension to which they don't have a relation. You basically have 2 options Use IgnoreUnrelatedDimensions Use an ...
user avatar
  • 15.4k
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 ...
user avatar
  • 15.4k
7 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, ...
user avatar
6 votes
Accepted

Ordering of a dimension in sql server datawarehouse

Analysis Services will set the data type of the dimension attribute to be the same as the source column. It is sorted exactly as you asked for it. Your days are declared as a character type. Therefore,...
user avatar
  • 15.6k
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 ...
user avatar
  • 15.4k
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 [...
user avatar
  • 15.4k
5 votes
Accepted

Is my understanding of Fact table granularity correct?

Your intuition of code smell is well honed. What you are dealing with on reserves is what Kimball calls a "semi-additive fact". It does not roll up nicely to quarter or year. The typical solution ...
user avatar
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 ...
user avatar
  • 15.4k
4 votes
Accepted

Modeling Conversion Events in a Data Warehouse

This is commonly represented with an accumulating snapshot fact table or timestamped accumulating snapshot fact table. Accumulating snapshot tables model events in progress for business processes that ...
user avatar
  • 824
4 votes

How can I get the measures of the last month with MDX?

Without sample data or structure it would be hard to provide the exact code, but here goes. You need to look into the PARALLELPERIOD function. That function takes a Level from a hierarchy and ...
user avatar
  • 15.4k
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 ...
user avatar
  • 279
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 ...
user avatar
  • 15.4k
3 votes

What if the lowest granularity level can legitimately be null?

I agree. Your business users would not like "AllOrders" and "SizedOrders" fact tables. I suggest creating a synthetic row in OrderDetailUnits for each OrderDetail where you have entries which are ...
user avatar
3 votes
Accepted

Designing a dimensional DB off a normalized source that already implements SCD's

As you are seeing, one of the benefits of Kimball Dimensional Modeling is that the data warehouse design is essentially your SSAS design. While there are always exceptions, you can typically select a ...
user avatar
  • 2,369
3 votes
Accepted

How do I make a nullable "parent" hierarchy level?

You actually have 2 questions in one question. If you create a new question for the attributes it would be neater and I'll cut/paste half of this as an answer there :) Nullable Parent Level You ...
user avatar
  • 15.4k
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 ...
user avatar
  • 15.4k
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 ...
user avatar
  • 15.4k
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. ...
user avatar
  • 172
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 ...
user avatar
2 votes

Database design for tracking referral dollars and referred dollars

It's a Multi-Level Marketing system! Jeff Moden has written a a pair of articles here and here on efficient implementation of Hierarchical Reporting against a SQL Server database. There are a number ...
user avatar
2 votes
Accepted

Business Logic in Data Warehouse

My answer is a big YES - you should store business logic in data warehouse. That is one of the ideas of data warehouses in the first place. If you have tens of reports showing or filtering leads ...
user avatar
  • 264
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 ...
user avatar
  • 121
2 votes
Accepted

Conformed Fields or Snowflake? - Shop and Customer Location

This is more of an outrigger dimension. You area allowed to do this with the Kimball methodology (star schema, not snowflake) but it does add complexity to your data model (happens with date ...
user avatar
2 votes

Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones

I've seen the idea of a warehouse using a combined DateTime dimension rejected, but I haven't seen a really clear reason why. Simplifying slightly, here's the fact table I'm building right now: ...
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/
user avatar
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 ...
user avatar
2 votes

Cube with 2 measure groups (with different relationships to dimensions) returns too many dimension members in reports

Add this calculated measure to the cube: Create Member CurrentCube.[Measures].[Max Current CP With Units] as IIf(Not IsEmpty([Measures].[Units]),[Measures].[Max Current CP],Null);
user avatar
  • 1,375
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 ...
user avatar

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