Business Intelligence – Oracle

Archive for March, 2008

Oracle BI EE 10.1.3.3/2 – Automating Passwords Resets – nqschangepassword.exe utility

Posted by Venkatakrishnan J on March 31, 2008

Though many of you might be aware of the simple utility called as nqschangepassword.exe, i thought this deserves a special mention here since it can serve as an automation utility to change the passwords/reset the passwords etc. You can find this utility under {OracleBI}\Server\Bin directory. As the name suggests, it helps in changing the passwords of the users in the Repository without opening the repository. The syntax for using this utility is

nqschangepassword.exe -d <data source name> -u <user name> -p <password> -n <new password> [-w <ssl passphrase> | -y]

For example,

nqschangepassword.exe -d AnalyticsWeb -u Administrator -p Administrator -n welcome1

      

This utility can be used in combination with DBMS_SCHEDULER and UTL_SMTP packages to automate the process of resetting the passwords and sending those passwords out to the end users. A very simple and very handy utility. We shall see the multitude of uses this utility can be put through in future blog entries.

Posted in All Posts, OBI EE Plus | 8 Comments »

Oracle BI EE 10.1.3.3/2 – Import from Repository – Deprecated 2-Way Merge

Posted by Venkatakrishnan J on March 28, 2008

As i have said here, the supported way to combine objects in 2 repositories is to use a 2-way merge without Parent. But for users who are new to BI EE and who are used to the “Import from Repository” feature in older releases, there is some good news. This feature still exists in the current release but has been deprecated right from 10.1.3.2. Remember, it is recommended to use a 2-way merge since that will prompt the end user to make the decision while doing the merge. But there are cases, wherein you have 2 repositories which are completely different and you are pretty sure that there are no objects that overlap each other. In such cases, you can still use the Import from Repository feature. By default if you navigate to File – Import – From Repository, you would notice that this would have been disabled by default.

      

In order to enable it, go to Tools – Options – General and enable the Import from Repository option.

      

Now, you should be able to import one repository into another. You would get this warning that this has been deprecated in the current release

      

Lets import the default bise1 repository into the paint repository.

      

But remember, you would not know what objects have been updated/created. This is very risky in situations where you have overlap of repository objects. You can end up losing some data. Always use the 2-way merge. Only if you are very sure about the underlying objects, use the Import from Repository.

Posted in All Posts, OBI EE Plus | 1 Comment »

Oracle BI EE 10.1.3.3/2 – Multi User Development of Repositories (MUD)

Posted by Venkatakrishnan J on March 27, 2008

I had some time today to put together a simple document for enabling Multi-User Development(MUD) for repositories. Typically when you have many data sources and lots of tables, it would make sense to distribute the repository development work to multiple users. MUD is basically a feature of the BI EE admin tool wherein multiple users can work on the repository at the same time. The concept of Repository Merging was actually introduced in order to facilitate MUD. Lets try to understand the concept of MUD today.

Lets start with the simple diagram below.

      

The above diagram illustrates how the MUD works. In order for the MUD to work following are the pre-requisites

1.   A shared drive to host the Master Repository
2.   All the client machines should have access to the master repository.
3.   All the client machines should have the admin tool installed.

For the MUD to work, the repository that is worked upon by all the users should be kept in a shared directory. This shared directory should be accessible to all the users. In each of the client’s Admin tool, enter the Shared Directory path.

      

Now, open the Master repository in offline mode. The entire concept of MUD revolves around objects called as Projects. So, from within the Admin tool navigate to Manage – Projects.

      

This will open up a project window. Projects are basically subsets of objects within the Admin tool that can be assigned to individual users. So, the idea is to assign different projects to different users. Also, each of these projects can contain one or more Logical Fact tables. As soon as a logical fact table is included all the other dependent objects would automatically be part of the project. Typically when we start with a repository, we would not be having any BM or presentation layers. So, it is recommended that one imports all the physical tables and creates the physical joins in the repository first before implementing MUD. After that we can create dummy BM and presentation layers so that they can be assigned to individual projects. Also, one can assign Users, Init Blocks and Variables to a project.

      

