Monday, April 2, 2012

BI Publisher Auditing and Monitoring Updates with 11.1.1.6


With the latest release of BI Publisher 11.1.1.6 we eliminated many steps out of your way to enable the BI Publisher Auditing and Monitoring.

Here is a list of the steps.

  1. Enable Auditing from BI Publisher Administrator UI
  2. Run RCU to create IAU schema
  3. Create JDBC connection pool with WebLogic Console
  4. Set Audit Level with EM Console
  5. Register JDBC connection for Audit Policy Store with EM Console

You still need to go through the above 5 steps, but hey, compared to the previous release where you had to manually modify some configuration files, run sql script, copy files, etc, now it got much more simple and easier!

Let's take a look at the each step.

1. Enable Auditing from BI Publisher Administrator UI

Only thing you need to do for this step is to check 'Enable Monitor and Audit'. It’s that simple.

1. Go to Administrator page and click 'Server Configuration' menu

skitched-20120402-165011

2. Scroll down to the bottom, and check 'Enable Monitor and Audit', then hit 'Apply' button.

image

2. Run RCU to create IAU schema

You need to run RCU to create IAU schema as you would have needed to do with older releases. But the cool thing is that now this process also runs a script to create a table (xmlpserver) to store all the BI Publisher specific information, so no need to run a separate sql script!

1. Go to RCU folder/directory to run RCU.

This step is basically exactly the same as before.

Here are the steps:

  1. Go to $RCU_HOME/bin and execute the ‘rcu’ command
  2. Choose Create at the starting screen and click Next.
  3. Enter your database details and click Next.
  4. Choose the option to create a new prefix, for example ‘BIP’, ‘KAN’, etc.
  5. Select 'Audit Services' from the list of schemas.

    image

  6. Click Next and accept the tablespace creation.
  7. Click Finish to start the process.

 

3. Create JDBC Connection with WebLogic Console

Now, you need to create a JDBC connection on your WebLogic Server for the RCU scheme you just created so that the Audit Framework can access to the schema and start loading the auditing log data.

image

  • Click ‘Lock & Edit’ so that you can make changes (If not done yet)image

  • Click New –> ‘Generic Datasource’ to create a new data source.
  • Enter the following details for the new data source:
    Name: Enter a name such as ‘bip_audit_datasource.
    JNDI Name: jdbc/AuditDB
    Database Type: Oracle

    image
  • Click Next and select ‘Oracle's Driver (Thin XA) Versions: 9.0.1 or later’ as Database Driver (if you’re using Oracle database), and click Next.
  • The Connection Properties page appears. Enter the following information:
    Database Name: Enter the name of the database (SID) to which you will connect.
    Host Name: Enter the hostname of the database.
    Port: Enter the database port.
    Database User Name: This is the name of the audit schema that you created in
    RCU. The suffix is always IAU for the audit schema. For example, if you gave
    the prefix as ‘KAN’, then the schema name would be ‘KAN_IAU’.
    Password: This is the password for the audit schema that you created in RCU.

    image
  • Click Next.
  • Accept the defaults, and click Test Configuration to verify the connection.
  • Click Next
  • Check listed servers where you want to make this JDBC connection available.

    image
  • Click ‘Finish’

Make sure you click ‘Activate Changes’ at the left hand side top to take the new JDBC connection in effect.

image

 

4. Set Audit Level with EM Console

Now you can set a level of the auditing for each BI Publisher’s auditing type by using Fusion Middleware Control (a.k.a. Enterprise Manager).

1. Login to Fusion Middleware Control UI

http://hostname:port/em (e.g. reporting.oracle.com:7001/em)

2. Access to Audit Policy configuration UI from the menu

Under WebLogic Domain, right-click bifoundation_domain, select Security and then click Audit Policy.

image

3. Set Audit Level for BI Publisher.

While you can select ‘Custom’ to set a customized level of Auditing for each component, you can simply select ‘Medium’ to enable all the BI Publisher related auditing.

image

 

5. Register JDBC connection for Audit Policy Store with EM Console

Finally, you can register the JNDI/JDBC data source to store your Auditing data. In order to do that, you just need to register the JDBC connection you created in the previous step as ‘Audit Store’ with Fusion Middleware Control (EM). Here are the steps:

1. Login to Fusion Middleware Control

2. Navigate to Weblogic Domain, right click on ‘bifoundation…..’, select Security, then Audit Store.

image

3. Click the searchlight icon next to the Data Source JNDI Name field.

4.Select the Audit JNDI/JDBC data source you created in the previous step in the pop-up window and click OK.

image

5. Click Apply to continue.

