Pierre Smits commented on OFBIZ-10953:

Currency definitions are not defined by the project, but rather by the ISO (see 
1). And based on publicly available information(this may take some time to 
find) the meaning of a currency code does not change over time. As you can see 
in the referred webpage there have been several updates to the currency of 
Zimbabwe (formerly known as Rodesia), where each new currency got a new code 
while the name stayed the same. 

We can therefore safely assume/expect that over the lifespan of a 
country-currency relationship the currency stays unique. We thus don't need to 
worry about multiple records (both in the transaction database in the Uom 
table) as in the dwh in the currency dimensionTable) with duplicate natural 
keys. As a fact: this is not possible because the natural key can not exist 
more than once in the source table (UoM).

In DWH and BI circles it is very common to use the natural key in dimension and 
fact table for currencies (as opposed to the business-own generated sequencedId 
for other transaction tables), as it reduces the need to have additional effort 
to set up - in star schemas and fact tables  - the relation between the 
(sequenced) dimensionId and what the common meaning is (the natural key or 
currency code).

Even with regards to the adopted load/update strategies (type 1, type 2, etc) 
the DWH toolkit book  does not dictate that values in the dimensionId field of 
dimension tables must be of the sequence type.



[1] https://en.wikipedia.org/wiki/ISO_4217

> have CurrencyDimension have a dimensionId that is based on the natural key
> --------------------------------------------------------------------------
>                 Key: OFBIZ-10953
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-10953
>             Project: OFBiz
>          Issue Type: Improvement
>          Components: bi
>    Affects Versions: Trunk, Release Branch 17.12, Release Branch 18.12
>            Reporter: Pierre Smits
>            Assignee: Pierre Smits
>            Priority: Major
>              Labels: CurrencyDimension, birt, currency, dimension, dwh
>         Attachments: OFBIZ-10953-BI.patch
> Currently the record sequencer (delegator.getNextSeqId) is used to determine 
> the dimensionId for the CurrencyDimension. This is unnecessary as the uomId 
> from the UOM table can be used for currency.
> It also makes it easier to set the foreign-key in fact tables by generating 
> it based on the date provided, than by retrieving the dimensionId based on a 
> retrieval through the getDimensionIdFromNaturalKey service.

This message was sent by Atlassian Jira

Reply via email to