topic Re: Adding missing values to a dimension table from fact table in App Development

Adding missing values to a dimension table from fact table

datagrrl — Fri, 08 Nov 2024 16:32:03 GMT

I have a situation where my User Dimension is basically internal contacts, but external contact records are also in my fact table.
I want to add values from the fact table that are missing from the dimension table so that I will have a complete User Dimension. The Title for my external user would be something like 'Outside Sales'.
I included a Dummy App. I feel like this is easier than I am making it. I have tried a couple things with join and concatenate and I end up getting the wrong results.
Any assistance is appreciated.

Re: Adding missing values to a dimension table from fact table

Qrishna — Fri, 08 Nov 2024 15:52:23 GMT

unfortunately i cannot open your file.

did you already concatenate both the fields ?while appending, create another field called 'tab' and assign 1 and 2 for table1 and table2 . once appended, you could do a pick(match()) or an if() to get what you need on the UI.

Re: Adding missing values to a dimension table from fact table

sbaro_bd — Fri, 08 Nov 2024 15:57:22 GMT

Hi @datagrrl ,

Try something like this :

Dimension: LOAD UserID, Title FROM DIMENSION_TABLE; LOAD UserID, 'Outside sales' as Title FROM FACT_TABLE WHERE NOT EXISTS(UserID) ;

Regards.

Re: Adding missing values to a dimension table from fact table

datagrrl — Fri, 08 Nov 2024 16:19:10 GMT

Okay, the table 1 and table 2 think makes a little sense.

How do I avoid duplicating the records from the dimension though when I load the records from the fact? Should I do some kind of join and add an exists clause? That is the part I am struggling with.

UserDim:
Load *,'Table1' as Tab
Resident User;

Concatenate(UserDim)
Load Distinct UserID,'Table2' as Tab, 'Outside Sales' as Title
Resident Fact;

Drop Table User

I end up with Duplicates of the values that existed in the User Dimension.

I really do not want to do any work in the UI. I just want to add the missing values to the user dimension. I already have a lot of data and don't want to do any more calculations in the UI.

I do think you might have lead me down the right path though and I can remove the duplicate records in another step. Again, I think I am making this more complicated than it needs to be.

Re: Adding missing values to a dimension table from fact table

sbaro_bd — Fri, 08 Nov 2024 16:12:42 GMT

@datagrrl , if you take a close look on my response, add a WHERE NOT EXISTS() statement on the second part of script.

Regards.

Re: Adding missing values to a dimension table from fact table

datagrrl — Fri, 08 Nov 2024 16:16:18 GMT

This doesn't make sense to me.

Load Distinct UserID,'Outside Sales' as Title
Resident Fact
Where NOT EXISTS(UserID);

I do this and just end up with the same records that were in the User table in the first place.

Re: Adding missing values to a dimension table from fact table

sbaro_bd — Fri, 08 Nov 2024 16:26:36 GMT

The EXISTS() take a lookup on your previous charged tables! You're using the resident statements, that means all UserId records already exists on previous tables. Try something like this :

UserDim: Load *, 'Table1' as Tab, UserId as KEY Resident User; Concatenate(UserDim) Load Distinct UserID,'Table2' as Tab, UserId as KEY 'Outside Sales' as Title Resident Fact where not exists(KEY, UserId);

Re: Adding missing values to a dimension table from fact table

Kushal_Chawda — Fri, 08 Nov 2024 16:33:20 GMT

@datagrrl see the attached

Re: Adding missing values to a dimension table from fact table

maxgro — Fri, 08 Nov 2024 16:33:47 GMT

User:
Load * Inline [
UserID, Title
ABC, Salesperson
DEF, Salesperson
BUR, Salesperson
JKL, Salesperson
FDR, Salesperson
QRS, Salesperson
TUV, Salesperson];

Fact:
Load * Inline [
UserID, Date, Value
ABC, 5/27/2022, 98
DEF, 4/27/2021, 41
BUR, 7/30/2022, 57
Thomas, 11/11/2020, 33
Jane, 2/18/2022, 48
ABC, 7/31/2021, 83
DEF, 6/26/2020, 74
BUR, 7/22/2020, 100
JKL, 12/4/2022, 100
FDR, 2/24/2021, 24
Palace, 10/6/2022, 92
QRS, 12/4/2020, 84
TUV, 8/29/2020, 69
Ranch, 6/21/2021, 1
Desert, 9/24/2022, 37
ABC, 7/16/2021, 98
DEF, 12/2/2022, 33
BUR, 9/10/2022, 14
JKL, 10/22/2021, 48
FDR, 3/4/2021, 39
QRS, 11/16/2021, 92
TUV, 8/3/2021, 48];

Tmp:
NOCONCATENATE Load Distinct UserID as NewUserID Resident User;

CONCATENATE (User)
load Distinct UserID, 'Outside sales' as Title
Resident Fact
where not exists(NewUserID, UserID);

drop table Tmp;

Re: Adding missing values to a dimension table from fact table

datagrrl — Fri, 08 Nov 2024 16:43:30 GMT

Thanks. I feel bad that you both posted the solution at the same time. I was definitely making it more complicated than it needed to be.

Re: Adding missing values to a dimension table from fact table

datagrrl — Fri, 08 Nov 2024 16:44:13 GMT

I tried to mark both as the solution since you posted at the same time. Thanks for the help.