Design Question

View previous topic View next topic Go down

Design Question

Post  pgadde on Thu Feb 02, 2012 4:07 pm

Hi,

I have a design question and I am hoping I can get help from DW gurus here.

We have a voucher line detail fact which has close to 80 Million records. One of the dimension is vendor dimension. Now, there are close to 35 million transactions in the fact table which are single payment transactions paid to different vendors. All these single payments are represented with a generic vendor called "Single Payment Vendor" in our main ERP table which is the source of our fact. We have a different ERP table which is having the same key structure as our source and contains vendor details for all the 35 million single payment transactions. Our users want to see these single payment vendor details now. I thought of adding these as degenerate dimensions in the fact but chose not to as I have 20+ attributes like names, addresses, etc. Other option that came to mind is to add these single payment vendors to our regular vendor dimension table. But that would be awfully large dimension with 35 million+ records. Please let me know how best I can do this. Thank You!!

-Prakash

pgadde

Posts : 3
Join date : 2012-02-02

View user profile

Back to top Go down

Re: Design Question

Post  ngalemmo on Thu Feb 02, 2012 4:25 pm

Add them to your existing vendor dimension. You may also want to de-dupe that list... it may knock out a few million rows.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Design Question

Post  pgadde on Fri Feb 03, 2012 1:13 pm

Yes. Definitely there is some data cleansing that can be done. Even after that, vendor dimension will be large. I gave it some additional thought and one option is to have this table created neither as a dimension table nor as a fact. Since it is having same subset of keys as my original fact, transform the common keys into SID values and keep vendor related attributes as is. Then onus is on reporting tool to have some drill through functionality to these single payment vendors. Is it going to work? I am sure it is not a perfect dimensional model. But I want to pick the one which is best of the worst. Thanks!!

pgadde

Posts : 3
Join date : 2012-02-02

View user profile

Back to top Go down

Re: Design Question

Post  Jeff Smith on Fri Feb 03, 2012 3:25 pm

What are your concerns about adding the single payment vendors to your existing vendor dimension? Is it purely an issue of size or do the single payment vendors have different attributes and would require fundamentally changing your existig dimension?

I think different solutions would come into play for different concerns.

If it's just a size concern, then it's not really an issue because no matter how you model it, you're going to have a big dimension table some where. If the concern is that a large dimension table will reduce performance for queries on the "normal" vendors, then you could try fragmenting the table or even creating 2 identical looking dimensions, one for normal vendors and another for the single payment vendors, forcing one of the surrogate keys to be negative, and then creating union join (a poor man's fragmented table).

I think the correct design depends on concerns and how the data will be used.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Design Question

Post  pgadde on Mon Feb 06, 2012 10:27 am

Hi Jeff,

My concern is regarding performance only. I am a little bit worried that it would effect performance if I add those single payment vendors into my regular vendor dimension.


pgadde

Posts : 3
Join date : 2012-02-02

View user profile

Back to top Go down

Re: Design Question

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum