Creating Custom Queries in Workforce Analytics

Query Workspace allows you to conduct custom detailed analysis of their published WFA data. You can search for business-critical issues or trends within the data. Query workspace has the ability to:

• Build queries that combine multiple measures and multiple analysis dimensions into a single (table) view.

• Apply additional filters on top of chosen measures and dimensions.

• Construct queries in a live Query Builder view, where the query results display automatically as selections are made.

• Present results in table or chart styles, format these queries and save them to be refreshed later or included in reports.

Using the Query Workspace tool, you can analyze data in ways that are not available in the default measure views.

To access the tool, please review the basic navigation lesson earlier in the documentation.

A query can be created, opened saved or deleted from the File menu.

To toggle between chart and table views, the View menu should be used.

Selectable dimensions used in the query can be changed with the Filters.

The edit query, edit query formatting or edit Custom Measures/Dimensions are options in the Edit menu.

When first entering Query Workspace, the view will be populated with the last measure previously viewed.

All dynamic options are enabled and you can immediately begin to drill down and change analysis using the Filters tile.

File menu is used to create, open, save or delete a query.

Edit menu is used to edit the query measure/dimension settings, edit the query display or access the Custom Measures & Dimensions function.

To save a query, select Save Query As from the File menu, then enter a name in the Title field and select a folder to save it to. Saved custom queries can be used and re-used in custom reports.

If you want to create a new query from scratch, or change an existing query, work in the design panel. The query design panel includes a measure & dimension list and the ability to toggle between table view and design view. In either case you can drag and drop measures and dimensions onto the workspace. You access the design panel from the edit menu.

The Show button allows you to toggle between Table view and Design view.

Time Periods allows you to create queries with ‘static’ years, quarters, and months for display. Time Models allow the dimension to dynamically be selected.

For example, you want to build a report that shows you End of Period Headcount over the last 4 years. You could do this by constructing a query that only returns results for 2016, 2015, 2014 and 2013. This would give you the information that you need however as time moves forward so do your needs. In 2018 you still want to see the last 4 years which are now 2017, 2016, 2015 and 2014. You can certainly adjust your query to cater for this but this means that you need to remember to do some annual maintenance of your query to show the desired results.

Alternatively in this example you can choose to use a Time Model called "Last4Years" that always shows you the latest 4 years of results. By using a model as opposed to specifying the specific years you can create a report that continues to work over time and returns you the information that you need without requiring maintenance. This is just one example of a Time Model. SAP SuccessFactors supports many others designed to make reporting easy and sustainable over time.

There are several groupings of time models: navigational, yearly, quarterly, monthly, weekly, and daily.

• Navigational - multi level models (Years, Quarters & Months) that allow the user to navigate through the time dimension using the filter panel.

• Yearly - based on calendar years like 2016 and 2017 and on fiscal years like 2016/2017 and 2015/2016.

• Quarterly - based the calendar quarters of Quarter 1 (January to March), Quarter 2 (April to June), Quarter 3 (July to September) and Quarter 4 (October to December) or alternatively on the fiscal quarters.

• Monthly - based on the twelve months of the year.

• Weekly – based on the 52 weeks of the year

• Daily – based on the 365/366 days of the year

There are 3 options in the Navigational Time Models: Time Periods, Time Periods (Current Node Only), and Time View. The examples below use data that is compiled through the end of December 2016.

Time Period: the report user can select a time node using the filter panel. Selecting a node will display the child nodes of the selected node. In the example, the year 2016 was selected

Time Period (Current node only): the report user can select a time node using the filter panel. Selecting a node will display the selected node only. In the example, the year 2016 was selected

Time View: the report user can select a time type (year, quarter, months of a year) using the filter panel. Selecting a node will:

• Year: display all years.

• Quarter: display all quarters.

• Months > Year: display all months of the selected year.

Other time models based upon calendar year, quarter, month, week, and days are also available. Week and days are only available when using the WFA on SAP HANA version of Workforce Analytics.

Many time models exist for that display node(s) that represent years.

Many time models exist for that display node(s) that represent quarters.

A Seasonal View displays the same quarter (Q1, Q2, Q3, Q4) of all the years, for example Q1 of 2016 and Q1 of 2017.

Many time models exist for that display node(s) that represent months.

Many time models exist for that display node(s) that represent weeks.

Many time models exist for that display node(s) that represent days.

Tokens allow you to display the textual information that is dynamically generated. This becomes a powerful tool especially when you consider how you might want users to navigate and adjust selection criteria in your report at run-time or even if you decide to adjust your report in the future. With tokens your report will automatically tell its own story without you needing to manually adjust text.

For example, you are building a report that contains several charts on a page. Each of these charts shows a distinct view of data that when shown together on a report page portray a data story that is important to your report. As the author of the report you understand this story but you want to make sure that other report users can easily discern what each chart is depicting. To do this you plan to add titles and axis descriptions to each chart to make this clear.

You could simply manually type in a text title/axis description for each chart. However, due to the dynamic ability of these tools, there is a way to let the chart automatically do this for you using tokens that describe the selected properties of the chart. If you are constructing a chart using the End of Period Headcount measure, you could manually type this into the title. Alternatively, you could include a token in the title that means "automatically display the name of the measure that this chart represents". Then if you later change this chart to use the Start of Period Headcount measure then the title will be correct without making any manual change to the title.

Tokens can be divided into categories that are largely defined by the major components of a chart or table. You can think of these components as being the major building blocks of the underlying query.

Token categories include:

• Organization – presenting an organization specific disclaimer, and the date data was published.

• Page – user selected filter in the filter.

• Measure - the measure(s) used in the query.

• X and Y Axis (Columns and Rows) - the data selections (dimensions) that appear on the two axis of a chart (X and Y) or table (columns or rows).

• Filter – any analysis dimension filters that are applied to the query.

• Context – any time or structural filters that have been applied to the query.

• Benchmark – any benchmark categories that have been applied to the component.

All tokens have a similar format when you are using them and that is [%TOKENNAME%]. The ‘[%’ and ‘%]’ characters are simply designed so that tokens may be distinguished from normal text that is manually typed.

You may build up sentences using multiple tokens combined with normal manually entered text.

Tokens can be used in a variety of locations for WFA tools. The token list can be retrieved by selecting "…" next to a text box in a variety of locations, or by selecting "Token Selector" within the certain components.

A Target is a desired level of performance for a measure that can be achieved through proactive management. Targets can be configured by WFA administrators. A variety of query and report components can utilize configured targets. Targets are defined for measure utilizing a time, structural dimension and optionally an analysis dimension. For example, you can set termination rate targets for the Location Organizational Units for the years 2016 and 2017.

Targets can be single value or range.

You can use thresholds to display a close to target/near range. They can be configured as a number or percentage above/below a value. Additionally, they can be set for one side of a target, or both.

When you create a new query or edit an existing query, you will be working with one of two views.

Table view gives the user a tabular view of the output of the current query. When in table view, you can view the results of any changes to the query. You can drag metrics onto the row or column to include them in the query. You can drag a dimension onto the row/column/filter to include those in the existing query. Table view provides the simplest way to modify an existing query.

Find the required measure from the list in the left side pane, then click/hold the measure to either the Rows or Columns bar. When creating a new query, a measure must be added before any dimensions. In this example, Average Headcount has been dragged onto the Rows bar.

Once a measure has been selected, the Dimensions list will automatically open. If the query is to be displayed in a chart format, it must contain a dimension in the columns. In this example, the structural dimension All Organizational Units has been dragged onto the Rows bar. A time dimension has not been selected, so the most recent year will appear as the default time period.

A time period for the query can be selected by dragging and dropping a time dimension onto the query pane. Using a time dimension in the rows/columns of the query makes the dimension visible as part of the query. In this example, the time dimension Calendar Years has been dragged and dropped onto the Columns bar. Calendar Years can now be seen as the columns in the query results.

A filter for the query can be selected by dragging and dropping a dimension node onto the query pane. In this example, the node Generation X (1964-1978) from the Generation analysis has been dragged and dropped onto the Filters bar. By adding a filter to the example, we now have a query showing the Average Headcount for All Organizational Units by Calendar Years filtered to Generation X employees.

Design view provides the user with more detailed management of the output of the query. There are several actions that can be completed that cannot be done in table view:

• Remove measures/dimensions.
• Change the query order of dimensions.
• Swap dimensions between rows/columns.
• Include a subset of a structural or analysis dimension.

Selecting Show Design View will bring up the design window.

Dragging and dropping measures, dimensions onto rows, columns and filters in the Table View is a quick and simple way to create a basic query.

In Design View you can see the parameters for the query designed.

Use of the Design View, will allow you to construct a richer, more complex query that will allow even closer analysis of the targeted measure. The drag and drop functionality is still applicable in the Design View.

The Measures section in Design View will show:

1. The measures in the query.

2. If more than one measure is used in the query, there is the ability to move measures up or down the query order.

3. The selected display options for those measures.

4. An Edit menu to change the display options.

5. A Remove option to take a measure out of the query.

6. A measure Edit option to toggle between Actual/Annualized results (if applicable for that measure).

