Data load editor script with Mapping and Date Bridge doesn't work as expected

[PGeorge]

Hello everyone,

I have created the below script in Data load editor

MallsMap:
MAPPING LOAD
    [Malls.Id] AS [Mall_Id],
    [Name]
RESIDENT [Malls];

DepartmentsMap:
MAPPING LOAD
	[Departments.Id] AS [Department_Id], 
	[Departments.Name]
RESIDENT [Departments];

[ShoeRecordsMapped]:
LOAD
	[ShoeRecords.Id] AS [ShoeRecordsMapped.Id],
	[ShoeRecords.ApplicationUser_Id],
	[ShoeRecords.Mall_Id] AS [ShoeRecordsMapped.Mall_Id],
	[ShoeRecords.Department_Id] AS [ShoeRecordsMapped.Department_Id],
    ApplyMap('MallsMap', [ShoeRecords.Mall_Id], null()) AS [MallName],
    ApplyMap('DepartmentsMap', [ShoeRecords.Department_Id], null()) AS [DepartmentName],
	Date([ShoeRecords.RecordDate]) AS [ShoeRecordsMapped.RecordDate],
	.
	.
	.
RESIDENT [ShoeRecords];

DERIVE FIELDS FROM FIELDS [ShoeRecordsMapped.RecordDate] USING [autoCalendar];

[BackpackRecordsMapped]:
LOAD
	[BackpackRecords.Id] AS [BackpackRecordsMapped.Id],
    [BackpackRecords.ApplicationUser_Id],
    [BackpackRecords.Mall_Id] AS [BackpackRecordsMapped.Mall_Id],
    [BackpackRecords.Department_Id] AS [BackpackRecordsMapped.Department_Id],
    ApplyMap('MallsMap', [BackpackRecords.Mall_Id], null()) AS [MallName],
    ApplyMap('DepartmentsMap', [BackpackRecords.Department_Id], null()) AS [DepartmentName],
    MakeDate([BackpackRecords.Year], [BackpackRecords.Month]) AS [BackpackRecordsMapped.RecordDate],
	.
	.
	.
RESIDENT [BackpackRecords];

DERIVE FIELDS FROM FIELDS [BackpackRecordsMapped.RecordDate] USING [autoCalendar];
    
[JewelleryRecordsMapped]:
LOAD
	[JewelleryRecord_Id-Id], --one to many association with another table
    [JewelleryRecords.Mall_Id] AS [JewelleryRecordsMapped.Mall_Id],
    [JewelleryRecords.Department_Id] AS [JewelleryRecordsMapped.Department_Id],
    ApplyMap('MallsMap', [JewelleryRecords.Mall_Id], null()) AS [MallName],
    ApplyMap('DepartmentsMap', [JewelleryRecords.Department_Id], null()) AS [DepartmentName],
    [JewelleryRecords.CreatedAt] AS [JewelleryRecordsMapped.CreatedAt],
	.
	.
	.
RESIDENT [JewelleryRecords];

[BackpackJewellery_DateBridge]:
LOAD 
    [BackpackRecordsMapped.Id],
    [BackpackRecordsMapped.RecordDate] AS [BackpackJewellery_Date]
RESIDENT [BackpackRecordsMapped];

LOAD 
    [JewelleryRecord_Id-Id],--one to many association with another table
    [JewelleryRecordsMapped.CreatedAt] AS [BackpackJewellery_Date]
RESIDENT [JewelleryRecordsMapped];

DERIVE FIELDS FROM FIELDS [BackpackJewellery_Date] USING [autoCalendar];

In my visualizations I have created a filter pane with "Mall" & "Department" options which were working as expected until I added the Date Bridge script and the options "Year" & "Month" in the filter pane.

I ran some test cases and the filterings of "Year" & "Month"  are correct but when I try to filter  from "Mall" or "Department" it doesn't take into consideration the [JewelleryRecord_Id-Id] which is an association to another table and I lose the data from the linked table.  

When I comment out the Date Bridge script, [JewelleryRecord_Id-Id] association is working when I filter from "Mall" or "Department".

Why am I losing the linked tables' data? What am I missing here? Is there a workaround?

P.S. : I am getting a synthetic key warning  "MallName+DepartmentName" which hasn't caused me any problems in other visualizations I have implemented so far. I don't think this causes any issues atm, I just wanted you to have the whole picture.

[PGeorge]

If more info are needed to help you, please let me know. 

[marcus_sommer]

The associations of the data between the various tables aren't complete in regard to your view-requirements respectively not be correct. Having synthetic keys is always a sign for an unsuitable data-model especially if you didn't created them intentional. I know they will work in many scenarios - but they won't ever have any benefit which means in the best case they won't bother and in each other case they are a disadvantage. As far as any data within an application aren't obviously clear and explainable the data-model must be regarded as invalid.

Your main-issue is that you didn't developed the data-model as star-scheme else as a multi-fact data-model connected with any link-tables. In my experience it's quite complicated to get such data-model to work. It's not impossible but it will relate to quite a lot of efforts, a heavy UI handling and a rather bad performance.

Better would be to develop a data-model as star-scheme with a single (merged) fact-table with n dimension-tables which is also the officially recommended way to create a data-model. In your case it seems not to be very difficult just by concatenating the shoe/backpack/jewellery tables by harmonizing their field-names. By adding an extra field Source to each fact-load you could later differentiate between them in dimensions, selections and/or conditions.

[PGeorge]

Your recommendation on changing to star-scheme model is quite helpful, as I was not aware of it.  I will try to make the necessary changes and set the application in motion. Let's hope it will work.

Thanks.