bw query management
TRANSCRIPT
-
8/7/2019 BW Query Management
1/20
2007 Wellesley Information Services. All rights reserved.
Unlock the Secrets ofQuery Management
Using SAP NetWeaver BI
Gary NolanTekLink International
1
In This Session
Learn your options for creating and managing queries in
SAP NetWeaverBI
Gain insights into important query development and
performance standards
Get recommendations for establishing and enforcing
query design standards
Learn the best ways to manage and roll out queries
Get guidelines to match complex query requirements
with the right solutions in SAP NetWeaver BI
2
What Well Cover
Development strategy
Publishing strategy
Performance management strategy Security strategy
Wrap-up
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
2/20
3
Develop a Data Management Strategy
Most of query management is about gathering the
proper requirements
Develop a sound methodology to gather and track the
requirements from both:
Top down from the query, to the data sources
Bottom up from the data sources, to the query
Developing a good methodology to gather the query
requirements allows for a sound data model
4
What Should Be in Your Functional Model Document?
Business questions asked and answered by the data
model
History requirements of information
Data sources required to complete model
Volume/system/frequency
Dependencies/constraints/assumptions
Transformation of data required
Frequency and timing
Functional model diagram to show flow and data model
5
Presentation requirements
Audience and distribution of presentation
Security Sign-off
What Should Be in Your Functional Model Document? (cont.)
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
3/20
6
Enforce Strong Query Governance to Ensure Consistency
Providing a coherent strategy for query development
means keeping queries consistent on the following
elements:
Naming
Look and feel
Security
Tools Ease of use
Performance
Templates
Keep the queries consistent
Develop a standards document to spell out the query strategy
and development, security, and naming standards
7
How Can You Ensure Standards Are Followed?
Establish a design steward for each functional area The steward
Is a member of the SAP NetWeaver BI development team
Is not a consultant
Is responsible for current and future design in onefunctional area
Can spot conflicts and scope creep into his/her area
Establishes some ownership in the development team
Allows team members to focus on one functional area
SAP is much too big to understand many areas
The design steward works with the business, projectmanager, and overall SAP NetWeaver BI data architect
Have some redundancy in case team members leave orare reassigned
8
Tool Strategy
Determine the right tool for the job
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
4/20
9
SAP NetWeaver BI Front-End Tool Tips
BEx Analyzer Excel-based and typically requires a desktop GUI to be set up
for all users
Can be cumbersome when rolling out front-end patches
Nice for users that are used to BEx Analyzer from 3.x or thosethat prefer the data in Excel
BEx Web Analyzer Runs on Java and does not require GUI on the user machine
Much faster to roll out new users
Memory intensive
Because it uses Java, it is typically much more memoryintensive than BEx Analyzer
Nice for users who want integration of Java with limitedfootprint
10
More SAP NetWeaver BI Front-End Tool Tips
BEx Report Designer
Formatted report development tool
Can be used for a subset of reporting requirements
Accommodates users that require a preformatted report
Robust in SAP NetWeaver BI 7.0, but is not a complete
replacement for Crystal Reports
Crystal has much more functionality but for many
implementations, Report Designer is enough
Overall nice toolset but should be limited to those reports that
are flat and without drilldowns
11
More SAP NetWeaver BI Front-End Tool Tips (cont.)
Web Applications Designer
Web development tool
Can be used to create Web applications to be used with SAP
NetWeaver BI
Most commonly used to develop Web templates to be used
with existing SAP NetWeaver BI reports
Robust tool set, but not very intuitive for a new developer
SAP NetWeaver Visual Composer
Robust tool to allow integrated reporting from multiple areas
Very promising tool
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
5/20
12
More SAP NetWeaver BI Front-End Tool Tips (cont.)
Third-party front-end tools such as Crystal, Cognos, etc.
A Why not SAP? approach should be followed
Any benefit should be heavily weighted against the cost of
integration of the third-party tool
No third-party tool is integrated as tightly as they tell you
Many times its difficult to determine where an issue
resides with SAP or with the third-party vendor
Note
13
What Is a Key Figure Matrix?
A universal document used to track and document the
most common key figures and KPIs
Establishes one place for all key figures and KPIs
Enforces one vision and view of these values
Allows agreement on one formula and method for calculating
a KPI
Establishes one central repository for naming and change
management
Sets common abbreviations, categories, granularity, etc.
14
What Is a Key Figure Matrix? (cont.)
A universal document used to track and document the
most common key figures and KPIs (cont.)
Assigns one owner to a KPI
It is quite common for one KPI to be measured quite
differently by different audiences
One customer I worked with had three different
definitions of a commonly used measure who
decides which measure is right?
This document keeps the Business Intelligence (BI) team
out of these decisions
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
6/20
15
How Do I Complete the KPI Matrix?
Most projects use a spreadsheet as their KPI matrix
The document must be a living document and owned
by the business and process teams
This should not be seen as a BI team deliverable
Before any work can be done in SAP NetWeaver BI,
insist that all key figures that are calculated be
documented in the KPI matrix
There should be no query formulas or InfoProvider
transformation calculations without the measure first
documented in the KPI matrix
16
How Do I Complete the KPI Matrix? (cont.)
Some projects take short cuts and allow measures to be
slipped in without documenting these on the KPI matrix
Eventually this will come back to haunt them
If the KPI conflicts with other KPIs or the calculation is not
clear, the BI team often has to go back and redo their work
This document helps get the calculations right the
first time
Insist on sign-off of the KPI matrix once complete
17
Query Development Strategy Tips
It is typically better to have fewer queries with many
different free characteristics and key figure values in
one query than many queries with very narrow scope
This strategy allows existing queries to answer many more
business questions by simply using new filter criteria or
drilling down differently in the report
Also allows for easier testing, documentation, and rollout
You can add many key figure values to one query by
putting it in the query and hiding it with the ability to
view when necessary
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
7/20
18
Hide (Can Be Shown)
Allows you tohave key figures
that are in the
query but not
initially shown
19
Utilize Key Figure Grouping in the InfoProvider
Key figure grouping can now be used
Folders
also appear
in BEx Query
Designer
These are like
dimensions
for key
figures
20
Query Development Strategy Tips
Do not develop queries to match to existing legacy
reports
They reflect existing measurement criteria
As SAP ECC becomes more mature and users become more
aware, there will be new measurements
Focus instead on the KPI or key figures that need to be
measured and build to them
Be careful of query requirements with many different
levels of granularity
Some metrics only make sense at one level and cannot be
aggregated
Make sure you understand the metric
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
8/20
21
Query Development Strategy Tips (cont.)
Always develop queries using a MultiProvider, even if
there is only data being read from one InfoProvider
Allows for growth, if needed, without moving or changing the
query drastically
Allows for logical partitioning to be added later to the data
model without disrupting the query
22
Develop Queries in Production?
Many customers develop queries directly in production This is a big debate among customers
Pros Quick deployment
Real production data can be used to test queries
Cons Hard to enforce governance
Performance issues may arise
Lack of understanding of the data leads to incorrectassumptions
The best way to achieve optimal performance andstandardize queries is to not allow query development inproduction Develop all queries in the development environment and
transport through the landscape (DEV QA PROD)
23
If Youre Developing Queries in Production
Keep query development in the hands of a few well-
trained power users
Have a certification/training process for the power users
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
9/20
24
If Youre Developing Queries in Production (cont.)
Make sure power users are
Strategic
They will steer the strategic vision of BI to the user
community; they need to understand the overall reporting
strategy
Detail-oriented
Not renegadesThey need to adhere to standards and documentation
rigorously
Business-oriented, yet technical
This allows them to understand both sides: the
development and the user community
25
Production Query Development Tips
Have strict naming and development criteria
This is where many SAP NetWeaver BI projects fail the
query development becomes the Wild West
There is no common naming (i.e., Net Sales in two queries
mean two different things)
Proliferation of queries makes the solution difficult to manage
Filter criteria is not administered consistently
Be mindful of data integrity
Try to keep queries with the same look and feel
Keep power users informed of the changing data model Filters or selection criteria may not be clear
26
Production Query Development Tips (cont.)
Make sure the query developers understand
performance strategy and adhere to this strategy
Consider the security implications of development Sometimes segmenting the data can make security
implementation easier
Develop a publishing strategy to segregate different
types of queries
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
10/20
27
What Well Cover
Development strategy
Publishing strategy
Performance management strategy
Security strategy
Wrap-up
28
Query Publishing Strategy
Publishing refers to rolling out new queries to end users
Group new queries into three categories:
Sanctioned Queries
These have been built by the development team, validated,
and published to the user base
Power User Queries
These have been created by a select group of the user
community
Favorites
Queries that have been created as a result of other queriesand saved to be reused
29
Sanctioned Queries
Published by the BI team
Follows strict standards
Transported from one environment to another Use a naming standard Z_* prefix and set security so no user
can add new Z_* or change these queries at all
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
11/20
30
Power User Queries
Published by the BI power users
Typically developed in production
Use a naming standard Y_* prefix and set security so only
power users can change
31
Favorites
Created from another query in production
Filtered result set
Characteristic/key figure view
Use a naming standard X_* prefix and set security so only the
user that created it can change it
32
Build an Effective Query Change Control Strategy
Understanding and communicating change control
helps to eliminate many issues with end users
Determine how your changes to the data model willaffect existing queries or the existing user base
Some query changes will break existing favorites built from
that query
Know what these changes are in order to prevent frustrated
users
Develop a methodology for communicating change
Link changes back to the original functional
requirements document and KPI matrix
Periodically purge non-used queries
Publish a list of these, wait for a few weeks, then delete
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
12/20
33
Query Publishing Strategy Segmentation
It may make sense to allow the power user queries to
exist in production for a limited time
If these power user queries are useful, they could be
copied and transported by the BI team
This would make them Sanctioned
Before sanctioning, queries can be reviewed to make
sure they adhere to:
Development standards
Naming standards
Performance standards
Security standards
34
Segment Queries on the Portal
35
SAP NetWeaver BI 7.0 and SAP Enterprise Portal
SAP NetWeaver BI 7.0 is much more integrated with the
SAP Enterprise Portal than SAP BW 3.x
A portal strategy needs to be developed in conjunctionwith the BI team
To federate or not to federate
Non-BI content
Custom Java
Organization of the SAP Enterprise Portal
Functional areas
Security
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
13/20
36
What Well Cover
Development strategy
Publishing strategy
Performance management strategy
Security strategy
Wrap-up
37
Query Performance Tips
Make sure the query developers understand the data
model
Limit the initial query result set
Many queries try to show too much data on the initial view and
the users then un-drill the report to match what they need
This degrades performance
Limit the level of hierarchies on initial view
This can slow performance if showing multiple levels of
hierarchies
38
Query Performance Tips (cont.)
Always use mandatory variables
Typically this is, at minimum, created for the date criteria
Use variable settings to default values into mandatory
variables
Have mandatory variables appear as the first selection
Make sure mandatory variables are turned on for all large
queries
Utilize filters based on InfoProviders
If a query is using data from only one InfoProvider, filter the
query on that InfoProvider to speed performance
Suppress results rows if theyre not needed
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
14/20
39
Query Performance Tips (cont.)
Eliminate or reduce Not logic in queries
When possible
40
SAP NetWeaver BI 7.0 Performance Tips
There is significant performance work being done in
SAP NetWeaver BI 7.0 in each Java support package
Do your best to stay current with support packages
This is difficult because the Java is now tied to stacks
This means that the ABAP and SAP Basis sides need to be
upgraded to the new patch at the same time as Java
This typically requires some regression testing
41
SAP NetWeaver BI 7.0 Performance Tips (cont.)
New features are even being added to support packages
You can find the list of features on the SAP Service
Marketplace
Many new features in Support Stack 12
SAP did a lot of work to improve Java performance in this
support stack
You should be on this if you are using BI Java
Plan for a great deal more memory in SAP NetWeaver BI
7.0 if using Java
A good Basis resource is a must
Getting SAP NetWeaver BI 7.0 tuned properly from an SAP
Basis standpoint is quite challenging
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
15/20
42
What Well Cover
Development strategy
Publishing strategy
Performance management strategy
Security strategy
Wrap-up
43
Security Options
SAP NetWeaver BI 7.0 analysis authorizations
SAP highly recommends moving to this new method of
security after upgrade
Analysis authorization security can be set at almost
any level
Characteristic/key figure
Characteristic value
Hierarchy node
InfoProvider
Query Plan a security strategy that will effectively secure
reports with the minimum amount of maintenance and
ongoing support
44
SAP Enterprise Portal Security
Adds another dimension of security
Make sure that this is coordinated and reconciled with SAP
NetWeaver BI security
Can be used in place of or in conjunction with the SAP
NetWeaver BI security
This can cause conflicts
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
16/20
45
Security Strategy
Group the users into logical groups
Use these roles to help develop the security roles
Do not forget how ad-hoc or power user development of
queries in production affects security
This is a big hole in most security strategies
There is often no check to make sure that a newly developed
query follows security standards
46
Security Strategy (cont.)
Plan for security during the requirement gathering
process
Do not attempt to retrofit the security into the model after
development is complete
Some security can be handled or made easier with data
modeling decisions
Plan adequate testing time
Test from end-to-end
Including portal
47
What Well Cover
Development strategy
Publishing strategy
Performance management strategy Security strategy
Wrap-up
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
17/20
4848
Resources
Gary Nolan, Maximize Your Query Optimization Options
in BW (BW Expert, July and August 2007).
Catherine Roze Best Practices for Allowing Power
Users to Develop Queries in BW (BW Expert, March
2004).
Gary Nolan, Effectively Gather BW Functional
Requirements (BW Expert, May 2007).
Gary Nolan, Efficient SAP NetWeaver BI Implementation
and Project Management(SAP PRESS, 2007).
4949
7 Key Points to Take Home
Develop a strong requirements gathering methodology
Establish and enforce query design standards to ensure
consistency
Understand the advantages and disadvantages to
developing queries directly in production
Develop a publishing strategy to segregate different
types of queries
Be cognizant of performance during query development
Have an end-to-end security strategy including theportal
Create a change control strategy to manage new and
changed queries and roll these out to the end user
5050
Your Turn!
How to contact me:
Gary Nolan
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
_______________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
________________________________
-
8/7/2019 BW Query Management
18/20
Notes:
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
____________________________________________________________________________________________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
____________________________________________________________________________________________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
____________________________________________________________________________________________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
-
8/7/2019 BW Query Management
19/20
Notes:
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
____________________________________________________________________________________________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
____________________________________________________________________________________________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
____________________________________________________________________________________________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
-
8/7/2019 BW Query Management
20/20
Wellesley Information Services, 990 Washington Street, Suite 308, Dedham, MA 02026
C i h 2007W ll l I f i S i All i h d