Sales facts vs sales goals & calls

I did a quick search of the forums and did not see this, but it seems a rather common scenario so I apologize if I am being redundant.

I have a primary sales fact table that lists sales as line items from an order, pretty common arrangement. There are a few wrinkles but they are not pertinent to my question.

I also have a sales goals table for all reps that is grained at the month, i.e. each sales rep has monthly goals, that change monthly.

Additionally I have what is basically an event based, factless, fact table that records the number of calls a rep takes and makes.

I am having trouble reconciling the dimensional design that will allow me to report on the fairly obvious things such as sales against goal, number of calls per sale. Is the best approach to make each of these facts the center of a separate star and relate them together at the reporting level. I have not pulled data from multiple stars into a single report, but I imagine I can figure it out. Or, should I try to stuff all this fact information into a single star with essentially 3 fact tables. If so, how do I resolve the grain issues daily vs monthly for sales vs. goals?

Thanks for all help and suggestions.

Combining data from different stars is a matter of joining (or a union of) individual queries against each star. Each star query aggregates the data in the star to the same level of detail (grain) as the other individual queries, then combined on the common dimensions.

Some prefer to use UNION ALL for this as it easily allows for missing facts in other tables and sometimes performs better than joins. For unions, each query would return zero for measures the particular fact table does not provide so that all columns line up properly. You would then summarize the unioned set.

If performance is an issue, you can always materialize the data monthly into an aggregate fact table.

Thanks for your response.

If I understand, you are suggesting that I make 2 stars, one for the sales, and one for the goals and then join the queries, presumably on the Sales ID number to get something like sales against goal. I can keep the calls as an event based fact where I count the events and join that to the Employee (Sales rep) table in the sales star.

Thanks again.

Yes, but assuming sales ID identifies and individual sale, you would not include it in the query since you are dealing with monthly goals. If sales ID identifies a type of sale and you have goals based on type of sale, then it can be used.

In other words, if the dimensions of the sales goals are month and sales person, the sales facts must be summarized by month and sales person to achieve the same grain. You can then combine the summarized sales facts with the goals.

Oops. I can see how my reply of Sales ID would be confusing. Nomenclature & taxonomy are important. In this case Sales ID was referring to a sales rep. The sales rep makes the sale, and the sales rep has a goal, so joining on sales rep between the stars seems the choice to make. Thanks very much for your helpful response.

Hi guys,

I have a question that is related to goals and how to compare measure facts vs. goals (an extension to the original question):

What if the goals come in two formats:
1. System defined goals, e.g. Rep's sales target, i.e.a standard goal
2. User defined goals, e.g. Formula that may be defined by the user

In my source system I have a single table that holds these goals (both system and user defined). From the initial problem description for this topic I understand that your goals are stored in a fact table (monthly period snapshot?).

My question is how should/could one handle both system and user defined goals?
I can't see the structure of the fact table changing each time a new user defined goal is defined.Should the goal definition be a dimension containing both the system and user defined goals and the fact simply has an FK to the dimension?

Furthermore what if the source system table contains goals for a sales goal for a suburb and for a region (2 different levels). The suburb belongs to a region and it has been modelled as such in the dimension table. The geographical dimension contains both suburb and region - a hierarchy.
Now the fact table should probably not have 2 grains, suburb and region and we probably dont want to repeat the region goal for each suburb that falls into a region. We could separate the fact into 2 fact tables at the different grains BUT then I stumble on how to reference effectively the same geographical dimension. I have only one primary key to the dimension. should the same foreign key be used in both fact tables? Should the foreign key to dimension relationship be constrained by the attribute that it is actually related to, i.e. suburb Id or region Id?

I don't consider this different grains... if you called the dimension "geography", would it make things simpler? A suburb and a region are members of Geography, have a hierarchical relationship and have facts related to them. If you had an employee dimension with an organizational hiearchy, would you create separate fact tables for "workers" and "managers"? I am assuming here as well that the regional goals are not a simple sum of the suburban goals... So what you really have is a single dimension, a hierarchy and a fact table with semi-additive measures.

As far as the goals thing, a Goal dimension makes sense. It will allow you to accomodate whatever happens without messing with the schema.

Thank you for the help, and yes the regional goals are not a simple sum of the suburban goals.


I just want to make sure that I am on the same page as you:

1. Have only a single dimension "Geography"
2. This dimension has a hierarchy suburb to region
3. Have only a single fact table
4. Have additive and semi-additive measures for the suburban and regional goals respectively

E.G.

GOAL_DEFINITION dimension

ID NAME UNIT
1 SALES_GOAL $


Geography dimension
ID SUBURB REGION
1 NEWLANDS WESTERN CAPE
2 RONDEBOSCH WESTERN CAPE


FACT table

GOAL_DEF_ID GEOGRAPHY_ID SUBURBAN_GOAL REGIONAL_GOAL
1 1 10000 50000
1 2 15000 50000


Is this what you are suggesting? The geography_Id links to the singular dimension that holds the hierarchy of suburb and region. The suburban goal makes sense becasue it is at the level of the geography dimension - and this measure is additive. The regional goal is repeated across suburbs and it is semi-additive.

OR, do you mean that the fact looks as follows:

FACT table

GOAL_DEF_ID GEOGRAPHY_ID GOAL
1 1 10000
1 1 50000
1 2 15000

The problem with the above is that all my keys would be the same and I would get an integrity violation on my fact table.

No, you would have a 3rd row in your geography dimension for the region. Goals for that region would reference that row. Add a natural key column to contain a concatenation of type (suburb or region) and either suburb code or region code so you can locate the correct dimension row depending on what you are loading.

Great, now I understand.

Thank you.