Having Problem with Cross Join to fill Junk Dimension

Last Post 12 Nov 2008 11:23 AM by bryanmurtha. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
bryanmurtha
New Member
New Member

--
12 Nov 2008 07:57 AM
Hello All,

I have 70 true false fields in a fact table which has 100 million + rows. I want to refactor this into a dimension that is a matrix of all possible values to speed query response. At first I tried this:

select
c.COLUMN_NAME
,'N' as Value
into _FalseBitFields
from
INFORMATION_SCHEMA.COLUMNS c
where (c.TABLE_NAME = 'MyTable')
and (c.DATA_TYPE = 'varchar' or c.DATA_TYPE = 'char')
and (c.CHARACTER_MAXIMUM_LENGTH = 1)
go
select
c.COLUMN_NAME
,'Y' as Value
into _TrueBitFields
from
INFORMATION_SCHEMA.COLUMNS c
where (c.TABLE_NAME = 'MyTable')
and (c.DATA_TYPE = 'varchar' or c.DATA_TYPE = 'char')
and (c.CHARACTER_MAXIMUM_LENGTH = 1)
go
select *
from _TrueBitFields t
cross apply _FalseBitFields f

That gave me the correct number of possibilities but I got Column, Value. Which I wanted a "row" of columns and the matrix of possible Y/N values.

Than I tried to build the table I need this way:

/* Construct the table */
select ',' + t.COLUMN_NAME + ' CHAR(1) NULL'
from _TrueBitFields t
order by t.COLUMN_NAME


select ',' + t.COLUMN_NAME + ' CHAR(1) NULL'
from _FalseBitFields t
order by t.COLUMN_NAME

/* populate each with one row of all true or false values */
select ',' + CHAR(39) + 'Y' + CHAR(39)
from _TrueBitFields t
order by t.COLUMN_NAME

select ',' + CHAR(39) + 'N' + CHAR(39)
from _FalseBitFields t
order by t.COLUMN_NAME

select *
from _TrueBitFields cross join _FalseBitFields

but I only get four rows, I want all possible combinations of all column values, what am I doing wrong?
nosepicker
New Member
New Member

--
12 Nov 2008 09:10 AM
Can you provide a small sample of what your table looks like? Perhaps just 10 columns and 10 rows of sample data, and then what you would like your output to be?
bryanmurtha
New Member
New Member

--
12 Nov 2008 11:23 AM
Hey Nose,

Ok so here is 10 fields. The expected output is below

CREATE TABLE dbo._BitFieldsFalse2
(
ACTIVE_FOR_RENT CHAR(1) NULL
,ACTIVE_FOR_SALE CHAR(1) NULL
,ACTUAL_OR_ESTIMATED_CODE CHAR(1) NULL
,AIR CHAR(1) NULL
,AIR_RIGHTS_YN CHAR(1) NULL
,AVAILABLE CHAR(1) NULL
,B_GARAGE_YN CHAR(1) NULL
,B_GARDEN_YN CHAR(1) NULL
,B_PETS_YN CHAR(1) NULL
,B_ROOFDECK_YN CHAR(1) NULL
)
CREATE TABLE dbo._BitFieldsTrue2
(
ACTIVE_FOR_RENT CHAR(1) NULL
,ACTIVE_FOR_SALE CHAR(1) NULL
,ACTUAL_OR_ESTIMATED_CODE CHAR(1) NULL
,AIR CHAR(1) NULL
,AIR_RIGHTS_YN CHAR(1) NULL
,AVAILABLE CHAR(1) NULL
,B_GARAGE_YN CHAR(1) NULL
,B_GARDEN_YN CHAR(1) NULL
,B_PETS_YN CHAR(1) NULL
,B_ROOFDECK_YN CHAR(1) NULL
)

INSERT INTO _BitFieldsFalse2
VALUES (
'F'
, 'F'
, 'F'
, 'F'
, 'F'
, 'F'
, 'F'
, 'F'
, 'F'
, 'F')

INSERT INTO _BitFieldsTrue2
VALUES (
'T'
, 'T'
, 'T'
, 'T'
, 'T'
, 'T'
, 'T'
, 'T'
, 'T'
, 'T')


select *
from _BitFieldsTrue2
cross join _BitFieldsFalse2

/* Essential output sould be every possible combination of true and false
Which is what I thought the cross product should produce */

T T T T T T T T T T
F T T T T T T T T T
F F T T T T T T T T
F F F T T T T T T T
F F F F F T T T T T
F F F F F F T T T T
F F F F F F F T T T
F F F F F F F F T T
F F F F F F F F F T
F F F F F F F F F F
SwePeso
New Member
New Member

--
12 Nov 2008 12:44 PM
10 bit columns has 1024 combinations.
bryanmurtha
New Member
New Member

--
13 Nov 2008 09:54 AM
Yes I know, but it's better to have a dimension table 70^2 combinations rather than have those fields all over the fact table taking up space as well as all the table scans that happen searching for those values.


Acceptable Use Policy
---