Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [slowly-changing-dimension]

2
votes
0answers
22 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 ...
0
votes
2answers
116 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 ...
0
votes
3answers
166 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 ...
2
votes
1answer
40 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 ...
0
votes
1answer
101 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 ...
3
votes
1answer
54 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 ...
0
votes
0answers
168 views

How to modify table in order to store historical information using SCD2 in ORACLE SYNTAX?

I have to modify my table PRODUCT_DIM so that I can be able to store historical information according to SCD2 A Type 2 SCD retains the full history of values. When the value of a chosen attribute ...
0
votes
1answer
2k 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 ...
0
votes
1answer
383 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 ...
1
vote
1answer
117 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 ...
1
vote
1answer
88 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 ...
3
votes
1answer
1k 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 ...
4
votes
1answer
852 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 ...
4
votes
2answers
176 views

Historicizing of database models

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 ...
3
votes
0answers
734 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 ...
3
votes
0answers
147 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, ...
4
votes
4answers
486 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 ...
2
votes
0answers
450 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: ...
4
votes
2answers
96 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, ...
6
votes
1answer
229 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 ...
4
votes
1answer
275 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 ... ...
2
votes
0answers
221 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 ...
4
votes
2answers
1k 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 ...
16
votes
1answer
997 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 ...
4
votes
1answer
718 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 ...
3
votes
1answer
6k 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 ...
0
votes
1answer
1k 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: ...
0
votes
2answers
780 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 ...
3
votes
1answer
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 ...
6
votes
1answer
455 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:...