Are you maintaining too much queries? Do you have many repetitive tasks? Maybe your queries are not flexible enough. In this post, I want to give you a checklist. This checklist shows you the places where you can check the source of your problem.
The main cause of rigidity
Inflexible queries use constant values in several places. This is their common characteristic. To improve your queries, you need to hunt after all of the constants as a first step. So where can you start?
Checkpoint 1: Restrictions
The most common place for the constants is the characteristic restrictions. In my example, I filtered the calendar year with the value 2015. Then the month from January to November.
Don't forget the restricted key figures. They also use restrictions.
Here I have selected November in my key figures.
The fixed values 2015, January and November increases the maintenance. What if somebody wants to run the same query for another time period?
Checkpoint 2: Descriptions
The second place is the description. Did you write constants for the months in the column descriptions? Like me in this example.
If yes, you need to maintain the same piece of information in 2 places. Not only in the restrictions but also in the descriptions. It's getting worse, if we forget to change the descriptions after a change in the filter.
Checkpoint 3: Exceptions
Do you have an exception? My exception colors a column. It’s called Books below the plan. The column will be red, if it drops below zero. And here is another constant. The value zero.
What if one of the managers wants to evaluate this column based on another benchmark? Than you need to change the query.
Checkpoint 4: Conditions
In my example, I have also created a condition. This condition filters the rows with the same logic. And the zero appears again.
What if you want to align this condition with the exception? Then you need to change the zero on 2 places. What if you forget it?
The Problem with Inflexible Queries
I saw a common "solution" for such a problem. Create as many copy of your query as you need. You can create several query variants. With different selections, descriptions and condition parameters. But this is a wrong way.
Having many replicas of the same thing cause serious problems.
- First of all, your queries get complex.
- You need to keep in mind the list of the similar queries.
- If you need a change, you need to think of all of them.
- You have to analyze the possible effects of your change. Just on many queries.
- And finally you have to change them. All of them.
This will cost you a significant time. The time spent also on small changes increases rapidly. And you will have less time for building new functionality.
The Solution: Use Variables
So what is the solution? Just eliminate the cause of this problem. The cause is the multiple versions of the same query. So try to keep only a single query version. Analyze the reason before you want to copy a query. Do you want to copy a query just because you want to change a constant? Replace this constant. Replace it with a variable. All in all, you can keep the number of the queries low with the variables. And the time spent on their change. That’s why we work with variables.
Do you have enough variables in your queries? This is the first question, you need to answer. Check the structure of your queries. Go to the places that I have showed you in this post and collect the fixed values. You just need to replace them with a variable.
Ok. I gave you a solution. Replace the constants with query variables.
But it's time to clarify several questions:
What is a variable? And what are the main usage scenarios for query variables?
You can find the answer in my next post in this topic about Variables: The flexible placeholders in your SAP BW queries. Happy reading!
Do you want to practice?
I plan to launch soon a new SAP BW training in email, "Practice the SAP BW query variables". With this SAP BW training, I want to help you in getting practical experience with the variables.
What will you get during the SAP BW training?
I want to show you the basic techniques how to create the most important query variables.
You will get...
- an exercise book with step by step instructions,
- real life examples for creating different variables,
- and solutions with screenshots from the system.
Will be the SAP BW training FREE?
Yes. It will be free.
But almost free...
If you feel, my work helped you in understanding the variables, I only want to ask you to share this post with your friends.
Do you want to attend?
Subscribe to the "Practice the SAP BW query variables SAP BW training"
What do you think? Is this training a good idea? If yes and you want to attend on this training, then you just need to opt in here. Just click on the button below. I will inform you about the details soon via email. Stay tuned.