Creating Analyses and Dashboards

This topic will cover the general navigation.
To log into Oracle BI EE, perform the following steps:

When you sign in, the Home page is displayed. The Home page is a task-oriented, centralized workspace combined with a global header, allowing access to Oracle BI EE objects, their respective editors, help documentation, and so on.

The Home Page contains a global header with drop-down menus for the most frequently performed tasks. The Create section provides short cut for creating objects such as Visual Analyzer projects, analyses, reports, and KPIs. The Jobs section provide information on currently running jobs. The Recent section displaying recently viewed or created analysis or dashboards, and the Most Popular section include frequently accessed items.

This topic covers the basic search of catalog objects from the global header. Depending upon how your system is configured, you will use the basic search or the fully integrated full-text search to quickly find an object within the catalog.

The basic Catalog search, which is the standard search delivered with Oracle BI EE, enables you to search for objects from the global header and the Home or Catalog pages. In the Catalog page, you can use the basic Catalog search to locate an object by searching for its exact name, description, location, and type, only. You find only those objects for which you have the appropriate permissions. When the desired object is located, you can select the object to display it for viewing or editing, as your permissions allow.

The Catalog page is displayed with the results that match your search criteria.

This topic covers creating a new analysis by using the Analysis Editor.

The Select Subject Area pop-up appears.
A subject area contains columns that represent information about the areas of an organization's business or about groups of users within an organization. When you create a new analysis, this subject area is known as the primary subject area and will appear in the Subject Areas pane of the Analysis Editor. If, as you work, you need more data, you can add additional subject areas if you have permission to access these additional subject areas.

The Analysis Editor is composed of tabs and panes, as shown in the screen shot, representing the subject area (columns), available catalog objects, selected columns for the analysis, and filters (which limit the selected data).

A subject area contains folders, measure columns, attribute columns, hierarchical columns, and hierarchy levels that represent information about the areas of an organization's business or about groups of users with an organization. Subject areas usually have names that correspond to the types of information that they contain, such as Period, Regions, Products, Orders, and so on.

In this example:

• The selected subject area is A - Sample Sales
• The four tabs - Criteria, Results, Prompts and Advanced are displayed at the top of the Editor
• Selected columns pane is empty as you are yet to choose the columns
• Filters is empty as well waiting for the column selections and further criteria

There are various column types in a subject area. They are:

The screen shot shows folders and columns.

The selected columns are displayed in the Selected Columns section. Your analysis criteria should look like this:

Note: In the Selected Columns section, you can reorder the columns in your analysis by clicking and dragging them.

Click the Results tab. The default Compound Layout is displayed.

The Compound Layout is a composition of many views. By default, both a Title and Table view are defined for you when using attribute and measure columns. A Pivot Table view is automatically created when using hierarchical columns in your analysis.

The Title view allows you to add a title (the default), a subtitle, a logo, a link to a custom online help page, and timestamps to the results. The Table view displays results in a standard table. You can navigate and drill down in the data. You can add totals, customize headings, and change the formula or aggregation rule for a column. You can also swap columns, control the appearance of a column and its contents, and specify formatting to apply only if the contents of the column meet certain conditions.

Note: In the Compound Layout, you can create different views of the analysis results such as graphs, tickers, and pivot tables. These are covered in this tutorial going forward.

This topic demonstrates how to filter, sort, and save the analysis you have created above.

You will add a filter to the analysis and then save the filter. Filters allow you to limit the amount of data displayed in the analysis and are applied before the analysis is aggregated. Filters affect the analysis and thus the resulting values for measures. Filters can be applied directly to attribute columns and measure columns.
A filter created and stored at the analysis level is called an inline filter because the filter is embedded in the analysis and is not stored as an object in the Presentation Catalog (Catalog). Therefore, an inline filter cannot be reused by other analysis or dashboards. If you save the filter however, it can be reused and is known as a named filter. (Named filters can also be created from the global header.)

Perform the following steps to filter, sort and save the previously created analysis.

Click OK. The Filters pane displays the newly created filter.

The Save As dialog box appears. A filter must be saved to a subject area folder so that it is available when you create an analysis using the same subject area.

Navigate to the Subject Area Contents folder under the My Folders and select the A - Sample Salesfolder. Name the filter Americas and EMEA filter and accept the default location. If a Confirm Save Location dialog box appears, accept the default. Oracle BI EE allows you to save any type of business intelligence object to any location within the Catalog. However, for some object types such as filters, Oracle BI EE suggests the best Catalog location.

The Save As dialog box should look like this:

The Filters pane should look like this:

Next, you save the analysis so that you can verify the creation of your named filter within the Catalog.

The analysis is saved to the catalog folder Regional Revenue.

Observe that a sort icon is added to 1- Revenue. The order of the sort is indicated by an arrow; in this case, the arrows points down, indicating that it is descending. Additionally, if multiple sorts are added, a subscript number will also appear, indicating the sequence for the sort order.

This concludes the topic of saving an analysis and sorting it.

This topic covers how to add selection steps to an analysis. Both filters and selection steps allow you to limit the data displayed in your analysis. Unlike filters that are applied before the analysis is aggregated, selection steps are applied after the analysis is aggregated. Selection steps only affect the members displayed, not the resulting aggregate values. For example, the outline total for the top level of a hierarchy is not affected if some members of the hierarchy are excluded from the selection. Selection steps are per column and cannot cross columns. While measure columns appear in the Selection Steps pane, you cannot create selection steps for them. Note that however, the grand totals and column totals are affected by selections. You can create selection steps for both attribute columns and hierarchical columns.

You will add a selection step for products.

The Selection Steps pane opens.

The Edit Member Step dialog box appears with the list of available products.

You will use the shuttle icons to move column members between the Available and the Selected columns.

The Selection Steps pane appears with the new values added. Observe that you can also save the Selection Steps as an object in the Catalog by clicking the Save icon.

The Table Editor appears.

Before adding a total for Region, remove the sort from 1 - Revenue: Click the Criteria tab, click the More Options icon for 1- Revenue, and then select Sort > Clear Sort.

Now you will add a total by region to your analysis.

Click the Edit View icon in the Table view. The Table Editor appears.

This concludes the section on adding totals to your analysis.

After you create and run an analysis, default formatting rules are applied to the analysis' results. Default formatting rules are based on cascading style sheets and XML message files. You can create additional formatting to apply to specific results. Additional formats help you to highlight blocks of related information and call attention to specific data elements. You can also use additional formatting to customize the general appearance of analyses and dashboards.

You will apply formatting to the C50 Region column.

In the Layout pane, click the More options icon for C50 Region and select Format Headings.

The Edit Format dialog box appears.

The Preview pane should look like this:

The Table Properties dialog box appears.

In this topic, you create a new analysis to which you add a graph, and then apply the named filter created in the first topic.

You will add a graph to this analysis.

The default Graph view appears below the Table view.

Both views are removed from the Compound Layout. Note however, that they are still available for use from the Views pane.

The Graph, like other view editors, is composed of three sections:

The Graph properties dialog box appears. The Graph properties dialog box is composed of four tabs: General, Style, Scale, and Titles and Labels. These tabbed pages allow you to do the following:

Note: The "Animate graph on Display" check box specifies whether to show initial rendering effects and is selected by default. For example, the bars on a horizontal graph start at the x-axis and move up the scale on the x-axis to the current measurement level.

"Listen to Master-Detail Events" allows you to specify this analysis as a detail view in a master-detail relationship. You will use this option in a subsequent step when working with pivot tables

Specifically setting axis limits and tick marks allows you to control what you see on your graph. If you override the system default for tick marks, the colors that you have selected for horizontal and vertical grid lines on the General properties tabbed page will be applied to both major and minor ticks.

Once you have zoomed in, a slider appears.

The prompt allows you to select each region individually, making the graph a bit easier to consume.

In this topic, you create a new analysis with hierarchical columns and apply selection steps.

In the Analysis Editor, double-click the following columns:

Folder	Columns
Products	Products Hierarchy
Time	Time Hierarchy
Base Facts	1 - Revenue