After creating and assigning objects to a project, the next step is to save the master repository in a shared drive. Now, open up a client Admin tool and navigate to File – Multiuser – Checkout. This Check out process does 2 things

1.   Copies the Master repository from the shared drive to the local drive ( This will serve as the local master repository).
2.   Gives you a screen to choose the project that you have the authority to work on.
3.   Creates a subset repository (would ask you to enter the name) which would contain only the selected project related data.

So, basically the idea is to work on the subset repository (like creating/deleting dimensions, hierarchies etc) and then merge the changes back to the local master repository. The merge process will lock the master repository in the shared drive. In order to release the lock one would have to Choose “Publish to Network” which will copy the modified and merged local master repository to the shared drive. The process would be the same for all the other users.

Posted in All Posts, OBI EE Plus | 12 Comments »

Oracle BI EE 10.1.3.3/2 – Merging Repositories – Substitute for Import From Repository – 2 way Merge with No Parent – Part 2

Posted by Venkatakrishnan J on March 24, 2008

If you look at the comments section of my blog entry here, there are quite a few users who have mentioned that the procedure did not work on their setup. One of the main reasons for that was that the changes being merged was not shown to the end user. Also, the procedure did not work for some of the earlier versions. So, i thought of blogging about another procedure that would basically merge only two repositories. If you had worked on the earlier releases of BI EE ( 7.8 or before versions ), you would have noticed that there was an option called Import from Repository. What this option basically did was it imported the objects from one repository into another. But this has been deprecated from 10.1.3.2 version of BI EE. Now the recommended way of getting one repository objects into another is to do a 2-way merge. Lets look at a step-by-step approach for achieving this. So basically our aim is to combine the contents of 2 repositories into a 3rd repository. The merge process would look like the one shown below

      

So basically, our idea is to use a blank repository as the baseline repository. Now, in our example we shall be using 2 repositories. One repository would be the default paint repository. The other would be the default bise1 repository. As the first step open up the paint repository in offline mode.

      

Then click on Merge. Specify a blank repository (create a repository which has no objects) as the original repository.

      

      

Once this is done, select the bise1 repository as the Modified Repository.

      

You would basically find the list of presentation catalogs, users, groups etc. In your case you would find either of the 2 descriptions below

Add to Current

Delete from Current

When it is Add to Current, you can choose your decision as Current or Modified(D). If you choose current, the presentation catalog would be added to the repository. Else it would be deleted from the repository. Choose Current as the decision for all the “Added to Current” descriptions.

      

If you have Delete from Current, you would have to decide whether to add the objects to the repository or delete them. Now, click on merge. This should merge both your repositories properly.

      

Posted in All Posts, OBI EE Plus | 13 Comments »

Oracle BI EE 10.1.3.3/2 – Choosing Multiple Columns in a Column Selector – Varying Multiple Columns in a Report

Posted by Venkatakrishnan J on March 20, 2008

I received a very interesting question yesterday with regard to Column Selectors. As you would already be knowing, Column Selector allows the end users to vary the columns in a report. But there is no out of the box way to choose more than 1 column simultaneously. To be more precise, consider the report below.

      

This is a very simple report containing only a tabular view of the Sales in all the Regions. Now, if we have a column selector for the Region column we can have different reports by changing the columns in the Column selector. But the major drawback with this is that it gives us a report only with 2 columns. What if we need more than 1. Like say, i would like to have Region, District as an Option and then Region,District, Brand as another option in the column selector. Lets look at an approach today to achieve this. But remember this would work only for table views. Also, you cannot use the column headings directly. In order to have Column Headings you need to create a narrative view. Lets look at the steps one by one.

Lets start with creating the above report first. For the Region column, ensure that you have set Repeating row property. Also convert this column data format to HTML. And then remove the column headers.

      

      

      

Once this is done, create a simple column selector on the Region column. Add 2 more columns to this column selector. Then add this column selector to the compound layout. So your report should look like the one below.

      

