Alerts for the Oracle BI Delivers :
This is about creating alerts for OBI delivers.
Go to Destination Tab – > Check Interactive Dashboard. It is very simple.
Interactive Dashboard
Active iBots will appear in a Dashboard Alerts section as well as on the Alerts! page. A link to this summary appears together with the application navigation links when new iBots are delivered. After these are cleared on the Alerts page, the alert link is removed.View the original article here
Oracle Support released a patch for the [nQSError: 37005] Transaction update failed error.
This was “randomly” generated in OBIEE 11g during the check-in, in the Administrator Tool, of an online opened repository and it was very
annoying.
The patch number is 9884975 and can be downloaded from the Oracle Support site.
You have to ask the patch password by opening a new Service Request in order to download it.
Cache:
Oracle BI Server can be configured to maintain a disk-based cache of query results sets:
• Saves the results of queries in cache files
• Enables Oracle BI Server to satisfy subsequent query requests without having to access back-end databases
Overhead because of Caching:
• Disk space
– Query cache requires dedicated disk space.
• Administrative tasks:
– Set the cache persistence time appropriately.
– Purge the cache when necessary.
• Keeping the cache up-to-date:
– Evaluate what level of noncurrent information is acceptable.
– Remove stale data.
Cache Management Techniques
• Configuring the cache parameters
• Setting caching and cache persistence for tables
• Using the Cache Manager
• Inspecting SQL for cache entries
• Modifying the Cache Manager column display
• Inspecting the cache reports
• Purging the cache entries manually using the Cache
Manager
• Purging the cache entries automatically
• Using event polling tables
• Seeding the cache
Cache Hit Conditions
A cache hit occurs only when certain conditions are met, such
as the following:
• Query WHERE clause constraints need to be equivalent to the cached results or to a subset of the results.
• All the columns in the SELECT list of a new query must exist in the cached query or they must be able to be calculated from the columns in the query.
• Join conditions must be equivalent.
• Queries that request an aggregated level of information can use cached results at a lower level of aggregation.
All the prompts in OBIEE are column based prompt. You can’t create a prompt if you don’t select a column. And as it’s the only way to set up a variable for a formula, it’s impossible to bypass this constraint.
In our case, we don’t care about the value in the dimension column as we want parameter values as for instance the hierarchy level :
{{:dat:obiee:presentation_service:obiee_parameter_prompt_result.jpg|}}
The idea behind the scene is :
– to create a column which is on the top of a dual view (or skinny table) in the physical layer.
– to create a dummy business model because BI Server requires us to have a minimal of one logical join.
– to create a special subject area (presentation catalogue) to separate the functional of the parameters columns
– to be able to choose this column in order to create one or several others parameter prompts.
The advantages of this solutions are that :
* as you query a skinny table (or dual view), the performance are very good
* as you use a column, you can translate in other languages the label of the parameter prompt
* you can add a parameter value without changing the original dashboard prompt
===== Creation of the Parameter Table =====
In this example, we show the creation of a parameter table with a dual view but you can choose to create a table to store your parameters.
* In the physical layer, right click on a physical schema and choose New Physical Table
* select “Select” as Table Type from the drop down menu
* and enter the Sql below
Example :
SELECT ‘MONTH’ AS REPORT_LEVEL FROM DUAL
UNION
SELECT ‘QUARTER’ AS REPORT_LEVEL FROM DUAL
{{:dat:obiee:presentation_service:obiee_dummy_table.jpg|}}
Second, you need to create the column report_level from the sql statement.
* click on the column tab and create it
{{:dat:obiee:presentation_service:obiee_dummy_table_columns.jpg|}}
If you choose this column in a query, the values MONTH and QUARTER will be returned.
===== Creation of the Business Model Layer =====
* Drag and drop **TWO** times this table in order to create two logical table source :
* Parameters1
* Parameters2
* Select this two tables,
* Right click / Business Model Diagramm / Selected Tables only
{{:dat:obiee:presentation_service:obiee_dummy_business_model.jpg|}}
* and create a new complex join between them.
{{:dat:obiee:presentation_service:obiee_dummy_model_2.jpg|}}
This step is important beacause Obiee require that a fact table has minimum one complex logical join
===== Creation of the Presentation Catalog =====
* Right click in the presentation layer / New Presentation Catalog and name it Parameter
* Just drag and drop one of the two logical table and you are done.
* You can cache this new presentation catalog to the others users by giving the correct privilege. In the Oracle BI Presentation Dashboard / Administration / Manage Privileges.
* Reload the metadata in the BI Presentation Dashboard in Administration / Reload Files and metadata
{{:dat:obiee:presentation_service:obiee_parameter_subject_privilege.jpg|}}
In the picture above, you can see that only the Presentation Server Administrator have the right to the Parameter Area but everybody can use a object (report, prompt) create with it
===== Creation of the dashboard prompt =====
Now that we have our parameter column in our parameter subject area, it’s possible to choose it in order to create a parameter prompt.
* Go to answer
* Click on the icon : New Dashboard prompt on the left side
* And select the subject area Parameter
{{:dat:obiee:presentation_service:obiee_new_dashboard_prompt.jpg|}}
* in the left frame, select the column of the select table (in our case report_level)
* and define the options that you need as the setting of a presentation variable
{{:dat:obiee:presentation_service:obiee_parameter_prompt.jpg|}}
* and you get :
{{:dat:obiee:presentation_service:obiee_parameter_prompt_result.jpg|}}
===== What about now if I don’t want MONTH and QUARTER but MONTH and YEAR ? =====
You can change the values in your parameter table or you can use the SQL value capabilities of the prompt :
* Go back to your prompt
* select SQL Value in the show result
* and tape this SQL :
SELECT CASE WHEN 1=0 THEN Parameters.REPORT_LEVEL ELSE ‘MONTH’ END FROM Parameter
UNION
SELECT CASE WHEN 1=0 THEN Parameters.REPORT_LEVEL ELSE ‘YEAR’ END FROM Parameter
As one is always different of zero, you will obtain MONTH and YEAR as value in the drop down list.
You can not enter this SQL :
SELECT ‘MONTH’ FROM Parameter
UNION
SELECT ‘YEAR’ FROM Parameter
because Parameter describe a presentation catalog and not a table. In the Sql of OBIEE, you really need to have for each column the name of the table otherwise you will obtain this error :
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 42021] The query does not reference any tables. (HY000)
SQL Issued: SELECT ‘MONTH’ FROM Parameter
===== Performance =====
As we use a dual or a skinny table as source table, the performance are really good.
===== Conclusion =====
You can add to your parameter subject area one column by parameter or you can change the values with the SQL capabilities from the prompt. In the two case, a sql statement against a little table of Oracle is performed and doesn’t therefore require to read data by an I/O operation on a disk. You can therefore choose your preferred solution but keep in mind that one column for one dashboard prompt is the natural Obiee mechanism and permit a good translation in other languages.
In response to an original Idea of Venkat with this blog entry: [[http://www.rittmanmead.com/2010/02/13/oracle-bi-ee-10-1-3-4-1-puzzlers-puzzle-1/|Puzzlers – Puzzle 1]]
“How do we make BI EE to generate different filters for every column(within a dimension) chosen from Answers?”
The idea is when you add the column Channel desc to an answer the query must be automatically filtered.
You can also found a copy of this entry in the wiki : [[http://gerardnico.com/wiki/dat/obiee/dimension_fragmentation_filter|OBIEE - Dimension fragmentation design to add an automatic filter with the choice of a column]]
An other simple solution to achieve this goal is to use the security filter:
[[dat:obiee:bi_server:security:security_automatic_filter_on_column]]
After a good night of rust, I deleted the alias of the physical table Channels because this solution creates a self join on the table channels. You can see the old solution [[dat:obiee:dimension_fragmentation_filter#the_old_solution|here]]
===== The design =====
*- Suppress the old logical column “Channel Desc”, create a new one with the same name
* 1 – Create a new logical table source “CHANNELS_FILTER” in the logical table Channels
* 2- Map the new logical column Channel Desc to the physical Column Channels.”Channel Desc” in the tab “Column Mapping”
* 3- Add the filter in the content tab
* – Drag and drop the new logical column in the presentation layer
{{:dat:obiee:repository_design_filter_on_column_new_logical_table.jpg|}}
===== The result =====
==== With the column “Channel class” and “Amount Sold” ====
**Sql Request**
SELECT Channels.”Channel Class” saw_0, “Sales Facts”.”Amount Sold” saw_1 FROM SH ORDER BY saw_0
{{:dat:obiee:repository_design_filter_on_column_result1.jpg|}}
As you can see no filter is added to the database query.
**Database Query**
select T161.CHANNEL_CLASS as c1,
sum(T245.AMOUNT_SOLD) as c2
from
SH.CHANNELS T161,
SH.SALES T245
where ( T161.CHANNEL_ID = T245.CHANNEL_ID )
group by T161.CHANNEL_CLASS
order by c1
==== With the column “Channel desc” and “Amount Sold” ====
**Sql Request**
SELECT Channels.”Channel Class” saw_0, Channels.”Channel Desc” saw_1, “Sales Facts”.”Amount Sold” saw_2 FROM SH ORDER BY saw_0, saw_1
By adding the column Channels.”Channel Desc”, OBIEE add automatically a filter and the join between the original table channel and its alias
{{:dat:obiee:repository_design_filter_on_column_result2.jpg|}}
**Database Query**
select T161.CHANNEL_CLASS as c1,
T161.CHANNEL_DESC as c2,
sum(T245.AMOUNT_SOLD) as c3
from
SH.CHANNELS T161,
SH.SALES T245
where ( T161.CHANNEL_ID = T245.CHANNEL_ID and T161.CHANNEL_CLASS = ‘Direct’ )
group by T161.CHANNEL_DESC, T161.CHANNEL_CLASS
order by c1, c2
==== With the column “Channel desc” and “Channel class” ====
**Sql Request**
SELECT Channels.”Channel Class” saw_0, Channels.”Channel Desc” saw_1 FROM SH ORDER BY saw_0, saw_1
**Database Query**
select distinct T161.CHANNEL_CLASS as c1,
T161.CHANNEL_DESC as c2
from
SH.CHANNELS T161
where ( T161.CHANNEL_CLASS = ‘Direct’ )
order by c1, c2
The security policy for Oracle Business Intelligence 11g defines what individual users and users with certain application roles can access and do. In Oracle Business Intelligence 11g, the security policy definition is split across the following:
Presentation Catalog – this defines which catalog objects and Oracle BI Presentation Services functionality given users and application roles can access.
RPD – this defines which application roles and users have access to which items of metadata within the RPD. You define this security policy in the Administration Console.
Policy Store – this defines which Oracle Business Intelligence Server, Oracle Business Intelligence Publisher and Real Time Decisions functionality can be accessed by given users or users with given application roles. Use Oracle Enterprise Manager to configure the default Oracle Business Intelligence Policy Store.
Oracle Business Intelligence10g and 11g security models differ in the following areas:
Defining users and groups – in Oracle Business Intelligence 10g, it was possible to define users and groups within a repository file using the Oracle BI Administration tool. In Oracle Business Intelligence 11g, you can no longer define users and groups within a repository. The Oracle Business Intelligence Enterprise Edition Upgrade Assistant migrates users and groups from a 10g repository into the embedded LDAP server in an 11g installation.
Defining security policies – in Oracle Business Intelligence 10g, security policies in the Web catalog and repository can be defined to reference groups within a directory. In Oracle Business Intelligence 11g, security policies are defined in terms of application roles, which are in turn mapped to users and groups in a directory. This allows an Oracle Business Intelligence 11g system to be deployed without changes to the corporate directory and eases movement of artifacts between development, test and production environments.
Use of the Administrator user – in an Oracle Business Intelligence 10g installation, a special user named, Administrator has full administrative permissions and is also used to establish trust between processes within that installation. In Oracle Business Intelligence 11g there is no special significance to the name Administrator and there can be one or more users who are authorized to undertake different sets of administrative functions. In Oracle Business Intelligence 11g the identity used to establish trust between processes in an installation is configurable and independent.
Repository encryption – in Oracle Business Intelligence 10g, certain sensitive elements within a repository are encrypted. In Oracle Business Intelligence 11g, the entire repository is encrypted using a key derived from a user supplied password. An 11g repository can only be opened with the password, and there is no mechanism to recover a lost password.
The following aspects of the Oracle Business Intelligence 10g security model remain in 11g:
Oracle Business Intelligence Server Initialization Blocks – Oracle Business Intelligence Server 11g continues to support the use of initialization blocks for authentication and authorization. In 10g Oracle Business Intelligence Server falls back to use initialization blocks if a matching user cannot be found in the repository. In 11g Oracle Business Intelligence falls back to use initialization blocks if the user cannot be authenticated by the installation’s configuration authentication provider.
Presentation Catalog Groups – Oracle Business Intelligence 11g continues to support the definition of catalog groups within the presentation catalog. These groups are only visible within Oracle Business Intelligence Presentation Services. Oracle recommends that presentation catalog groups be used for backward compatibility only and that application roles be used instead for new installations.
SA System Subject Area – Oracle Business Intelligence 11g supports the use of SA System Subject Area in combination with Oracle Business Intelligence Server initialization blocks to access user, group, and profile information stored in database tables.
You may want to view the data in [[analytic:dense_sparse|dense]] form, with rows for all combination of dimension values displayed even when no fact data exist for them.
And it’s also the most problem that you have when you work with [[analytic:function_time|time series calculations]] as the [[dat:obiee:presentation_service/obiee_period_to_period_lag_lead_function|lag/lead function]], is that the data are not [[analytic/dense_sparse|dense]].
This article show the capability of the [[dat:obiee:logical_sql:|OBIEE logical sql]] to [[analytic:data_densification_sparse_to_dense|densify]] the data. To know more about the Obiee logical Sql, you can also read the good post of [[http://oraclebizint.wordpress.com/2009/05/07/oracle-bi-ee-101341-understanding-logical-sql-part-1/|Venkatakrishnan]].
The sample example come from this article : [[analytic:data_densification_sparse_to_dense]] and the sql below is a translation in [[dat:obiee:logical_sql:|OBIEE logical sql]] of [[analytic:data_densification_sparse_to_dense#densification_with_cross_join_and_outer_join|the densification solution with cross and right outer join]].
As the gold rule is to embedded the difficulties in the repository, I highly recommend this solution instead : [[dat:obiee:bi_server:design:obiee_densification_design_preservation_dimension]].
===== Sparse data to densify =====
A typical situation in the [[database:oracle:sample_schemas|sh schema]] with a sparse dimension is shown in the following example, which computes the weekly sales and year-to-date sales for the product Bounce for weeks 20-30 in 2000 and 2001:
SELECT SUBSTR(p.Prod_Name,1,15) Product_Name,
t.Calendar_Year Year,
t.Calendar_Week_Number Week,
SUM(Amount_Sold) Sales
FROM
Sales s,
Times t,
Products p
WHERE
s.Time_id = t.Time_id AND
s.Prod_id = p.Prod_id AND
p.Prod_name IN (‘Bounce’) AND
t.Calendar_Year IN (2000,2001) AND
t.Calendar_Week_Number BETWEEN 20 AND 30
GROUP BY
p.Prod_Name,
t.Calendar_Year,
t.Calendar_Week_Number
order by
product_name,
year,
week asc
PRODUCT_NAME YEAR WEEK SALES
————— ———- ———- ———-
Bounce 2000 20 801
Bounce 2000 21 4062.24
Bounce 2000 22 2043.16
Bounce 2000 23 2731.14
Bounce 2000 24 4419.36
—- Miss one row for the week 25
—- Miss one row for the week 26
Bounce 2000 27 2297.29
Bounce 2000 28 1443.13
Bounce 2000 29 1927.38
Bounce 2000 30 1927.38
Bounce 2001 20 1483.3
Bounce 2001 21 4184.49
Bounce 2001 22 2609.19
Bounce 2001 23 1416.95
Bounce 2001 24 3149.62
Bounce 2001 25 2645.98
—- Miss one row for the week 26
Bounce 2001 27 2125.12
—- Miss one row for the week 28
Bounce 2001 29 2467.92
Bounce 2001 30 2620.17
In this example, we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001.
===== The cross join and the outer join =====
select D.saw_0, D.saw_1, D.saw_2, A.saw_3
FROM
(
SELECT
Products.”Prod Name” saw_0,
Calendar.”Calendar Year” saw_1,
Fiscal.”Fiscal Week Number” saw_2,
“Sales Facts”.”Amount Sold” saw_3
FROM SH
WHERE
(Calendar.”Calendar Year” IN (2000, 2001))
AND (Products.”Prod Name” = ‘Bounce’)
AND (Fiscal.”Fiscal Week Number” BETWEEN 20 AND 30)
) A RIGHT OUTER JOIN
(
SELECT C.saw_0 saw_0, B.saw_0 saw_1, B.saw_1 saw_2 FROM
( Select Calendar.”Calendar Year” saw_0, Fiscal.”Fiscal Week Number” saw_1
FROM SH
WHERE Calendar.”Calendar Year” IN (2000, 2001)
AND Fiscal.”Fiscal Week Number” BETWEEN 20 AND 30 ) B,
( Select Products.”Prod Name” saw_0 FROM SH WHERE Products.”Prod Name” = ‘Bounce’ ) C
) D
ON ( A.saw_0 = D.saw_0 and A.saw_1 = D.saw_1 and A.saw_2 = D.saw_2 )
ORDER BY D.saw_0, D.saw_1, D.saw_2
===== The result in answer =====
{{:dat:obiee:presentation_service:obiee_logical_sql_densification.jpg|}}
===== The result in issue sql =====
In dashboard, Setting / Administration / Issue Sql
{{:dat:obiee:presentation_service:obiee_sh_densification.jpg|}}
