The business scenario is quite common in the BI reporting. Consider a KPI, required to be calculated at a low level of granularity, but the reporting is required at the higher level of granularity. With the default aggregation behavior of the calc view, the constituents of the Calculated KPI might also be aggregated at the reporting granularity level, resulting in incorrect value for the Calculated KPI.
To elaborate more on this, consider the following data model for the Sales Transaction. The transaction data is captured for Product Sold on a given date in a store. The table structure can be defined as follows:
( STORE_ID nvarchar(10), --- Store Id
PROD_ID nvarchar(10), --- Product Id
PROD_CAT nvarchar(10), --- Product Category
PROD_PRICE decimal(10,2), --- Product Price
SOLD_QTY integer, --- Product Quantity Sold in a Sales transaction
TRANS_DATE nvarchar(8), --- Sales Transaction Date
STORE_REG nvarchar(10) --- Region in which the Store is located
);
The business rule could be:
The product price may vary each day, hence the Net Sales need to be calculated for the Product Price on the transaction date and Quantity Sold on the day.
To elaborate more on this, consider the following data model for the Sales Transaction. The transaction data is captured for Product Sold on a given date in a store. The table structure can be defined as follows:
( STORE_ID nvarchar(10), --- Store Id
PROD_ID nvarchar(10), --- Product Id
PROD_CAT nvarchar(10), --- Product Category
PROD_PRICE decimal(10,2), --- Product Price
SOLD_QTY integer, --- Product Quantity Sold in a Sales transaction
TRANS_DATE nvarchar(8), --- Sales Transaction Date
STORE_REG nvarchar(10) --- Region in which the Store is located
);
The business rule could be:
The product price may vary each day, hence the Net Sales need to be calculated for the Product Price on the transaction date and Quantity Sold on the day.
