Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Transactional schema to dimensional modelling

3 posters

Go down

Transactional schema to dimensional modelling Empty Transactional schema to dimensional modelling

Post  bstr 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

Back to top Go down

Transactional schema to dimensional modelling Empty Re: Transactional schema to dimensional modelling

Post  ngalemmo 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?
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Transactional schema to dimensional modelling Empty Re: Transactional schema to dimensional modelling

Post  bstr 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

Back to top Go down

Transactional schema to dimensional modelling Empty Re: Transactional schema to dimensional modelling

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Transactional schema to dimensional modelling Empty Re: Transactional schema to dimensional modelling

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Transactional schema to dimensional modelling Empty Re: Transactional schema to dimensional modelling

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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