Now, you need to restart the WebLogic Server to take all the changes in effect.

Completing these steps, you should be able to start seeing all the BI Publisher related auditing data in the database scheme. As I have demonstrated in the previous post, now you can use BI Publisher to start visualizing the data.

If you are not seeing any data in IAU_BASE or XMLPSERVER table, then you might want to check the log file, which is located at $BI_HOME/user_projects/domains/bifoundation_domain/servers/AdminServer/logs/AdminServer-diagnostic.log

And lastly, here is a video that shows you the power of BI Publisher Auditing and Monitoring.

 

 

Happy Auditing and Monitoring!

Wednesday, July 27, 2011

BI Publisher 11G Upgrade Part 2 ! – Things to Know

The Stonehenge in UK

I just talked about the 11G Upgrade the other day, but looks there are some confusions around this topic, so here I am, trying to clear them up today.

Data Set with No Columns!

When your report’s data model is using SQL Query or BI Answers as the data source type then after the upgrade you would notice that your data sets don’t have any column in the Data Model Editor. So it would like this.

image

This is because such data sets in 10G don’t capture necessary information to populate such columns for the new 11G Data Model. For example, SQL Query data sets don’t have any data type information for each column nor any grouping or hierarchy information. We wanted to make the upgrade process as easy and quick as possible so when you do the upgrade with the Upgrade Assistant utility BI Publisher doesn’t require any database connection. That means, you can perform the upgrade at anywhere without requiring any database connection. However, the Upgrade Assistant has no idea about each column or element data.

However, if you uses Data Template with SQL Queries in 10G then after the upgrade your 11G data model will have all the columns and associated information. This is because, as you imagine, all the necessary information is already provided in the Data Template.

image

 

So you can keep running the reports using the data model without any modification after the upgrade. However, this type of data model is not really expandable. For example, you can’t add a calculated column from the UI and you can’t create a link with other data sets. Having said that, I would recommend that you take a look at such Data Models and decide if you want to keep them as they are. If you like to have those columns populated then simply, you can create a new data set copying the same SQL query from the original data set, then you can delete the old data set. One thing to note though, when you do create a new data set then make sure the generated XML structure and the element names match to the RTF Template using the data model. Instead of updating the RTF templates, which can be cumbersome, you can use the Structure Pane to update the XML element name.

image 

 

11G Data Model is More Restrictive

Since we’ve moved completely to a Web UI based Data Model Editor instead of the creating/updating the Data Template with Text editors model, now there are some restrictions for the Data Model. Therefore, you might get a warning message when you try to save the data model with invalid configuration. There are two things you want to be aware for this validation thing. 

1. Case Sensitive

Let’s take a look at the following example, you see there is a aggregated element ‘DEPTSAL’, which is referencing ‘G_EMP.SALARY’ with ‘SUM()’ function.

bip_data_template_case 

With 10G Data Template you can reference to other elements without caring much about whether the letters are upper cases or lower cases. So if you write ‘G_EMP.salary’ it would still work with 10G. But not with 11G. That means it has to be ‘G_EMP.SALARY’.

2. Orphan Elements

Now let’s take a look at another example below.

bip_data_template_orphant

As you see, the ‘FAVORITE_MOVIE’ element is referencing to ‘FAVORITE_MOVIE’ column but it doesn’t exist in the SQL query. With 10G, BIP just returned a null value for this. But not with 11G. This would give you an invalid warning unless you delete this column from the Data Model editor UI.

Therefore, it’s important to review the original data templates and correct them before the upgrade. If you didn’t correct them or didn’t catch them then you can open such data models with the Data Model Editor and remove such columns or recreate a new data set within the same data model.

Upgrade or Upload ?

I’ve got this question of ‘Can I upload the upgraded reports to 11G from the UI ?’, and the short answer is ‘No’. The Upgrade Assistant utility is meant to upgrade your 10G catalog to 11G converting each report to an appropriate format for 11G. So Yes, it does migrate the 10G reports to 11G, however, it is not a migration tool that you want to use to migrate one report after another in order to upload them later.

But there might be some circumstances where you want to upgrade only a set of the reports instead of all of them. If that’s the case, I would suggest you do the upgrade first with the Upgrade Assistant utility by pointing your 10G catalog as the source then pointing to an empty folder as a target, which will create a new 11G catalog with your upgraded reports. Then you can manually move a selected set of the reports by copying them over to another 11G catalog on the file system where the catalog is located. But not from UI.

 

How They Look Like in Catalog ?

