excluding certain sales data by default

View previous topic View next topic Go down

excluding certain sales data by default

Post  VHF on Tue Aug 17, 2010 6:19 pm

We have a fairly straightfoward Kimball-method star-schema DW for reporting sales data. Mixed in with the regular sales data are facts for intercompany sales (from one wholly owned business entity to another.) Most of the time, users do not want to see intercompany data in their reports—only regular sales.

There is a CustomerType attribute in the customer dimension that indicates that a given customer is an intercompany customer. It is easy to exclude intercompany sales by filtering when DimCustomer.CustomerType <> ‘Intercompany’ when developing canned reports.

We are starting to open up ad hoc access to the DW. Right now, an ad hoc query will include intercompany sales unless the user explicitly filters them out. However, it would be desirable if the default behavior of an ad hoc query did not include intercompany sales. They should only be included if the user explicitly designates that they want to include intercompany sales in their query.

I am trying to figure out whether to try to engineer the desired default behavior into the DW database itself or if it is better handled in the BI layer.

We expect to be rolling out Business Objects Web Intelligence (WebI) in a few weeks as our primary ad hoc tool for end users. I have taken a class in universe design and played with it a bit, but the BObj world is still pretty new to me.

Any thoughts on how best to exclude certain data by default?


Last edited by VHF on Tue Aug 17, 2010 6:22 pm; edited 1 time in total (Reason for editing : typo)

VHF

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

View user profile

Back to top Go down

Re: excluding certain sales data by default

Post  ngalemmo on Tue Aug 17, 2010 6:46 pm

You can force filtering in BOBJ using a couple of different techniques.

One would be to define a self-join on the table in the universe based on a constant. This would affect all users.

Or. you can define a filter which can be forced on all users or can be assigned based on user roles. This approach is the most flexible, since you can have user roles that could see intercompany as well as roles that cannot.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: excluding certain sales data by default

Post  VHF on Wed Aug 18, 2010 9:34 am

I have also been considering handling this in the DW itself rather than in the BI layer. Here is an approach I'm contemplating:

During ETL, in addition to loading the regular sales data and the intercompany sales data, also load offsetting records for the intercompany sales data that would cause intercompany sales to net out to zero. I would identify these records as "SuppressIntercompany".

By default, any ad hoc queries against the DW would not include intercompany sales amounts (because the query would actually include both intercompany sales facts and the offsetting "SuppressIntercompany" facts.)

When a user does want to include intercompany sales in their results, they would explicitly filter out "SuppressIntercompany" records.

I like this approach because it is independent of the BI tool. For example, if we build an SSAS cube and let users access it with Excel we will get the desired behavior. But I'm not sure that inserting the ETL-sythenized "SuppressIntercompany" offsetting records into the DW is a best practice.

Thoughts?

VHF

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

View user profile

Back to top Go down

Re: excluding certain sales data by default

Post  ngalemmo on Wed Aug 18, 2010 11:59 am

I don't see what that would buy you, because you basically have the same issue... in some cases you want to suppress certain fact rows. A simple flag on the customer should suffice.

A simple generic solution would be to have two views of the customer dimension. "All Customers" which includes intercompany and "External Customers" which excludes them.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: excluding certain sales data by default

Post  VHF on Wed Aug 18, 2010 12:24 pm

Along the same lines I'm thinking it would be possible to have two sets of measure objects in the BObj Universe, for example "Gross Sales" and "Gross Sales including intercompany". The "Gross Sales" measure object would filter out intercompany customers.

I want to make sure to provide the desired behavior even if they do an ad hoc query that doesn't involve the customer dimension--for example, a user might look at sales by product segment over time. But they shouldn't see intercompany sales in their results unless they request it--in this case by using the "Gross Sales including intercompany" measure.

VHF

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

View user profile

Back to top Go down

Re: excluding certain sales data by default

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