Skip to main content
insightsoftware Documentation insightsoftware Documentation
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

Advanced Use of Angles for Oracle Generator Service

Overview

This chapter provides information about how to use the advanced settings of the Angles for Oracle Generator Service.

Editing Metadata Refresh Query

What Is Metadata Refresh Query?

The metadata refresh query is available in the Metadata Refresh Query box that can be accessed by selecting the Show Advanced link on the Metadata Source tab of the Data Source window. The query is used by the Angles for Oracle Generator Service to determine if any changes have been made to the source metadata for the selected data source since the last load. Before the load process, the SQL statement is run and returns the date when the NoetixViews, Noetix Analytics, or RapidDecision metadata was last modified. The load process is run only if the date of the metadata cache refresh is older than the date returned by the metadata refresh query. However, the load process will not be skipped if the Force Metadata Reload option is selected in the Loader area of the Noetix Data Source Administrator.

When to Edit Metadata Refresh Query

By default, the metadata refresh queries are included in the definitions of the NoetixViews and, Noetix Analytics and RapidDecision data sources, and you do not need to modify the queries in most cases. However, the queries need to be modified for the data sources of custom metadata. For assistance with editing the metadata refresh queries, contact insightsoftwareSupport.

Editing BI Application Data Filter Template

What Is BI Application Data Filter Template?

The BI Application Data Filter Template allows you to define an SQL statement that will be used by the (Undefined variable: General.ProductShortName) to provide data authentication (row-level security) within a Microsoft Power BI interactive report template. In the Data Source window, the BI Application Data Filter Template box is available on the Metadata Source tab when the Show Advanced link is selected.

The template is a SELECT statement that includes out-of-the-box macro variables.

Macros for BI Application Data Filter Template

The out-of-the-box macros are as follows:

  • BEGIN_LOOP: Used to define the start of a loop that runs for each column selected in the (Undefined variable: General.ProductShortName). A loop opened with BEGIN_LOOP is closed by END_LOOP. Any text within BEGIN_LOOP and END_LOOP will be replicated for each selected column. Macros within the loop text will be processed before writing out the text.
  • BEGIN_COMMA_LOOP: Similar to BEGIN_LOOP, with the exception that BEGIN_COMMA_LOOP intelligently creates a comma delimited list such that a final trailing comma is not placed, which is useful when writing out columns within the SELECT clause of an SQL statement.
  • CATALOG_NAME: Replaces instances of the macro string with the name of the database catalog defined on the Data Query Source tab of the data source. This value is blank for Oracle data sources.
  • COLUMN_ALIAS: Is replaced by the formatted name of the column selected in the (Undefined variable: General.ProductShortName).
  • COLUMN_NAME: Is replaced by the name of the column in the physical database table.
  • DATABASE_NAME: Is replaced by the database name or TNS value defined on the Data Query Source tab of the Data Query window of the Noetix Data Source Administrator.
  • END_LOOP: Used to close a loop that was opened with BEGIN_LOOP.
  • END_COMMA_LOOP: Used to close a loop that was opened with BEGIN_COMMA_LOOP.
  • METADATA_SCHEMA_NAME: Is replaced with the name of the metadata source schema defined on the Metadata Source tab of the Data Query window of the Noetix Data Source Administrator.
  • REGISTERED_BI_TOOL_NAME: Is replaced with the name of the registered business intelligence (BI) tool on the Metadata Source tab of the Data Query window of the Noetix Data Source Administrator.
  • SCHEMA_NAME: Is replaced with the name of the schema that owns the reporting object. For NoetixViews, it is the NoetixViews schema that is specified on the Metadata Source tab. For Noetix Analytics, depending on the selected reporting object, it is either NOETIXODS or NOETIXDM. For RapidDecision, it is the RapidDecision data mart.
  • SECURITY_ALIAS: Currently not supported.
  • TABLE_NAME: Is replaced with the name of the table or view that a column is associated with.

Edit BI Application Data Filter Template

You will typically edit the BI application data filter template for non-Noetix data sources or if the default template does not meet your business requirements.

The following step-action table contains the procedure to edit the BI application data filter template.

Step

Action

1.

On the Data Source Administrator window, select a data source, and select Edit. The Data Source window appears.

2.

Select the Metadata Source tab, and select Show Advanced. The BI Application Data Filter Template box is displayed.

3.

In the box, type the required SQL statement.

4.

To use an out-of-the-box macro, in the SQL statement, place the cursor where the macro needs to be added. In the macro list, select the macro, and select Insert. Alternatively, select and double-click the macro. The macro is added to the SQL statement.