Now, go the advanced tab and copy the XML to a Text Editor. Your XML would look something like the one shown below.

      

      <saw:report xmlns:saw=”com.siebel.analytics.web/report/v1″ xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:sawx=”com.siebel.analytics.web/expression/v1″ xmlVersion=”200705140″>
   <saw:criteria subjectArea=”Paint”>
      <saw:columns>
         <saw:column formula=”Markets.Region” columnID=”c0″>
            <saw:displayFormat suppress=”repeat” interaction=”default”>
               <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
            <saw:columnHeading>
               <saw:displayFormat interaction=”default”/></saw:columnHeading></saw:column>
         <saw:column formula=”&quot;Sales Measures&quot;.Dollars” columnID=”c2″/></saw:columns></saw:criteria>
   <saw:views currentView=”0″>
      <saw:view xsi:type=”saw:compoundView” name=”compoundView!1″ rptViewVers=”200510010″>
         <saw:cvTable>
            <saw:cvRow>
               <saw:cvCell viewName=”titleView!1″>
                  <saw:displayFormat/></saw:cvCell></saw:cvRow>
            <saw:cvRow>
               <saw:cvCell viewName=”columnSelectorView!1″/></saw:cvRow>
            <saw:cvRow>
               <saw:cvCell viewName=”tableView!1″>
                  <saw:displayFormat/></saw:cvCell></saw:cvRow></saw:cvTable></saw:view>
      <saw:view xsi:type=”saw:titleView” name=”titleView!1″ rptViewVers=”200510010″/>
      <saw:view xsi:type=”saw:tableView” name=”tableView!1″ rptViewVers=”200510010″ showHeading=”false” deck=”bottom” headingDisplay=”none” rowsPerPage=”100″>
         <saw:displayFormat/></saw:view>
      <saw:view xsi:type=”saw:columnSelectorView” name=”columnSelectorView!1″ rptViewVers=”200510010″ labelPosition=”left” goButton=”false”>
         <saw:selector columnID=”c0″ bPrompt=”true”>
            <saw:choice type=”column” formula=”Markets.District”/>
            <saw:choice type=”column” formula=”Products.Brand”/>
            <saw:choice type=”column” formula=”Markets.Region”>
               <saw:displayFormat suppress=”repeat” interaction=”default”>
                  <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
               <saw:columnHeading>
                  <saw:displayFormat interaction=”default”/></saw:columnHeading></saw:choice></saw:selector></saw:view></saw:views></saw:report>

In the above XML, the part that is of our concern is the column selector view part. Now, our aim is to display multiple columns simultaneously by clicking on one column selector value. In order to do that, we shall be using a column formula as shown below

      Markets.Region||'<td>’||Markets.District||'</td>’

What this would do is that this will display both the columns in the same row if we treat the above formula as HTML. So, basically in our column selector part of the XML use the below XML

      <saw:view xsi:type=”saw:columnSelectorView” name=”columnSelectorView!1″ rptViewVers=”200510010″ labelPosition=”left” goButton=”false”>
         <saw:selector columnID=”c0″ bPrompt=”true”>
            <saw:choice type=”column” formula=”Markets.Region||’&lt;td&gt;’||Markets.District||’&lt;/td&gt;'”>
            <saw:displayFormat suppress=”repeat” interaction=”default”>
                       <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
               <saw:columnHeading><saw:caption>       
                     <saw:text>Regions and Districts</saw:text></saw:caption></saw:columnHeading></saw:choice>
            <saw:choice type=”column” formula=”Markets.Region||’&lt;td&gt;’||Markets.District||’&lt;/td&gt;’||’&lt;td&gt;’||Products.Brand||’&lt;/td&gt;'”>
                 <saw:displayFormat suppress=”repeat” interaction=”default”>
                            <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
                    <saw:columnHeading><saw:caption>       
                     <saw:text>Regions , Districts and Brand</saw:text></saw:caption></saw:columnHeading></saw:choice>
            <saw:choice type=”column” formula=”Markets.Region”>
               <saw:displayFormat suppress=”repeat” interaction=”default”>
                  <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
               <saw:columnHeading>
                  <saw:displayFormat interaction=”default”/></saw:columnHeading></saw:choice></saw:selector></saw:view></saw:views></saw:report>

instead of the one shown below

      <saw:view xsi:type=”saw:columnSelectorView” name=”columnSelectorView!1″ rptViewVers=”200510010″ labelPosition=”left” goButton=”false”>
         <saw:selector columnID=”c0″ bPrompt=”true”>
            <saw:choice type=”column” formula=”Markets.District”/>
            <saw:choice type=”column” formula=”Products.Brand”/>
            <saw:choice type=”column” formula=”Markets.Region”>
               <saw:displayFormat suppress=”repeat” interaction=”default”>
                  <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
               <saw:columnHeading>
                  <saw:displayFormat interaction=”default”/></saw:columnHeading></saw:choice></saw:selector></saw:view></saw:views></saw:report>

Now, if you go back and look at your report you should now be able to select more than one column in your column selector.

      

      

Posted in All Posts, OBI EE Plus | 13 Comments »

Oracle BI EE 10.1.3.3/2 – Cross Database Joins

Posted by Venkatakrishnan J on March 19, 2008

One of the advantages of BI EE is its ability to make cross database joins. Currently, BI Server component of BI EE supports database joins across any database. This feature is primarily meant for joins between a database and some lookup tables coming from Excel files. Though this would work for databases too, its not recommended to use them in a production environment since BI EE does the joins in memory by extracting the data from the source databases first. We will see how to go about creating a simple cross database join today. There is not much difference in the way we actually set up the repository. But still i thought this deserves its own blog entry since its a very unique feature of BI EE. We shall simulate this using 2 simple tables. One table (EMP table of the normal Oracle SCOTT schema) would be from Oracle and the other table would be from an Excel file. For demonstration purposes we shall be exporting the normal DEPT table from the SCOTT schema in the form of an excel file. So, basically we have 2 tables.

EMP Table in an Oracle Database
DEPT Table in an EXCEL file

Our final aim is to create a simple report in Answers with data coming in from both the EMP table and the DEPT table. In order to do that the first step is to import the EMP Table from the Oracle Database into the repository.

      

Similarly, import the Excel file (which is basically an export of the DEPT table) using ODBC.

      

      

So, our repository should basically have 2 tables in 2 different databases as shown below.

      

Once this is done, the next step is to create a join between these 2 tables in the physical layer.Typically, if you go to the physical layer diagram, it would allow joins only between tables in the same database. In order to achieve cross-database joins, the first step is to right click on the EMP table and then click on Physical Diagram -> All Objects and Joins.

      

In the same way (ensure that the physical Diagram window is open), right click and click on view physical diagram on the DEPT Excel table source. That will automatically bring both the tables inside the physical layer.

      

Now, create a database join between both the tables using DEPTNO as the join column.

      

Once this is done, save the changes made in the repository. Now, create a new Business Model Cross DB join. Drag and drop both the tables (DEPT and EMP) from the physical layer into the Cross DB join Business Model. Then right click on Cross DB Join and click on view the Business Model diagram.

      

Similar to what we did in the Physical layer, create a foreign key join between DEPT and the EMP table based on DEPTNO column.

      

Once this is done, drag and drop the Business Model into the Presentation layer and save the repository. Ensure that your repository is clear of any warnings or errors. Once this is done, go to Answers and try creating a simple report having DNAME and SAL as thecolumns.

      

As you see, we have now created a report that contains data from 2 different data sources. The BI Server does the joins in the background. To see what exactly the BI Server, is doing, lets look at the logs to get the exact SQL. You would basically see 2 SQLs. They are

select T7105.SAL as c1,
T7105.DEPTNO as c2
from
EMP T7105
order by c2

and

select T7088.”DNAME” as c1,
T7088.”DEPTNO” as c2
from
“‘Export Worksheet$'” T7088

      

So, what BI Server basically does is it fetches the data from both the databases using separate queries and then makes the joins in memory at run-time.

