Separate Product dimension from Organisation ?

Hi,

Am fairly new to dimensional modelling so this may well be an obvious one..

I am beginning the process for researching and designing our company's first data warehouse.

A lot of our business processes have Product as the lowest level of granularity such as stock trades and portfolio valuations. We seem to have a natural roll up from Product of the following form:

Product -> Broker -> Branch -> Division -> MyCompany (all level)

This means that 1 Broker manages 1 to many Products, a Branch has 1 to many Brokers work there, and a Division contains many Branches. Products are managed by different Brokers over time and Broker Branches are slowly changing over time.

Is it logical to keep Products separate from the 'Organisation' as two distinct dimensions? If so, I presume that I'd just need to have a Surrogate key in the Fact table for both DimProduct and DimOrganisation - I'd just need to deduce at ETL time the DimOrganisation FK for the given day for the given Product? Would I have more flexibility with separate dimensions? Currently I haven't come across any business processes that don't originate from a Product, but that's not to say that there wont be in the future. I don't want to box myself into a corner at the initial design stage e.g.

Separated scenario
DayID, ProductID, OrganisationID, Measure1, Measure2....MeasureN

Non-separated scenario
DayID, ProductID, Measure1, Measure2....MeasureN

Any suggestions are appreciated.

regards,

MrPeds

If product is not a monster dimension (>1 million rows), denormalising the hierarchy into a single product dimension seems to be the best solution. In general, dimension hierarchy relationship should be contained within the dimension instead of being reflected through the fact table unless we are dealing with a monster dimension. Obviously fact table is far bigger than product dimension even considering SCD2 implication and getting the relationship by joining to the fact table is much more costly and less straightforward.

Hang,

Our Products (for the present time at least) will definitely be below 1m rows including any historical attribute changes that we'd want to track. Given this, you have confirmed what I was edging towards in terms of a solution.

It appears that I will simply need to ascertain the appropriate Org ID for the corresponding Product on the given DayID to get the association between the entities.

Thank you,

mrPeds

Hi MrPeds, apply SCD 2 (SCDStartDate, SCDEndDate) on Org ID and other ID's along the hierarchy and you are guaranteed to track all the hierarchical changes. Using SCD date range, the product dimension alone should give you proper version of Org structure at any point of time throughout the whole history.

You seem to be describing a broker hierarchy. What is the nature of the relationship between broker and product? Does a broker exclusively 'own' a product or do other brokers handle the same product? Is the nature of the broker/product relationship important at the time of sale or would another broker assume past sales of a broker who left the organization?

If it is a time of sale thing, then broker simply becomes a dimension to the sales fact. Product is independent. You then have the hierarchy off broker for reporting the facts.

MrPeds wrote:Products are managed by different Brokers over time and Broker Branches are slowly changing over time.

Make sure you find out how your users want to report on this over time so you can do the right type of SCD. In a similar situation, my users always want to report based on the current Product-Broker assignment even when looking at historical sales, so I use SCD Type 1. If your users want to report based the way things were at the time of the sale, then you would need Type 2.

And if they want both you might need to implement Type 1 + Type 2!

Hi ,

In answer to the questions:

Our business is actually a firm of Stock Brokers.

A Broker looks after ("owns") a number of clients (Products). The majority of the time the Broker Buy/Sells stock for the given Product or Products on behalf of customers.

It is important to track over time the portfolio Valuations, Buy/sells, and Commission which stem from a Product. Ownership will pass to different Brokers for a number of reasons such as Brokers leaving the firm, changing Branch, or just to 'shake things up' a bit.

I imagine that we will need to concentrate on Type2, potentially with some attributes being Type 1 but I need to drill down on this requirement as the analysis continues.

MrPeds

After your elaboration on the broker, I think ngalemmo's question on mixing product with org structure is quite valid. It seems to me the broker/org structure do not look like hierarchical attributes of the product dimension, unlike product category and department in retail industry. Brokers are more like sales reps with pre-assinged customers in which a coverage factless fact table may be more suitable.

I'd definitely have separate dimensions for Product, Broker and Org. I think you'll save yourself a lot of heartburn in the end.

Implementing a type1+2 SCD is pretty straightforward as well and it might be worth determining if it's needed at the org and broker levels right from the start.