Scroll down to view the Selection Steps pane and expand it. In the Products - Products Hierarchy section, click 2. Then, New Step, then select Select Members based on Hierarchy.

The New Hierarchy Selection step dialog box appears.

Notice that the table include only BizTech and HomeView, and not FunPod, because you selected Siblings of Funpod.

Observe that FunPod is now included in the table.

The Selection Steps view is displayed in the compound layout.

This new group is added to the Compound Layout view.

You will now be able to see and edit the values in My Group.

You can also see the values if you expand the My Group in the Pivot table. You can add My Group to all other views in addition to the current pivot table of the analysis.

This concludes the topic of creating a Pivot table and applying selection steps to the table.

In this topic, you by create a new analysis with a hierarchical column, and you apply a named filter, gauges, and some formatting. You also add totals. Pivot tables provide the ability to rotate rows, columns, and section headings to obtain different perspectives of the same data. They are interactive in that they are drillable, expandable, and navigable. You review some features of pivot tables.

The Column Properties dialog box appears.

Click the Results tab. Review the formatting changes that you made to the Revenue column.

Click on icon to expand Total Orders.

Notice Total Orders has moved to the bottom of the hierarchy.

Delete the Title view from the analysis and then click the Edit View icon to format the pivot table. Use the Layout pane to format the pivot table as follows: Drag P4 Brand below Measure Labels, and then drag C50 Region to the Sections area.

The Layout pane should look like this:

The pivot table should look like this:

Next, you add a calculation to the pivot table by duplicating the Revenue column. Click the More Options icon for the Revenue column and select Duplicate Layer.

The duplicated Revenue column appears.

To add a calculation to reflect a percentage of the parent, for the duplicate Revenue column click More Options > Show Data As > Percent of > Row Parent.

The calculation is added to the column. The pivot table should look like this:

Expand the Orders Hierarchy by clicking the carat sign icon for Total Orders for the Americas. The carat icons are used to expand and collapse the data for analysis. The Orders Hierarchy contains Orders on the row edge and Total Orders as the parent. Revenue is the measure.

Because hierarchical columns imply pivot tables, you are able to not only sort on members and measures, but on rows. Hierarchical members on the row edge can include sort carat icons () , which allow you to sort the members on the column edge by that row, in either ascending or descending order. These carat icons do not appear for attribute columns, which do not have the concept of a row edge.

When you sort members in a hierarchical column, you always sort within the parent; that is, children are never sorted outside of their parent. The children appear below the parent in the proper sort order; the parent is not sorted within its children.

To Add a gauge view to this pivot table, click New View in the analysis toolbar and select Gauge > Dial.

The gauge view is added.

Master-detail linking of views allows you to establish a relationship between two or more views such that one view, called the master view, will drive data changes in one or more other views, called detail views.
You will create a Master-Detail linking for the Regional Revenue Pivot analysis.

Both the Pivot Table view and the Gauge view (the detail view) update to reflect the drill. Notice that the slider in the Gauge view includes the drill-down members for region.

You create a Narrative view to provide information such as context, explanatory text, or extended descriptions along with column values for an analysis. You can include values from attribute, hierarchical, and measure columns. If you want to include hierarchy levels in a Narrative view, use selection steps to display the levels. The Narrative view is a combination of text and query column values.

To create a meaningful Narrative view, begin by creating a new analysis that includes a calculated item.

Create a new analysis that includes the following columns:

Folder	Columns
Customers	C50 Region
Customers	C0 Customer Number
Customers	C1 Customer Name
Base Facts	4-Paid Amount
Base Facts	3-Discount Amount

Tip: You can copy the line of code from this page and paste it into the Column Formula text box.
The Column Formula text box should look like this:

