Skip to main content
The 2024 Developer Survey results are live! See the results

Questions tagged [slowly-changing-dimension]

Filter by
Sorted by
Tagged with
0 votes
1 answer
36 views

Point-in-Time-Recovery of Postgres at an arbitrary time for audit purposes [closed]

TL;DR Would you recommend using CDC or pg_wal-based recovery to reproduce the state of the database in an arbitrary time, or stick with joining SCD2 tables (with start end end date for every record) ...
Mr Arkadin's user avatar
0 votes
0 answers
48 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 ...
user137279's user avatar
0 votes
0 answers
79 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 ...
Sam Johnson's user avatar
1 vote
1 answer
727 views

Optimal join for joining facts with scd-type-2 dimension for aggregation/reporting

I have a fact table and an scd-type-2 dimension table. I want to produce sales report by region and year. I have working solution with a query that joins them for reporting purposes. When I run the ...
Kashyap's user avatar
  • 149
1 vote
1 answer
1k views

How to deal with fact table data that needs to be version controlled?

I have the following simplified sport_match 'fact' table: match_id tournament_id player_id_p1 player_id_p2 p1_final_score p2_final_score 1 1 1 2 1 0 2 1 1 2 3 1 3 2 3 2 2 3 4 2 3 2 4 0 The table ...
Jossy's user avatar
  • 83
0 votes
1 answer
34 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 ...
Jossy's user avatar
  • 83
0 votes
1 answer
188 views

Converting EAVT table into SCD type 2

After a lot of research and head picking, I'm still unable to find a good/clean solution to convert an entity-attribute-value-timestamp table to an scd type 2 dimension. Here's the issue: I have a CRM ...
Rami Ouanes's user avatar
0 votes
1 answer
236 views

Fact table linking to natual key rather than row key in a slowly changing dimension table?

I’m designing a data warehouse and have a Slowly Changing Dimension Type 2 table something like this: UserKey UserID Group EffectiveDate ExpiryDate IsCurrent 1 1001 GR1 2021-01-01 9999-12-31 Y 2 ...
stovroz's user avatar
  • 631
0 votes
1 answer
787 views

Implementing revisioning logic with SCD2/4

I posted this question on StackOverflow, but it didn't get much luck so I'm hoping I could have better luck here. I'm working on a project that is a sort of bill of materials manager that needs to ...
user1960118's user avatar
0 votes
0 answers
372 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 ...
seve's user avatar
  • 1
1 vote
1 answer
44 views

Type 2 Dimension for a fact measure

I have a need to store products along with their prices and quantities (and many other things) and track the history. Now, given both price and quantity are stored as integers, it is my understanding ...
sksamuel's user avatar
  • 113
7 votes
6 answers
2k views

Are duplicated NVARCHAR values stored as copies in SQL Server?

I'm designing a table that will contain a lot of rows. So need to be careful not to store to much information. One of the columns is a NVARCHAR(MAX) column and it contains the address of our customers....
Dejan's user avatar
  • 175
1 vote
3 answers
671 views

The right way to model multiple FACTs schema

Background I'm in a process of designing a database (using a STAR schema). There are three tables to model: products, tests, states. The database will be used to store results of tests conducted on ...
JustGiveMeData's user avatar
1 vote
0 answers
301 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 ...
user3065757's user avatar
2 votes
0 answers
109 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 ...
user avatar
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 ...
user avatar
0 votes
3 answers
2k views

Create Slowly Changing Dimension table from Repeated Data

I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. Many ...
user avatar
2 votes
1 answer
1k views

History table implementation: "Tuple-versioning" vs Effective Date

I'm designing a database that will have history tables (SCD Type 4 I guess) for auditing purpose. The question is what are the advantages of having tuple-versioning (start and end dates) vs just ...
AC.'s user avatar
  • 123
-1 votes
1 answer
6k views

SQL Server Stored Procedure Slowly Changing Dimension

Can someone code review this slowly changing dimension for our Kimball Data warehouse? This is Type 2 dimension. I reviewed a lot of code on the internet, however I want to use non-TSQL specific ...
user avatar
3 votes
1 answer
211 views

How to store hierarchical dimension for timeseries data

I have a requirement to store data about a load of Searches. Each time someone searches we want to record it for analyzing later on. As part of the search the user can select one or more Categories ...
Martin Brown's user avatar
1 vote
1 answer
10k views

Join Fact table to SCD Type 2 ... how to write query? (SQL Server)

I can't seem to find this simple fact anywhere. I have a fact table like such in SQL server. fact_picked Emp_Name Date Apples_Picked John May 1 17 100 And a type 2 dimension table ...
user45867's user avatar
  • 1,683
0 votes
1 answer
894 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 ...
Alistair's user avatar
  • 141
1 vote
1 answer
394 views

