Launching the CI/CD and R Collectives and community editing features for OBIEE 12C: dashboard prompt auto fill when selection is sql result, dashboard prompt doesn't work in obiee 11g when using javascript, Oracle gives error Message on prompt refresh, OBIEE 11 - How to export a multipage dashboard in excel without prompt, Using table function in OBIEE RPD (physical layer) and pass parameter from dashboard prompt to it, obiee12c dashboard prompts running before analysis filters. Security The SKIN variable points to an Oracle BI Presentation Services folder that contains the nonalterable elements (for example, figures such as GIF files). StartDate)', 1, 3) = '200' or substr('valueof(NQ_SESSION. variables is plural. Legacy groups are mapped to application roles automatically. Collection Exists only for compatibility with previous releases. To test initialization blocks (optional): In the Variable Manager dialog, double-click the initialization block. Don't forget the quotes which enclose the Variable Name. Click here to get started. Text The following list summarizes the scenarios in which execution of session variable initialization blocks cannot be deferred: The Row-wise initialization option is selected in the Session Variable Initialization Block Variable Target dialog and the variables have not been declared explicitly with default values. The string you enter here is processed by the Oracle BI Server, and therefore as long as it is supported by the Oracle BI Server, the string will work with different data sources. Versioning Relational Modeling If you are using a calendar thats different from the normal Gregorian calendar (i.e. Spatial If there are fewer variables than columns, extra column values are ignored. select level_nr from obiee_authenticatie where user_id = 'VALUEOF(NQ_SESSION.USER)' When testing in the variable manager it works perfect. See "About Row-Wise Initialization" for more information. Example message: "The execution of init block 'A_blk' cannot be deferred as it is used by session variable 'PROXY'.". The user can alter some elements of the user interface by picking a style when logged on to Oracle BI Presentation Services. Quick question, a colleague of mine was telling me that the session variable, NQ_SESSION.GROUP, only shows groups which are assigned at the presentation >> level (or passed in from LDAP or other data source) but it does not show any of the group assignments made within the RPD itself. In the Variable Manager dialog, select Action > New > Session > Variable. Data Persistence Operating System This happens automatically when you double-click the variables to paste them into the expression. The value of LOGLEVEL (a number between 0 and 5) determines the logging level that the Oracle BI Server uses for user queries. Used for Oracle BI Presentation Services. In the Data Filters tab, create the data filter expression: Note that the Expression Builder, as shown in the image that follows, displays only the multi-source session variable MVCOUNTRY, and not the regular session variables that were used during the creation of the multi-source session variable. Number Grammar OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ? It contains the name that is displayed to the user in the greeting in the Oracle BI Presentation Services user interface. If you use a SQL tool, be sure to use the same DSN or one set up identically to the DSN in the specified connection pool. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide, How to Use Variable Expression for Default Value of Dashboard Prompt in OBIEE 11g, The open-source game engine youve been waiting for: Godot (Ep. There are many times when it is very beneficial to pass the value of session variables (or report variables) into the SQL used to define a Select table in the physical layer. 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. Distance In other words, if you change the SQL Server back-end database to Oracle, the query will still work. Dimensional Modeling You can change this behavior so that the first connection pool is available for selection by selecting Allow first Connection Pool for Init Blocks in the Options dialog, although this is not recommended. For example, to filter a column on the value of the variable LOGLEVEL, set the filter to the variable NQ_SESSION.LOGLEVEL. When you have create a server variable, you have to reference it. New to My Oracle Support Community? Why is there a memory leak in this C++ program and how to solve it, given the constraints (using malloc and free for objects containing std::string)? Create a second session variable called MVCOUNTRY____ORCL, making sure to include four underscores between the variable name and the source name. Logical Data Modeling Contains the global unique identifiers (GUIDs) for the application roles to which the user belongs. In the [Repository|Session] Variable Initialization Block Execution Precedence dialog, click Add. User A was able to login to OBIEE presentation and see assigned product ID's being filtered in the report. To use the multi-source session variable MVCOUNTRY in a data filter, perform the following steps: In the Administration Tool, select Manage, then select Identity. It identifies the default dashboard the user sees when logging in (the user can override this preference after logged on). In the [Repository|Session] Variable Initialization Block dialog, click Edit Data Source. 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. If you select Database as the data source type: If you select Database as the data source type, and do not select the Use OBI EE Server option. For example, you could define a nonsystem variable called SalesRegion that would be initialized to the name of the sales region of the user. There seems to be a glitch in the way obiee (11.1.1.6.0) interprets server variables when using them as default values for a dashboard prompt: only when the variable name is in uppercase and contains no underscores will it be recognised. Dynamic repository variables are useful for defining the content of logical table sources. Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition, Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition, Allow first Connection Pool for Init Blocks, Action > New > Session > Initialization Block, "Syntax and Usage Notes for SELECT_PHYSICAL", "About Using Initialization Blocks with Variables", "When Execution of Session Variable Initialization Blocks Cannot Be Deferred", "About Connection Pools for Initialization Blocks", "Assigning a Name and Schedule to Initialization Blocks". Data Persistence Data Analysis The initialization block is a predecessor to another initialization block which does not have the Allow deferred execution option selected. In the View Data from Table dialog, type the number of rows and the starting row for your query, and then click Query. Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI). I have created variables with the same name using row vise initialization. Not correct. The value of LOGLEVEL, a number between 0 and 5, determines the logging level that the Oracle BI Server uses for user queries. It is also saved as the author field for catalog objects. NQ_SESSION.ROLES is a session variable that returns a semicolon delimited string of roles assigned to the . Using the convention that weeks begin on Sunday (adjust accordingly if thats not the case for your enterprise), we can think of Current, Previous, and Next weeks. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. Since you are going to be setting these date variables using physical SQL in initialization blocks, the SQL issued will be specific to the database platform you are using. 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. 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 Type You can also right-click an existing initialization block in the Variable Manager and choose Disable or Enable. 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 modify the value of the session variable with a dashboard prompt. Data Processing Data Concurrency, Data Science When Jane connects to the Oracle BI Server, her session contains three session variables from row-wise initialization: LEVEL, containing the value 8; STATUS, containing the value FULL-TIME; and GRADE, containing the value AAA. This chapter contains the following topics: Working with Multi-Source Session Variables. Url Contains the global unique identifier (GUID) of the user, typically populated from the LDAP profile of the user. See <> for more information. Values in repository and session variables are not secure, because object permissions do not apply to variables. When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. Monitoring An error will generate if a session variable is used. Select this option to identify the variable as sensitive to security when using a row-level database security strategy, such as a Virtual Private Database (VPD). To add a Default initializer value, type the value in the Default initializer box, or click the Expression Builder button to use Expression Builder. When the user modifies the date range using the first calendar, the format of the first date changes. How to get the closed form solution from DSolve[]? To create initialization blocks, perform the steps in the following sections: Assigning a Name and Schedule to Initialization Blocks, Selecting and Testing the Data Source and Connection Pool. The following session variables would be created: When John connects to the Oracle BI Server, his session contains two session variables from row-wise initialization: LEVEL, containing the value 4, and STATUS, containing the value FULL_TIME. For information about using session variables when setting up security, see Managing Session Variables in Security Guide for Oracle Business Intelligence Enterprise Edition. Example message: "The execution of init block 'A_blk' cannot be deferred as it is required for authentication. Required for authentication. Such directories begin with sk_. First, you open the block that you want to be executed last and then add the initialization blocks that you want to be executed before the block you have open. Business Intelligence Server Enterprise Edition - Version 11.1.1.6.12 and later: OBIEE 11g: Error: "[nQSError: 23006] The session variable, NQ_SESSION.ICX_SESSION_CO The variables receive their values from the initialization string that you type in the Initialization Block dialog. For example, this option lets non-administrators to set this variable for sampling. See "When Execution of Session Variable Initialization Blocks Cannot Be Deferred" for more information. These multi-source session variables can be used in logical queries or in repository data filters, and contain the union of values from the different data sources. Browser Javascript Lexical Parser For example, suppose you want to create session variables using values contained in a table named RW_SESSION_VARS. Log, Measure Levels For example, the NQ_SYSTEM initialization block is used to refresh system session variables. The repository variable, NQ_SESSION.MyYear, has no value definition. Data Quality The intent is to filter that data just for that retailer. Used for Oracle BI Server. In the Variable dialog, type a name for the variable. When the execution of session variable initialization blocks cannot be deferred, a message is displayed that explains why. In the Variable Manager dialog, double-click the last initialization block that you want to be initialized. The next step is to select the data source and connection pool. Click Edit Data Source next to the Connection Pool field. You can also use this variable in a SQL statement. Nominal You won't be able to use the Oracle date functions for many of the values you want, but you can still write the SQL to return the values according to the fiscal periods in your calendar using different methods. Execution of session variable initialization blocks during session logon can be deferred until their associated session variables are actually accessed within the session. OAuth, Contact If the administrator user (defined upon install) has a Logging level defined as 4 and the session variable LOGLEVEL defined in the repository has a value of 0 (zero), the value of 0 applies. OBIEE - Connection Pool: The picture below show a repository variable call but you can use in the same way the session variable syntax. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. Connect and share knowledge within a single location that is structured and easy to search. This kind of variable can be initiate with the help of SQL statement and ca ". OAuth, Contact Is there any way to do this? You can't use SQL to set your default initializer, that's why you only see Constants as an option. OBIEE - How to set and use a server variable (system/repository) of type date ? If any of the row-wise initialization blocks returns null results, this is logged in the Oracle BI Server log, nqserver.log. DataBase Operating System If you want the initialization block to be required, in the [Repository|Session] Variable Initialization Block dialog, select the Required for authentication option. There seems to be a glitch in the way obiee (11.1.1.6.0) interprets server variables when using them as default values for a dashboard prompt: only when the variable name is in uppercase and contains no underscores will it be recognised. An error will generate if a session variable is used in the opaque view. The Variable Manager appears. For example, to filter a column on the value of the variable LOGLEVEL, set the filter to the variable NQ_SESSION.LOGLEVEL. This system session variable overrides a variable defined in the Users object in the Administration Tool. For repository variables, you can specify the day, date, and time for the start date, as well as a refresh interval. Why is the error occurring only for User B? Ratio, Code Discrete Select Use OBI EE Server, and then perform the following steps: In the box, enter the SQL initialization string needed to populate the variables. Mathematics The names of system session variables are reserved and cannot be used for other types of variables. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them. Initialization blocks are used to initialize dynamic repository variables, system session variables, and nonsystem session variables. The initialization block is used by session variables named PROXY or USER. Don't have a My Oracle Support Community account? Click OK to return to the Initialization Block dialog. Spatial You open initialization block B, and then specify that block A will execute before block B. 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). I am seeing a strange issue when using SESSION variable in OBIEE 11g RPD for data level security. This is another system variable whose presence is always assumed when the USER system session variable is used. The initialization block is used by session variables where the Security Sensitive option is selected in the Session Variable dialog. Css What are some tools or methods I can purchase to trace a water leak? Unlike dynamic repository variables, however, the initialization of session variables is not scheduled. For session variable initialization blocks, you can also select LDAP Server or Custom Authenticator. Relation (Table) . For example, if a folder were called sk_companyx, the SKIN variable would be set to companyx. Order Session variables are primarily used when authenticating users against external sources such as database tables or LDAP servers. Relation (Table) (Repository initialization blocks only) In the Schedule area, select a start date and time and the refresh interval. In addition, you can use Expression Builder to insert a constant as the default initializer, such as Date, Time, and TimeStamp. rev2023.3.1.43269. Contains the global unique identifiers (GUIDs) for the application roles to which the user belongs. Then, enter its associated string. Business Intelligence Server Enterprise Edition - Version 12.2.1.0.0 to 12.2.1.4.200414 [Release 12g]: OBIEE 12c - case sensitivity of USER session variable name OBIEE 12c - case sensitivity of . The SQL SELECT statement in the Default initializer list can contain multiple columns. When you check in the initialization block, the value of the dynamic variables is reset to the values shown in the Default initializer. This section contains the following topics: The value of a static repository variable is initialized in the Variable dialog. However, it will still work with other data sources because the SQL statement is processed by the Oracle BI Server. The table describes the available system session variables. Data (State) Selector This variable has a possible value of 0 or 1. If you stop and restart the Oracle BI Server, the server automatically executes the SQL statements in repository variable initialization blocks, reinitializing the repository variables. (Optional) Click Test to test the data source connectivity for the SQL statement. There are two "flavors" of system variables: System session variables: User ID, the user's data security groups, and the user's web catalog group(s) are all examples of system . You could then set a security filter for all members of a group that would allow them to view only data pertinent to their region. You can also use this variable in a SQL statement. Oracle BI, This value persists, and does not change until an administrator decides to change it. Unlike dynamic repository variables, session variables are not updated at scheduled time intervals. You could then set a security filter for all members of a group that would allow them to view only data pertinent to their region. The value is case-insensitive. Testing In the [Repository|Session] Variable Initialization Block Data Source dialog, click Test. Enter a title that clearly identifies the subject of your question. Values can still be added to the multi-source session variable from other component initialization blocks that succeed in returning values. 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. It contains the name that is displayed to the user in the greeting in the Oracle BI Presentation Services user interface. The Required for authentication option is dimmed, because this type of initialization block is executed after authentication. If you selected LDAP Server for your data source type, perform the following steps: Click Browse to select an existing LDAP Server, or click New to open the General tab of the LDAP Server dialog and create an LDAP Server. For example, if a folder were called sk_companyx, the SKIN variable would be set to companyx. A proxy user is a user that has been authorized to act for another user.