Slowly changing dimension Puns

I am building a dashboard for a membership database, and I can't get the formulas to work correctly for reporting when a member has been gained or lost.

The underlying table, History, has columns for Report Date, User ID, and Active Local (a yes/no column of whether they are active as of the report date).

I want to count someone as a gained member if they are active on the report date AND either did not exist in the previous report date (i.e., they are new to our database), or on the previous report date they were inactive (e.g., they were a prospect who we've now converted to membership.)

I have a measure that gives me a correct result for whether the member was gained at the row level of report date-user ID, but does not aggregate correctly:

newGained in Last Month = 
VAR PreviousDate = [Previous Report Date]
VAR CurrentUserID = SELECTEDVALUE(History[User ID])
VAR CurrentStatus = SELECTEDVALUE(History[Active Local])
VAR PreviousStatus = CALCULATE(
    SELECTEDVALUE(History[Active Local]),
    FILTER(
        All(History),
        History[Report Date] = PreviousDate &&
        History[User ID] = CurrentUserID
    )
)

VAR Gained = IF(
    CurrentStatus = "Yes",
    IF(
        IsBlank(PreviousStatus) || PreviousStatus = "No",
        1,
        0
    ),
    0
)

RETURN Gained

The total for the measure across the whole table ends up being 1, instead of a count of the number of gained member.

I tried a summing measure to aggregate by row, but that is not working as expected. The measure is:

zGained Total = SUMX(History, _Calculations[newGained in Last Month])

I have filtered down a PowerBI table to a subset of rows in order to try to understand what is happening. It looks like the discrepancy is when a user is an active member in the current month AND the previous month. In that case, newGained is correctly giving 0 as a result. However, GainedTotal is showing a 1, and so the total for GainedTotal is too high because it is also counting the users in that situation.

What do I need to do to fix this measure?

Total beginner here, tasked with designing a Transportation domain datawarehouse. When I look at my dim tables, I can't tell if I have to use SCD or not. Is there a question that I can ask to myself that would help me decide whether or not I should go for it?

Curious about the strategies some of the teams take when it comes to managing SCDs -

1.) Do you partition to the old dimension into storage elsewhere?
2.) Create another dimension to handle the active dimension?
3.) Leave as is?

I'm loathe to post anything on Stack Overflow because I know it'd get shot down so I'm asking here: how does one represent relationships on an entity-relationship diagram where the relationship between two tables is not simple equality?

For example, I have a fact table that links to a slowly changing dimension (type 2) table where the record date in the fact table is between the "from date" and "to date" in the dimension table. I've tried to research how to document this on an ERD and haven't found anything to address this specific type of relationship - is that because it's "improper"?

Edit for better clarity:

My tables look a bit like this (dates non-US format):

Table A - historical orders, where I want to know the salesperson for each region at the time of the order:

Table B - slowly changing dimension table telling me the salesperson for each region with the applicable dates:

The proper join to get the salesperson would be on

A.region = B.region

AND A.order_date >= B.from_date

AND A.order_date <= B.to_date

giving

My question is what's the right way to document this relationship on a diagram, since it's more complexing than just joining on column A = column B?

I've created a slowly changing dimension:

https://imgur.com/a/erSkVEu

The 4 columns that matter are highlighted

I'd like to be able to select 2 dates, and show which entities (the VendorListID is unique) changed between those 2 given dates, in The SimpleAcceptance column.

I can do that fine in SQL, but am having trouble with the setup in PowerBI.

Right now, I have the above table joined to my DateDIM 2x (both inactive). The following can give me how many entities existed at any given point in time

VAR LastDT =  MAX ( 'Dates'[Date] )

RETURN 
CALCULATe (
   count(CustomerVendorAcceptanceSCD[VendorListID]),
    FILTER (
        CustomerVendorAcceptanceSCD,
        CustomerVendorAcceptanceSCD[ValidFrom] <= LastDT
            && CustomerVendorAcceptanceSCD[ValidTo] >= LastDT
    )

)

But how to get the DAX so that I can show a table, with all the entities that changed between 2 points in time?

I am currently thinking about what the best approach for implementing a scd type 2 approach with snowflake might be. Beeing quite new to snowflake makes this a challenge... Does anyone have a good idea?

Hello awesome data engineers, I need your help with implementing slowly changing dimensions type 2 using Matillion for Snowflake.I have been able to follow the Matillion guide for Redshift and now the main challenge is how to retire changed rows.Any help will be highly appreciated. Link to matillion scd type 2 for redshift: youtube.com/watch?=4LQKkYEI44

Hi everyone,

I'm a long time Tableau user switching to Power BI and learning the data modeling tools Power BI has to offer.

My question is - how do you handle slowly changing dimensions? Or do you just handle that in the SQL ETL side and only model the 'current' state of things? Can power BI even handle slowly changing dimensions?

