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

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.

Environment: Cognos Report Studio 8.2, SQL Server Cube 2005 Suppress zero rows and columns for crosstab reports built in report studio over SQL server cube can be quite a bit of pain, due to the fact that there is no in-built suppression facility. You might be tempted to use the Suppression option from Query properties, but will be disappointed to know that it works only for SAP. So, in order to suppress the rows or columns you can use following. Solution: Assume that you are showing LOCATIONS on rows, and MONTHS on columns. Your measure is UNIT SOLD. This is the easiest example I can think of. You simply need to modify the rows and columns expression to below: filter([myCube].[Location].[Location Names],([myCube].[Measures].[Units Sold])>0) filter([myCube].[Date].[Months],([myCube].[Measures].[Units Sold])>0) So Cognos will populate the rows and columns with only those members whose 'Units Sold' is not zero. If your measure can have negative values too, for example: Rev