Re: Architecture

Tech-Archive recommends: Fix windows errors by optimizing your registry

Michael,

Let me see if I have the question correct. You have 2 source tables(11 million dimension table rows and 4 million fact table rows?). If that is the case then what dimension has 11 million rows?

What is the source?  Some sources need handling differently
Are the two tables in a database on a different server?
Is this a one off feed?

OR

Do you want to merge the two tables together on the destination from your two sources?

That might become more complicated if your keys clash. Whilst you will be generating new primary key values (Surrogate Key) you will need a point of reference when loading the fact table so will need the old key to refer to.

You could use partitioned views so you would split this 15 million row table based on an attribute say Date. The view unions the partitions back together again and the index optimizer is smart enough to know from where the rows that you want will come.

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_06_17zr.htm




The "combining" idea presented below will hurt you as not only do you have a lot of rows to play with anyway but you do 2 extra queries for every row as well.

The index optimizer will use whatever indexes it chooses unless you override her with an index hint.

Let me know if I have grabbed the wrong meaning from the post and we can revisit my comments.

Allan


"Michael Vardinghus" <michaelvardinghus@xxxxxxxxxxx> wrote in message news:michaelvardinghus@xxxxxxxxxxx:

Getting two tables from one base with 4 mio and 11 mio records.

The 11 mio records are dimension specification on the 4 mio records so I
need to put these 11 mio records alongsid the 4 mio in my fact table.

-- Example table 1
create view trans1 as
select 100 as beløb, 1 as linje
union all
select 200 as beløb, 2 as linje

-- Example table 2
create view trans2 as
select 1 as linje, 'a' as dim1, 'dim1' as type
union all
select 1 as linje, 'b' as dim2, 'dim2' as type
union all
select 2 as linje, 'a' as dim1, 'dim1' as typ
union all
select 2 as linje, 'a' as dim2, 'dim2' as type

-- Example of combining
select a.*, (select dim1 from trans2 b where
b.linje = a.linje and b.type = 'dim1') as dim1,
(select dim1 from trans2 c where c.linje = a.linje and
c.type = 'dim2') as dim2
 from
trans1 a

My question is how to go about this with the best performance - here's one
shot:

1) Pulling the 2 tables into my relational warehouse base
2) An index combines the 2 tables and this index fills up my fact table

My concern is what to do about indexing ? Should I have indexes when filling
up from base 1 ? Or should the be removed when doing so ? Do I need to index
the two tables and the view ?

Perhaps I don't need a view - perhaps a could just use the sentence above
and let index optimizer give me a hint to what to make indexes for ?

Thanx in advance

/Michael 

.

Relevant Pages

  • Re: Architecture
    ... The dimensions table file is large because you have multiple rows in there for each fact table row telling you the dimension key value for that fact table row. ... The view unions the partitions> back together again and the index optimizer is smart enough to know from> where the rows that you want will come. ... >> union all ...
    (microsoft.public.sqlserver.dts)
  • Re: Complement of zero dimensional space
    ... written as the union of n sets of dimension zero. ... The ray as a simpler ... numbers (the reals) we see the traditional definition of dimension ...
    (sci.math)
  • Re: Architecture
    ... and in table 2 there is the dimension specifications for each transaction. ... then afterwards replacing these with surrogate keys. ... > back together again and the index optimizer is smart enough to know from ... >> union all ...
    (microsoft.public.sqlserver.dts)
  • Re: Architecture
    ... Besides delregnskab and sted there is 3-4 other dimension that should be ... > back together again and the index optimizer is smart enough to know from ... >> create view trans1 as ... >> union all ...
    (microsoft.public.sqlserver.dts)
  • Re: Two views on the space and time.
    ... Is it hard to see the fourth dimension. ... There is simultaneous union of space and time ... Is there a medicine for this illness? ... If we don't take this medicine our human society ...
    (sci.physics.relativity)