Using "constant union" dimension views instead of dimension tables - good/bad idea?

  • Hi -

    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)

    UNION ALL

    SELECT (1 AS SKey, 'Female' AS Description, 'F' AS Abbreviation)

    UNION ALL

    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.

    So:

    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?)

    Muchas gracias!

    Harald M.

  • Harald,

    just two side notes:

    1) Your CREATE VIEW statement doesn't run (it's erroneous).

    2) If your'e on 2008, you may now use row constructors like this:

    create view DIMSexes as

    select * from (values(0,'Male','M')

    ,(1,'Female','F')

    ,(2,'Unknown','U')) as s(SKey, Description, V)

    I remember a discussion like this, where Joe Celko suggested using views for simulating "small tables". There were some pros and cons inside this discussion and I think it'd be helpful, if you find it. If I only could remember the place where this discussion was going on... Does anybody have an idea?

    SQL Server 2008 Enterprise Edition has Bitmapped Filters for optimizing star queries (see: http://msdn.microsoft.com/en-us/library/bb522541.aspx). Not sure, if this works with views too.

    I don't know if this helps very much but that's all I can say at the moment.

    Regards,

    Holger

  • Holger -

    Ad 1) - oops. Was late. Just imagine the parentheses are gone ...

    Ad 2) Thx!

    Ad 3) That would be interesting!

    Regards

    Harald M.

  • Re the discussion:

    Holger I (3/8/2011) wrote

    I remember a discussion like this, where Joe Celko suggested using views for simulating "small tables". There were some pros and cons inside this discussion and I think it'd be helpful, if you find it. ...

    Googling for "celko "VIEW defined with a table constructor"", I found a few places - but none had a discussion :-(.

  • Hello hope it's not o late...

    I'm looking for the same problem and found:

    http://dba.stackexchange.com/questions/15619/table-of-constants-is-this-common-practice

    and here..

    http://www.sqlservercentral.com/Forums/Topic411110-403-1.aspx

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply