How to Create and Use Restricted Key Figures in Queries in SAP BW/4HANA for Flexible Reporting
SAP BW/4HANA is a next-generation data warehouse solution that enables you to access and analyze data from various sources in real time. Queries are the main way to create reports and dashboards based on the data stored in SAP BW/4HANA. Key figures are one of the essential components of queries, as they provide the values that are reported on and aggregated. However, sometimes you may want to restrict the key figures by applying filters on one or more characteristics, such as region, product, or customer. This is where restricted key figures come in handy. In this blog post, we will explain what restricted key figures are, how to create and use them in queries, and how to optimize them for better performance and quality.
What are Restricted Key Figures in SAP BW/4HANA?
Restricted key figures are key figures that are restricted by one or more characteristic selections. For example, you can create a restricted key figure that shows the sales revenue for a specific region or product category. Restricted key figures can be derived from basic key figures, calculated key figures, or other restricted key figures. Restricted key figures can have different types, such as amount, quantity, number, integer, date, or time. Restricted key figures can also have different properties, such as aggregation type, exception aggregation, currency, unit, or scaling factor.
Restricted key figures are defined as reusable components in SAP BW/4HANA and can be assigned to InfoProviders, such as Advanced DSOs, Composite Providers, or Open ODS Views. Restricted key figures can also be created as local objects within queries using the Eclipse-based query modeling tool.
How to Create and Use Restricted Key Figures in Queries in SAP BW/4HANA?
To create and use restricted key figures in queries in SAP BW/4HANA, you need to use the Eclipse-based query modeling tool. This tool allows you to create, edit, and preview queries in a user-friendly and integrated environment. To create a restricted key figure using the tool, you need to follow these steps:
- Choose the New Restricted Key Figure option from the context menu of the Key Figures folder in the InfoProvider tab. You can also create a restricted key figure as a copy of an existing restricted key figure by choosing the Copy To option.
- Enter a unique technical name and a description for your restricted key figure. You can also add your restricted key figure to your local favorites by choosing the Add to Favorites option.
- Select the key figure that you want to restrict from the InfoProvider tab. You can search for the technical name or a corresponding search term. The system will display a list of matching items for you to choose from.
- Choose Edit. The system will open the Change Restricted Key Figure dialog box.
- Drag and drop one or more characteristics from the InfoProvider tab to the Details of Selection area of the dialog box. You can also use variables instead of characteristic values by choosing Select Variable.
- Choose OK. The system will create the new restricted key figure and display its properties in the Properties tab.
To use a restricted key figure in a query, you need to follow these steps:
- Choose the New BW Query option from the context menu of a BW project or an InfoProvider in the Project Explorer view. You can also create a query as a copy of an existing query by choosing the Copy To option.
- Enter a unique technical name and a description for your query. You can also add your query to your local favorites by choosing the Add to Favorites option.
- Select the InfoProvider that you want to use as the data source for your query. You can search for the technical name or a corresponding search term. The system will display a list of matching items for you to choose from.
- Choose Finish. The system will open the new query in the Eclipse Editor.
- Go to the Sheet Definition tab and drag and drop your restricted key figure from the InfoProvider tab or from the Reusable Components tab to the rows or columns area of your query layout.
- Go to the General tab and configure the properties of your restricted key figure, such as aggregation type, exception aggregation, currency, unit, scaling factor, etc.
- Go to the Preview tab and see how your query will look like when executed. You can apply drilldowns, filters, sorting, formatting, charts, etc. to your query result.
- Go to the Query Monitor tab and analyze the performance and quality of your query. You can view the SQL statement, execution plan, runtime statistics, memory consumption, warnings, errors, recommendations, and best practices of your query.
How to Optimize Restricted Key Figures in Queries in SAP BW/4HANA?
To optimize restricted key figures in queries in SAP BW/4HANA, you need to follow some best practices and recommendations that can improve the performance and quality of your queries. Some of these best practices are:
- Use appropriate key figure types and data types that match your reporting requirements and avoid unnecessary conversions or calculations.
- Use appropriate aggregation types and exception aggregations that match your reporting logic and avoid unnecessary aggregations or calculations.
- Use fixed currencies or units for key figures whenever possible and avoid variable currencies or units that require additional lookups or conversions.
- Use scaling factors for key figures whenever possible and avoid large numbers that can cause overflow or rounding errors.
- Use restricted key figures sparingly and only when necessary and avoid complex filters or expressions that can cause performance issues or errors.
- Use HANA views for key figures whenever possible and leverage the power of SAP HANA for faster processing and analysis.
Disclaimer: This content is generated by AI.