Transactional schema to dimensional modelling

View previous topic View next topic Go down

Transactional schema to dimensional modelling

Post  bstr on Thu Apr 01, 2010 6:22 pm

I am in the process converting a transactional schema to dimensional modeling for reporting solution using business objects. I have a lot of component entities and classification entities which have a one to many relationships. When I convert them to dimensional modeling I can think of two approaches.

Approach 1: Combine them into one single dimension table.
Approach 2: Keep them as individual tables.

When I follow Approach 1 there is going to be data redundancy in the dimension table. When I follow approach 2 I have to snowflake.

I am in dilemma of which approach is best and what are the pros and cons. Appreciate your valued thoughts on this and the best approach which would be advantageous while using business objects.

Thanks.

bstr

Posts : 3
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Transactional schema to dimensional modelling

Post  ngalemmo on Thu Apr 01, 2010 6:38 pm

It would help if there was a bit more information...

What kind of transactions? What is the nature of the many-to-many relationship? Have you considered more than one dimension table (not snowflake)?

Why would you think you would have a snowflake?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Transactional schema to dimensional modelling

Post  bstr on Thu Apr 01, 2010 6:55 pm

Thanks for your response ngalemmo. The transactions are purchase transactions and to give you an little more detail. I have companies and group companies data in two seperate tables. The relation between group company and company is one-to-many. I cannot keep this as two seperate dimension tables because the reporting scenario is such they also want to report based on dimension tables. So if I only have joins between the company to fact table and group company to fact table, there will not be any join between the company and group company table. So I would not be able to pull a report on what are the group companies for a company.

Because of this I thought to make it a Snowflake schema. Please let me know your thoughts on the best approach in this situation.

Thanks.

bstr

Posts : 3
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Transactional schema to dimensional modelling

Post  BoxesAndLines on Thu Apr 01, 2010 10:42 pm

Generally speaking, I avoid snowflaking if at all possible. The amount of redundancy in the dimension should be nominal compared to the fact table volumetrics.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Transactional schema to dimensional modelling

Post  ngalemmo on Fri Apr 02, 2010 12:04 pm

What you have is a company dimension with a hierarchy. Since there are only two levels, the easiest thing to do would be to carry parent company attributes in the dimension table as well. For rows representing parent companies, you could either leave those attributes blank, or, to make reporting easier, populate them with duplicate information about that parent. This will allow you to report by either company or parent company.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Transactional schema to dimensional modelling

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