Posted in All Posts, OBI EE Plus | 1 Comment »

Oracle BI EE 10.1.3.3/2 – Level Based Measures from Answers – Using Advanced Logical SQL

Posted by Venkatakrishnan J on March 18, 2008

I was on an interesting customer call yesterday wherein the customer wanted to know whether BI EE can solve one of their reporting problems. Let us first try to understand their problem by a simple example. Look at the screenshot below.

      

As you see, the above picture shows a simple report of Sales By All the Regions in US. Now the customer wanted to have another column in this report which should be a summation of all Sales in all the Regions which in turn should be independent of the filter applied on the report i.e. even if we have a filter in the above report to show ‘EASTERN REGION’ and ‘CENTRAL REGION’ alone, the third column should show all Sales in all the Regions (without applying filter on this SUM). This can be easily achieved by using Level Based Measures. But the problem with this approach is that it has to be setup in the repository which the customer did not like. His argument was there are so many measures in his organization and it is not simply feasible to create LBM’s across all the levels. He basically wanted a workaround by which the above feature can be directly obtained from the UI instead of modifying the repository. Unfortunately, there is no out of the box way to achieve this directly in answers. There are 2 approaches to solve this

1.   using EVALUATE and then call a database function

2.   Using Advanced Logical SQL.

We have the seen the option 1 quite a few times before. Hence i will not be discussing that here. Instead lets look at the 2nd option. One of the major advantages of BI Server is that one can fire logical SQL’s directly. The logical SQL would basically fire the query to the BI Server and then the BI Server would convert the query to database specific queries. So, basically when you create any report, you can see the Logical SQL getting created in the Advanced Tab of the report.

      

This logical SQL follows ANSI standards. Now, lets add a filter to the above report (1st screenshot) to display only CENTRAL AND EASTERN REGIONs. Also, add another column as SUM(Dollars).

      

      

Now, lets look at the results.

      

As you see, the 3rd column does a summation of Sales only for the Regions within the filter. So, the summation is being done after the filter has been applied. In order to achieve our desired results, go to the Advanced tab and look at the SQL. THE SQL would look like the one shown below

SELECT Markets.Region saw_0, “Sales Measures”.Dollars saw_1, SUM(“Sales Measures”.Dollars) saw_2 FROM Paint WHERE Markets.Region IN (‘CENTRAL REGION’, ‘EASTERN REGION’) ORDER BY saw_0

Now, change this SQL to the one shown below

SELECT A.Region saw_0, A.Dollars saw_1, A.Dollars1 saw_2 FROM (SELECT Markets.Region , “Sales Measures”.Dollars, SUM(“Sales Measures”.Dollars) as Dollars1 FROM Paint) A WHERE A.Region IN (‘CENTRAL REGION’, ‘EASTERN REGION’) ORDER BY saw_0

Click on Set SQL. Now, if you go back to the results tab, now you should see the Summation of Sales for all the Regions in the 3rd column even after applying the filter.

      

You can also use advanced SQL clauses like the one shown below

SELECT A.Region saw_0, B.Dollars saw_1, A.Dollars saw_2 FROM (
SELECT
Markets.Region,
SUM(Measures.Dollars) as Dollars
FROM Paint
) A LEFT OUTER JOIN (
SELECT
Markets.Region,
“Sales Measures”.Dollars
FROM Paint
) B ON A.Region = B.Region WHERE A.Region IN (‘CENTRAL REGION’,’EASTERN REGION’) ORDER BY saw_0

The above would be pretty useful if you want to make joins across subject areas. But the main disadvantage with the above approach is that you would lose your normal drilling capability.

Posted in All Posts, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.3/2 – Executing iBots from Dashboards/Answers – Sending Emails to Administrators

Posted by Venkatakrishnan J on March 17, 2008

In my previous blog entry here, i had shown you how to go about calling command line utilities(for Purging the Cache) from Dashboards/Answers. Along the same lines, lets look at another possible use case of the procedure that was used there today. Today, we shall see how to go about calling the ibots from Dashboards/Answers. i.e our aim is to execute the ibots directly from Dashboards by clicking a single URL. This would be useful in a lot of situations wherein you would like to send emails to Administrators whenever the database server is down or when there is some error in some of the reports. In order to achieve this, the first step is to create a simple ibot which would basically send out an email to the Administrator. Also, this ibot would contain a report (generally a diagnostics report). In our case, lets include a simple report.

      

      

Once this is done, find out the jobid of this ibot from the Job Manager console.

      

Now, create a batch file to call this ibot on an on-demand basis(on unix you would have to create a shell script). This batch file would basically have this command.

saschinvoke -u Administrator/Administrator -j 1

      

The advantage of the above batch file is that you can also pass parameters (jobids etc) dynamically to it. The next step is to create a simple DBMS_SCHEDULER job to call this utility.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘saschinvoke1’,
job_type => ‘EXECUTABLE’,
job_action => ‘D:\Oracle\OracleBI\server\Bin\saschinvoke.bat’,
repeat_interval => ‘FREQ=YEARLY’,
enabled => TRUE
);
END;

Now, create another procedure to run this job.

CREATE OR REPLACE PROCEDURE PRO_INVOKEIBOTS IS
BEGIN
DBMS_SCHEDULER.RUN_JOB(‘saschinvoke1’);
HTP.PRINT(‘<html>’);
HTP.PRINT(‘<head>’);
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>EMAIL Successfully Sent</title>’);
HTP.PRINT(‘</head>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>Email Successfully Sent</h1>’);
HTP.PRINT(‘</body>’);
HTP.PRINT(‘</html>’);
END;

This is the procedure which we shall expose to be called from a URL using the Pl/SQL gateway. In order to do that, grant execute privilege on the above procedure to ANONYMOUS and add the procedure to the list of accepted and executable packages by the pl/sql gateway.

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)
return boolean is
begin
if upper(procedure_name) in (‘HR.PRO_INVOKEIBOTS’) then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

GRANT EXECUTE ON PRO_INVOKEIBOTS to ANONYMOUS;

Once this is done, create a URL link in the dashboard to call the below URL

http://localhost:7779/apex/hr.pro_invokeibots

      

Clicking on the above URL should automatically send an email to the Administrator.

      

Posted in All Posts, APEX, OBI EE Plus | 5 Comments »

Oracle BI EE 10.1.3.3/2 – BI Server Cache Purge from Dashboards – Using Pl/sql Gateway and DBMS_SCHEDULER package

Posted by Venkatakrishnan J on March 13, 2008

I came across a good question on forums the other day. The question is how do we purge the BI Server cache from Answers or Dashboards. Basically, the user wanted to have a link clicking on which should enable him to purge the cache. Currently there is no out of the box way to achieve this since cache purging is done through some ODBC calls. It would have been much easier if we had a go URL to automatically purge the cache. But unfortunately there is none (if anyone comes across a GO URL or some other method of doing this feel free to share in the comments section). Lets look at a round about approach today to achieve this. Remember to test this out completely since it involves a lot of dependency across multiple components. Our methodology is based on below list of steps.

1.   Create a batch file which would make a call to nqcmd.exe to purge the cache.
2.   Call this batch file using DBMS_SCHEDULER package of Oracle Database.
3.   Create a package which would call the above dbms_scheduler package and then would do HTP.P prints to a static Html file.
4.   Expose this package to the end users using Pl/SQL gateway
5.   Call the Pl/SQL gateway URL from dashboards.

One of the main reasons of using the above approach is that this gives us a lot of flexibility. We shall be seeing more blog entries next week based on this approach. Lets start first with creating a simple batch file. This batch file would have the following command.

D:\Oracle\OracleBI\server\Bin\nqcmd.exe -d AnalyticsWeb -u Administrator -p Administrator -s D:\CachePurge.txt -o D:\Output.txt

As you see what this batch file does is it calls nqcmd.exe to purge the cache. NQCMD.exe is basically an utility which can make ODBC calls. CachePurge.txt file would contain the below command.

