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 ...
- 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 ...
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 ...
- 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 ...
- 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, ...
- 753
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,...
- 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
...
- 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 [...
- 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 ...
- 6,055
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 753
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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. ...
- 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 ...
- 40.8k
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 ...
- 1,890
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 ...
- 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 ...
- 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 ...
- 316
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:
...
- 5,812
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/
- 147
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 ...
- 66
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);
- 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 ...
Only top scored, non community-wiki answers of a minimum length are eligible