Business Intelligence, Analytics and Cognos

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...

Continuing to the business problem mentioned in last post, consider a retail firm. They sell many products, one of it is Umbrella. According to business owner, Umbrella falls under two Product Groups: Fashion and Seasonal. So they want to capture the Umbrella sales under both Product Groups. Now, say we create below schema to hold the data. Sales Invoice Prod_ID QTY 1 1 2 2 1 1 3 1 4 4 2 3 PRODUCT Prod _ID Prod_Name Prod_Group 1 Umbrella Fashion 2 Paracetamol Medical Notice we can not add the information that Umbrella also belongs to Seasonal as a new row. (Prod_ID will be primary key and we need one and only one entry in the dimension table. If we add multiple entries, the facts will be double counted) So we are left with a option to create another column. Say, we call it Secondary_Prod_Group. So our table looks like this. PRODUCT Prod _ID Prod_Name Prod_Group Secondary_Prod_Group 1 Umbrella Fashion Seasonal 2 Paracetamol Medical Seasonal 3 Babyspoon Medical Other Now, if business w...