• You use the Prefix text box to enter the header for the narrative. This text is displayed at the beginning of the narrative.
• You use the Narrative text box to enter the narrative text that will appear for each row in the results. You can include both text and column values. Include a line break code at the end of this field to force each line of text and values onto its own line. To include values, use the at sign (@ by itself to indicate the first column. If you include multiple signs, then the first occurrence of the sign corresponds to the first column, the second occurrence corresponds to the second column, and so on. You use the @ sign to include the results from the designated column in the narrative.
• You use the Row separator text box to enter a row separator for each line from the Narrative field that contains values. For example you might enter a string of plus signs (+) between each line.
• You use the Rows to display text box to enter the number of rows from the column to return. For example, enter 5 to display values from the first 5 rows of the column. For a hierarchical column, you can use selection steps to display hierarchy levels with the hierarchical column. A hierarchy level is considered a row.
• You use the Postfix text box to enter the footer text to appear at the bottom of the narrative. To display the footer information on a separate line from the actual narrative text, include markup tags in the Postfix field. Ensure that the narrative ends in a line break, or that the footer begins with a line break.
• The toolbar allows you to use HTML code and markup to enhance the narrative text.

• In the Prefix text box, enter This analysis shows the discount percentage for each customer within the , ensuring that you leave a single space following the last word.
• In the Narrative text box, enter @1, where the number "1" represents the first column in the analysis C50 Region. Then, select the @1 that you entered and click the bold icon.
• In the Postfix text box, enter region., ensuring that you include a space before region and period after region.
• Enter 1 in the "Rows to display" text box.

Notice that a preview is provided at the bottom of the editor.

In this topic, you add a Column Selector view and a View Selector view to an analysis. A Column Selector view adds a column selector to the results. A column selector is a list from which users can dynamically select the columns that display in results. This allows you to analyze data along several dimensions. By changing the measure columns, you can dynamically alter the content of the analyses you have created. A View Selector view enables you to select from saved views.

Click the Edit View icon for the Column Selector view.
The Column Selector editor appears.

The Compound Layout appears:

The values change appropriately. Note, because you have set a custom heading for the C50 Region column earlier, the custom heading is still displayed for the column.

• Delete the Title view from the Compound Layout.
• In the Choose a column: list of the Column Selector view, select C50 Region, and then delete the Column Selector view from the Compound Layout.
• From the New View menu, select Graph > Bar > Vertical to add a vertical bar graph.

These changes will allow you to showcase the analytic data-driven views.

A preview appears at the bottom of the editor. Note that these views are data-driven views, unlike the Column Selector and Title views, which were deleted from the Compound Layout.

The Compound Layout should look like this when the Graph view is selected:

You can include dimensions and measures from external data sources in your analyses. The external data is loaded to the database, but is not part of the BI metadata catalog. You can create an analysis that include only data from an external source, or you can blend dimensions and measures from the external data source with dimensions and measures defined in the BI metadata catalog. The external data must be stored in a Microsoft Excel format spreadsheet file. You import the data from the spreadsheet file into the BI EE database as an external data source. You then add columns from the external data source to your analyses. When the data in spreadsheet file changes, you can refresh the BI EE database with the changes.

In this example, you will create a blended analysis. You will extend the dimensions for the Regional Revenue analysis by adding columns for Currency and Capitol City from an external source. The source data for the columns is stored in the file XSA World Statistics.xlsx.

XSA World Statistics - World Statistics is added to the Subjects Areas pane as a data source.

• Drag C50 Region from the Columns and Measures area to the Sections area.
• Drag C52 Country Name and drop it to the left of P1 Product, so that C52 Country Name is the first column.
• Drag Capital City and drop it to the left of P1 Product, so that it is between C52 Country Name and P1 product.

The analysis now includes detail by country. For each country, the Capital City and Currency columns display extended dimension data loaded from XSA World Statistics.xls.

My Dashboard, a personalized view, is a dashboard page that you create and save as your default, personal starting page by using the Preference tab of the My Account dialog box. To open My Dashboard, perform the following steps:

An empty My Dashboard page appears.

When you open a dashboard, including My Dashboard, the content appears in one or more dashboard tabbed pages. Pages contain the columns and sections that hold the content of a dashboard, and  every dashboard has at least one page. Multiple pages are used to organize content.

This example shows an empty My Dashboard page with no content. You can hover over the Edit icon to edit the dashboard and add content.

Note: If you have chosen, or if your company has setup, My Dashboard as your default, then you use dashboard template pages to populate your personal dashboards (My Dashboard) when you first log in as a new user. This allows you to see one or more dashboard pages with content, rather than an empty dashboard. It also gives you a starting point to build your own dashboard pages.

Click the Edit icon () in the middle of the page to add content to your empty dashboard page.

The Dashboard Builder appears and automatically creates page 1 of your dashboard .

Using the Dashboard Builder, you can add pages and objects to a dashboard and control the page layout. The Dashboard Builder is composed of the following:

• Dashboard Toolbar: The toolbar allows you to perform tasks such as adding or deleting pages, previewing, saving, and so on.
• Dashboard Objects pane: Items that are used only in a dashboard. Examples of dashboard objects are sections to hold content, action links, and embedded content that is displayed in a frame on a dashboard
• Catalog pane: Items that you or someone else has saved to the Catalog, for example, analyses, prompts, and so on. In a dashboard, the results of an analysis can be shown in various views, such as a table, graph, and gauge. (The results of an analysis are the output that is returned from the Oracle BI Server that matches the analysis criteria.) Users can examine and analyze results, save or print them, or download them to a spreadsheet.
• Page Layout pane: This is a workspace that holds your objects, which are displayed on the dashboard.

In the Dashboard Toolbar, the Tools toolbar button provides options to set dashboard properties, set page report links, and so on.

• Columns are used to align content on a dashboard. (Sections within columns hold the actual content.) You can create as many columns on a dashboard page as you need.
• Sections are used within columns to hold the content, such as action links, analyses, and so on. You can drag and drop as many sections as you need to a column.
• Alert Section is used to add a section in which you display Alerts from Agents, if any. ( Agents dynamically detect information-based problems and opportunities, determine the appropriate individuals to notify, and deliver information to them through a wide range of devices such as e-mail, phones, dashboard alerts, and so on.) An Alert section is added by default to the first page of My Dashboard if you do not manually include one. You cannot disable the appearance of an Alert section on the first page of My Dashboard. You can add an Alert section to an additional dashboard page so that section will then appear on both dashboard pages.

The Column object appears on the Page Layout pane.

Regional Revenue Graph appears in the column. Observe that a Section is automatically created for you. You can also drag an analysis directly onto an empty Layout Pane without first creating a column. The Dashboard Builder automatically creates the column for you. You can then add sections automatically to that column by dragging analyses below the existing sections.

Click the Save icon () to save the dashboard page and then click Run .

My Dashboard appears with the selected analysis Regional Revenue Graph.

The New Dashboard dialog box appears.

Note: If you save the dashboard in the Dashboards sub folder directly under the /Shared Folders/ sub folder, the dashboard will be listed in the Dashboard menu on the global header. If you save it in a Dashboards sub folder at any other level (such as /Shared Folders/Sales/Eastern), it will not be listed. If you choose a folder directly under /Shared Folders in which no dashboards have been saved, then a new Dashboards folder is automatically created for you. For example, if you choose a folder named /Shared Folders/Sales in which no dashboards have been saved, a new Dashboards folder is automatically created and the Location entry changes to /Shared Folders/Sales/Dashboards. A new Dashboards folder is not automatically created if you choose a folder at any other level.

As mentioned above, because this dashboard was not created in a Dashboards sub folder directly under a /Shared Folders/first level sub folder, the dashboard will not be listed in the Dashboard menu on the global header. To open the dashboard, navigate to it in the Catalog, or open it from the Recent list on the Home page or in the global header's Open menu.

Dashboard editing is allowed for users with the appropriate privileges. In this topic, you enhance My Dashboard.

The Dashboard Properties dialog box appears.

From this dialog box, you can do the following:

• Change the Styles—Styles control how dashboards and results are formatted for display, such as the color of text and links, the font and size of text, the borders in tables, the colors and attributes of graphs, and so on
• Add a description—Descriptions are displayed when Oracle BI Administrators use the Catalog Manager
• Add hidden prompts, filters, and variables
• Specify the links that will display with analyses on a dashboard page
• Rename, hide, reorder, set permissions for, and delete dashboard pages

Page 1 has been renamed Regional Revenue.

Click the Save icon () to save the dashboard page and then click the Run icon .

My Dashboard appears with the column containing Performance Tile Analysis anchored at the top of the page layout. It will not scroll off the page as you scroll the content in the other column.

You use the Section Properties drop-down list to do numerous tasks:

Previously, your analysis returned more than 25 records, therefore this test should evaluate to False.

The Order Status and Order Type detail for Diego Link are displayed.

Saved customizations allow you to save and view dashboard pages with your most frequently used or favorite preferences for items such as filters, prompts, column sorts, drills in analyses, and section expansion and collapse. By saving customizations, you do not need to make these choices manually each time you access the dashboard page.

Other tabbed pages in the My Account dialog box include the following:

• BI Publisher Preferences—Use this tabbed page to view the default profile for BI Publisher.
• Delivery Options—Use this tabbed page to configure your delivery profiles for the delivery of alerts by agents.
• Roles and Catalog Groups—Use this tabbed page to view a list of the roles to which you have been assigned by the Oracle BI Administrator.

You can export an entire dashboard or a single dashboard page to a Microsoft Excel 2007+ spreadsheet.

The following example shows the Regional Revenue page of My Dashboard exported to a spreadsheet:

Named Dashboard Prompts in the Catalog can be applied to any dashboard or dashboard page that contains the columns specified in the prompt. 

The Definition and Display panes appear. The Definition pane allows you to add, organize, and manage a named prompt's columns. You can use column prompts, image prompts (maps), currency prompts, and variable prompts. The Definition table lets you view high-level information about the prompt's columns. You can also use this table to select columns for editing or deleting, arrange the order in which the prompts appear to the user, or insert row or column breaks between prompt items.

The Display pane is a preview pane that allows you to view the prompt's layout and design.

In the Definition pane, click the New prompt icon (), and select Column Prompt.

The User Input list appears for column and variable prompts and provides you with the option to determine the User Input method for the user interface. The user will see one of the following: check boxes, radio buttons, a choice list, or a list box. You use this item in conjunction with the Choice List Values item to specify which data values appear for selection. For example, if you selected the User Input  method of Choice List and the Choice List Values item of  All Column Values, the user will select the prompt's data value from a list that contains all of the data values contained in the data source.

The New Prompt dialog box should look like this:

You can manage prompts with different options.

• You can choose to show or hide a prompt's apply and reset buttons. If the designer chooses to hide the apply button, then the specified prompt value is immediately applied to the dashboard or analysis.
• The prompt Reset button now provides three reset options: Reset to last applied values, Reset to default values, and Clear All.
• The row-based layout prompt option is added to the prompt editor's Definition pane. You can display your prompts in a row or in a column.

Click the Add Dashboard Page icon.

In the Page Name field, enter Sales Rep Detail and click OK.

The analysis is filtered by your selections.

Variable prompts allow you to make a selection from a list of custom values and pass the selection to a Presentation Variable. You can use the presentation variable in your analysis.
In this topic you create a variable prompt for Revenue Projection.

In the Definition pane, click the New prompt icon (), and select Variable Prompt.

• The Prompt for field allows you to name a Presentation Variable or Request Variable whose value will change as per the selection made in the variable prompt. Accept the default value of Presentation Variable. In the text box next to Prompt for field, enter VarRevProj for the variable name.
• The Label text box allows you to enter a meaningful label that appears on the dashboard next to the prompt. Enter Revenue Projection (%): (add a space following the colon).

• In the Choice List Values area, you add the values that you want to display in the variable prompt choice list. Add three values, 10, 20, and 30. To add the values, click the Select Values icon () icon, enter a value, and click OK.

Expand the Options section, and in the Variable Data Type list, select Number. In the Default selection list, select Specific Custom Value, then click the Select Values icon(), select 10, and click OK .

The New Prompt dialog box should match the following:

The prompt is added to the Definition pane.

The purpose of this formula is to calculate the projected revenue based on the value of the presentation variable VarRevProj. The value of VarRevProj varies with the user selection for the Revenue Projection prompt.

The dashboard is updated to show 30%.