The following measure display options are available in Design View:

• Show Measures on Rows or Columns: This option determines whether measures that have been chosen for this query are shown on the rows or on the columns in the results.

• Show Measures on Major Grouping or Minor Grouping: If two or more measures are included in the query and an additional analysis dimension has been associated with these measures on rows/columns, this option determines how these measures are shown relative to this analysis dimension.

• Exclude rows or columns that contain no data: Selecting this option will hide from the results generated by the query any rows or columns of data that contain no data at all. Note that rows and columns of data that contain valid "zero" results will still be shown.

There are two Grouping Options:

• Major Grouping option: the measures will be the primary grouping broken down by the analysis dimension as the secondary grouping.

• Minor Grouping option: the analysis dimension will be the primary grouping broken down by the measures as the secondary grouping.

The Columns and Rows section in Design View will show:

1. The dimension names used in the columns/rows.

2. An option to swap the column/row dimensions.

3. An Edit menu to change the dimension levels used in the query.

4. A Remove option to take a dimension out of the query.

5. If more than one dimension is used in columns/rows, there is the ability to move dimensions up or down the query order.

Dimension Levels can be changed by selecting Edit for the dimension you wish to change. Dimension Levels consist of Qualifiers and Distance.

• Qualifiers determine which dimension nodes will be included in the query, according to their relationship to the selected node.

• Distance refers to the difference in depth between the dimension node selected and its associated target node(s). For example, if you wanted to examine the items immediately below the dimension node (the child elements), then a distance of one (1) applies. Similarly, the grand-children nodes are identified by a distance of two (2).

In this example the Qualifier All descendants to distance with a Distance of 1 has been used with the All Organizational Units node of the Organizational Unit structural dimension – this will show all the dimension nodes immediately underneath All Organizational Units.

To change the existing selection:

1. Select Remove to delete the existing setting.

2. Navigate the dimension tree to the required node.

3. Select your Qualifier and Distance.

4. Select Add.

5. Select the up arrow to close the Edit window.

Qualifier Options definitions:

• Selected element only includes in the query only the dimension node selected in the explorer.

• Selected element and all descendants to distance includes in the query the dimension node selected in the explorer plus all the nodes below, down to the level specified by the distance value.

• All descendants to distance Includes in the query all descendants of the dimension node selected in the explorer down to the level specified by the distance value. Note that the dimension node selected in the explorer is not included.

• All descendants at distance only includes in the query all descendants of the dimension node selected in the explorer at the level specified by the distance value. Note that the dimension node selected in the explorer is not included nor are any node at distances between the dimension node and the specified distance.

• All descendants leaf elements includes in the query only the lowest level or terminal descendants of the dimension node selected in the explorer. These elements are often referred to as the leaf elements having no 'child' nodes below them. Note that the dimension node selected in the explorer is not included nor are any nodes at distances between the dimension node and the leaf nodes.

• Selected element and all descendants includes in the query the dimension node selected in the explorer plus all the nodes below to the bottom of the dimension.

• All descendants includes in the query all descendants of the dimension node selected in the explorer to the bottom of the dimension. Note that the dimension node selected in the explorer is not included.

Instead of adding a time dimension to Rows/Columns. A time period for the query can be selected by adding a Time Context. Time Context is used when it is not intended that the time can be seen as rows/columns in the query, but simply as a background filter to the query. Time Context is added to the query via the Design View. Select a time context and choose Set. Then close the window by selecting the up arrow. In this example, the time model Last Year has been selected.

Include Benchmarking Results in your query in Additional Data Items. Additional Data Items allows you to add benchmarking results to your query.

Clear Query Parameters allows you to clear all query settings and begin a new query. Use Structural Context if an organizational structure is not selected on the rows or columns, and the query is to be used in a Report. Ticking this box will enable the organizational structure to be selected at runtime.

From the Edit menu, select Edit Query Formatting to change the look and feel of the query display.

Any formatting applied in Query Workspace will NOT be applied if the query is used in a custom canvas report. You will have to format the component in the report separately.

From the View menu, select Table or Chart to toggle the query display between table/chart view.

The Custom Dimensions function provides users with the ability to create specific dimension views for use within Query Workspace, Investigate, and Page Designer .

Custom Dimensions can be especially useful where there is a requirement to view and/or aggregate parts of analysis dimensions on an ongoing basis and within separate queries/reports.

For example, a user may be looking at Average Headcount within a particular line of business, and decide to slice this by the age dimension. Rather than looking at all age bands however, the desire may be to group ages above 40 and look at the total result of those groupings. Furthermore, rather than having this view pertaining to a single query, the user may want this view to be available at anytime for query work without having to recreate it.

