Skip to Content

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

 

Scenario

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

 

Data Model

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)

 

Design Objects

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

Cube Snapshot                                                    

DataSet in Exclusive Customers Metadata ODS

Metadata ODS DataSet

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

The Design

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.

Design

 

Main Query

The Main Query contains User entry variable for Fiscal Period/Year (0I_FYPER).

Main Query

We also have a precalculated variable created ‘ZLIKE_DO’ on the Customer characteristic for determining the list of exclusive customers.

Precalculated Variable

 

Precalculated Query

Precalculated Query

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.

Query Comparison

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.

 

Customer Exit Variable For Fiscal Period

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.

Condition Variable

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.

 

Conditions

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)

Conclusion

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.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Pratik Shah
    Shikher,
        This was very informative and adds a lot of value to the precalculated queries feature of BEx reporting. Also the case study was presented in an excellent fashion which was easy to read and understand.

    Regards,
    Pratik Shah
    Deloitte Consulting LLP.

    (0) 
    1. Dirk Herzog
      I also like this case. But the performance of precalculation querys can be a bit weak compared to other querys, especially if the number of lines in the precalculation query is very high.
      (0) 
  2. Dirk Herzog
    Another idea for this would be to use a variable that counts the weeks in the selection. So you define a formula variable by exit that counts the number of weeks. Next you define a variable with a replacement path based on the InfoObject 0CALWEEK and the replacement path ‘Replacement by Attribute’. Then you can choose the attribute that selects 1 as a constant. This gives you the number of weeks in the selection. You can then create a column that subtracts both values and create a condition to select only those values that are 0.
    (0) 
    1. Shikher Verma Post author
      Nice idea Dirk. I also thought about it but if we look at the design, we are making use of precalculated queries and if we calculate counters at OLAP time, it will further effect the performance.

      (0) 
  3. Edward Zawatski
    Many years ago, I also had a complex customer calculation where I needed to show customers that had sales in 10 out of the 12 months of last year, AND 10 out of the 12 months from the prior year. What I did was create a boolean for each of the 24 months to calculate if there was a sale or not. Then I create 2 formulas; one to add up each year. Then I placed a condition on the query to show only customers where both formula fields were 10 or higher. This worked out great and did not require any backend changes or pre-querying. I highly recommend trying this for your design.
    (0) 
  4. John Kurgan
    Many years ago, I also had a complex customer calculation where I needed to show customers that had sales in 10 out of the 12 months of last year, AND 10 out of the 12 months from the prior year. What I did was create a boolean for each of the 24 months to calculate if there was a sale or not. Then I create 2 formulas; one to add up each year. Then I placed a condition on the query to show only customers where both formula fields were 10 or higher. This worked out great and did not require any backend changes or pre-querying. I highly recommend trying this for your design.
    (0) 
  5. Kris Rava
    Excellent case study and presented with useful steps , however the cube shows calday in the screen shot which I think should not be there as cube should agrregate the daya by week only as per the design.

    Also can you please advise more on last step where you are putting condition ? so when we calculate the week for period , we calcuate number of week?

    (0) 

Leave a Reply