I thought it would be helpful to show you how the reports look like in the Catalog for both 10G and 11G. The main thing is, as I talked in the previous post ‘10G Users! Let’s Upgrade to 11G!’, a single report in 10G will be split to two reporting objects in 11G. One is a Report (.xdo) and another is a Data Model (xdm). And here’s how they look like.

Report in 10G Catalog

image

 

Report in 11G Catalog

This is the sample report upgraded to 11G. Now there are two report objects, one is ‘Employee Salary Report.xdo’, which contains report definition, layout templates, etc, and another is ‘Employee Salary Report.xdm’, which contains only the data model (such as SQL query) information.

Report - XDO

image

Data Model - XDM

image

 

Hope this post have clarified some of the uncertainty around the upgrade, and your upgrade process will be as smooth as possible. But if you have hit any issue or question or anything, please feel free to contact me at Kanichiro.Nishida@oracle.com. Happy Upgrade!

10G Users! Let’s Upgrade to BI Publisher 11G!

image image

10G users! Have you had a chance to try out 11G yet ? Are you planning to upgrade to 11G soon ?

BI Publisher 11G release is a huge milestone for us. We have changed the way the traditional reports development have been with this release completely. With 11G, you can start developing your reports quickly and easily with the new web UI tools such as Data Model Editor and Layout Editor, and view the reports interactively with the new Interactive View format option, to gain more insights about your data. You can do all of that within your browser without installing any desktop application or any plug-ins.

On top of that, if you prefer the RTF template (MS-Word) as your report design option you can continue to do so with the BI Publisher Template Builder (MS-Word plug-in) 11G, and enjoy its super flexible reports development capability with its enhanced functionality. Also, we made the Sub-template and Style template much easier to link to the main templates from the web UI. Oh, and if you like to use other template types such as PDF, Excel, XSL, Flash, of course! you can continue to do so as well. So 11G is like BI Publisher on steroid if you’re coming from 10G !

Now, let’s take a look at how to upgrade the BI Publisher 10G to 11G.

Things to Know Before the Upgrade

With 11G, now the data model is completely a separate object. You can create multiple reports all pointing to a single shared data model. So after the upgrade, your single report on the 10G will be split into two Reporting Objects, one is a Report (.xdo) that contains the layouts and another is a Data Model (.xdm) that contains the data model information.

Another thing to be aware is, BI Publisher 11G shares the catalog, where all the reports are stored, with BI Presentation Server. That means, when you install BI Publisher 11G as part of the BIEE suite then there will be only one catalog, which is called BI EE catalog (or, BI Presentation catalog), and that’s where all the BI contents will be stored including BI Publisher reports, BI Answers requests, Dashboards, etc. If you installed only BI Publisher standalone then you will have a BI Publisher catalog, it’s the same way as the 10G.

Overall Process

There are three main steps to upgrade your BI Publisher 10G to 11G.

  • Install BI Publisher 11G
  • Upgrade BI Publisher 10G catalog to 11G
  • Migrate BI Publisher 11G catalog to BI Presentation catalog (Only for BIEE integrated environments)
  • Upgrade BI Publisher 10G Scheduler Schema to 11G

There are two things you need to upgrade, one is the BI Publisher catalog and another is the BI Publisher Scheduler schema. After upgrading the catalog you’ll have a BI Publisher 11g catalog with your contents. And if your BI Publisher is integrated with BIEE then you need to migrate (or upload) the catalog to the BIEE catalog. And of course, if your BI Publisher is running on a standalone mode then this step is not necessary.

Install BI Publisher 11G

There is an installation guide that takes you through the steps with detail information. Or, there are many blog posts already written about the installation including mine, ‘Oracle BI Publisher 11G Installation’.  So I’m not going to repeat the installation steps here, sorry! (If you have any question around the installation please feel free to contact me at kanichiro.nishida@oracle.com or tweet us @bipublisher!)

Upgrade BI Publisher 10G Catalog to 11G

Backup!

Before you start the upgrade, as always, I’d highly recommend that you make a backup for both the 10G and 11G catalogs. I know it’s a normal practice so nothing special here, but just to make sure…

Source Catalog and Target Catalog

One thing you want to decide before you start the upgrade is the locations for the original 10G catalog and for the target catalog which will be generated by the Upgrade Assistant utility.

You can find the 10G BI Publisher catalog location by logging into BI Publisher 10G and go to Admin –> Report Repository page.

image

Start Upgrade!

You can use the Upgrade Assistant utility to upgrade the BI Publisher 10G catalog. The utility is an UI based application It is located under %Oracle_Home%/Oracle_BI1/bin folder (or directly) and you can simply double click on ‘ua.bat’ for Windows or run ‘ua’ for Linux/Unix to start.

