1

In order to simplify reporting, I am wondering if for each of the Fact tables we have, whether I can include a column that states the name of the Fact table, albeit in a more user friendly format

e.g.

FACT_SALES    
Source     ID      Name   
Sales      1       Bob    
Sales      2       Terry
....

FACT_TESTING    
Source              ID      Type   
Product Testing     23      Adhoc   
Product_Testing     29      ...
...

If it should not, then what is an appropriate way of adding this information without requiring any action by the end user in terms of them having to add some sort of coding to show this?

1

What I recommend is that you have a fact_source table:

CREATE TABLE fact_source
(
  fact_source_id INTEGER,  -- auto-increment.
  fact_source_name VARCHAR (50)
);

Use your own RDBMS's (you didn't say which) own auto incrementing key system to generate the fact_source_id)

INSERT INTO fact_source fact_source_name) VALUES ('Sales'), ('Testing');

and use that as a FOREIGN KEY in the relevant tables.

You can then "pre-cook" your user-friendly (i.e. text) output in report queries if your testing shows that it works nicely and isn't a resource burden.

You should not use ENUMs for the reasons outlined here. Just because systems have features does not mean that you have to use them! You should always use either CHECK constraints (Unbelievably, MySQL doesn't have these) or a lookup table as described above.

Storing your key as a SMALLINT (or similar) will save a bit of space!

1

If the intention here is to provide a fixed alternate table name (rather than record a source, which may vary), one option would be to implement a non persisted virtual column (calculated column in SQL Server).

  • good idea but seemingly only from sql server 2016 onwards – adolf garlic Oct 4 '17 at 7:36
  • @adolfgarlic The feature was added in SQL Server 2005 – Paul Holmes Oct 5 '17 at 8:29
0

This seems to be a feasible approach. It is somewhat similar to the approach in Data Vault, where a column RecordSource is being used to track the source of the data set.

It seems to me that this is not the case here, though. So I'd suggest two ways:

1) Exactly the one you suggested

2) Building a data mart / fact table which would speak for itself, so that the Fact_Testing cube is clearly bound to the "Product Testing" source. Either through documentation, naming or providing pre-build reports.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.