CODEWITHSUNDEEP
sqldata-warehousemodeling
sbrbot
sbrbot

Reputation: 6469

Dimension/Translation tables aggregated inside only one table

In DWH system one has a lot of dimension (lookup) tables (like statuses, types, categories, etc.) These tables are almost always very small but in one regular DWH there could be hundreds of them. Many times values from these dimension tables should be presented (for applications or reports) in more than one language. Here is one such a dimension table:

StatusID | StatusName 
=========+============
    1    |    PENDING
    2    |  INITIATED
    3    |     ACTIVE
    4    |     CLOSED

To support more than one language one approach could be to add custom column(s) (one for each needed language translation) inside each (hundreds of them) dimension table like :

StatusID | StatusName | StatusNameDE | StatusNameHR
=========+============+==============+==============
    1    |  INITIATED |    initiiert |     pokrenut
    2    |     ACTIVE |        aktiv |      aktivan
    3    |     CLOSED |  geschlossen |     zatvoren

Simple query then would look like:

SELECT CustomerName,CustomerStatusHR
FROM Customers c
JOIN DimCustStatus cs ON cs.StatusID=c.StatusID

Another approach could be to create one common lookup/translation table (e.g. AllDimensions) for all languages and for all dimension tables:

 Dimension | Lang | ID | TransValue
===========+======+====+============
CustStatus |  DE  |  1 |   initiiert 
CustStatus |  DE  |  2 |       aktiv 
CustStatus |  DE  |  3 | geschlossen 
CustStatus |  HR  |  1 |    pokrenut 
CustStatus |  HR  |  2 |     aktivan 
CustStatus |  HR  |  3 |    zatvoren

Not much more complicated query looks like:

SELECT CustomerName,TransValue
FROM Customers c
JOIN AllDimensions d ON d.ID=c.StatusID AND d.Dimension='CustStatus' AND d.lang='HR'

First approach requires hardcoding of column names (StatusNameHR) in queries while second one does not.

Note: if explicitly needed for some reason, from common AllDimensions table one could create particular dimension tables as views:

CREATE VIEW DimCustomerStatus AS
SELECT ID AS StatusID, TransValue AS StatusName
FROM AllDimensions
WHERE Dimension='CustStatus' AND lang='HR'

Could somebody elaborate pros and cons for each of these DWH modeling approaches?

Upvotes: 0

Views: 26

Answers (0)

Related Questions