15 The Data Analysis Tool
The Data Analysis tool enables you to create Analytic Views with multidimensional metadata.
To reach the Data Analysis page, select the Data Analysis menu in the Data Studio tab of the Launchpad.
Description of the illustration data-analysis-pane.png
You create Analytic Views on top of a fact table with several dimensions and hierarchies. Analytic views refer to tables in the database and allow users to create hierarchies for dimensions. You can also create Analyses and reports using information from the Analytic Views. The Data Analysis homepage enables you to search for Analyses, view and perform tasks such as edit, delete, view or rename Analyses. You can also analyze, find errors, export, edit, compile and delete Analytic Views. You can analyze tables and generate SQL reports from them.
Select the Data Analysis card from the Data Studio suite to access this tool. You can also access it by clicking the Selector icon and selecting Data Analysis from the Data Tools menu in the navigation pane.
Note:
If you do not see the Data Analysis card then your database user is missing the required DWROLE role.
The Data Analysis home page consists of three parts: Analyses, Analytic Views and Tables.
Description of the illustration dataanalysis-homepage.png
Analyses
Description of the illustration adp-analyses.png
The top section of the homepage comprises of a list of Analyses. Use the search field to search for Analyses you create. The top section of the homepage comprises of a list of Analyses. Use the search field to search for Analyses you create.
Analyses are analysis of multiple Analytic Views. The Analyses card displays the name of the analysis. Click Actions (three vertical dots) to open the context menu.
- View: Opens the Analysis View page in a new window where you can view the analysis.
- Edit: Opens the selected Analysis page where you can edit the reports present in the analysis.
- Rename: Allows you to rename the Analysis. Click save to modify the new name.
- Delete: Opens the delete Analysis dialog where you can delete the analysis.
Analytic Views
Description of the illustration adp-analytic-views.png
- Analyze: Opens the Analytic View browser and the Analysis View page in a new window where you can view the Analysis.
- Data Quality: Opens the Data Quality page where the tool validates the selected Analytic View for errors and lists them out.
- Export: Allows you to export the Analytic View to Tableau and PowerBI.
- Edit Analytic View: Opens the Edit Analytic View dialog box where you can edit the properties of the selected Analytic View.
- Compile Analytic View: This option compiles the Analytic View and returns compilation errors if there are any.
- Show DDL: Displays the DDL statements for the Analytic View.
- Delete Analytic View: Deletes the selected Analytic View.
The +Create button enables you to create Analysis and create Analytic View from the home page.
You can select both hierarchies and measures from Analytic Views. Hierarchies are DB objects that allow users to define relationships between various levels or generations of dimension members. As the name implies, hierarchies organize data using hierarchical relationships. With this tool you can analyze and visualize data in different Points of View (POV). You can export the metadata and visualize it with sophisticated tools like Oracle Analytics Cloud (OAC) and Tableau.
Advantages of Data Analysis tool
- Visualize, analyze and inspect your data clearly and efficiently with pivot tables
- Calculate total number of errors present in the Analytic View you create and provide solutions to minimize the errors
- Automatically display meaningful insights to help you make better decisions
- Analyze your data across dimensions with support for hierarchical aggregation and drill-down
- Share your Analytic Views with the tool of your choice over various options of raw data consumption to draw meaningful insights and make them accessible to any user
By identifying relationships among tables and columns, Analytic Views enable your system to optimize queries. They also open new avenues for analyzing data. These avenues include data insights, improved hierarchy navigation, and the addition of hierarchy-aware calculations.
This tool runs complex and hierarchical SQL queries along with SQL extensions in the background, which simplifies real-time calculations. It makes complex data more accessible and easier to understand.
The Data Analysis Page
Note:
- OAC has in-built tools to search and utilize Analytic Views.
- We have no direct support for Microsoft Power BI, yet its users can map their tool to the AV transparency views to avail some of the benefits of Analytic Views.
Searching and obtaining information about Analytic Views
When you first open the Data Analysis page, it displays the list of schemas and Analytic Views. With Select Schema, you can select a preferred Schema from a list of schemas available in the drop-down.
The Select Analytic Views drop-down enables you to select an available Analytic View associated with the schema. When you create an Analytic View, it appears in the drop-down option with your schema. The Refresh AV icon refreshes the contents of the selected Analytic View.
The Action icon next to the Refresh AV button enables you to manage Analytic Views. You can Create Analytic View, Edit Analytic View, Compile Analytic View, Show the Data Definition Language (DDL) that generates the Analytic View or Delete Analytic View from the menu.
Obtain information about Analytic Views
By default, Analytic Views are filtered by the current user's schema, as indicated by the schema list below the menu-bar. You can remove the selected schema filter by selecting another user's schema. To search for Analytic Views in other schemas, select one of the schemas from the drop-down.
If there is no Analytic View associated with the schema selected, the tool prompts you to create an Analytic View.
Creating Analytic Views
You can create Analytic Views and view information about them. You can also edit and perform other actions on them.
When you create an Analytic View, you identify a fact table that contains the data to inspect. The Generate Hierarchies and Measures button looks at the contents of that table, identifies any hierarchies in the fact table, and searches for other tables that may contain related hierarchies.
- Autonomous Aggregate Cache, which uses the dimensional metadata of the Analytic View to manage a cache and that improves query response time.
- Analytic View Transparency Views, which presents Analytic Views as regular database views and enables you to use your analytic tools of choice while gaining the benefits of Analytic Views.
- Analytic View Base Table Query Transformation, which enables you to use your existing reports and tools without requiring changes to them.
Create Analytic View
To create Analytic View, click Create from the Data Analysis home page and select Create Analytic View to begin the process.
Click Cancel to cancel the creation of the Analytic View at any time.
Specify Attributes of the Analytic View
- The name for the Analytic View
- The fact table for the view
- Advanced options
You can also preview the data of the fact table and see statistics about that data.
In the Name field, specify a name of your choice.
The Schema field has the current user's schema. You can only create an Analytic View in that schema.
In the Fact Table field, expand the drop-down list and click More Sources. The Select Sources dialog box has a list of the available tables and views. Select a table or view from the list.
To filter the list, begin typing characters in the Filter field. As you type, the list changes to show the tables or views that contain the characters. Clear the field to show the complete list again. After you select a table or view, click OK.
To enable or disable the advanced options, on the Create Analytic View pane, click the Show Advanced Options icon at the bottom left. Select or deselect options as desired.
To view the data in the fact table and statistics about the data, click the Preview Data button. In the Preview and Statistics pane, the Preview tab shows the columns of the table and the data in the columns.
The Statistics tab shows the size of the table and the number of rows and columns. The statistics may take a few moments to appear, during which time the message, "No statistics available..." may appear. The statistics include the names of the columns, their data types, the number of distinct values and null values, the maximum and minimum values, and other information. The bar graph displays the top unique column values and the number of their occurrences for the selected column. Point to a bar in the graph to see the number of occurrences of the unique value.
Click Close to close the Preview and Statistics pane and return to the Create Analytic View pane.
Click on Generate Hierarchies and Measures icon.
The Generating Hierarchies and Measures dialog box displays the progress of searching for dimension tables, analyzing the dimension tables and identifying and creating the data sources, joins, hierarchies, and measures to use. When the process completes, click Close.
The Search for Dimension Tables check box when selected, enables you to search for dimension tables while generating hierarchies and measures.
After the hierarchies and measures are generated, they are displayed under their respective tabs. Review the hierarchies and measures created for you.
Specify the Name, Fact Table and select Advanced Options in the General tab of Create Analytic View pane. Click Create to generate an Analytic View.
View Data Sources
The Data Sources tab displays the sources of the data and the relationships among them. It has a graphical display of the fact table and the related dimension tables. For example, a fact table of health insurance data might have columns for geography identifiers, income codes, and gender codes. The Data Sources tab would display items for the fact table and for the geography, income, and gender dimension tables.
You can add hierarchies from data sources even after generating hierarchies from the existing fact table. You can add one or more hierarchies to your new or existing analytic view. Multiple hierarchies can be defined and used in an analytical view, however only one will be used by default.
Right-click the Data Sources tab and select Add Hierarchy Sources or select Add Hierarchy Sources.
Selecting Add Hierarchy Sources launches an Add Hierarchy Source dialog box.
You can view all the fact tables and views associated with the analytic view.
In the filter field, you can either manually look for the source or start typing to search for the fact table or views from the list of available fact tables and views. After typing the full name of the source, the tool automatically matches the fact table or view.
Select Generate and Add hierarchy from Source to generate analysis and hierarchies associated with the source data you select.
Select Find and Add Joins to link all the data sources with the fact table. You can add multiple join entries for a single hierarchy.
Click OK to select the source.
The Generating Hierarchies and Measures dialog box displays the progress of analyzing the dimension tables and creating the hierarchies. When the process completes, click Close.
Note:
When you add a hierarchy from the data source, you see the new hierarchy in the list of hierarchies in the Hierarchies tab. You can navigate between the Data Sources tab, the Hierarchies tab, the Measures tab, the Calculations tab. You can add a hierarchy from a source that is not connected by navigating back to the Data Sources tab.
Select Remove Hierarchy Source to remove the hierarchies you create from the data sources. You cannot remove hierarchies generated from the fact table you select from this option.
Expand Joins to view the Hierarchy Source, Hierarchy Column and the Fact column mapped with the Analytic View. The Joins is visible only when the hierarchy table differs from the fact table. You can add multiple join entries for a single hierarchy.
Expand Sources to view the fact table associated with the Analytic View. The data model expands to include the data from the source that you added.
Pointing to an item displays the name, application, type, path and the schema of the table. Click the Actions (three vertical dots) icon at the right of the item to display a menu to expand or collapse the view of the table.
An expanded item displays the columns of the table. Pointing to a column displays the name, application, type, path, and schema of the column.
The lines that connect the dimension tables to the fact table indicate the join paths between them. Pointing to a line displays information about the join paths of the links between the tables. If the line connects a table that is collapsed, then the line is dotted. If the line connects two expanded tables, then the line is solid and connects the column in the dimension table to the column in the fact table.
View and Manage Hierarchies
The Hierarchies tab displays the hierarchies generated by the Analytic View creation tool. The display includes the name of the hierarchy and the source table.
An analytic view must include at least one hierarchy.
To add a Hierarchy, click Add Hierarchy. This results in a display as a list of column in that table. Select a column that operates as the detailed level of the hierarchy and be the join-key to the fact table.
To remove the hierarchy, select the hierarchy you want to remove from the list and click Remove Hierarchy
Select Move Up or Move Down to position the order of the Hierarchy in the resulting view.
Click Switch Hierarchy to Measure to change the hierarchy you select to a measure in the Measures list.
You can also Add Hierarchy and Add Hierarchy From Table by right-clicking the Hierarchy tab.
If you click on a hierarchy name, a dialog box displays the Hierarchy Name and Source.
To change the source, select a different source from the drop-down list.
Select Add Level to add a level to the hierarchy. Click Remove Level to remove the selected level from the hierarchy.
To view the data in the fact table and statistics about the data, click the Preview Data button. In the Preview and Statistics pane, the Preview tab shows the columns of the table and the data in the columns. The Statistics tab shows the size of the table and the number of rows and columns.
Note:
You can enter multiple level keys Member Name, Member Caption, Member Description and Sort By.
Member Captions and Member Descriptions generally represent detailed labels for objects. These are typically end-user-friendly names. For example, you can caption a hierarchy representing geography areas named GEOGRAPHY_HIERARCHY as "Geography" and specify its description as "Geographic areas such as cities, states, and countries."
To see the measures for the Analytic View, click Measures tab. To immediately create the Analytic View, click Create. To cancel the creation, click Cancel.
View and Manage Measures
The Measures tab displays the measures suggested for the Analytic View. It displays the Measure Name, Column, and operator Expression for each measure.
The measures specify fact data and the calculations or other operations to perform on the data.
To add measures, click Add Measure. You can view a new measure at the bottom of the measures list. To remove the measure, select the measure you want to remove from the list and click Remove Measure.
To alternatively add a measure from the data source, right- click the Measures tab. This pops up a list of columns that can be used as measures. Select one measure from the list.
You can exclude a column from the measures on right-clicking the Measures tab and selecting Remove Measure.
Click Switch Measure to Hierarchy to change the measure you select to hierarchy in the Hierarchies list.
You must specify a measure as the default measure for the analytic view; otherwise, the first measure in the definition is the default. Select Default Measure from the drop-down.
To add a measure, right-click the Measures tab and select Add Measure. To remove a measure, select the particular measure you want to remove, right-click on it and select Remove Measure.
You can select a different column for a measure from the Column drop-down list. You can select a different operator from the Expression drop-down list.
In creating an analytic view, you must specify one or more hierarchies and a fact table that has at least one measure column and a column to join to each of the dimension tables outside of the fact table.
Create new calculated measures
You can add measure calculations to a query of an analytic view.
The measures and hierarchies associated with the analytic views enable us to create new calculated measures.
Calculated measures return values from data stored in one or more measures. You compute these measures at run time.
Note:
You can create the measures without increasing the size of the database since the calculated measures do not store the data. However, they may slow performance. You need to decide which measures to calculate on demand.
The Analytic Views provides easy-to-use templates for creating calculated measures.
Once you create a calculated measure, it appears in the list of measures of the Analytic View .You can create a calculated measure at any time which is available for querying in SQL.
The Data Analysis tool provides easy-to-use templates for creating calculated measures.
Click Add Calculated Measure to add calculations to the measures. You can view the new calculation with system generated name in the Calculations tab.
Click the newly created calculated measure.
In the Measure Name field, enter the name of the calculated measure.
You can select preferred category of calculation from a list of options such as Prior and Future Period, Cumulative Aggregates, Period To Date, Parallel Period, Moving Aggregates, Share, Qualified Data Reference, and Ranking using the Calculation Category drop-down.
Your choice of category of calculation dynamically changes the Calculation Template.
For more details on how to use Calculation templates, see Using Calculation Templates.
Select the Measure and Hierarchy on which you want to base the calculated measures.
Select Offset value by clicking the up or the down arrow. The number specifies the number of members to move either forward or backward from the current member. The ordering of members within a level is dependent on the definition of the attribute dimension used by the hierarchy. The default value is 0 which represents POSITION FROM BEGINNING.
The Expression field lists the expressions which the calculated measure uses.
On the creation of the Analytic view, the calculated measure appears in the navigation tree in the Calculated Measures folder.
Click Create. A confirmation dialog box appears that asks for your confirmation. Select Yes to proceed with the creation of Analytic View.
After creating the Analytic View, you will view a success message informing you of its creation.
On editing the Analytic View you create, you can view the calculated measure in the navigation tree in the Calculations folder.
Click the Tour icon for a guided tour of the worksheet highlighting salient features and providing information if you are new to the interface.
Click the help icon to open the contextual or online help for the page you are viewing.
Click Show DDL to generate Data Definition Language statements for the analytic view.
Edit Analytic View
You might want to edit an Analytic View to make changes to the data sources, the hierarchies, or the measures.
To edit an Analytic View, click the Action icon on the Analytic View item, then click Edit Analytic View. On the Edit Analytic View screen, select a tab and make changes as desired.
When you have completed the changes, click Update.
Working with Analyses
Analyses are a collection of multiple reports on a single page, which provides quick access to multiple data analyses collected from different Analytic Views.
Analyses enable you to monitor performance, create reports and set estimates and targets for future work. It provides you a visual representation of performance with charts and graphs.
You can access the Analyses page by clicking the Analyses tile on the Data Analysis home page.
Viewing Analyses
Analyses provide you an insight into the performance of your data.
You can use the Analysis and the Analyze pane to search or browse Analytic Views, view their analysis, or reports you have access to. Clicking on the Analyses takes you to a page where you can view the Analyze pane. Here you can view default hierarchy level and measures selected. You can drag and drop any levels and measures from the Analytic View browser to rows/columns and Values in the drop area respectively. This defines your analysis criteria. Once the values are dropped, the Data Analysis tool generates a query internally. The tool displays the results of the analysis in the form of reports in the Analyses that matches your analysis criteria. You can add multiple reports to the Analysis. You can also examine and analyze the reports and save them as a new analysis. You can just save the Analysis and not a single report. Once you save all the reports, it will be part of that single Analysis. Reports are unnamed.
Workflow to build Analyses
Here is the workflow to build an analyses.
- Create a useful analysis: Before creating your first analysis, you can construct a useful analysis over a single Analytic View. This way you can generate analyses on which you can create reports that you display on the Analyses.
- Create Analysis: Create an analysis to display data from analysis.
- Create Reports: An Analysis can have multiple reports that are independent of one another. This can be used to compare and analyze data generated from different Analytic Views.
- Save Analysis: Create customized Analyses that enable you to view reports and their analyses in the current state and save it for future reference.