Business Intelligence, Analytics and Cognos

Okay... So last time we discussed a model with WEIGHT or WEIGHING FACTOR, which helps in distributing the facts (Sales) across multiple Product Groups. It still had limitation on the maximum number of Groups, a Product can be assigned to. So, now consider this scenario. What if we bring the grain down to PRODUCT-PRODUCT GROUP combinations? So, in our example, we treat Umbrella-Fashion as one entity, and Umbrella-Seasonal as another entity..?? This will raise a question - When actual Sales happens, what we know is only that a product called 'Umbrella' is sold!! Here, comes in the Intelligent Modelling. So, we can weave some kind of 'intelligence' at the Source or in ETL to classify the Product, or split the facts across multiple entities. Imagine the whole new world this idea opens! If business agrees, the PRODUCT-PRODUCT GROUP combinations can be exposed to the source system. So the fancy, colourful umbrellas can be connected to Umbrella-Fashion, whereas the basic umbre...

As discussed in previous post, the idea of attaching one product to many product group is fundamentally flawed. In previous example, we have seen that every time there is jump in Umbrella sales, they will be jump in Fashion sales and Seasonal sales. So, this is clearly not going help the trend analysis or intelligent decision making. Also, the schema proposed in previous example has an upper limit on the number of groups a product can belong to. If the business is happy with above limitations, you don't need to read further. However, business also agrees that it is a flaw and they would like to see what options are available - we can a step further. Depending on the nature of the business, we will assign a weighing factor to each product group for each item. This weighing factor (now on referred to as WF) will be less than 1 for each entry, such that the sum of WF for a product equals ONE . In our example, Umbrella is more of a seasonal product. It is also a fashion (because there...

Some times business poses questions that are fundamentally flawed. However, the beauty of dimension modelling is, most times it doesn't allow you to build wrong logic. A typical example is many-to-many relationship between levels . Question for this series: Consider a company where a product where belongs to one or more Product Groups, and a Product Group can have one or more Products. How will you represent that in a dimension model or cube? So, in this series we will discuss various options, their pros and cons, the fundamental flaw in this question and something I call as 'intelligent modelling'. I hope I will get a good audience for this series, and useful comments.