excluding certain sales data by default

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?

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.

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?

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.

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.