The macro must be enclosed within braces.

5.

Select OK.

Defining Custom Parameters

What Is a Custom Parameter?

A custom parameter is one that is referenced by the event-driven SQL statements during run time. The custom parameter contains an SQL statement that references default and other custom parameters. The SQL statement can be defined to return one value, which the Noetix ODBC for Oracle driver uses to run the event-driven SQL statement. The SQL statement can also be defined to return a list of values (LoV) and the Noetix ODBC for Oracle driver will prompt the user to select a value during run time. The LoV is populated with data fetched with the use of the default parameter RESPONSIBILITY that is defined out of the box and is listed under Parameters in the Data Query Source tab.

Currently, only one SQL statement is supported in the custom parameter definition.

Define a Custom Parameter

The following step-action table contains the procedure for defining the custom parameter.

Step

Action

1.

In the Noetix Data Source Administrator window, select a data source, and select Edit. The Data Source window appears.

2.

Select the Data Query Source tab, and select Show Advanced. The Parameters area is displayed.

3.

In the Parameters area, select New. The Security Query window appears.

4.

In the Name box, type a name for the parameter. You will use this name to refer to the parameter in the event-driven SQL statements.

5.

In the Script box, type the SQL statement that will retrieve the value for the parameter.

You can also specify the out-of-the-box macros that are available in the Parameters list.

6.

In the Parameters list, select the default or custom parameter that need to be used in the SQL statement. You can use the Up and Down to arrange the parameters in the order in which they will be referenced by the SQL statement where the question marks have been provided. In the following screenshot, the value for the first question mark will come from the first parameter, which is {WINDOWS_IDENTITY}, and the value for the second question mark will come from the second parameter, which is {METADATA_SCHEMA_NAME}.

7.

Select the Enabled check box to make sure that the custom parameter is available for use in the event-driven SQL statements.

8.

Select OK. The parameter is added in the list in the Parameters area.

Defining Event-Driven SQL Statements

What Is an Event-Driven SQL Statement?

An event-driven SQL statement is one that is run when any event occurs.

Types of Events

In the (Undefined variable: General.ProductShortName), the SQL statement can be run for the following events:

  • Execute on connect: This event occurs when a user makes a connection to a data source through the Noetix ODBC for Oracle driver. The event may also occur only when the first query is sent to the BI tool, and then the open connection is cached for later user. This behavior is dependent on the BI client. Some BI tools may close and reopen connections for each request, and some may reuse connections.
  • Execute before query: This event occurs when the user issues a query against a previously opened connection. The statement blocks will be run before the main query is run for data retrieval.
  • Execute after query: This event occurs after the main query is completed.
  • Execute on disconnect: This event occurs when the connection to the data source from the BI tool is closed, and before the actual connection is closed.

How Event-Driven SQL Statements Are Processed

Multiple SQL statements can be grouped into logical units. The Noetix ODBC for Oracle driver will run the grouped statements as follows:

  • If multiple SQL statements are defined for a single event, the statements are run in the order in which they appear in the area for the event in the Data Query Source window.
  • If the statement is not a part of a group and fails to run, the Noetix ODBC for Oracle driver will return an error message.
  • If the statement is a part of a group, it will be run as follows:
    • The Noetix ODBC for Oracle driver will validate that all the parameters referenced in the statement have values specified and will run the statement if the validation is successful. If the statement cannot be run, an error will be returned.
    • If any parameter does not contain a value, the Noetix ODBC for Oracle driver will skip the statement and process the next statement in the group.
    • When a statement has been run successfully, the Noetix ODBC for Oracle driver will skip the remaining statements in the group and move to the next statement that is not a part of the group.
    • If no statement in the group can be run, the driver will process the statements in the group that were missing parameter values. For each statement, the SQL code will be run for each custom parameter, and the values will be returned in a list to the user for selection. After the values for all the custom parameters have been selected, the statement will be run. If the statement fails, the next statement will be processed. If all the statements fail, an error will be returned.

Define an Event-Driven SQL Statement

The following step-action table contains the procedure to define an event-driven SQL statement.

Step

Action

1.

In the Noetix Data Source Administrator window, select a data source, and select Edit. The Data Source window appears.

2.

Select the Data QuerySource tab, and select Show Advanced. The events for which SQL statements can be specified are listed.

3.

Select New for the event for which you want to define the SQL statement. The Security Query window appears.

4.

If the SQL statement should be a part of a group, type a name in the Group box.

5.

In the Script box, type the SQL statement. Insert a question mark wherever the SQL statement will reference a default or custom parameter. If you want to provide procedures, use the CALL statement and use braces.

