Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using "constant union" dimension views instead of dimension tables - good/bad idea? Expand / Collapse
Author
Message
Posted Monday, March 7, 2011 9:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 6:57 AM
Points: 43, Visits: 28
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.
Post #1074264
Posted Tuesday, March 8, 2011 12:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:13 AM
Points: 1, Visits: 67
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
Post #1074616
Posted Tuesday, March 8, 2011 5:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 6:57 AM
Points: 43, Visits: 28
Holger -

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

Regards
Harald M.
Post #1074725
Posted Tuesday, March 8, 2011 1:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 6:57 AM
Points: 43, Visits: 28
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 :-(.
Post #1075081
Posted Tuesday, March 4, 2014 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 6:02 AM
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


Post #1547359
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse