Separation of data by process?

View previous topic View next topic Go down

Separation of data by process?

Post  careid on Mon Apr 04, 2011 9:49 am

How do you pick which is the better of two evils? I have been asked to develop reporting solutions for a group of companies belonging to a single organization. There is a point however where legitimately each companies information separates by function and process. Should I create separate fact tables for each company and one for global reporting (seven in total) or one fact table and use NA for companies where this value does not exist?

Help!!!!!

careid

Posts : 1
Join date : 2011-04-04

View user profile

Back to top Go down

Re: Separation of data by process?

Post  VHF on Mon Apr 04, 2011 10:06 am

As you identify the business process modeled by each fact table (just before identifying the grain of each fact table), it should become more clear which company or comapnies within the organization to which each process applies and which measures apply to each company.

It is certainly OK to have some fact records that point to one or more dimensions with an 'NA' value, but you want to be careful about having numeric measures that doesn't always apply--these would typically need to contain a 0 or NULL which might throw off certain aggregate functions such as AVG.

It would not be unreasonable to end up with a solution that includes both company-specific fact tables (to model company-specific processes) and enterprise-wide fact tables to support enterprise-level roll-up reporting.






Last edited by VHF on Mon Apr 04, 2011 10:06 am; edited 1 time in total (Reason for editing : clarification)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Separation of data by process?

Post  ngalemmo on Mon Apr 04, 2011 12:56 pm

There isn't a right answer to this... it all depends on the businesses, their differences and the desire to consolidate the information. As VHF pointed out, incomplete information isn't unusual and can be accomodated. Normally their is enough common ground where consolidation it worthwhile, but if they are truly disparate businessess (say, one is a bank and the other a retail chain) then deal with each separately and consolidate what you can in aggregates.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Keep it simple

Post  AndreLinssen on Sat May 07, 2011 10:50 am

Interesting problem, and a familiar one too.
I would suggest you don't mix things that have no relation in the outside world (other then that they do belong to the same organization).
Besides that, think about the costs of making changes in your data warehouse in the long run.
If you have separate fact tables, one change in that fact table will only affect that single company. So, you only have to test that part.
If you have one fact table for all companies, every change will force you to test if that fact table still works for the other companies. In the worst case scenario, a change for company A will result in an error in a report for company B. In that case, company B won't be a very happy customer.
So I would suggest you use different fact tables. It might be a bit more work now, but it pays off in the long run.

PS: Using the same dimension for more then one company is ok, especially for dimensions that exist independently of the organization or companies (like: country, address, time). However, if all companies have, for instance, a product code, but with a totally different format, I would not put them in the same dimension. If you really want to put data together, that is unrelated, or not directly related, use a materialized view.
avatar
AndreLinssen

Posts : 6
Join date : 2011-02-15
Age : 51
Location : Netherlands

View user profile http://www.linssen-it.nl

Back to top Go down

Re: Separation of data by process?

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