Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
h.mueller-978167
h.mueller-978167
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 42
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.
Holger I
Holger I
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 78
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
h.mueller-978167
h.mueller-978167
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 42
Holger -

Ad 1) - oops. Was late. Just imagine the parentheses are gone ...
Ad 2) Thx!
Ad 3) That would be interesting!

Regards
Harald M.
h.mueller-978167
h.mueller-978167
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 42
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 :-(.
h.sackmann
h.sackmann
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 86
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search