Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [slowly-changing-dimension]

43 questions
18
votes
1 answer

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 large relational data warehouse? For example, assume…
Justin Grant
  • 751
  • 4
  • 14
7
votes
6 answers

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. As addresses do not change often, this column…
Dejan
  • 175
  • 6
6
votes
1 answer

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: Monthly Fin facts about the Customer Product…
ray
  • 61
  • 1
6
votes
1 answer

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 handles type-2 updates, and so the [NDS] generates…
Mathieu Guindon
  • 914
  • 2
  • 7
  • 16
5
votes
1 answer

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 about an object and dimensions should contain…
Michael Plautz
  • 165
  • 2
  • 7
5
votes
4 answers

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 rough example of what I'm talking about; CREATE TABLE…
user2045064
  • 63
  • 5
4
votes
1 answer

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 daily load runs and I see the changes flowing in,…
IronicMuffin
  • 663
  • 5
  • 15
4
votes
1 answer

Data History Table vs Using a Current Record Flag

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 all versions in the same table with a flag on the…
AnotherDeveloper
  • 143
  • 1
  • 5
4
votes
2 answers

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, these sales people are each connected to one sales…
user45867
  • 1,739
  • 5
  • 24
  • 41
4
votes
1 answer

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 ... [Material v] Code AltCode Name ... [Id …
Mathieu Guindon
  • 914
  • 2
  • 7
  • 16
4
votes
2 answers

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 hospital data warehouse and gender could be a…
LearnByReading
  • 711
  • 10
  • 26
3
votes
1 answer

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 to search within. We would like to record the…
Martin Brown
  • 728
  • 1
  • 5
  • 15
3
votes
1 answer

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 my hierarchy seems to error out and it may be due…
User Smith
  • 307
  • 1
  • 7
  • 14
3
votes
1 answer

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 explaining SCD types:…
w_n
  • 133
  • 1
  • 4
3
votes
2 answers

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 insurance you need different data in 2015 than…
herbertp.
  • 89
  • 1
  • 3
1
2 3