Questions tagged [slowly-changing-dimension]
The slowly-changing-dimension tag has no usage guidance.
12
questions with no upvoted or accepted answers
3
votes
0
answers
202
views
Slowly changing dimensions - data integrity
When using SCD is it possible to still enforce FK contraints. Consider the following table structure:
customer (data_version_id PK, customer_id PK, name, address1,...etc)
order(order_id PK, ...
2
votes
0
answers
105
views
Data Warehouse Kimball Interest Rate and Interest Payment Amount
I am modelling a bank account.
Should interest rate be in the DimCheckingAccountId (which shows ProductDimensions, or should it be in fact table, or both). Same with another LoanAccount, should ...
user162241
2
votes
0
answers
2k
views
PostgreSQL: Deadlock on insert trigger that also updates same table.
I am trying to implement a type 2 slowly changing dimension in a PG data warehouse using a trigger function.
The basic idea is that when a new row is inserted, we should check whether NEW is a ...
- 271
2
votes
0
answers
828
views
What can cause an "SCD is not compatible with this version of the DataFlow" error?
I created an SSIS package which includes several SCD data flows. This works fine in Visual Studio SSDT, but when scheduled (using SQL Agent), it fails, with this error repeated for each SCD:
Code: ...
- 5,967
2
votes
0
answers
320
views
Keeping version history / Slowly changing dimension
We're creating a new version of a 20 years old database, in which we used to keep version history by creating new rows in each table (SCD Type 2).
SCD Type 2 is not that hard when you need to keep ...
- 123
1
vote
0
answers
295
views
Create SCD type 2 table from change log table using SQL
I have a change log table, which captures the transition of a state with its time, for example:
user_id,user_status_from,user_status_to, created_date
7cc4d A2 A1 2019-11-03 ...
- 111
1
vote
2
answers
1k
views
Data Warehouse: Auto Generate Slowly Changing Dimension Code For Any Table with Dynamic Sql
Can someone code review to take OLTP/Stage data and transform it into Data Warehouse Kimball Slowly Changing Dimension Type 2? This code should be dynamic, and should be able to utilize any table as ...
user162241
0
votes
0
answers
32
views
SQL Query group by to find EffectiveFrom and EffectiveTo Dates for repeating key value pair
I have a table with a snapshot of repeated data for a key and value pair with record_timestamps. The dataset can also contain late-arriving historical record_timestamps as well.
I want to generate an ...
0
votes
0
answers
70
views
How Do I create history table that allows me to retrieve the times when a row active by year & month?
I want to be able to retrieve the in-between months when a client was active but from a historical table.
Table: DimensionEpisode
episode_id
client_id
episode_status
discharge_date
admission_date
...
- 21
0
votes
1
answer
32
views
Do I have the right Slowly Changing Dimensions type for my version controlled tennis match database?
I'm trying to version control my database using the principles of Slowly Changing Dimensions. I've opted to use Type 2 with a generation start and end column instead of datetimes.
In a simplified ...
- 83
0
votes
0
answers
340
views
Configure Type 2 SCD on existing tables in SQL Server
I've got two tables, FACILITIES and CONTRACTS. It's a 1:M facility->contract. The tables have unique keys generated by the upstream system and are truncate-reload every night. Neither table has any ...
- 1
0
votes
1
answer
873
views
Periodic snapshot fact table with monthly grain - Question on dimensions
I am fairly new to data-warehousing but have read my fair share of books and online tutorials to gain an introductory understanding of the basic components that build up a data warehouse.
My end goal ...
- 141