Invoice dimensional modeling question

View previous topic View next topic Go down

Invoice dimensional modeling question

Post  esreis on Tue Jun 04, 2013 11:16 am

My invoice has a field "description " of type TEXT. I need show this field to the users of my datamart.
The system generates approximately 30000 invoices per month, and each invoice has a description field.
The descriptions do not have a standard. Each invoice can have a different description.

How can i do a modeling. put the field in the fact table or in dimension ?

Thanks
Eduardo

esreis

Posts : 3
Join date : 2013-04-29

View user profile

Back to top Go down

Re: Invoice dimensional modeling question

Post  wonka on Wed Jun 05, 2013 11:40 am

As there is no standardization of this field, I would simply include in the fact and be done.

wonka

Posts : 13
Join date : 2011-08-10

View user profile

Back to top Go down

Re: Invoice dimensional modeling question

Post  LAndrews on Wed Jun 05, 2013 12:37 pm

I'd put it in its own table, not in the fact.

Even though its a 1:1 relationship with the invoice, I'd rather not hamper the performance of the fact by increasing the record size significantly.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Invoice dimensional modeling question

Post  PrasannaGanduri on Tue Jun 11, 2013 4:56 am

I would suggest you have in the Invoice Dimension.

This design is of a Fact that is at a Transactional level which needs aggregation on Invoice info. However, You also create a dimension using the same table to hold all these details at Invoice level.

In short,
1. Have an Invoice Dimension with Invoice Number as Primary Key with Invoice Desc or any other Text Descriptions at the transactional granularity of Invoice.
2. Have a fact table using the same table including the Invoice Number,Transaction Date any other numerical/aggregatable fields that should be part of fact

PrasannaGanduri

Posts : 2
Join date : 2013-06-10

View user profile

Back to top Go down

Re: Invoice dimensional modeling question

Post  ngalemmo on Tue Jun 11, 2013 7:13 pm

It is a matter of performance.

In general, you do not want to store large text columns in a fact table because it makes the fact table much larger. The larger row sizes means fewer rows are fetched when reading, which translates to more reads, and longer query times.

But specific database implementations do alleviate some or all of the downsides based on how they physically store such columns.

In SQL Server, for example, if you define a column as a BLOB or MEMO, it stores the value in a separate structure and carries a reference to it in the main table. This is almost the same as creating a separate dimension. This allows queries that do not reference the text to execute as it would if the text was not there. Only queries that reference the column would access the value data stored outside the table itself.

Or, in the case of a column oriented data base, such as Vertica or Sybase IQ, it doesn't matter as it only stores unique values anyway.

But, if you storing it as a VARCHAR column, then it will make the fact table larger than it should be and will impact query performance. It is usually better to place it in its own dimension. If you are dealing with a very large number of invoices you may consider a dimension that only holds unique comments. Odds are a large portion of the comments will be blank, and there will probably a relatively small collection of common comments that account for another large portion of invoices. People do tend to be creatures of habit and are usually not terribly inventive when writing invoice comments.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Invoice dimensional modeling 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