Precalculation has been used in BI mainly in the form of Information Broadcasting or Reporting agent features (Exception Reporting/Printing/Filling OLAP Cache..).
This blog shows how Precalculation can be used in firing Subqueries in the Background from the Main query
– To show the sales for exclusive customers in the query result for a given period entered by the user. The definition for exclusive customer is what makes the query tricky.
- A customer will be an exclusive customer if it records sales for the same number of weeks in a given Current Year Fiscal Period/year and Last year Fiscal period/Year
To summarize the above statement-
If I have a customer who has recorded sale in all the 4 weeks of Feb 2008 (Current Year) and Feb 2007 (Last Year), the customer will be an exclusive customer.
When the user runs the query, the user will only enter the Fiscal Period Year of Current year. The query should be able to implicitly determine the list of exclusive customers and display the analytics for them.
In the normal OLTP system,the query will follow the following passes
Pass 1 – Calculate the number of weeks for the entered Fiscal Period/Year
Pass 2 – List the customers which have recorded sales in the current year for the number of weeks calculated in pass 1
Pass 3 – Calculate the number of weeks in the last year Fiscal Period/Year
Pass 4 – List the customers which have recorded sales in the last year for the number of weeks calculated in pass 3
Pass 5 – Find the subset of Pass 2 and Pass 3
Pass 6 – Display the analytics for subset calculated in Pass 5
All these passes will involve firing subqueries from the main query.
We start by creating two counter infoobjects one for Last Year and the other one for Current Year.
– ZLYCOU (Last Year Counter)
– ZCYCOU (Current Year Counter)
Daily Sales ODS – This ODS will have information about daily sales at document level
Weekly Sales cube – This cube will contain data from the daily sales ODS at week granularity
Exclusive Customer Metadata – This ODS will get the data from Weekly Sales Cube on daily basis and the counters will be updated in the ODS in update rules. The ODS will be at Week/Customer/Fiscal period/Fiscal Variant granularity with the Counters being in the data part.
The Last Year counter will be updated in case the sales have occured in the last Fiscal Year. The Current Year counter willbe updated if the sales have occured in the current financial Year.
DataSet in the Weekly Sales Cube
DataSet in Exclusive Customers Metadata ODS
In the above data set, the customer 109450 is exclusive customer since it has recorded sales in all the five weeks of Period 07/2008 and 07/2007.
The customer 109647 is not an exclusive customer since it has not recorded sales in all the five weeks for period 07/2008 and 07/2007.
During the change of the fiscal year we will realign the current year counters to last year counter
We will create two queries – Main Query and Precalculated Query. The precalculated query will be fired from Main Query and will act as a subquery to main query. The main query will display the analytics for exclusive customers and Precalculated query will calculate the list of exclusive customers.
The Main Query contains User entry variable for Fiscal Period/Year (0I_FYPER).
We also have a precalculated variable created ‘ZLIKE_DO’ on the Customer characteristic for determining the list of exclusive customers.
The precalculated query will perform the following operations
– Retrieve the List of all the customers and their counters for the given Periods
– Evaluate the number of weeks lying within the entered Fiscal Period entered
– Equate value of aggregated counters to number of weeks Calculated
Retrieve the List of all the customers and their counters for the given Periods
Same variable must be used in the precalculated query to pass the value of Fiscal Period/Year value user entered in the Main query. In our case it is 0I_FYPER.
To calculate the previous year Fiscal period/Year we create a customer Exit variable ‘ZLYPER’. If the user enters 07/2008 in the Variable 0I_FYPER in the main Query, the Fiscal period/year values in the precalculated query will be 07/2008 and 07/2007.
This step will bring the resultset with all the customers for the given Fiscal period/Year and their respective counter values showing when the sales occurred.
Evaluate the number of weeks lying within the entered Fiscal Period entered
The number of weeks for a particular fiscal period/year can differ and depend on the Fiscal Year Variant.
This will be calculated dynamically using the formula variable ‘ZCOUWK’ of the customer exit processing type. The formula variable will take the Fiscal Period/Year value entered in the variable ‘0I_FYPER’ and calculate the number of weeks.
Equate value of aggregated counters to number of weeks Calculated
To equate the number of weeks for last year Fiscal Period and Fiscal period/Year calculated through formula variable ‘ZCOUWK’, we will make use of conditions. These conditions will be placed on the counter Key figures.
Whenever Condition 1 and Condition 2 become true, we will get the exclusive customer since they will filter out all the customers which have recorded sales for required number of weeks (calculated in formula variable ‘ZCOUWK’) to number of weeks sales was actually recorded in the counters (Last Year and Current Year)
We can make use of precalculation to fire multiple subqueries in the background though our underlying data model can change according to requirement.
BI offers us powerful features which we need to tweak and find some workarounds for the requirements.