clip_image013

 

Once you launch the utility you will see the following UI.

clip_image015

 

First, select ‘Upgrade Oracle BI Publisher Repository’

clip_image016

 

Select ‘Upgrade 10g BI Publisher Repository Directory’ and specify your 10G catalog location.

clip_image020

 

And now you specify a target location for the upgraded catalog. (e.g. C:\BIP\11G\repository)

clip_image022

 

Now you can enter WebLogic Admin Server connection information. Let’s say, if you can access to the WebLogic Console with ‘http://bipublisher.us.oracle.com:7001/console’ then the Host is ‘bipublisher.us.oracle.com’ and the Port is ‘7001’. The username and passwords are the same ones to login to the Console.

clip_image024

 

When you click ‘Next’ button you will see an Examination window which checks the server connectivity. If’ the status shows ‘succeeded’ then Click ‘Next’.

clip_image026

 

You’ll get a Review window. If the source and target catalog locations look ok then click ‘Upgrade’ button.

clip_image028

 

Once the upgrade is done, you will see the Status showing ‘succeeded’. Click ‘Next’ button.

clip_image030

 

Here, you can find a log file location. (e.g. C:\biee11\Oracle_BI1\upgrade\logs\postupgrade.txt) You might want to review the file to ensure that there is no error during the upgrade process. Well, if you see the ‘succeeded’ in the previous step there shouldn’t be any error message though.

clip_image032

 

Now you should be able to find a newly generated catalog at the specified location !

clip_image034

 

If your BI Publisher is integrated with BIEE then you need to migrate (or upload) the catalog to the BIEE catalog following the steps at the next section. But if your BI Publisher is running on a standalone mode then you can skip the next step and jump to ‘Upgrade BI Publisher 10G Scheduler Schema’ section. .

 

Upload to BIEE (BI Presentation) Catalog

From 11G, the integrated of BIEE and BI Publisher is very tight. BI Publisher shares the BIEE catalog so that the users can access to all the BI contents including BI Publisher reports, BI Answers requests, BI Dashborad, etc, in a single catalog and from a same UI. Now you have an upgraded BI Publisher catalog, which was generated at the previous step, so you need to upload that to the BIEE catalog. 

1. Login to BI Publisher 11G as an user with BI_Admin application role or ‘weblogic’ user.

2. Go to Administration UI and click ‘Server Configuration’ link, you should see the following page.

clip_image036

 

3. Now the Catalog Type should be set to ‘Oracle BI EE Catalog’. Click ‘Test Connection’ to ensure BI Publisher can connect to Oracle BI Presentation Services

clip_image038

4. Enter the path of the upgraded BI Publisher catalog location

5. Click ‘Upload to BI Presentation Catalog’ button. This will upload the contents of your BI Publisher catalog to the BI Presentation catalog.

clip_image040

 

Now when you go to the Catalog UI by clicking the ‘Catalog’ menu, then you should see all the folders and reports from the 10G catalog along with other 11G contents !

clip_image042

 

Upgrade BI Publisher 10G Scheduler Schema to 11G

After finishing the catalog upgrade, there is one more thing. You need to upgrade the BI Publisher Scheduler schema. You can do so by using the same Upgrade Assistant utility.

Start Upgrade Assistant Utility and select ‘Upgrade Oracle BI Publisher Scheduler Schema’ and click ‘Next’.

clip_image018

 

Type an information about the database where the BI Publisher 10G Scheduler schema is installed, and click ‘Next’.

image

 

Now, type an information about the target database where the BI Publisher 11G scheduler schema is installed. This is the same database that you run the RCU against before the 11G installation.

image

 

Select a Schema Name that you installed the RCU, and click ‘Next’

image

 

Once the ‘Examination’ process is done and the Status is showing ‘succeeded’ then click ‘Upgrade’.

image

 

After the upgrade process is done you should see the Status is showing ‘succeeded’, and click ‘Next’.

image

 

In the last page, you can find a location where you can find a log file for the upgrade. You might want to review the log file even if the Status in the previous is showing ‘succeeded’ to ensure there is no error or warning.

image

 

Explore 11G!

And that’s it ! Now you have all your 10G BI Publisher reports migrated to 11G. You’re ready to start taking advantage of BI Publisher 11G new features to create, update, schedule, and share your reports. I have written a post about the 11G new features here before if you haven’t checked yet. Also, we have a series of BI Publisher 11G introduction videos at our BI Publisher Youtube channel. There is also a tutorial specific to 11G as well at OTN. Enjoy 11G!