6.

In the Parameters list, select the default or custom parameter that needs to be used in the SQL statement. You can use the Up and Down to arrange the parameters in the order in which they will be referenced in the SQL statement where the question marks have been provided.

If you type the name of the macro or custom parameter, make sure that it is enclosed within braces.

7.

Select the Enabled check box to make sure that the SQL statement is run for the event.

8.

Select OK. The SQL statement is added for the event.

Customizing Metadata Query Files

What Is a Metadata Query File?

The metadata query file is an XML file that contains SQL statements used by the Angles for Oracle Generator. for extracting the metadata objects from the NoetixViews, Noetix Analytics, or RapidDecision source.

insigihtsoftwarerecommends that you do not modify the default NoetixViewsLoader.xml and NoetixAnalyticsLoader.xml file. Instead, you can copy the files and customize their contents.

When to Specify a Custom Metadata Query File

If you need to customize the metadata information, such as the names of NoetixViews, Noetix Analytics, or RapidDecision relationship sets that will be loaded, insightsoftware recommends that you specify a custom metadata query file. Make sure that the custom metadata query file is selected in the Metadata Query File box on the Application Arguments tab of the Data Source dialog box. For assistance with customizing the metadata and load process, contact insightsoftwareSupport.

Modifying Noetix ODBC for Oracle Driver Settings

When to Modify Noetix ODBC for Oracle Driver Settings

By default, the Noetix ODBC for Oracle driver provides for certain SQL functions and system tables (apart from the usual data tables) to be accessible to the user in Microsoft Power BI. You can modify these default settings when they are not sufficient for the user's needs. For example, you can make new SQL functions available to the user in Microsoft Power BI and provide access to non-Noetix data sources.

Modify Noetix ODBC for Oracle Driver Settings

The following step-action table contains the procedure for modifying the Noetix ODBC for Oracle driver settings.

Step

Action

1.

In the Noetix Data Source Administrator window, select a data source, and select Edit. The Data Source window appears.

2.

Select the ODBC tab, and do one of the following:

  • To add an entry to a list:
    1. Type the name of the SQL function or system table in the box for the list.
    2. Select Add for the list.
  • To delete an entry from a list:
    1. Select the name of the SQL function or system table in the list.
    2. Select Delete for the list.

3.

Select OK. The Data Source window closes.

Published:

Advanced Use of Angles for Oracle Generator Service

Overview

This chapter provides information about how to use the advanced settings of the Angles for Oracle Generator Service.

Editing Metadata Refresh Query

What Is Metadata Refresh Query?

The metadata refresh query is available in the Metadata Refresh Query box that can be accessed by selecting the Show Advanced link on the Metadata Source tab of the Data Source window. The query is used by the Angles for Oracle Generator Service to determine if any changes have been made to the source metadata for the selected data source since the last load. Before the load process, the SQL statement is run and returns the date when the NoetixViews, Noetix Analytics, or RapidDecision metadata was last modified. The load process is run only if the date of the metadata cache refresh is older than the date returned by the metadata refresh query. However, the load process will not be skipped if the Force Metadata Reload option is selected in the Loader area of the Noetix Data Source Administrator.

When to Edit Metadata Refresh Query

By default, the metadata refresh queries are included in the definitions of the NoetixViews and, Noetix Analytics and RapidDecision data sources, and you do not need to modify the queries in most cases. However, the queries need to be modified for the data sources of custom metadata. For assistance with editing the metadata refresh queries, contact insightsoftwareSupport.

Editing BI Application Data Filter Template

What Is BI Application Data Filter Template?

The BI Application Data Filter Template allows you to define an SQL statement that will be used by the (Undefined variable: General.ProductShortName) to provide data authentication (row-level security) within a Microsoft Power BI interactive report template. In the Data Source window, the BI Application Data Filter Template box is available on the Metadata Source tab when the Show Advanced link is selected.

The template is a SELECT statement that includes out-of-the-box macro variables.

Macros for BI Application Data Filter Template

