0

I have a set of OLAP cubes, in the form of a snow-flake schema, each representing one factory.

I have three concepts that for some factories clearly behave as 3 dimensions, and for other factories clearly behave as 2 dimensions.

The concepts are always the same: "products", "sales agents" and "customers".

But for some cases, I doubt if I should model it as a purely 3 dimensional cube or I should play around with some tweak or trick with a 2 dimensional cube.

Cases A and B are the ones that are clear for me, and Case C is the one that generates my wonderings.

CASE A: Clearly a 3 dimensional cube

Any agent can sell any product to any company. Several agents are resposible together for the same set of customers.

I model this case as this:

enter image description here

CASE B: Clearly a 2 dimensional cube

Every agent is 'responsible' for a portfolio of customers, and he can sell any product but only to his customers. The analysis is made on 'current responsability on the portfolio' so if an agent leaves the company, all his customers are reassigned to a new agent and the customer uniquely belongs to the new agent.

I model this case as this:

enter image description here

CASE C: My doubts

A customer may have been assigned a single agent or a set of several agents each one being responsible for a ProductCategory.

For example:

  • Alice manages TablesAndWoods ltd and GreenForest ltd.
  • Bob manages Chairs ltd and FastWheels ltd.
  • Carol manages Forniture ltd ONLY for ProductType = 'machinery' and also manages FrozenBottles ltd for ANY type of product.
  • Dave also manages Forniture ltd but ONLY for ProductType = 'consumables' and also manages HighCeilings ltd for ANY type of product.

QUESTION:

In this example "Case C":

Are customer and agent independent dimensions because Forniture ltd has relation both to Caroland Dave, so it is a 3D cube?

Or it is a 2D cube, where agent is not an independent dimension, but it is an aggregator of customer "conditioned" somehow by the ProductCategory product aggregator?

I would like to see how would you model this. Thanks in advance.

Xavi Montero
  • 9,239
  • 7
  • 57
  • 79

1 Answers1

2

Here is how I would model it:

Your fact table is Sales.

Your dimensions are (probably) Date, Product, Customer and Agent. This is closest to your Case A.

Collapse your snowflake (white entities) into the dimensions. The presence of these entities suggest that you should consider whether type-2 slowly changing dimensions are needed for at-time analysis.

Consider a Bridge table to capture the many-to-many relationship between Agent and Product.

Ron Dunn
  • 2,971
  • 20
  • 27
  • I don't need to preserve the history, and even for the example Date does not appear there. So even Type1 is okey. I may consider "collapsing". The problem is that I don't know how to model that "bridge table" you mention in an OLAP-Cube concept. I use bridge tables for many-to-many in normal relational databases, but I don't know how should I use them in the "arms" of the star or the snowflake so they are useful for an OLAP approach. – Xavi Montero Apr 25 '15 at 12:02
  • 1
    The best resource on dimensional modelling techniques is the Kimball forum. Rather than summarising someone else's work, here is a link that will take you to the information you need. [link]http://forum.kimballgroup.com/search?search_keywords=bridge+two+dimensions – Ron Dunn Apr 25 '15 at 22:29
  • 1
    Also, you might want to re-think your need for dates. That is how you will manage the change in relationship between Agent and Customer. It is almost inconceivable that a dimensional model would not contain a date table, and your dimensions do seem to have attributes that could change over time, altering the rollup of data. Examples are Customers who may change Location, Products which may change Category, and Agents who may change Manager. Of course, you know your requirement better than me, but I'd be astonished if your users don't hit you with questions like this once the cube is built. – Ron Dunn Apr 25 '15 at 22:37
  • Hi Ron, thanks for the pointer to the Kimball forum. Didn't know it. Tons of info to read and process. I'll upvote your answer for the tip to the forum. Still awaiting for the final answer where one can see in a glance how to set that specific needed bridge in the presented "Case C". – Xavi Montero Apr 26 '15 at 12:02