p.s. If you have experienced any issue with the upgrade process please feel free to contact me at ‘kanichiro.nishida@oracle.com’ or tweet to @bipublisher with #bipupgrade.

Follow bipublisher on Twitter

Thursday, July 7, 2011

Start BI Publisher Enterprise Server for your E-Business Suite Data!

 image

image

image

BI Publisher has always supported the EBS (Oracle E-Business Suite) security as one of the security model options. However, when it comes to the EBS data level security there was no straight forward way to support it before. And this has prevented many folks from moving to BI Publisher Enterprise Server for the EBS reporting even when we know that BI Publisher Enterprise Server would provide a great deal of benefits that come with it such as Data Model Editor, Layout Editor, Interactive View, just to name a few.

So, we added the EBS data level security support with the latest release of 11.1.1.5 to help our EBS customers to move to BI Publisher Enterprise Server and take advantage of such benefits.

I created a quick video demo for this new feature, if you are interested check the following video.

 

If you prefer reading or like to know more detail please continue.

What is the EBS Role Based Data Security?

If you are familiar with EBS you probably know the data level security comes with the responsibility and org_id combination. Each responsibility is associated with an org_id and the user selects a responsibility before running a report. And many of the EBS database views, which typically many of the EBS reports query against, use the org_id to filter the data. Therefore, based on your selection of the responsibility you will get a different set of the data for a single report. And of course this means that another user with a different responsibility would see a different set of the data from the one you would see.

 

image

 

And that’s exactly what we did with BI Publisher Enterprise Server with the release of 11.1.1.5. Now the user can select one of the responsibilities that are assigned to the user at the login time or from the My Account profile setting. Once the user select it BI Publisher automatically takes care of that by setting an application context passing the org_id when it opens a EBS database connection for the user.

How to Configure?

There is no extra configuration to enable the Role Based Data Security at all. Once you have integrated your BI Publisher Enterprise Server with your EBS by selecting ‘Oracle E-Business Suite’ as the security model and upload the EBS application dbc file, then BI Publisher will take care of all the security related stuff from there based on the user’s selection of the responsibility!

image

How It Works?

Right after you login to BI Publisher Enterprise Server with your EBS user id and password, you will be asked to select one of the responsibilities assigned to you. Once you select one then it’s all set. Any following database query will have the application context with the org_id that is associated with your responsibility.

1. Login to BI Publisher Enterprise Server with EBS user id and password.

image

2. When you login you’ll be asked to select one of the responsibilities you have. And BI Publisher will use this information to support the EBS role based security.

image

 

If you want to change the responsibility after you have logged in then you can go to ‘My Account’ dialog by clicking your username at the right hand side top and select one of the responsibilities listed under ‘Group’ tab.

image

Here is a sample report ‘Customer Open Balance Report’, which I have migrated from the EBS instance by using the BI Upgrade Assistant Utility and added some report layouts. The following screenshot shows when I select a responsibility ‘Receivables Vision Germany’.

image

When I change the responsibility to ‘Receivables Vision France’ in the My Account dialog box, now I get a different set of the data.

image

The above example is based on the RTF template that I have downloaded from the EBS instance, which is one of the out-of-box report layout templates that come with EBS R12, and uploaded to this report. Now, because I’m in BI Publisher Enterprise Server I can use the Layout Editor, which is the pure web browser based report design tool, so I created a similar layout with the Layout Editor. Here is the one.

image

And, one step further, since I can now use the Interactive capability of BI Publisher Enterprise Server why not create another layout that takes full advantage of that ! And here is the one.

image

This sample layout actually shows the Role Based Data Security much more obvious. The above example is when I select ‘Receivables Vision Germany’ where you can see the Country list shows ‘DE’ and ‘US’. The below example is when I select ‘Receivables Vision France where you can see the Country list is showing ‘FR’ and other components in the report showing a different set of data compares to the above. 

image

 

Start BI Publisher Enterprise Server for your EBS data!

With the new EBS Role Based Data Security support this is the best time to start moving your Oracle E-Business Suite reporting to BI Publisher Enterprise Server to take advantage of all the great features that come with the Bi Publisher Enterprise Server. It’s not just that it offers better usability and rich functionality over the embedded version inside the EBS and it allows you to extract data from many other different data sources and combine and aggregate, but also it enables you to deliver the next generation operational reporting that satisfies both pixel perfect customer facing requirements and interactive analytical reporting requirements to gain much greater insights from your operational/transactional data.

Happy Operational Reporting 2.0!

If you have any feedback to share please contact me directly at ‘kanichiro.nishida@oracle.com’