Skip to main content

Suggest an Idea

Vote for your favorite Qlik product ideas and add your own suggestions.

Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Compose: Creating a current view (type 1) fact table on a type 2 entity

MaikelJ
Contributor
Contributor

Compose: Creating a current view (type 1) fact table on a type 2 entity

This is a feature request to allow the building of a datamart fact table on the most current records of a Type 2 entity for fact attributes. The current generated SQL retrieve the version of records valid at the transaction date but this is not necessarily logical, some use cases would require to include the latest version of the records in the fact table.

This type of option is available for dimensions and should also be available for fact tables

The suggested solution would look like this, per star schema you choose whether you want the latest record for a fact or the historic based on transaction date:

The sql part is:

WHERE               "E1"."ID" <> 0
                AND "TXND"."TD" = CAST('9999-12-31' AS TIMESTAMP)
                AND "TXND"."S_CREATED" >= "E3"."FD"
                AND "TXND"."S_CREATED" <  "E3"."TD"

Currently for attributes in the facts by default a where statement is create where From date FD and To date TD are filtered, for facts where you have the latest version it should say e.g. :

"E3"."TD"="TXND"."TD"

 

 

 

Tags (1)
3 Comments
Tzachi_Nissim
Employee
Employee

Hi MaikelJ,

Thanks for your idea. Do I understand correctly that you are talking about the denormalization of a type 2 entity into the fact, right? 

Regards,

Tzachi

Status changed to: Open - Collecting Feedback
MaikelJ
Contributor
Contributor

Hi Tzachi,

Denormalization of dimensional data would be one example of how this is useful. However my primary focus was on fact data. Let me clarify with an example:

Let's say we have a Sales order which is the driving table for our fact this is E1 in the above query. On this fact we would also like to have fact attributes of another table e.g. a related Purchase order E3 in the above query. So let's say we have Sales order #01 with is Related to purchase order #02, for Sales order #01 we want to know the related purchase price which we could find in purchase order #02.  For the star schema we need to chose a transaction date, which could be the Sales creation date for example. What happens is that when the Purchase order #02 price would change, a new sat records would be created, this new sat record would be 'filtered' by the where clause   "TXND"."S_CREATED" >= "E3"."FD" AND "TXND"."S_CREATED" <  "E3"."TD". On our fact still the old sat value with the old purchase price would be visible.  

Please let me know if you understand my example.

kind regards, Maikel Jaspers

TimGarrod
Employee
Employee

Feature to "Update fact with changes to Type 2 data warehouse entities" was delivered in May 2021 Qlik Compose release. 

Update fact with changes to Type 2 data warehouse entities - Select this option (the default) if you want the fact table to always be updated with the last record version of any Type 2 data warehouse entities the star schema contains.

Example:

Assuming the data warehouse has the following Type 2 entities:

Orders
Order Details
Address


And the data mart consists of the following:

Fact = Orders and Order Details
Transaction date = Order Date in Orders
Dimension = Address (Type 2)


Then the last version of Orders and Order Details will always used and Address will be updated according to the Order Date.

Status changed to: Delivered