Business Intelligence, Analytics and Cognos

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