{call SAPurgeAllCache()};

      

      

The next step is to create a DBMS_SCHEDULER job to make a call to this batch file. DBMS_SCHEDULER is a database package which has a variety of features. One of its features is to make a call to command line utilities or shell scripts. In order, to use this package you need to ensure that that Job Scheduler service of the database (not BI EE) to be up and running. For all the database packages and procedures i have used the HR schema.

      

The job that you need to create is as shown below.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘saschinvoke1’,
job_type => ‘EXECUTABLE’,
job_action => ‘D:\Oracle\OracleBI\server\Bin\saschinvoke.bat’,
repeat_interval => ‘FREQ=YEARLY’,
enabled => TRUE
);
END;

      

Now, once this is done, create another pl/sql procedure to run this job on demand. The procedure is shown as below

CREATE OR REPLACE PROCEDURE PRO_INVOKECACHEPURGE IS
BEGIN
DBMS_SCHEDULER.RUN_JOB(‘saschinvoke1’);
HTP.PRINT(‘<html>’);
HTP.PRINT(‘<head>’);
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>CACHE SUCCESSFULLY PURGED</title>’);
HTP.PRINT(‘</head>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>List of Employees</h1>’);
HTP.PRINT(‘</body>’);
HTP.PRINT(‘</html>’);
END;

This is the procedure which we shall execute via the pl/sql gateway. Thats why we are using HTP.P procedure inside the above procedure.

      

Now, the next step is to configure the Pl/SQL gateway. In order to configure Pl/SQL gateway use a 11g database and configure APEX. That will automatically configure the pl/sql gateway. For more details on this check my blog entry here. Once the above procedure has been compiled, lets grant execute on this procedure to anonymous so that everyone can execute this (Remember this has security implications. Try this only on a test instance. To use this on production you need to make this more secure by granting access on this only to relevant users).

GRANT EXECUTE on PRO_INVOKECACHEPURGE to ANONYMOUS;

Once this is done, ensure that you have added this procedure to the list of accepted and accessible procedures by adding it to wwv_flow_epg_include_mod_local function.

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)
return boolean is
begin
if upper(procedure_name) in (‘HR.PRO_INVOKECACHEPURGE’) then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

Now, test this procedure using the URL as shown below.

http://localhost:7779/apex/hr.pro_invokecachepurge

      

Now, all we need to do is to include this URL in dashboards by using the below in a URL object.

      

This is a very round about way of achieving such a small functionality. One of the main reasons why i have used the above approach is to introduce you to the above method. We shall see much more complex uses of the above approach in future blog entries.

Posted in All Posts, APEX, OBI EE Plus | 8 Comments »

Oracle BI EE 10.1.3.3/2 – Conditional Formatting based on Multiple Columns

Posted by Venkatakrishnan J on March 12, 2008

Another simple question came today to me via email today. Though everyone would know about this, i thought i would blog about it to keep this as a future reference. As soon as 11g comes out(not sure when though), i shall be blogging about how the same can be done in 11g. The question is “I have a very simple report containing Region, Dollars Year Ago, Forecast and Dollars as shown below. I need to highlight all the Regions in Red for which Forecast is greater than Dollars Year Ago but is less than the actual sale( Dollars).” i.e in the report shown below we would have to highlight all the regions except the WESTERN REGION.

      

In order to achieve this we will be following a very simple technique. The first step is to create a new column containing the below formula.

CASE WHEN Measures.Dollars > Measures.”Forecasted Dollars” AND Measures.”Forecasted Dollars” > Measures.”Year Ago Dollars” THEN 1 ELSE 0 END

      

Now, hide this column and go to the properties of the Region Column and add a conditional Format, something like “If the new column created above is 1 then Red Color”.

      

      

Now, you would have all the Regions for which Forecast is lesser than Sales but greater than year Ago sales, to be highlighted in Red.

      

Again very useful technique. But this can be applied only for tabular views since conditional formatting on one column based on other columns are not available in other views.

Posted in All Posts, OBI EE Plus | 6 Comments »