9
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 source tables to redshift fully, do not do any significant
transformations until the data has been loaded. For this you can
either load to s3, then use redshift copy ...
7
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 one of facts is semi-additive. Type of fact (additive or not) does not define table's grain.
From what you describing, I see your grain as "monthly claim ...
7
If you are implementing party-role-relationship model or part of it, having party_type as a separate entity is very important. You may have many more tables with foreign key to party_type (for instance, to limit applicability of certain roles to particular party type[s], or to enforce relationships between different types of parties, etc). Using check ...
7
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 MDX solution
I would suggest you try the IgnoreUnRelatedDimensions first, as the measures would be aggregated better, and NonEmptyCrossJoins would be able to ...
7
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 also have customer groups or item types and your schema looks more like
fact table -> customer table -> customer group table
fact table -> item table -> item ...
6
Typically dimensional modelling starts by identifying facts and the dimensions about those facts. Most of those things you mention are dimension attributes.
Staff/employees alone and their place in an organization is probably going to be modeled with a factless fact table.
You may well have a supervisor/employee relationship, and this would be an instance ...
6
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, they will be sorted according to character sorting (1, 10, 11) and not numeric sorting (1,2,3).
You didn't specify whether this was multi-dimensional or ...
6
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
SELECT 2 AS id, 'India' AS country, 'Bangalore' AS city
Sales:
SELECT 1 AS city, 5 AS salesamount
UNION
SELECT 2 AS city, 5 AS salesamount
...
6
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 [Measures] dimension but that is mostly a syntax convention.
As for a common name, I would say they both are partly definitions of a cube design. The measures ...
5
I would use an enum type for that.
5
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 to this is to have two fact tables, one for the additive fact (payments in your case) and one for the non-additive fact. The non-additive fact does not actually ...
4
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 involve a predefined series of steps. The timestamped.
You'll have a date key in the table that represents the date the opportunity hit each stage. If you ...
4
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 navigates back a number of steps in that level.
Also, I'm using the assumption here that you want to use the TAIL() function to get the last month in your dimension ...
4
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 types of complaints – e.g. Billing, Technical, ... – each of them can be categorized and have different targets. By applying slowly changing dimensions type 2 on ...
4
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 Kimball when looking for datawarehouse design tips.
This is what they say in Snowflakes, Outriggers, and Bridges:
We generally encourage you to handle many-...
4
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, etc. It often does not contain any information about what was ordered.
The order line generally contains information about what was ordered, this is done ...
3
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 which are bridge tables by people who fail to use the correct terminology.
Some people use helper table as a synonym for bridge tables, but helper tables also ...
3
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 probably don't want NULLs in your OLAP dimensions, and Kimball seems to agree.
Nulls should also be avoided when we can’t provide a value for a
dimension ...
3
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 table in the DSV and immediately move to hierarchy design, cube relationships etc.
I'd recommend moving to the DDS with the caveat that you phase out the NDS. ...
3
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 legitimately null. I recommend populating *NotApplicable* or something like that for each dimension which is in the OrderDetailUnits.
While not exactly ...
3
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 relationships between country -> city -> user which should improve performance when you create hierarchies using those attributes.
It will usually also ...
3
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 only aggregate over dimensions I think you should still be able to query that. You couldn't join on the dimension keys in the fact table but you could still ...
3
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. Whether you truly need a dimension at the grain of an individual opportunity is a question I'd think about.
You don't mention measures in your question at all, ...
3
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 Customer dimension.
answered Apr 16 at 23:11
David Browne - Microsoft
32.7k3 gold badges33 silver badges73 bronze badges
2
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 imagine if a rule to qualify someone as lead would change. Also, what if you have to access DWH data with different tools/systems - you'd need to replicate all the ...
2
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 dimensions often when you have a date in another dimension).
I would simply add location information to both the shop and customer dimensions. If you want to do ...
2
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:
Transactions
(
...
CreatedDateTimeSK INT NOT NULL, -- Four bytes per date...
AuthorizedDateTimeSK INT NOT NULL,
BatchSubmittedDateTimeSK INT NOT NULL,...
2
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/
2
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 techniques we should definitely apply when working with MPP.
To answer the question
The question I have is about what is the best practice for loading a star ...
2
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);
Only top voted, non community-wiki answers of a minimum length are eligible
