How to create logical column from two logical tables

3404982

Hello,

I'm using oracle HR data to practice. I want to create a logical column in the fact table Employee called Estimate Work Experience(number of years since the employee's last job start date). Is this possible? Or is there other measures that I could create instead?

Here is the calculation that I tried to do I use the year function to extract the date and do the calculation but it didn't work:

Hire_date(in Table Employee) - Start_date( in Table Job history)

Below is the BMM for your reference. Thank you for your help in advance.

Gianni Ceresa

Hi,

Dimensional modelling has it's own rules

To make your calculation and to get that as a measure (I guess you will want an AVG aggregation on it) you must get the required tables in a LTS for the logical fact table.

In the same way, based on your screenshot, your logical fact table is supposed to have only aggregated columns (the ones with the yellow ruler icon) and not attributes.

If you model things cleanly it will be easier to understand the behaviour or your system as you will not get random queries returning you none, some, all your data (or maybe wrong data).

If you want to have this information as an attribute for the employee then you must model that in the dimensions and not the logical fact table.

[Deleted User]

+1 to Gianni.

In your example this would mean to include the source for D2 Job History in the LTS of your F1 Employee fact with and then do the calculation in the with a formula in the column source mapping.

Mind you - a calc with physical columns, not a derived calc using logical columns! Otherwise you'll hit the usual pre-agg/post-agg problems when not looking at atomic records.

3404982

Thanks for your reply. Gianni

I guess I have to work under the rules then. I could calculate a different logical column as an attribute instead, which is great to know!

3404982

Thank you for your advice. Christian

Do you mean to calculate the column under the "Derived from physical mappings" instead of the "Derived from existing columns using an expression"? I tried to calculate under the Table Job History for the start date and end date. it works.

[Deleted User]

yes