idea Re: Allow an entity to be a dimension and a fact in the same star schema in a data mart to enable simple joins for point-in-time data analysis - Status changed to: Closed - Archived in Suggest an Idea

Allow an entity to be a dimension and a fact in the same star schema in a data mart to enable simple joins for point-in-time data analysis

paulstevens — Fri, 15 Jan 2021 21:56:30 GMT

When managing the data marts, an entity can be selected as a fact in a star schema, but the same entity cannot be selected as a dimension in that star schema to be related to the fact.

It would be beneficial for data modeling and end-user querying for point-in-time data analysis to enable an entity to be a fact and dimension in the same star schema (and thereby be linked to each other). This would enable the _VID column in the table join columns to be present on the fact table in addition to the dimension table, and enable point-in-time data analysis.

Typically, the fact table will have columns used for metric calculations, date values, and foreign key columns (_OID and _VID suffixed) to dimension tables, and a dimension table will have descriptive attribute columns in a Type 2 Slowly Changing Dimension. In Qlik Compose, it appears the fact and dimension table relationships are driven from the entity model relationships, and the physical fact and dimension tables will have two key columns joining the tables with an _OID and _VID suffix.

This join between the fact and the dimension physical tables using the _VID suffixed column is crucial for simple joins to see the dimension record version (in Type 2 SCD) as-at the time of the fact event record. This would be consistent with fact tables joined to a Type 2 SCD of a different entity.

Even if the entity is selected as a dimension in another star schema, that dimension cannot be added to the star schema with the entity as the fact.

For example:

Star Schema 1: Entity1 as Fact 1Fct_Entity1

Star Schema 2: Entity2 as Fact 1Fct_Entity2; Entity1 as Dimension 1Dim_Entity1 related to 1Fct_Entity2

1Dim_Entity1 cannot be added to Star Schema 1 using the 'Add Dimension to Star Schema' function in the 'Manage Data Marts' window. (refer to attachment screenshot)

If the entity exists as a dimension, the fact and the dimension share the key_OID column so it is possible to query and join the two tables in a query, but only to see the latest version of the dimension record, not the version of the dimension record as-at the time of the fact; the fact table lacks the key_VID column to provide an easy join for 'point-in-time' linkage to the dimension.

@QTC_Manuel

Re: Allow an entity to be a dimension and a fact in the same star schema in a data mart to enable simple joins for point-in-time data analysis - Status changed to: Open - Collecting Feedback

Tzachi_Nissim — Tue, 06 Apr 2021 11:12:02 GMT

Hi Paul,

Can you please explain what is the use-case you are trying to solve? Do you want to add the entity to the fact as type-1 and connect to it as a type-2 dimension? Can you maybe provide a real-life example?

Thanks,

Tzachi

Re: Allow an entity to be a dimension and a fact in the same star schema in a data mart to enable simple joins for point-in-time data analysis

paulstevens — Thu, 08 Apr 2021 15:27:38 GMT

Hi Tzachi

Thanks for the reply. Typically a state-oriented fact would be type-1 and linked to a type-2 dimension. The fact has numeric fields, foreign keys to dimension tables, and datetime fields. The dimension has descriptive attributes like text attributes, for example, the name and status of the opportunity.

Opportunity Entity: ID, Name, Status, Date Created, Date Modified, Amount, CustomerKey (relationship to separate Customer entity)

These are the physical tables in the data mart:

1Fct_Opportunity: DateCreated, DateModified, Amount, Opportunity_OID, Customer_VID, Customer_OID
1Dim_Customer: Customer_VID, Customer_OID, Name

This is what I would like to see:

1Fct_Opportunity: DateCreated, Amount, Opportunity_VID, Opportunity_OID, Customer_VID, Customer_OID
1Dim_Opportunity: Opportunity_VID, Opportunity_OID, Name, Status
1Dim_Customer: Customer_VID, Customer_OID, Name

The 1Fct_Opportunity.OPPORTUNITY_VID field enables me to query the Status of the Opportunity (1Dim_Opportunity.Status) as at the DateModified of the Fact record, as 1Dim_Opportunity is a type-2 dimension.

Without the 1Fct_Opportunity.Opportunity_VID field, I cannot easily get the Status as at the DateModified datetime of the Opportunity.

Thanks,
Paul

From now on, please track this idea from the Ideation por...

Meghann_MacDonald — Wed, 02 Aug 2023 15:01:32 GMT

From now on, please track this idea from the Ideation portal.

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Re: Allow an entity to be a dimension and a fact in the same star schema in a data mart to enable simple joins for point-in-time data analysis - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 15:01:34 GMT