Thanks!

Firstly I need to say that I'm a complete beginner with databases. I've played about with microsoft access in the past but no-one seems to use that anymore.

My company has asked me to set up a database for storing data on commercial partners. Apparently I am the most qualified to look into this, despite having no clue where to start.

Since I need to store historic records, I am looking at slowly changing dimensions (maybe type 6). More fundamentally, I don't know what software I should be using and there seem to be endless options for this. Searching for how to implement type 6 SCD returns lots of software specific info and sql , but I can't find a simple 'beginner's guide to storing data with slowly changing dimensions'. I don't even know if I should be using sql, SSMS, MySql etcetc.., or could be using r (with which I'm more familiar).

For example, one part of my data are currently in four excel spreadsheets, each with the same columns ; cust_id, valid_from, valid_to, contact, data_source

...and each spreadsheet contains records from a particular datasource. The different spreadsheets overlap in time, so in order to merge them I need to match the customer ids, check which valid_from date comes latest, and reset the valid_to date in the historic records.

If anyone can point me in the right direction I would be very grateful.

How should my data model look if I have SCDs?

Let’s say I have sales data by sales rep and the sales rep moves location. If I present data over time by sales rep then it’s fine as that is just my fact table, but if I want to do the same by location then I want the reps sales to appear in whichever region they were at the time.

TIA

We got an azure sql database and azure data factory set up. I now want to implement a slowly changing dimension, do I do this:

1. with Azure Data Factory; like in this blogpost
2. with a stored procedure; something like this

We will be starting with one table, but I am certain that we will need this on many more tables. So I am looking for an easy to implement/ copy solution!

Does anyone have a case study/experience with slowly changing dimensions where the columns also change every once in a while? For example, let's have a table with customers and some features like favorite color, favorite animal, and favorite movie, with start date and end date. Then in a few months, favorite season gets added and a few months after that favorite movie gets changed to favorite movie/TV show, and so forth. Is there a good way to handle this situation?

Is there a simple or best practice way of handling SCD through SqlAlchemy and python. I would like to expire records and maintain state of a transactional application through SqlAlchemy. Does any one have any ideas about this.

if we update the record we want to expire it and keep it in history so I can see the current records or see a record based on its validity date. In between date a and date b

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 changes, the current record is closed. A new record > is created with the changed data values and this new record becomes > the current record. Each record contains the effective time and > expiration time to identify the time period between which the record > was active.

EXAMPLES: https://i.stack.imgur.com/ACXsG.png https://i.stack.imgur.com/yjV8v.png

So in my original script that I created I have:

CREATE TABLE PRODUCT_DIM
(
    PRODUCTKEY integer NOT NULL,
    PRODUCTID integer,
    PRODUCTDESCRIPTION VARCHAR2(50 BYTE),
    PRODUCTLINEID integer,
    PRODUCTLINENAME VARCHAR2(50 BYTE),
    CONSTRAINT PRODUCT_DIM_PK PRIMARY KEY (PRODUCTKEY)
);

CREATE SEQUENCE PRODUCT_KEY_SEQ
  MINVALUE 1001
  START WITH 1001
  INCREMENT BY 1
  CACHE 25;

INSERT INTO PRODUCT_DIM
(PRODUCTKEY, PRODUCTID, PRODUCTDESCRIPTION, PRODUCTLINEID, PRODUCTLINENAME)
SELECT PRODUCT_KEY_SEQ.NEXTVAL, nvl(to_char(p.PRODUCTID), 'Undefined'), nvl(to_char(p.PRODUCTDESCRIPTION), 'Undefined'),
nvl(to_char(p.PRODUCTLINEID), 'Undefined'), nvl(to_char(pl.PRODUCTLINENAME), 'Undefined')
FROM PRODUCTLINE_T pl, PRODUCT_T p
WHERE p.PRODUCTLINEID = pl.PRODUCTLINEID;

Then I know I have to create a new date for when the data was stored:

ALTER TABLE PRODUCT_DIM
ADD EFF_START_DATE DATE DEFAULT '21-NOV-17';

And then I created a new column with a new date for when the data was updated/deleted:

ALTER TABLE PRODUCT_DIM
ADD EFF_END_DATE DATE DEFAULT '27-NOV-17';

But then I don't know what to do next or if I even started correctly?

credit to @theinnerdivine for their comment on the last post. It really got me thinking. Is this what season 3, 4 or 5 would have been? She has been gathering the same number of people around her to follow her and trying to learn as much as she can about the dimensions. she has been trying so hard to defeat HAP, but is there a dimension in which she feels the need to either continue his work (or it’s been her work all along)? There is also the repeated “please take me with you” line that would show mirroring eluding to this.

I'm having a problem in that I have an extract that takes over an hour for my server to load. I know the bottleneck is too much data going over the network, but there's not a lot I can do on the hardware/network side of things.

I'd like to enable the incremental option, but the particular view it's built on has numerous slowly changing dimensions. Since the incremental load won't re-load changed data, this option is out.

Splitting the view up into multiple data sources, with the changing dimensions being loaded from full, and the non-changing incremental would solve a big part of the issue. The downside is tableau doesn't do well with multiple data sources on the same workbook, doubly so if you need to aggregate measures from different sources. Having a bit more control over joining sources ala SQL joins would go a long way to solving this.

The last option is I could have Tableau pass the query parameters over to SQL, and have SQL compute the aggregate dimensions on the fly. This isn't so bad, but then my users lose the ability to view the underlying data, and my non-sql desktop users won't have as much flexibility when doing ad-hoc analysis.

Am I missing something big, or is this a problem with no good solution? Is there anything Tableau could be doing on their end for something like this?

This is going to be a very long post.

To preface, because sometimes people take criticism of the game as a personal attack;

I've been playing Hunt Showdown since early access. It's a game I love, and I'm a big fan of Crytek for taking the plunge on a very unique game, and the passion of the developers, artists, sound designers, and everyone else on board who have made Hunt the treat that it is.

I don't write this to shit on the game, quite the opposite really, because I love Hunt and I want it to do well and continue to be a great game for everyone.

With that said, let's begin:

The Economy of Hunt is changing in a slow and incremental fashion, and you may not have even noticed yet.

Hunt Showdown has typically been pretty generous with how it awards Blood Bonds, the premium currency of the game. Freely earnable without paying any money (beyond your initial purchase of the game of course), Hunt also lets you purchase Blood Bonds to accelerate unlock progression too.

However, this system has been changing over the course of the last year or so. While Hunt is still better than most games on this front, this post is intended to make you aware of these changes, as they're intentionally being spaced out to make them more digestible and less likely to arouse ire. Whether or not you agree with these changes, it's important you are aware of them, so I would ask politely that you don't downvote this thread if you don't personally mind these changes being made. Let's try to have a polite, mature discussion about this.

#Earning Bonds Before 1.6:

This is going on memory for a lot of things, as I didn't think to document the exact payout mechanics before the changes, so if I'm not 100% numbers accurate, please forgive me.

Hunt pays out bonds in the post game using an accolade system. You have probably noticed this already, but the 'cards' you get in your post game report are bordered in line with their rewards; bronze, silver, and gold respectively, with no special border for lesser accolades to indicate that they didn't payout bonds.

The game uses your 5 best accolades in a match to dictate your bond payout; this caps your bond earnings at 15 for an excellent match, as you earn up to 3 bonds for a Gold accolade. 3 times 5 makes 15, etc.

Some of the bond payouts prior to 1.6 included;

1 bond for 3 clues

1 bond for discovering the boss lair (either by entering the lair or damaging the boss monster)

Doing the Tutorial mission, with 3 difficulties, aw

after over a year of this dragging on and the bad actors having plenty of time to minimize their exposure and use crime to stave off any kind of squeeze - well, it doesn’t take a rocket scientist to see the narrative changing due to any number of factors. and everyone seems to be ok with that. going along with that. promoting that new narrative of the long-term value investment.

smdh.

Ok, so as we saw in Horse aswell as in Rider, beings from Earth who stay in Centaurworld start to change, and start to be of a similar art style to Centaurs. But I wonder if the reverse is true aswell? If a Centaur stayed on Earth for too long, would they become more realistic? I think this may be seen in the Elktaur, as he seemed to fit more with the art style of the Earth People, which may have been caused by his long stays in the rift and on Earth. I just wonder if my theory is right about the Elktaur. I wonder what the herd would look like realistically, and how waterbaby would look aswell.

Firstly I need to say that I'm a complete beginner with relational databases. I've played about with microsoft access in the past but no-one seems to use that anymore.

My company has asked me to set up a database for storing data on commercial partners. Apparently I am the most qualified to look into this, despite having no clue where to start.

Since I need to store historic records, I am looking at slowly changing dimensions (maybe type 6). More fundamentally, I don't know what software I should be using and there seem to be endless options for this. Searching for how to implement type 6 SCD returns lots of software specific info and sql , but I can't find a simple 'beginner's guide to storing data with slowly changing dimensions'. I don't even know if I should be using sql, sql server management studio, mysql etc... or could I just use r (with which I'm more familiar).

For example, one part of my data are basically in four excel spreadsheets, each with the same columns ; cust_id, valid_from, valid_to, mesh_size, data_source

...and each spreadsheet contains records from a particular datasource. The different spreadsheets overlap in time, so in order to merge them I need to match the customer ids, check which valid_from date comes latest, and reset the valid_to date in the historic records.

If anyone can point me in the right direction I would be very grateful.