The Custom Dimensions functions make the above possible, as well as providing the scope for many other uses.

The Custom Measures function provides users with the ability to create custom calculated measures for use within Query Workspace, Investigate, and Page Designer.

Create custom measures by dragging and dropping measure inputs and formula operators onto a designer and see your measure build itself in real time.

The tool also supports a filter(s) for each input measure as the measure is constructed.

Custom Measures/Dimensions can be accessed via the Edit menu in Query Workspace. Any previously created custom views appear within the folder structure. The tools tile displays the two custom options not currently selected.

Any Custom Dimensions you create will be hidden from other users of the site unless you give it a public status. Select Status to see a list of the dimensions you have access to and their statuses. The views status will be displayed as an ‘x’ if the dimension is not shared, and a ‘√’ if it is shared. It is important to note that users can edit/delete their own custom views and any views created by other users that have a public status. When accessing views for query use within Query Workspace, users can use their own custom views and any public custom views. Role Based Security permissions continue to apply a user needs the correct Role Based Permissions to view results in a query using Custom Members, Sets and/or Calculations, even if the view has a public status. Share your custom dimension with other users by selecting Status, then Make Public.

Folders can be created to simplify the management of custom views and views can be moved to different folders by dragging and dropping. Manage the folder structure with the New, Edit and Delete buttons. If a folder is not selected when a dimension is created, the dimension will save to the (Default) folder.

Custom members and custom sets are created in the same way but will display different results when used in a query.

• Custom Members: groupings within a dimension will create a single total result made up of all the selected groupings.

• Custom Sets: groupings within a dimension will be made up of only the selected groupings.

To create a new member or set, choose Custom Members or Custom Sets from the left tools tile and select File New. The Name field is used to give the custom dimension an appropriate label. Build a custom dimension from by selecting the structural or analysis dimension you wish.

As can be seen in the example, the organization has an Employee Preference Survey cube containing measures and dimensions based on Employee Survey results.

• TheCube Id selection is necessary to define the type of data by which the analyses are sliced. The workforce cube houses the majority of data and analyses in most instances, but other measures such as those involving financial data will often be housed in other cubes and thus will need to be selected if the goal is to create a custom view for analyzing those particular measures.

• TheDimension Id specifies the analyses that can be used to create the custom dimension. Continuing the Age example, the relevant dimension in this case would simply be Age. Only dimensions that are available in the organizations cube can be used to create a custom dimension. Custom dimensions cannot be created from other custom dimensions.

To edit the nodes of the member or set, choose thePaths tab then adjust the nodes from the selected Dimension. Then select OK to save, or Cancel to ignore the changes. Select the appropriate nodes from the dimension tree. Use CTRL + Click to select more than one node at a time and drag to add them to the custom dimension. Nodes can be deleted from the Members list by dragging or selectingRemove All.

To create a new calculation, choose Custom Calculations from the tools tile. Select File New. Enter the Measure details in the Custom Calculation Editor pane:

1. The Name

2. A brief Description

3. A detailed Description

4. Assign the calculation Format

5. The number of Decimal Places

6. Assign the metric Category

7. Assign the metric SubCategory

Select Custom Calculation Formula to access the formula building pane:

• Select first measure from Measure List

• Drag it onto the drag designer

The Select Dimension Filter window will automatically open each time an input measure is dropped onto the designer. Filter the measure by selecting one or more filter nodes from the required analyses.

Should a filter not be required, simply select OK.

In the example, the input measure will be filtered by age 20-29.

Continue building the formula by dragging operators and more measures into the designer. After the addition of the first measure, each subsequent operator and measure will need to be dropped onto the appropriate gray bar to either side of every component in the formula. Select from the list of available operators and drag them onto the designer.

To remove an input, drag the input from the designer and drop it onto the Delete button.

The Preview Window displays your calculation.

The Validate function can be used to check the validity of the custom dimension or measure and is accessible by selecting the Validate button. Validation will check all dimensions/measures saved in the tool. If no errors are detected, an "All is valid" message will be displayed. Validation becomes an increasingly necessary function throughout the life cycle of the cube and portal as measures and dimensions are added, modified and, on occasion, removed as part of the refresh process.

To use the custom measure/dimension, the user must create a query within Query Workspace, Investigate or Page Designer.

Query Workspace and Page Designer use the same drag and drop paradigm.

Custom dimensions are included in the Special Functions category and can be located under Dimensions.

Custom measures are and can be located under the Measures panel and are included in either Custom Calculations category or the selected Category and SubCategory during its creation.

For more information about using custom measures and custom dimensions with Investigate, review the Investigate content later in the course.