The out-of-the-box macros are as follows:

  • BEGIN_LOOP: Used to define the start of a loop that runs for each column selected in the (Undefined variable: General.ProductShortName). A loop opened with BEGIN_LOOP is closed by END_LOOP. Any text within BEGIN_LOOP and END_LOOP will be replicated for each selected column. Macros within the loop text will be processed before writing out the text.
  • BEGIN_COMMA_LOOP: Similar to BEGIN_LOOP, with the exception that BEGIN_COMMA_LOOP intelligently creates a comma delimited list such that a final trailing comma is not placed, which is useful when writing out columns within the SELECT clause of an SQL statement.
  • CATALOG_NAME: Replaces instances of the macro string with the name of the database catalog defined on the Data Query Source tab of the data source. This value is blank for Oracle data sources.
  • COLUMN_ALIAS: Is replaced by the formatted name of the column selected in the (Undefined variable: General.ProductShortName).
  • COLUMN_NAME: Is replaced by the name of the column in the physical database table.
  • DATABASE_NAME: Is replaced by the database name or TNS value defined on the Data Query Source tab of the Data Query window of the Noetix Data Source Administrator.
  • END_LOOP: Used to close a loop that was opened with BEGIN_LOOP.
  • END_COMMA_LOOP: Used to close a loop that was opened with BEGIN_COMMA_LOOP.
  • METADATA_SCHEMA_NAME: Is replaced with the name of the metadata source schema defined on the Metadata Source tab of the Data Query window of the Noetix Data Source Administrator.
  • REGISTERED_BI_TOOL_NAME: Is replaced with the name of the registered business intelligence (BI) tool on the Metadata Source tab of the Data Query window of the Noetix Data Source Administrator.
  • SCHEMA_NAME: Is replaced with the name of the schema that owns the reporting object. For NoetixViews, it is the NoetixViews schema that is specified on the Metadata Source tab. For Noetix Analytics, depending on the selected reporting object, it is either NOETIXODS or NOETIXDM. For RapidDecision, it is the RapidDecision data mart.
  • SECURITY_ALIAS: Currently not supported.
  • TABLE_NAME: Is replaced with the name of the table or view that a column is associated with.

Edit BI Application Data Filter Template

You will typically edit the BI application data filter template for non-Noetix data sources or if the default template does not meet your business requirements.

The following step-action table contains the procedure to edit the BI application data filter template.

Step

Action

1.

On the Data Source Administrator window, select a data source, and select Edit. The Data Source window appears.

2.

Select the Metadata Source tab, and select Show Advanced. The BI Application Data Filter Template box is displayed.

3.

In the box, type the required SQL statement.

4.

To use an out-of-the-box macro, in the SQL statement, place the cursor where the macro needs to be added. In the macro list, select the macro, and select Insert. Alternatively, select and double-click the macro. The macro is added to the SQL statement.

The macro must be enclosed within braces.

5.

Select OK.

Defining Custom Parameters

What Is a Custom Parameter?

A custom parameter is one that is referenced by the event-driven SQL statements during run time. The custom parameter contains an SQL statement that references default and other custom parameters. The SQL statement can be defined to return one value, which the Noetix ODBC for Oracle driver uses to run the event-driven SQL statement. The SQL statement can also be defined to return a list of values (LoV) and the Noetix ODBC for Oracle driver will prompt the user to select a value during run time. The LoV is populated with data fetched with the use of the default parameter RESPONSIBILITY that is defined out of the box and is listed under Parameters in the Data Query Source tab.

Currently, only one SQL statement is supported in the custom parameter definition.

Define a Custom Parameter

The following step-action table contains the procedure for defining the custom parameter.

Step

Action

1.

In the Noetix Data Source Administrator window, select a data source, and select Edit. The Data Source window appears.

2.

Select the Data Query Source tab, and select Show Advanced. The Parameters area is displayed.

3.

In the Parameters area, select New. The Security Query window appears.

4.

In the Name box, type a name for the parameter. You will use this name to refer to the parameter in the event-driven SQL statements.

5.

In the Script box, type the SQL statement that will retrieve the value for the parameter.

You can also specify the out-of-the-box macros that are available in the Parameters list.

6.

In the Parameters list, select the default or custom parameter that need to be used in the SQL statement. You can use the Up and Down to arrange the parameters in the order in which they will be referenced by the SQL statement where the question marks have been provided. In the following screenshot, the value for the first question mark will come from the first parameter, which is {WINDOWS_IDENTITY}, and the value for the second question mark will come from the second parameter, which is {METADATA_SCHEMA_NAME}.

7.

Select the Enabled check box to make sure that the custom parameter is available for use in the event-driven SQL statements.

8.

Select OK. The parameter is added in the list in the Parameters area.

Defining Event-Driven SQL Statements

What Is an Event-Driven SQL Statement?

An event-driven SQL statement is one that is run when any event occurs.

Types of Events