SSIS: Slowly changing dimension with lists (one-to-many relationship)

Slowly changing dimension (specifically type 2) is a brilliant concept for being able to keep historical periodic (e.g. daily) data without the storage overhead of taking an entirely new snapshot of ...
Michael Plautz's user avatar
1 vote
1 answer
204 views

Data Warehouse how normalized for tracking metadata changes?

I need to build out a warehouse structure that is populated by periodic feeds consisting of a mix of scraped data, authority sources, and primary sources. In order to prioritize which data should be ...
user4446237's user avatar
3 votes
1 answer
4k views

Slowly Changing Dimensions Type 4

SCD type 4 keeps the foreign keys to the original dimension and the mini-dimension in the fact table. Why is the foreign key to the mini-dim not stored in the original dim? Link to an article ...
w_n's user avatar
  • 133
5 votes
1 answer
4k views

Building slowly changing dimension on a Fact/Dimension Star Schema

I have heard textbook definitions of how to design a star schema regarding what goes in the fact table and what goes in the dimension tables, such as: The fact table should contain core information ...
Michael Plautz's user avatar
3 votes
2 answers
410 views

Historicizing of database models [closed]

We want to build a SQL-Database for a system to manage regulations in different countries for different years. The problem is that the regulations change every year for every country. Like for social ...
herbertp.'s user avatar
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 ...
ChristopherTull's user avatar
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, ...
Neil Griffiths's user avatar
5 votes
4 answers
985 views

Consolidating a row of data, based on previous rows

I'm trying to build a history table out of an audit log (ultimately to build out a type 2 dimension table). Unfortunately, the audit log only records the specific fields being changes. Here's a ...
user2045064's user avatar
2 votes
0 answers
837 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: ...
Jon of All Trades's user avatar
4 votes
2 answers
605 views

How to create a view with added rows for missing dates?

I'm not sure how to accurately phrase this question. Essentially, say I have a bunch of salespeople on different sales teams. Like, the Blue Region, the Orange Region, the Red Region, whatever. Now, ...
user45867's user avatar
  • 1,683
6 votes
1 answer
390 views

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

I have built an SSIS ETL to integrate various data sources (one from MySQL, two from SQL Server) into a single SQL Server relational and normalized database, which I've called [NDS]. The SSIS ETL ...
Mathieu Guindon's user avatar
4 votes
1 answer
665 views

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

I'm setting up my first dimensional database with SSAS, and I have this [Materials] dimension that needs a hierarchy that goes something like this: [PriceCode v] --> nullable Price Code ... ...
Mathieu Guindon's user avatar
2 votes
0 answers
321 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 ...
Midas's user avatar
  • 123
4 votes
2 answers
2k views

When to choose an attribute vs. a new dimension?

I am fully aware of what is a fact, attribute and dimension. However, I'm quite confused to which traits I should choose for dimensions vs attributes of that dimension. For instance, I'm building a ...
LearnByReading's user avatar
17 votes
1 answer
1k views

Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

When using a system-versioned temporal table (new in SQL Server 2016), what are the query authoring and performance implications when this feature is used to handle Slowly Changing Dimensions in a ...
Justin Grant's user avatar
4 votes
1 answer
1k views

What known strategies, if any, can I use to validate a Slowly Changing Dimension?

I have a customer dimension in our data warehouse (SSAS 2014 Multidimensional). It is set up as a Type II SCD, with just Start and End Date fields to track changes. (null end date = current). As our ...
IronicMuffin's user avatar
4 votes
1 answer
8k views

Data History Table vs Using a Current Record Flag [closed]

There are two strategies when dealing with data records which contain multiple versions. One is to have current records in one table, and their past versions in a history table. The other is to have ...
AnotherDeveloper's user avatar
1 vote
2 answers
2k views

Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships

As an example, let's say I have a fact table with two dimensions and one measure FactMoney table ProjectKey int PersonKey int CashAmount money The two dimensions are defined like this: ...
Scott Herbert's user avatar
0 votes
2 answers
851 views

How to build dimensions from SCD tables

Having the foresight to begin collecting data from our production environment several years ago, I have a table with a following structure- Part Status Startdate Enddate I can write a relational ...
Jeff Sacksteder's user avatar
3 votes
1 answer
4k views

SCD Type 2 Dimension -> Is this the correct layout for a type 2 scd with this type of data present?

Here is an example picture of my layout. As you can see I have my SCD types present (status/startdate/enddate/businesskey). My key is a surrogate key that identifies each record. My problem is that ...
User Smith's user avatar
6 votes
1 answer
576 views

Dimension Help - Deciding Fact or Dimension

We have the following Dim and Facts: Customer Dim: SCD Type 2, Info about the customer ie first purchase date, name, address, etc Product Dim: SCD Type 2, about our products Customer Snapshot Fact:...
ray's user avatar
  • 61