I have to implement some stars in a DWH (which actually has to live in an OLTP database - but this is not that important unless someone thinks so).
Anyway, I have quite a few dimensions which behave as follows: They are type 0; or "type 1 where each change justifies a software update" - i.e., the dimension value list changes slower than our database upgrade cycles.
My idea is now to use "constant union dimension views" for those dimensions, like that:
CREATE VIEW DIMSexes AS
SELECT (0 AS SKey, 'Male' AS Description, 'M' AS Abbreviation)
SELECT (1 AS SKey, 'Female' AS Description, 'F' AS Abbreviation)
SELECT (2 AS SKey, 'Unknown' AS Description, 'U' AS Abbreviation);
So the dimension data are not put into tables, but into "hard-wired views". The idea is that it easier for us to create and check the schema (we use Redgate SchemaCompare; and a homegrown tool to change the schema in software upgrades).
Performance is a big issue for us - we will have (at max) some 10s of millions of records in the FACT tables; and no chance to go above standard edition; and have to handle "hard" (<0.1s) OLTP transaction on the same box/server instance/disks.
Is this a good idea (because it fixes data; because the planner knows about the data "hard-wired")
- or -
a bad idea (because the planner can handle JOINs to tables better than to such "weird" views?)
- or -
"unclear" (but does anyone maybe have some experience
with that idea?)