7BIPUWorkshopBExQueryDesigner
-
Upload
zaynab-fadlallah -
Category
Documents
-
view
215 -
download
0
Transcript of 7BIPUWorkshopBExQueryDesigner
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
1/32
BEx Query DesignerWashington State HRMS BusinessWashington State HRMS Business
Warehouse/Business Intelligence (BW/BI)Warehouse/Business Intelligence (BW/BI)
BW/BI Power User Workshop MaterialsBW/BI Power User Workshop MaterialsGeneral TopicsGeneral Topics BW/BI Power UsersBW/BI Power Users
Section 7
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
2/32
The following section provides an overview of BEx Query Designer.
BEx Query Designer Overview
Creating a New Ad Hoc Query
BEx Query Designer Toolbar
Accessing BEx Query Designer
Saving an Ad Hoc Query
Opening/Running an Existing Ad Hoc Query
Business Intelligence
Business Explorer (BEx)
BW/BI Security
BW/BI EnvironmentWhat is BEx?
BEx Tools Overview
BEx Query Designer
Deleting an Existing Ad Hoc Query
Closing BEx Query Designer
BEx Query AnalyzerModifying an Existing Ad Hoc Query
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
3/32
BEx Query Designer is a tool used to create ad hoc queries and is part of the BEx suite of tools. Ad hoc
queries are comprised of Characteristics (usually in the Rows section), Free Characteristics, Key Figures(usually in the Columns section) and Filters.
The following diagram displays the main work area of BEx Query Designer:
Report FiltersReport Filters
InfoProvider IdentifierInfoProvider Identifier
ToolbarToolbar
Key FiguresKey Figures
Default ValuesDefault Values
(restrict(restrict
Characteristics andCharacteristics and
add variables)add variables)
Report LayoutReport Layout
PreviewPreview
InfoObjects: Fields in theInfoObjects: Fields in the
InfoProvider used toInfoProvider used to
create queries.create queries.
Key FiguresKey Figures
(Report Columns)(Report Columns)DimensionsDimensions
CharacteristicsCharacteristics
AttributesAttributes
CharacteristicsCharacteristics
(Report Rows)(Report Rows)
FreeFree
CharacteristicsCharacteristics
(Rows)(Rows) ForFor
Drilldown (hiddenDrilldown (hidden
on initial entry)on initial entry)
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
4/32
Quit and UseQuit and Use
QueryQuery IncludeInclude
TechnicalTechnical
NamesNames
RowsRows
ColumnsColumns
Create aCreate a
ConditionCondition
SaveSaveQueryQuery
NewNew
QueryQuery Display QueryDisplay Query
on the Webon the Web InfoProviderInfoProvider
CopyCopy
QueryQuery
PropertiesProperties
PropertiesProperties
MessagesMessages
OpenOpen
QueryQuery
CutCut
Create anCreate an
ExceptionException
WhereWhere--usedused
ListList
CheckCheck
QueryQuery
ExitExit
QueryQuery
FilterFilter
Save AllSave All
PastePaste DefineDefine
CellsCells
TasksTasks DocumentsDocuments
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
5/32
Quit and Use QueryQuit and Use Query Execute the query in MS Excel (enabled only when BEx Query Designer is opened from BEx QueryExecute the query in MS Excel (enabled only when BEx Query Designer is opened from BEx Query
Analyzer).Analyzer).
Exit QueryExit Query Logoff and Exit BEx Query Designer.Logoff and Exit BEx Query Designer.
New QueryNew Query Create a new ad hoc query.Create a new ad hoc query.
Open QueryOpen Query Open an existing query.Open an existing query.
Save QuerySave Query Save the ad hoc query.Save the ad hoc query.
Save AllSave All Save the ad hoc query.Save the ad hoc query.
The following table provides an overview of functions available from the BEx Query Designer Toolbar:
xecu e e query on e we .xecu e e query on e we .
Check QueryCheck Query Before a new query is saved, use theBefore a new query is saved, use the Check QueryCheck Querycommand to check the query definition for errors.command to check the query definition for errors.
Query PropertiesQuery Properties Set query properties such as the query description,Set query properties such as the query description,
CutCut Cut object out of query.Cut object out of query.
CopyCopy Copy object in query.Copy object in query.
PastePaste Paste object that have been Cut or Copied in the query.Paste object that have been Cut or Copied in the query.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
6/32
InfoProviderInfoProvider Select the InfoProvider pane.Select the InfoProvider pane.
FilterFilter Select the Filter pane.Select the Filter pane.
Rows/ColumnsRows/Columns Select the Rows/Columns pane.Select the Rows/Columns pane.
CellsCells Define formulas and selection conditions explicitly for cells in queries with two or moreDefine formulas and selection conditions explicitly for cells in queries with two or more
structures.structures.
Create a ConditionCreate a Condition Create a condition for the ad hoc query.Create a condition for the ad hoc query.
Create an ExceptionCreate an Exception Create an exception for the ad hoc query.Create an exception for the ad hoc query.
BEx Query Designer Toolbar overview
PropertiesProperties Select the Properties pane.Select the Properties pane.
TasksTasks Select the Tasks pane.Select the Tasks pane.
MessagesMessages Select the Messages pane.Select the Messages pane.
WhereWhere--used Listused List Check to see which objects (for example, workbooks) are using the current query.Check to see which objects (for example, workbooks) are using the current query.
DocumentsDocuments Displays the documents pane.Displays the documents pane.
Technical NamesTechnical Names Show or hide the technical names of the query components.Show or hide the technical names of the query components.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
7/32
To access BEx Query Designer:
1.1. Logon to the HRMS BusinessLogon to the HRMS Business
Intelligence via SAP GUI asIntelligence via SAP GUI as
described in the Businessdescribed in the Business
Explorer (BEx) module.Explorer (BEx) module.
2.2. DoubleDouble--click Start theclick Start the
BusinessBusiness
Explorer Analyzer link ORExplorer Analyzer link OR
enter RRMX in the Commandenter RRMX in the Command
FieldField
7
Click Start All Programs
Business Explorer Analyzer
NOTE:NOTE: When opening the AnalyzerWhen opening the Analyzer
through the Start menu, you willthrough the Start menu, you will
not get the log on pad until younot get the log on pad until you
create/open a query.create/open a query.
OROR
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
8/32
Result: MS Excel will open with the Business Explorer menu and BEx Query Analyzer toolbar enabled.
Logged in with Start the Business Explorer Analyzer, system icon shows connected:
Excel BEx Query
Analyzer Toolbar
and BEx Analyzer
Menu
8
Logged in through the Start Menu, system icon shows not connected:
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
9/32
1. Click the BEx
Analyzer menu item.
2. Select Tools.
3. Click Create New
Quer .
9
OROR
1. Click the Toolsicon on the BEx
Query Analyzer
toolbar.
2. Click Create New
Query.
NOTE:NOTE: If opened with theIf opened with the
Start Menu, this is the pointStart Menu, this is the point
that you will be required tothat you will be required to
log on before the Querylog on before the Query
Designer is displayed.Designer is displayed.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
10/32
Result: BEx Query Designer will be opened in the Standard View.
10
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
11/32
To access the SAP BW 3.x view:(which allows users to view all
sections at one time)
1. Click the View tab.
2. Select Predefined
3. Click Sap BW 3.x View
Result: BEx Query Designer
will be o ened in the SAP
11
BW 3.x View.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
12/32
The example below provides an overview of toolbar options available from the New Query options of BEx
Query Designer.
PropertiesProperties
On/OffOn/Off
12
On/OffOn/Off
The table below provides a description of toolbar options available from the New Query options of BEx
Query Designer.
FindFind Find an InfoProvider from the list of InfoAreas.
Up LevelUp Level Go back to previous level.
RefreshRefresh Refresh list.
Technical Name On/OffTechnical Name On/Off Toggle the InfoArea Technical Names On/Off.
Properties On/OffProperties On/Off Toggle the InfoArea Properties On/Off.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
13/32
The example below provides an overview of options available when creating a new ad hoc query from BEx
Query Designer.
Click New Query icon fromClick New Query icon from
the BEx Query Designerthe BEx Query Designer
toolbar to create a New adtoolbar to create a New adhoc queryhoc query
13
HistoryHistory: Displays a list of the most recently used: Displays a list of the most recently used
InfoProviders (this tab is selected by default)InfoProviders (this tab is selected by default)
InfoAreasInfoAreas: Displays the list of InfoAreas to select: Displays the list of InfoAreas to select
InfoProviders for new queriesInfoProviders for new queries
Available InfoAreas will beAvailable InfoAreas will be
displayed.displayed.
Click InfoAreas when the New Query: Select InfoProvider screen is displayed. The History tab is selected by default.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
14/32
The Business Intelligence contains a list of InfoAreas with InfoProviders. To create a new ad hoc query,
select the appropriate InfoProvider from the InfoAreas section.
InfoAreas include:
AFRS Payroll
Human Resources Organizational
Management
Payroll
Personnel Administration
Click here to toggleClick here to toggle
Technical namesTechnical names
on/offon/off
Double click theDouble click thefolder icon tofolder icon to
expand/collapseexpand/collapse
InfoAreasInfoAreas
14
Grievance Data
Technical ContentTechnical Content
InfoProviders areInfoProviders are
used by HRMS BW/BIused by HRMS BW/BI
Production SupportProduction Support
staff onlystaff only
InfoProviders forInfoProviders for
QueriesQueries
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
15/32
1. Select the InfoAreas tab from the New
Query Select InfoProvider screen.
2. Expand the Human Resources InfoArea.
3. Expand the Personnel Administration
InfoArea.
4. Select the Headcount and Personnel
Actions InfoProvider.
5. Click OK.
15
Result: All InfoObjects ofthe Headcount and
Personnel Actions
InfoProvider are displayed in
BEx Query Designer.InfoObjects of theInfoObjects of the
HeadcountHeadcount
InfoProviderInfoProvider
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
16/32
6. Drag&Drop the Number of
Employees Key Figure from
the Key Figures section to
the Columns section of the
ad hoc query.
7. Drag&Drop the Personnel
Area Characteristic from the
Personnel Area Dimension to
the Rows section of the ad
16
hoc query.
Result: An ad hoc query has been created. See Saving an Ad Hoc query for information on Saving.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
17/32
BW/BI Power Users have the ability to create an ad hoc query that is visible only to themselves
(Personal) or to other BW/BI Power users in their Agency (Agency-specific).
The technical name used when the query is saved determines if it is Personal or Agency-specific.
Ad hoc queries saved as Personal or Agency-specific can only be modified or deleted by the BW/BI
Power User who created the query.
The following pages show how to save Personal and Agency-specific ad hoc queries.
17
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
18/32
Saving a Personal Ad Hoc Query
To save a Personal ad hoc query, BW/BI Power Users are required to prefix the ad hoc query Technical
Name with A (indicates a Personal query), followed by the users agency number with an underscore
(_). Any alpha numeric characters can be entered after the required prefix.
The example below shows a sample Personal query for a user who belongs to Agency 1790:
18
A Indicates aA Indicates a
Personal ad hocPersonal ad hoc
query (required)query (required)
BW/BI Power UsersBW/BI Power Users
Agency (required)Agency (required) Name of queryName of query
BW/BI Power Users will not be able to save ad hoc queries if the Technical Name prefix requirements have not been
added or if the user enters an agency number they are not assigned to.
Underscore (_)Underscore (_)
(required)(required)
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
19/32
Saving an Agency-specific Ad Hoc Query
To save an Agency-specific ad hoc query, BW/BI Power Users are required to prefix the ad hoc query
Technical Name with X (indicates an Agency-specific query), followed by the users agency number
with an underscore (_). Any alpha numeric characters can be entered after the required prefix.
The example below shows a sample Agency-specific query for a user who belongs to Agency 1790:
BW/BI PowerBW/BI Power
Users AgencyUsers Agency
(required)(required) Name of queryName of query
19
BW/BI Power Users will not be able to save ad hoc queries if the Technical Name prefix requirements have not been
added or if the user enters an agency number they are not assigned to.
Underscore (_)Underscore (_)
(required)(required)
X Indicates anX Indicates an
AgencyAgency--specificspecific
ad hoc queryad hoc query
(required)(required)
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
20/32
1. Click the Save Query icon from the BEx
Query Designer toolbar of the ad hoc queryto save.
Result: The Save Query screen will be displayed.
2. Enter the query Description in the
The example below uses a sample query, My Personal Query, to show how to save a Personal ad hoc
query developed in the Headcount InfoProvider for a BW/BI Power User in Agency 1790.
To save a Personal ad hoc query once a query has been
created:
20
Description text box:
My Personal Query
3. Enter the query technical name in the
Technical Name text box (using YOUR
Personnel Area number):
A1790_MYPERSONALQUERY
4. Click Save.
Result: The ad hoc query is saved.(A Personal query can be displayed/run/edited/deleted by the BW/BI Power User who created the ad hoc query only. To save the query as
agency specific, use an X instead of an A)
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
21/32
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
22/32
The Open Query screen will default to the History tab. The example below provides an overview of tab
options available from the Open Query screen.
HistoryHistory: Displays a list of the most: Displays a list of the most
recently run or edited queriesrecently run or edited queries
FavoritesFavorites: Displays a list of the: Displays a list of the
22
to the Favorites folderto the Favorites folder
RolesRoles: Not implemented in BW/BI: Not implemented in BW/BI
InfoAreasInfoAreas: Displays the list of: Displays the list of
InfoAreas to select InfoProviders forInfoAreas to select InfoProviders for
new or existing queriesnew or existing queries
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
23/32
In addition to the Open Query tab options, the Open Query screen will display toolbar options. The
example below provides an overview of toolbar options available from the Open Query screen.
U LevelU Level
DeleteDelete
RefreshRefresh
TechnicalTechnical
NameName
On/OffOn/Off
23
FavoritesFavorites
On/OffOn/Off
FindFind Find an ad hoc query.
DeleteDelete Delete an ad hoc query from a view (Favorites or History).
Add to FavoritesAdd to Favorites Add ad hoc query to the users Favorites folder.
Up LevelUp Level Takes you back to prior level.
Technical Name On/OffTechnical Name On/Off Toggle the InfoArea Technical Names On/Off.
Properties On/OffProperties On/Off Toggle the InfoArea Properties On/Off.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
24/32
The example below uses a sample ad hoc query from the Headcount InfoProvider to show how to open
and run an ad hoc query from BEx Query Designer:
1. From BEx Query Designer, click the
Open Query icon .
2. Click on the InfoAreas tab.
3. Expand the Human Resources InfoArea.
4. Expand the Personnel Administration
24
n o rea.
5. Expand the Headcount and Personnel
Actions InfoProvider.
6. Find the query you want to open,
select it.
7. Click Open.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
25/32
Result: The sample ad hoc query (Demo Open Query) has been opened in BEx Query Designer.
To run the ad hoc query:
Click the Execute Query on the
Web icon through Internet
Explorer.
ORClick the Exit and use the
query icon to generate
25
your report through Excel.
Note:Note: If you get this userid and password promptIf you get this userid and password prompt
when using the Execute Query on the Web,when using the Execute Query on the Web,clickclick CancelCancel. The Portal log on screen will. The Portal log on screen will
display. That is where you need to enter yourdisplay. That is where you need to enter your
userid and password.userid and password.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
26/32
1. Enter Variable values, if applicable.
2. Click OK.
Variable screen on the Web
Variable screen through Excel
26
The Demo Key Figure query displayed on the Web The Demo Key Figure query displayed through Excel
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
27/32
The example below uses a sample ad hoc query
from the Headcount and Personnel ActionsInfoProvider to show how to open and run an ad
hoc query from BEx Query Designer:
1.1. From BEx Query Designer, click the OpenFrom BEx Query Designer, click the Open
Query icon .Query icon .
2.2. Click on the InfoAreas tab.Click on the InfoAreas tab.
3.3. Expand the Human Resources InfoArea.Expand the Human Resources InfoArea.
4.4. Expand the Personnel AdministrationExpand the Personnel Administration
InfoArea.InfoArea.
27
5.5. Expand the Headcount and PersonnelExpand the Headcount and Personnel
Actions InfoProvider.Actions InfoProvider.
6.6. Find the query you want to open, selectFind the query you want to open, select
it.it.
7.7. Click Open.Click Open.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
28/32
1. Drag&Drop the Gender Characteristic from
the Personal Data Dimension to the Rows
section of the query under Personnel Area.
Result: The query is ready for modifications.
To modify the ad hoc query:
. .
3. At the Execute Query box, click Yes to save
and run ad hoc query.
User can save theUser can save the
query without runningquery without running
it by clicking the Saveit by clicking the Save
icon.icon.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
29/32
Result: The Variables screen is displayed.
4. Enter query Variable(s).
5. Click the OK icon.
Result: The modified ad hoc uer will be dis la ed on the Web
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
30/32
It is important BW/BI Power Users manage the number of ad hoc queries they have created. Ad hocqueries that are no longer being used need to be deleted by the BW/BI Power User who created them.
The example below uses a sample ad hoc query (Delete Demo) to show how to delete an ad hoc query.
To delete an ad hoc query that has been opened in
BEx Query Designer:1. Click the Query options from the BEx
Query Designer toolbar.
30
2. At the prompt, Query [Query Name]
This deletion process is irreversible. Are you
sure you want to continue? If you are sure,
click Yes.
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
31/32
Result: The sample ad hoc query, Delete Demo, is deleted. The BEx Query Designer screen willbe blank.
31
-
8/12/2019 7BIPUWorkshopBExQueryDesigner
32/32
To close BEx Query Designer:
1. Click the End and Discard Changes icon from the toolbar.
OR
1. Click the Close Window icon.
Click the End
and Discard
Changes icon
to Close BEx
Click the Close
Window icon to
Close BEx
Query Designer
32
Query Designer
Result: BEx Query
Designer is closed.