In the (Undefined variable: General.ProductShortName), the SQL statement can be run for the following events:

  • Execute on connect: This event occurs when a user makes a connection to a data source through the Noetix ODBC for Oracle driver. The event may also occur only when the first query is sent to the BI tool, and then the open connection is cached for later user. This behavior is dependent on the BI client. Some BI tools may close and reopen connections for each request, and some may reuse connections.
  • Execute before query: This event occurs when the user issues a query against a previously opened connection. The statement blocks will be run before the main query is run for data retrieval.
  • Execute after query: This event occurs after the main query is completed.
  • Execute on disconnect: This event occurs when the connection to the data source from the BI tool is closed, and before the actual connection is closed.

How Event-Driven SQL Statements Are Processed

Multiple SQL statements can be grouped into logical units. The Noetix ODBC for Oracle driver will run the grouped statements as follows:

  • If multiple SQL statements are defined for a single event, the statements are run in the order in which they appear in the area for the event in the Data Query Source window.
  • If the statement is not a part of a group and fails to run, the Noetix ODBC for Oracle driver will return an error message.
  • If the statement is a part of a group, it will be run as follows:
    • The Noetix ODBC for Oracle driver will validate that all the parameters referenced in the statement have values specified and will run the statement if the validation is successful. If the statement cannot be run, an error will be returned.
    • If any parameter does not contain a value, the Noetix ODBC for Oracle driver will skip the statement and process the next statement in the group.
    • When a statement has been run successfully, the Noetix ODBC for Oracle driver will skip the remaining statements in the group and move to the next statement that is not a part of the group.
    • If no statement in the group can be run, the driver will process the statements in the group that were missing parameter values. For each statement, the SQL code will be run for each custom parameter, and the values will be returned in a list to the user for selection. After the values for all the custom parameters have been selected, the statement will be run. If the statement fails, the next statement will be processed. If all the statements fail, an error will be returned.

Define an Event-Driven SQL Statement

The following step-action table contains the procedure to define an event-driven SQL statement.

Step

Action

1.

In the Noetix Data Source Administrator window, select a data source, and select Edit. The Data Source window appears.

2.

Select the Data QuerySource tab, and select Show Advanced. The events for which SQL statements can be specified are listed.

3.

Select New for the event for which you want to define the SQL statement. The Security Query window appears.

4.

If the SQL statement should be a part of a group, type a name in the Group box.

5.

In the Script box, type the SQL statement. Insert a question mark wherever the SQL statement will reference a default or custom parameter. If you want to provide procedures, use the CALL statement and use braces.

6.

In the Parameters list, select the default or custom parameter that needs to be used in the SQL statement. You can use the Up and Down to arrange the parameters in the order in which they will be referenced in the SQL statement where the question marks have been provided.

If you type the name of the macro or custom parameter, make sure that it is enclosed within braces.

7.

Select the Enabled check box to make sure that the SQL statement is run for the event.

8.

Select OK. The SQL statement is added for the event.

Customizing Metadata Query Files

What Is a Metadata Query File?

The metadata query file is an XML file that contains SQL statements used by the Angles for Oracle Generator. for extracting the metadata objects from the NoetixViews, Noetix Analytics, or RapidDecision source.

insigihtsoftwarerecommends that you do not modify the default NoetixViewsLoader.xml and NoetixAnalyticsLoader.xml file. Instead, you can copy the files and customize their contents.

When to Specify a Custom Metadata Query File

If you need to customize the metadata information, such as the names of NoetixViews, Noetix Analytics, or RapidDecision relationship sets that will be loaded, insightsoftware recommends that you specify a custom metadata query file. Make sure that the custom metadata query file is selected in the Metadata Query File box on the Application Arguments tab of the Data Source dialog box. For assistance with customizing the metadata and load process, contact insightsoftwareSupport.

Modifying Noetix ODBC for Oracle Driver Settings

When to Modify Noetix ODBC for Oracle Driver Settings

By default, the Noetix ODBC for Oracle driver provides for certain SQL functions and system tables (apart from the usual data tables) to be accessible to the user in Microsoft Power BI. You can modify these default settings when they are not sufficient for the user's needs. For example, you can make new SQL functions available to the user in Microsoft Power BI and provide access to non-Noetix data sources.

Modify Noetix ODBC for Oracle Driver Settings

The following step-action table contains the procedure for modifying the Noetix ODBC for Oracle driver settings.

Step

Action

1.

In the Noetix Data Source Administrator window, select a data source, and select Edit. The Data Source window appears.

2.

Select the ODBC tab, and do one of the following:

  • To add an entry to a list:
    1. Type the name of the SQL function or system table in the box for the list.
    2. Select Add for the list.
  • To delete an entry from a list:
    1. Select the name of the SQL function or system table in the list.
    2. Select Delete for the list.

3.

Select OK. The Data Source window closes.

For an optimal Community experience, Please view on Desktop