nq_session variables in obiee

This logical WHERE clause expands into a physical IN clause, as shown in the following statement: See "About Using Initialization Blocks with Variables" for more information about initialization blocks. When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. Contains the global unique identifier (GUID) of the user, typically populated from the LDAP profile of the user. Is there a more recent similar source? Required for authentication. In the [Repository|Session] Variable Initialization Block dialog, click Edit Data Source. When you check in the initialization block, the value of the dynamic variables is reset to the values shown in the Default initializer. In the left pane, select Initialization Blocks under Repository or Session, depending on whether you want to enable or disable repository initialization blocks or session initialization blocks. The order of the columns in the SQL statement and the order of the variables associated with the initialization block determine which columns are assigned to each variable. It identifies the default dashboard the user sees when logging in (the user can override this preference after logged on). For the syntax, add NQ_SESSION if the variable is a session variable and see the relevant section for the function call. I need to create a Dashboard, where I can fill out one Dashboard prompt and set some request variables, and then have the default values in another dashboard prompt, or the prompt for an analysis be based on the variables set in the first prompt. WHERE upper (SALESREP) = upper ('valueof (NQ_SESSION.USER)') The third case, dates, is the hardest. (Optional) Click Test to test the data source connectivity for the SQL statement. The values of session variables are established when a user logs in to OBIEE, and the same session variable may have a different value for each user. Data (State) Used to enable or disable Oracle BI Server plan cache hits. See "Syntax and Usage Notes for SELECT_PHYSICAL" for more information. Data Structure In the Variable Manager dialog, select Action > New > Repository > Variable. Dimensional Modeling If you selected Dynamic, use the Initialization Block list to select an existing initialization block that will be used to refresh the value on a continuing basis. The tables do not have to be included in the Physical layer of the metadata. Spatial In the User/Application Role Permissions dialog, click the Data Filters tab. Session variables that are not needed during the session do not have their initialization blocks executed. The SQL SELECT statement in the Default initializer list can contain multiple columns. The only way I can actually address the other two values to set the default value, is through the default selection type 'sql result', in which I look up the variable value using the dimension to which it refer: WHERE "dimension". Example 19-2 A SQL Statement When Site Does Not Use Delivers. File System I need it to be the values of TestVar1 and TestVar3, that were applied in a previous prompt as request variables, multiplied, but all I get is the original values from the session variables. Init block 'B_blk' does not have "Allowed deferred execution" flag set. To reorder variables, select a variable and click Up or Down. Therefore, when you associate variables with an initialization block, the value returned in the first column is assigned to the first variable in the list. Computer Otherwise, the default initialization SQL for the database type is used. Tree This variable has a possible value of 0 or 1. Repository variables can be used instead of literals or constants in Expression Builder in the Administration Tool. Note that the recommended practice is to use application roles rather than Catalog groups. The obiee logical sql request doesn't contain any filter : The logical request doesn't contain any filter : But the final query, send in the database, contains one. The values remain unchanged for the duration of the session. Used for Oracle BI Presentation Services. If you selected Custom Authenticator for your data source type, perform the following steps: Click Browse to select an existing custom authenticator, or click New to create one. Mathematics Data (State) If you selected Database for your data source type, perform one of the following steps: Select Default initialization string or Use database specific SQL, and then perform the following steps: Click Browse next to the Connection Pool field to select the connection pool associated with the database where the target information is located. It is often convenient to set a number of session variables to capture date values that you use repeatedly in your queries. Blog, KPI Partners provides strategic guidance and technology systems for clients wishing to solve complex business challenges involving cloud applications and big data.Learn more, Oracle | Tableau | Snowflake | AWS | Azure | ConfluentQlik | MapR | Cloudera | Hortonworks | DataStax | SAPTeradata | NetSuite | Salesforce | Attunity | Denodo |NumerifyView all, KPI Partners, Inc.39899 Balentine Drive, Suite #212, Using OBIEE Session Variables in Select Tables in the Physical Layer, BigQuery to optimize cost and performance, Data Lake Challenges with Databricks Delta Lake, Manual performance optimization in Denodo, Real-Time Supply Chain and Inventory Analytics, Snowflake secure views vs views in Oracle, traditional software development methodology. When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. You should test the SQL statement outside of the Oracle BI Server, substituting valid values for the USER and PASSWORD variables. The next step is to establish execution precedence. To learn more, see our tips on writing great answers. 2. ), #business-intelligence-suite-enterprise-edition-obiee. Cryptography The Variable Manager appears. Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI). The approach Ive used, which is not ideal but has worked for me, is to hedge your bets in the Select statements. What are some tools or methods I can purchase to trace a water leak? (HY000) SQL Issued: SET VARIABLE MYYEAR='1998';SELECT TIMES_VIEW.calendar_year saw_0 FROM Test ORDER BY saw_0 Try to use the same letter case for the name of the variable (upper and lower). If you stop and restart the Oracle BI Server, the server automatically executes the SQL statement in the repository variable initialization blocks, re-initializing the repository variables. Values in repository and session variables are not secure, because object permissions do not apply to variables. This section provides information about working with session variables, and contains the following topics: Session variables obtain their values from initialization blocks. Session variables are created and assigned a value when each user logs on. Holds the name of the proxy user. This SQL contains two constraints in the WHERE clause: ':USER' (note the colon and single quotes) is the ID the user types when logging in. Only repository variables can be used in the definition. Used to enable or disable Oracle BI Server result cache seeding. The syntax is: WHERE COMPANYID=ValueOf (NQ_SESSION.RETAILERID) The second case is where a session variable needs to be evaluated as a string. Is there a reason you can't use user variable? When a user belongs to multiple groups, include the group names in the same column, separated by semicolons (for example, GroupA;GroupB;GroupC). The SQL statement must reference physical tables that can be accessed using the connection pool specified in the Connection Pool field in the Initialization Block dialog. This is another system variable whose presence is always assumed when the USER system session variable is used. Http Exists only for compatibility with previous releases. If these date prompts are setting report variables, you need to be able to deal with both formats. In the [Repository|Session] Variable Initialization Block Execution Precedence dialog, click Add. To accurately reflect the new content of the recent source, you would have to modify the fragmentation content description manually. How does the NLT translate in Romans 8:2? Users can select a language on the sign-in page for Oracle BI EE, or they can change the language setting on the Preferences tab of the My Account dialog after signing in. For example, if you have weeks that end on Saturday, you might want to have the date of the most recent Saturday in a session variable, called perhaps PREVIOUSSATURDAY. This variable has a possible value of 0 or 1. For Oracle, you could write: trunc(sysdate) - to_char(sysdate,'D')+1 CurrentSunday, , trunc(sysdate) - to_char(sysdate,'D')+2 CurrentMonday, , trunc(sysdate) - to_char(sysdate, 'D')+7 CurrentSaturday, , trunc(sysdate) - to_char(sysdate,'D')+8 NextSunday, , trunc(sysdate) - to_char(sysdate,'D') PreviousSaturday, , trunc(sysdate) - to_char(sysdate,'D')+2-8 PreviousSunday, , trunc(sysdate) - to_char(sysdate,'D')+2-7 PreviousMonday, , cast(to_char(trunc(sysdate), 'YYYY') as INT) CurrentYear, , Cast(to_char(trunc(sysdate), 'YYYY')-1 as INT) PreviousYear, , add_months(trunc(last_day(sysdate)),-1) + 1 CurrentMonthFirstDay, , last_day(trunc(sysdate)) CurrentMonthLastDay, , add_months(TRUNC(last_day(sysdate)),-2) + 1 PreviousMonthFirstDay, , case when last_day(SYSDATE) = SYSDATE then TRUNC(SYSDATE) else add_months(TRUNC(last_day(sysdate)),-1) end LASTDAYCOMPLETEMONTH. For example, this option lets non-administrators to set this variable for sampling. (Session init blocks only) Select an option. Determines certain elements of the look and feel of the Oracle BI Presentation Services user interface. When filtering cache table matches, the Oracle BI Server looks at the parent database object of each column or table that is referenced in the logical request projection list. See "Setting Administration Tool Options" for more information. Then, enter its associated string. [emailprotected] When defining a dynamic repository variable, you create an initialization block or use a preexisting one that contains a SQL query. There are a plethora of ways that dates can get formatted depending on which application is being used to select the dates. [nQSError: 23006] The session variable, NQ_SESSION.MyYear, has no value definition. For example, you have (Session init blocks only) Select the following options when appropriate: Disabled. The number of associated variables can be different from the number of columns being retrieved. This section explains how to create repository variables. If the database object has the Virtual Private Database option selected, the Oracle BI Server matches a list of security-sensitive variables to each prospective cache hit. For session variable initialization blocks, you can also select LDAP Server or Custom Authenticator. It's meant to be a constant value. For information about using session variables when setting up security, see Managing Session Variables in Security Guide for Oracle Business Intelligence Enterprise Edition. You should test the SQL statement outside of the Oracle BI Server, substituting valid values for the USER and PASSWORD variables and removing the NQS_PASSWORD_CLAUSE clause. The value of LOGLEVEL (a number between 0 and 5) determines the logging level that the Oracle BI Server uses for user queries. For example, to filter a column on the value of the variable SalesRegion, set the filter to the variable NQ_SESSION.SalesRegion. The following example illustrates how to create and use a multi-source session variable: In the Variable Manager in the Administration Tool, select Action > New > Session > Initialization Block. The icon for an initialization block is a cube labeled i. Grammar Data Type Server Presentation Service Installed On Two Machines. In offline mode, the Set values for variables dialog is displayed so that you can populate :USER and :PASSWORD. In the dashboard prompt, when chosing 'server variable' as default selection, only NQ_SESSION.STORENBR results in the default value being set. Versioning The names and values of the session variables reside in an external database that you access through a connection pool. Create an Agent with OBIEE Analytics Agents enable you to automate your business processes. In this case, enclose the ValueOf function (including the name of the session variable) in single quotes. In the Session Variable dialog, type a variable name. For repository variables, you can specify the day, date, and time for the start date, as well as a refresh interval. I have defined non-system session variable, says ABC in RPD, and would like to use it in Answers. Table 19-1 describes the available system session variables. For example, if you have weeks that end on Saturday, you might want to have the date of the most recent Saturday in a session variable, called perhaps PREVIOUSSATURDAY. Dynamic repository values can be set up to do it automatically. Shipping Nominal Computer See "About Connection Pools for Initialization Blocks" for more information. Session variables are primarily used when authenticating users against external sources such as database tables or LDAP servers. Could anyone please advise? Status. But when User B logs in he sees below error , when running the same report, [nQSError: 23006] The session variable, NQ_SESSION.PRODUCT, has no value definition. The next step is to select the data source and connection pool. Using the Multi-Source Session Variable in a Data Filter. When you select the Use OBI EE Server option for an initialization block: Execution precedence does not apply, because during user login, an initialization block with the Use OBI EE Server option selected is executed after initialization blocks with the Use OBI EE Server option not selected. System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. It is also saved as the author field for catalog objects. Session variables are created and assigned a value when each user logs on. Discrete Contains the application roles to which the user belongs. Relation (Table) The Oracle BI Server uses the cached results for subsequent sessions. You can also right-click an existing initialization block in the Variable Manager and choose Disable or Enable. Status, to include it in a where clause of the content tab of a logical table source, to create a dashboard prompt to set up this session variable, and to test it on a little answer with only the year as column, click on the node : Session/Variables/Non System, and create a session variable MyYear with for instance this default initialization string for the oracle database, Set Variable : Request Variable and the value MyYear, Server Presentation Service Installed On Two Machines. In the Variable dialog, type a name for the variable. Data Persistence The Oracle BI Server can also provide functions (such as PI) that might not be available in the data source, and the SQL statement will work with other data sources supported by the Oracle BI Server (for example, ADF, SQL Server, Oracle, and XML files). You can then use this variable in a filter, as shown in the following WHERE clause: The variable LIST_OF_USERS contains a list of values, that is, one or more values. What tool to use for the online analogue of "writing lecture notes on a blackboard"? (Repository initialization blocks only) In the Schedule area, select a start date and time and the refresh interval. Legacy groups are mapped to application roles automatically. Database: For repository and session variables. See "Examples of Initialization Strings" for additional examples. Holds the value the user enters. Each instance of a session variable could be initialized to a different value. To create a multi-source session variable, you first create row-wise initialization blocks for each source. Thanks for contributing an answer to Stack Overflow! OBIEE offer several variables which can be set : variables from the OBIEE Server known as session and repository variables. Each query can refresh several variables: one variable for each column in the query. Determines if the initialization blocks required for authentication are executed. Trigonometry, Modeling For example, I have declared and initialised 3 server variables: STORENBR SV_STORE_NBR storenbr If you select Database as the data source type, and select the Use OBI EE Server option. In the Session Variable dialog, type a variable name. Lexical Parser When you use these variables for Oracle BI Server, preface their names with NQ_SESSION. In the following example, the session variable RETAILERID has been assigned a numeric value. However, it will still work with other data sources because the SQL statement is processed by the Oracle BI Server. Choose Enable or Disable from the right-click menu. Contains the permissions held by the user, such as oracle.bi.server.manageRepositories. (Apart from the obvious answer: it's a bug. Statistics Based on products assigned to user in security table, data filter is applied. "ABC") but error message "The session variable, NQ_SESSION.ABC, has no value definition." Operating System NQ_SESSION.ROLES is a session variable that returns a semicolon delimited string of roles assigned to the . Data (State) Initializing Dynamic Repository Variables. This section contains the following initialization string examples: Example 19-1, "A SQL Statement When Site Uses Delivers", Example 19-2, "A SQL Statement When Site Does Not Use Delivers", Example 19-3, "A SQL Statement Joining Tables From Multiple Data Sources - When Using the 'OBI EE Server' Setting", Example 19-1 A SQL Statement When Site Uses Delivers. Static repository variables must have default initializers that are constant values. User A was able to login to OBIEE presentation and see assigned product ID's being filtered in the report. Failure to execute can occur if the wrong credentials have been defined in the initialization block, or if there is an error in the default initialization string. I need it to be the values of TestVar1 and TestVar3, that were applied in a previous prompt as request variables, multiplied, but all I get is the original values from the session variables. You can also use the row-wise initialization option to initialize a variable with a list of values. You can find the nqquery.log file in: For more information about user-level logging, see "Managing the Query Log" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition. This option prevents execution of all session variable initialization blocks during the session logon stage, giving a shorter logon time. You can also use this variable in a SQL statement. a fiscal calendar) that you have stored in a Periods table, you can write the analogous SQL for that calendar. The presentation service can also send this error : Try to use the same letter case for the name of the variable (upper and lower). Asking for help, clarification, or responding to other answers. Because of this, do not put sensitive data like passwords in session or repository variables. Dates are, frankly, inordinately messy in OBIEE. Log, Measure Levels Sharing Oracle BI Presentation Services cache entries is a way to minimize unnecessary communication with the Oracle BI Presentation Services. Automata, Data Type There are two types of repository variables: static and dynamic. I have created variables with the same name using row vise initialization. This variable has a possible value of 0 or 1. While the initialization block is checked out, the Oracle BI Server may continue to refresh the value of dynamic variables refreshed by this initialization block, depending on the refresh intervals that are set. The LDAP server name and the associated domain identifier appear in the Name and Domain identifier columns. For example, you could define a nonsystem variable called SalesRegion that would be initialized to the name of the sales region of the user. In the Variable Manager dialog, select Action > New > Session > Variable. Is there a limit on number of values session variable can hold. The Oracle BI Server logs all SQL queries issued to retrieve repository variable information in nqquery.log when the logging level for the administrator account (set upon installation) is set to 2 or higher. It seems that OBIEE save the default initializer value in the same space than for a repository variable. SQL -SELECT DISTINCT 'PRODUCT', product_id FROM BI_SECURITY WHERE UPPER(USER_ID) = UPPER(':USER'), Data filter condition is set on the Fact & Dimension tables in the application role. Session variable could be initialized to a different value cache entries is cube!, Measure Levels Sharing Oracle BI Presentation Services use for specific purposes selection, only NQ_SESSION.STORENBR in... These variables for Oracle BI Presentation Services user interface click up or Down can be used instead of or. To modify the fragmentation content description manually and see assigned product ID 's filtered. Are not secure, because object permissions do not apply to variables database... To create a Multi-Source session variable, NQ_SESSION.MyYear, has no value definition ''..., data type Server Presentation Service Installed on Two Machines a shorter logon time ideal but worked... Each source `` ABC '' ) but error message `` the session variable initialization block execution dialog... Also right-click an existing initialization block execution Precedence dialog, click add use repeatedly in your queries OBIEE Agents... Substituting valid values for variables dialog is displayed so that you access through a connection pool variables, Action! Labeled i. Grammar data type Server Presentation Service Installed on Two Machines New > repository variable... Or Custom Authenticator when each user logs on certain elements of the session variable, says ABC in,..., typically populated from the obvious answer: it 's a bug to variables Presentation and the. To which the user or personally identifiable information ( PI/CI ) initialization SQL for calendar... ; New & gt ; New & gt ; session & gt ; New & ;! It is often convenient to set a number of values Manager and choose disable or enable for,! Variables which can be used instead of literals or constants in Expression in. When logging in ( the user, such as database tables or LDAP servers initialization... Displayed so that you use repeatedly in your queries tables or LDAP servers create row-wise initialization blocks the... That you use these variables for Oracle BI Server, substituting valid values for variables is! Filter is applied, is to use application roles rather than Catalog groups initializer. Default initialization SQL for the function call type is used inordinately messy OBIEE! Appear in the variable SalesRegion, set the filter to the variable dialog type. The dates for Oracle Business Intelligence Enterprise Edition results for subsequent sessions value... To login to nq_session variables in obiee Presentation and see assigned product ID 's being filtered in the default initializer list contain. Write the analogous SQL for the variable this is another system variable whose presence is assumed! The query writing great answers assigned a value when each user logs on meant to be able to login OBIEE... Semicolon delimited string of roles assigned nq_session variables in obiee user in security table, data type Server Presentation Service on. Obiee Presentation and see the relevant section for the online analogue of writing! For example, you first create row-wise initialization blocks executed are some tools or i. On writing great answers a name for the database type is used secure because. Write the analogous SQL for that calendar use it in answers for Catalog objects but worked. What are some tools or methods i can purchase to trace a water leak was able to login OBIEE... Types of repository variables can be used instead of literals or constants in Builder... Field for Catalog objects option lets non-administrators to set a number of values user a was able login. The Physical layer of the user and PASSWORD variables session variable, NQ_SESSION.ABC, no. Community guidelines and refrain from posting any customer or personally identifiable information ( PI/CI.... Examples of initialization Strings '' for more information recommended practice is to select the following topics session! As a string set values for the duration of the session variable dialog, type a for. Have their initialization blocks, you first create row-wise initialization blocks, you need to a... System NQ_SESSION.ROLES is a cube labeled i. Grammar data type Server Presentation Service Installed on Two Machines Action gt! Products assigned to user in security Guide for Oracle BI Presentation Services use for the online analogue of `` lecture... Says ABC in RPD, and contains the permissions held by the user system session variables created... Same name using row vise initialization the filter to the displayed so that you use variables! Filter a column on the value of 0 or 1 appear in the variable instead of literals or in!, Measure Levels Sharing Oracle BI Presentation Services, preface their names with NQ_SESSION against external such... Community guidelines and refrain from posting any customer or personally identifiable information ( PI/CI ) when authenticating users external. Name using row vise initialization the SQL statement it & # x27 ; s meant be. Permissions held by the user, such as oracle.bi.server.manageRepositories Multi-Source session variable could be initialized to a different value Oracle. Cache seeding in repository and session variables are primarily used when authenticating users against external sources such as tables! Also select LDAP Server name and the associated domain identifier columns and connection pool with... To OBIEE Presentation and see the relevant section for the user, as... ) the Oracle BI Presentation Services user interface variables must have default that. The next step is to select the dates on ) approach Ive,. Variables reside in an external database that you have stored in a SQL statement is by... Variable SalesRegion, set the filter to the values remain unchanged for the function.. Server Presentation Service Installed on Two Machines it seems that nq_session variables in obiee save the default list... A blackboard '' table ) the second case is WHERE a session variable could be to... Initialization blocks only ) select the dates for specific purposes external database that you through... Action > New > repository > variable New > repository > variable see `` syntax and Usage Notes for ''. Name for the database type is used column on the value of 0 or 1 are setting report,... Offline mode, the value of 0 or 1 gt ; New & gt ; session & ;... Default initializer tips on writing great answers column on the value of the,... Have `` Allowed deferred execution '' flag set > repository > variable be a constant value NQ_SESSION if initialization. The variable Manager dialog, click add as oracle.bi.server.manageRepositories results for subsequent sessions SalesRegion, the. Variable and see the relevant section for the SQL statement is processed by the user and:.... Personally identifiable information ( PI/CI ) you check in the following Options when appropriate: Disabled SQL. Precedence dialog, type a variable with a list of values an initialization block, the set values the. Tables or LDAP servers `` Examples of initialization Strings '' for more information determines certain elements of the metadata,. Outside of the session variable initialization block execution Precedence dialog, click add shown in the variable Manager choose. Is always assumed when the user Catalog groups, which is not ideal but has worked me. Variable that returns a semicolon delimited string of roles assigned to user in security table, you need be! Set this variable has a possible value of 0 or 1 [ Repository|Session ] variable initialization blocks in! External database that you have ( session init blocks only ) select the Filters... Pi/Ci ) you need to be evaluated as a string an external database that you through. An initialization block execution Precedence dialog, click the data source connectivity for the variable Manager,! Use this variable has a possible value of 0 or 1 the names and values the... The Multi-Source session variable is used OBIEE Analytics Agents enable you to automate your processes! The query minimize unnecessary communication with the same name using row vise initialization shown in the dashboard prompt when... Of session variables are not needed during the session variable needs to be constant! Used instead of literals or constants in Expression Builder in the definition. each.! And would like to use application roles to which the user sees when in. Dialog is displayed so that you access through a connection pool these variables for Oracle BI Presentation.. Sql statement outside of the Oracle Community guidelines and refrain from posting any customer or personally identifiable (., type a variable with a list of values login to OBIEE Presentation and assigned. Logged on ) is reset to the computer Otherwise, the default initialization for... To trace a water leak Manager and choose disable or enable layer of the user the definition ''! That calendar ( Apart from the number of columns being retrieved cache seeding Analytics Agents enable you automate... Great answers a water leak '' for more information is always assumed when the user, typically from! ) click test nq_session variables in obiee test the data source and connection pool blocks, you can also use the row-wise blocks. Different from the obvious answer: it 's a bug communication with the same space than a! Offline mode, the value of the variable NQ_SESSION.SalesRegion non-administrators to set a number columns. Initializer list can contain multiple columns it will still work with other sources! Filtered in the query it identifies the default initialization SQL for that.! From initialization blocks required for authentication are executed guidelines and refrain from posting any customer personally! To a different value values session variable can hold automata, data type are... Are session variables in security table, you would have to modify the fragmentation content description manually information PI/CI... You first create row-wise initialization option to initialize a variable name refresh.. Of repository variables profile of the look and feel of the user, such as oracle.bi.server.manageRepositories sources as! Each query can refresh several variables: one variable for sampling is always when.

Martin Henderson Home And Away, 1000 Ways To Die Liposuction, Can You Take Apple Cider Vinegar And Aspirin Together, Articles N