How to create a star schema with two fact tables?

[Alessia97]

Hi,

Let's suppose i have two fact tables and one dimension table, with the following fields:

FACT TABLE 1         DIMENSION TABLE        FACT TABLE 2

Field_A                      Field_ID                             Field A

Field_B                      Field_C                              Field B

Field_ID                                                                  Field C

Is there a way to merge (like you would do in SAP BO) the Field_C from the fact table 2 and the Field_C you get from the fact table 1 joining the dimension through ID?

This should be done without reloading the dimension table and the fact table 1 as one table, because we need to mantain a star schema

[anat]

populate Field C in  Fact Table1 by using mapping load with dimension table  then remove Field_ID from Fact Table1 

then concatenate Fact Table1  and Fact Table2

[rwunderlich]

See here for some options:

https://qlikviewcookbook.com/2020/03/creating-temporary-script-associations/

-Rob

[Scotchy]

If I understand this question , maybe this approach will do...

To do this in Qlik Sense, assuming you've already loaded Fact Table 1, Fact Table 2, and the Dimension Table:

1. Load the Dimension Table: Load the Dimension Table as is. Qlik Sense automatically creates associations between tables based on common field names.

   DimensionTable: LOAD Field_ID, Field_C FROM [source of your dimension table];

2. Load Fact Table 1: Load Fact Table 1. If Field_ID is present, Qlik Sense will automatically associate it with the Field_ID in the Dimension Table.

   FactTable1: LOAD Field_A, Field_B, Field_ID FROM [source of your Fact Table 1];

3. Load Fact Table 2: Load Fact Table 2. Qlik Sense will associate Field_ID with the Dimension Table and any other common fields with Fact Table 1.

   FactTable2: LOAD Field_A, Field_B, Field_C, Field_ID FROM [source of your Fact Table 2];

After loading these tables, Qlik Sense will automatically manage the associations between them. You can then create visualizations or tables in your app that display data from these tables. For instance, you can create a table object where you can select fields from both Fact Table 1 and Fact Table 2 along with the dimension's Field_C. Qlik Sense's associative engine will manage the relationships and display the merged data accordingly.

This method leverages Qlik Sense's associative data model and does not require explicit JOIN operations as you would in SQL. It is a more intuitive approach, especially for complex data models commonly found in retail banking analytics. This way, you can maintain the star schema without needing to merge or reload the tables.

[Alessia97]

The problem is we do not have Field_ID in fact Table 2. The previous solution we applied in Qlikview was to load fact table 1 with the dimension fields, and perform an outer join with fact table 2 based on common fields (fact table 2 is supposed to have all of fact tables fields + dimension fields). Problem is in Qliksense we get RAM problems, so we have been told to transform the single big table schema in a star schema, but we have been having some troubles because of the different nature of the two fact tables

[Scotchy]

If Field_ID is not present in Fact Table 2, you'll need a different approach to
integrate the data in Qlik Sense while maintaining a star schema and managing RAM usage effectively.

Since Fact Table 2 does not directly link to the Dimension Table, and you're experiencing performance
issues with large table joins, you'll need to find an alternate method to associate Fact Table 2 with
the Dimension Table. Here are some steps you can follow:

Create a Link Table:
A link table can be used to bridge Fact Table 1 and Fact Table 2. This table will contain the common
fields between these two tables and Field_ID, which links to the Dimension Table.

LinkTable:
LOAD DISTINCT
Field_A,
Field_B,
Field_ID
FROM [source of your Fact Table 1];
Load Fact Table 1 and Fact Table 2 Without Field_ID:
Load both fact tables, but exclude Field_ID from Fact Table 1.

This will prevent circular references and reduce the data load.

FactTable1:
LOAD Field_A,
Field_B
FROM [source of your Fact Table 1];

FactTable2:
LOAD Field_A,
Field_B,
Field_C
FROM [source of your Fact Table 2];
Load the Dimension Table:
Load the Dimension Table as is.

DimensionTable:
LOAD Field_ID,
Field_C
FROM [source of your dimension table];

This structure creates an indirect link between Fact Table 2 and the Dimension Table via
the Link Table. In Qlik Sense, this setup helps manage large datasets more efficiently,
as it avoids creating one huge table. The associative engine of Qlik Sense will automatically
handle the relationships between these tables.

When you create visualizations or reports, you can select fields from both fact tables, and
Qlik Sense will use the Link Table to correctly associate data with the Dimension Table.

Keep in mind, the actual implementation might need to be adjusted based on the specific nature of
your data and the relationships between the tables. This approach should help in balancing the need
for a star schema with the constraints of RAM usage in Qlik Sense.