Business Intelligence – Oracle

Archive for November, 2007

Oracle BI EE 10.1.3.3/2 – One Dimension – Multiple Hierarchies

Posted by Venkatakrishnan J on November 30, 2007

There was a question yesterday on the forums regarding the usage/availability of Multiple Hierarchies for a single dimension. I thought i would blog about it since there is very little documentation on this. Now let us take an example and try to understand what multiple hierarchies are and where they are used. Classic example for this would be the Time Dimension. In time dimension we can have an hierarchy based on Calendar Year, Quarter, Month and Day and the Fiscal Year, Quarter, Month and Day. BI EE can accomodate such possible multiple hierarchies within the same dimension. Lets try to build one for a Product Dimension. There are certain ground rules for building multiple hierarchies for the same dimension.

1.   There must be a unique key for the dimension at the lowest level.

2.   This unique key must be shared by all the mutliple hierarchies i.e. all the hierarchies shoul have their last levels as a level with the unique key.

3.   All the hierarchies should roll up to the top Total Level. And Top Level attributes should not have a Logical Key defined.

In our case, we have a unique key called DIMENSION_KEY which would form the last level. Our aim is to build 2 hierarchies like this

               Total Level

   Pack Size                  Category

   Products                    Sub-Category

               DIMENSION_KEY

i.e

Hierarchy1 = Total Level -> Pack Size -> Products -> Unique Dimension Key
Hierarchy2 = Total Level -> Category -> Sub-Category -> Unique Dimension Key 

As you see above DIMENSION_KEY is a shared level by both Products and sub-category. Now lets start building the hierachy.

      

Once we have reached till the 3 level for both the hierarchies, create a child level for the level Sub-Category. The child level would be DIMENSION_KEY.

      

After this create a Shared Child Level for the Products level and use the DIMENSION_KEY for this shared level.

      

Now we have basically created a dimension containing 2 hierarchies. Just test it out to see whether you get the proper drill downs.

      

      

Advertisements

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

Oracle BI EE 10.1.3.3/2 – Saved Selections and Collapsible Sections

Posted by Venkatakrishnan J on November 29, 2007

I thought i would blog today about a simple feature called Saved Selections as it pertains to user customizations. In a multi user environment one of the most important things that any end-user would need is the ability to customize the dashboards according to his own taste/needs. And of course, he/she would like the customization to be only for him i.e it should not affect the entire the dashboard for other users. In order to achieve this we have an option in the dashboards called “Saved Selections”.
So, if you navigate to your dashboard you find this option under Page Options.

      

Saved Selections are like customization screenshots. You can do as many customizations as possible and of course you can store them so that you can revert back to any specific customization at any point in time. There are 2 options while saving a selection. One is you can save the customization only for you i.e it will not affect the dashboard for other users. The other is you can make the customization visible for other users if you have that privilege.

      

Now what are the customizations that we can do in a dashboard. Customizations can include the prompt values including column selectors and the dashboard prompts, section collapsing etc. For example, if you want to have a default value for a dashboard prompt everytime you log into the dashboard, what you can do enter value in the dasboard prompt and click on Saved Selections and make it as default. So, basically you can have default values specific to individual users. Another very important use of this is that one can collapse reports so that they do not run everytime you login. For example, if you have a report in your dashboard that takes a lot of time to run, all you need to do is collapse that report and click on saved selections. Make that selection as default. So next time you login, that report would be collapsed and you would run the report only when you expand the report. Lets try a simple example here. Our aim is to collapse certain reports for the Administrator user and make that as default for him. But this should not affect other users.

Lets log in as Administrator and Collapse the reports that we do not need.

      

Now lets save this as a saved selection(using for me i.e Administrator user along ) and make it as default.

      

Now log out and log back in as Administrator. You would see that the reports that we had collapsed would still be collapsed.

Now lets log out and log back in as another user Administrator1. You would see that the reports would not have been collapsed for this user.

      

Very simple but can be very useful in a lot of situations.

Posted in All Posts, OBI EE Plus | Leave a Comment »

Oracle BI EE 10.1.3.3/2 – Sorting of Reports in Dashboards

Posted by Venkatakrishnan J on November 28, 2007

I just picked up an old unanswered question in the forums to blog about today. The question is “How do we enable sorting of reports within Dashboards”?. We will see two ways of achieving this. One approach will enable sorting on a report to report basis. The other approach will make it default across all the reports in all the dashboards. In order to enable sorting one by one for all the reports, open the reports for which you want to enable the sorting in dashboards for and go the table properties. There just enable the “Sorting in Dashboards” check box and save the report. Now you would see that the report has sorting enabled within the dashboard.

      

      

In the second approach our aim is to make the sorting enabled(default) for all the reports in the dashboards. Setting this one by one for all the reports would be a bit cumbersome. In order to do avoid this we need to modify a custom XML message. Go to {OracleBI}/web/msgdb/messages and you would find a file called answerstemplates.xml.

      

Copy this XML file to {OracleBIData}/web/msgdb/customMessages. If you do not have the customMessages directory, create it.

      

Now, edit this copied XML file and search for the following messages

<WebMessage name=”kuiCriteriaDefaultViewElements” translate=”no”><HTML/></WebMessage>

Change the above message to

<WebMessage name=”kuiCriteriaDefaultViewElements” translate=”no”><HTML>
<view signature=”tableView” sortable=”true” />
</HTML></WebMessage>

As you see we are basically overriding the webmessage which is responsible for the sorting. Now save the XML file and restart BI Server and the Presentation Services. Now if you log back into the dashboards you would find that the sorting would have been enabled for all the reports.

      

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

Oracle BI EE 10.1.3.3/2 – InstanceConfig.xml Parameters

Posted by Venkatakrishnan J on November 27, 2007

I have been thinking for quite some time to have a document list containing all the InstanceConfig.xml parameters. Well, i have started the compilation and i will keep updating them as and when i find some new ones. The current list looks like the one below. Do let me know if you come across any new parameters so that i can add them to the list here. Your thoughts/comments are welcome.

List of Parameters – You can download the file here

Update: Thanks to Bryan. All the list of parameters are available from within the BI Management Packs. You can download the list provided by Bryan here

 Update2: Thanks to Renga for sharing this. Another wonderful document which details all the parameters and their corresponding place of usage. You can get that here

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

Oracle BI EE 10.1.3.3/2 – Same Page Navigation Drills and Passing Parameters

Posted by Venkatakrishnan J on November 26, 2007

We had a couple of questions on our internal blogs with regard to navigation drills and passing parameters. I thought i would blog about both of them since this might prove useful for others who are attempting the same. The first question was, how do we enable “Drill in Place” for navigation drills. The reason why i included it here is that we would using this to answer our second question. If you enable Navigation Drills(drills to another report instead of standard drills) in your report, you would notice that you would lose the ability to drill in place even if you enable the “Drill in Place” option within the Dashboard. This might be pretty annoying for end users. So, in order to enable this drill in place option, one way is to include the report using the “Embedded Section” option within the dashboard rather than dragging and dropping the report into the dashboard. So, while including your report in the dashboard, instead of dragging and dropping it into the Sections, include the Embedded Section and then enter the GO URL for the report in the properties of the Embedded section.

      

      

      

      

Now, if you use the above procedure to enable drill in place, then you would lose the ability of using dashboard prompts on these reports. Yes, one cannot use dashboard prompts on a report that has been included within an Embedded Section. One of the main reasons for this is that, Embedded sections create a seperate sub-pages(iframes) within the same page and the prompts actually lose their scope. This takes us to our next question. How do we have drill in place for Navigation drills and at the same time have the ability to pass parameters to the report included in the Embedded section. The following list of steps outlines what we will be doing to achieve this

1.   Create a dashboard Prompt on a column and make the prompt to set a presentation variable. In our example, i have created a prompt on the COUNTRY_NAME column.

      

2.   In the report containing the Navigation drill have the filter enabled with this presentation variable. In our example, we will be filtering on COUNTRY_NAME column of the report using the iframeprompt presentation variable.

      

3.   Create another report with 1 dummy column(this is for the report to work). Create another column and put the presentation variable as the column.

      

      

4.   Go to the narrative view of the report and enter the GO URL for the navigation drill report inside an iframe. Remember we need to pass the parameters to this report from the presentation variable. So, the GO URL would be like the one below

http://localhost:9704/analytics/saw.dll?GO&NQUser=Administrator&NQPassword=Administrator&Path=/shared/Paint%20Demo/iFrame+Prompt/Report&Action=Navigate&P0=1&P1=eq&P2=GEOGRAPHY.COUNTRY_NAME&P3=@2&Options=md

Here, @2 denotes the presentation variable column that we included. So basically we are building a dynamic go url using the narrative view iframe.

      

5.   Go to the dashboard, then include the dashboard prompt and the narrative view report. Now you would notice that you are able to pass parameter and at the same time you are able to drill in place.

      

The reason why we included the narrative view was to dynamically build the url and at the same time not to lose the scoping the presentation variable that was passed.

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

Oracle BI EE 10.1.3.3/2 – Working with Disconnected Analytics – sadis Utility

Posted by Venkatakrishnan J on November 23, 2007

I came across a very interesting utility today. I am not sure how many are actually aware of it. It is called as sadis.exe. As you would know, BI EE has a very unique feature called as Disconnected Analytics. Now lets try to understand what disconnected analytics is and then later see how sadis and disconnected analytics are related. Disconnected Analytics is a small footprint of the actual BI EE that can be used in a standalone laptop or a PC. It is primarily meant for users who would like access the BI Answers and BI Dashboards but do not have the ability to connect to the network where the BI EE is actually installed (say CEOs/CFOs who are travellling frequently). So, in order for these users to leverage the features of BI EE(one can argue that the reports and dashboards can be exported in various formats and then can be given to the travelling users. But one of the major drawbacks with this is that one does not get the same navigation interface as the BI EE so the ability to analyze the data is compromised. Disconnected Analytics overcomes this) disconnected analytics was introduced. Given below is the architecture of disconnected analytics (taken directly from the docs).

      

Now, this article is not about configuring disconnected analytics. But rather this is for users who would like to know how disconnected analytics would look like if they have BI EE already installed. This is where the sadis utility comes in. This is located under {OracleBI}/Server/Bin. If you have BI EE already installed, then you can still startup disconnected analytics on the same machine. sadis utility does that and you can browse through your presentation catalog without even having the presentation services running!!!. Now lets see what this utility does. Open up a command prompt and navigate to {OracleBI}/Server/Bin. Now enter the following command in the command prompt.

sadis /u Administrator /p Administrator /c {OracleBIData}\web\catalog\paint

      

sadis accepts 4 parameters. They are

      /u – Username for BI Server

      /p – Password for BI Server

      /c – Full path to the Web Catalog

      /s – Start Disconnected analytics in silent mode (You would just get the system tray icon. You need to manually navigate to the web browser and open up the catalog.)

Now this would open up a new browser. Also in addition to that you would find a new icon in your system tray. If this icon is present then that means that disconnected analytics has been started on your machine.

The browser opened would have the Dashboards for the user Administrator.

      

If you notice the URL in the browser carefully, it would have something like this

http://localhost:9762/sawd?Dashboard&NQUSER=Administrator&NQPASSWORD=Administrator

So, the port for disconnected analytics is 9762 and also instead of saw?Dashboard you would find sawd?dashboard where sawd denotes disconnected mode. This would accept all the go url parameters that you would use for normal SAW URL. This would also work even if you bring down the presentation services. Very interesting indeed!!!

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

Oracle BI EE 10.1.3.3/2 – Merging Repositories

Posted by Venkatakrishnan J on November 22, 2007

Update: Based on the comments below, i have created a new blog post which would work in all the scenarios. You can find it here. 

One of the features of BI EE is that one can merge repositories.So if you have 2 repositories each having its own set of Physical, BM and Presentation layers then you can use the Merge option to merge both of them into a single repository. Before going ahead, lets try to understand 3 important terms that would be used while merging. The definitions are specific to the example that we would be seeing later. But this should give you an idea.

      Current Repository   –   This is the repository that you would be merging the changes into. This is the repository that would be open while merging.

      Original Repository   –   This is the repository which is the pivot for Merge. This is just a dummy repository ( for our discussion alone. This is generally used in BI Applications when you are merging objects from an older repository)

      Modified Repository   –   This is the repository whose objects you would like to copy to the Current Repository.

What merging would do is, it would merge the difference of the repository objects that you would find between Original and the Current Repository and also the Original and the Modified Repository. Lets try to understand this with a simple example. In our example, we will be using 2 repositories. One is the default Paint Repository and the other is the Usage Tracking repository that is bundled along with BI EE. Our aim is to get the contents of the Usage Tracking Repository into the Paint repository. Lets look at the contents of both the repositories. Paint.rpd would be located under {OracleBI}/Server/Repository and the Usage Tracking.rpd would be located under {OracleBI}/Server/Samples/usagetracking

      

      

Now lets click on Merge under the File menu (assuming you already have the paint.rpd opened in offline mode). As soon as you click on Merge, you would get a dialog box asking you to choose the Original Repository. In our case, we would have to choose a dummy repository(copy the paint repository to create a new one and then include that here). Enter the password for the dummy repository and click ok. As soon as you enter that you would get a message like the one below that the repositories are identical.

      

Once this is done select the modified repository. In our case select the usagetracking.rpd and then click on merge.

      

Now you would see that the usagetracking repository objects have been merged into the paint.rpd. The Original dummy repository acts as a pivot. What BI EE basically does is that it first finds the difference between the dummy and the Current Repository. It would not find any since both are copies of each other. As soon as we include the modified repository(usage tracking), the merge operation would find the differences between the original and the modified(paintdummy and usagetracking) and then merge the differences into paint repository and would produce a new repository.

This can be very useful in quite a lot of scenarios. I have just used a simple example here. But this should give you an idea.

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

Oracle BI EE 10.1.3.3/2 – Metadata Reports

Posted by Venkatakrishnan J on November 21, 2007

We saw earlier here about how Catalog Reports can be created and used. In the same way we also have an option of creating Metadata Reports. Metadata Reports are those reports that shows how the Presentation Layer is related to the Business Model layer and in turn also shows how BM is related to the Physical layer. This report would give all the details like the init blocks used, variables used etc. It is a very comprehensive report that again can be very useful. Lets see how to go about creating this Report. This is available as a utility in the Administration Tool. If you open the Administration tool and go to Tool -> Utilities you would find a utility called as Repository Documentation.

      

      

As soon as you click on Execute it would ask you to save the output into a flat file. Save the report and open the report.

      

If you notice there are quite a few important column information that we can get out of this Report. Following are the list of columns that the report would contain.

Presentation Catalog
Presentation Table
Presentation Column
Business Model
Derived logical table
Derived logical column
Expression
Logical Table
Logical Column
Logical Table Source
Expression (BM Expression)
Initialization Block
Variable
Database
Physical Catalog
Physical Schema
Physical Table
Alias
Physical Column

The above set of columns can be very useful if you quickly want to understand your metadata. Also, if need be you can use the below script to load this file into an external table

CREATE OR REPLACE DIRECTORY METADATAREPO_DIR AS ‘D:\VENKAT\ET’

CREATE TABLE METADATAREPORT_ET (
PRESENTATION_CATALOG VARCHAR(1000),
PRESENTATION_TABLE VARCHAR(1000),
PRESENTATION_COLUMN VARCHAR(1000),
BUSINESS_MODEL VARCHAR(2000),
DERIVED_LOGICAL_TABLE VARCHAR(4000),
DERIVED_LOGICAL_COLUMN VARCHAR(4000),
EXPRESSION VARCHAR(4000),
LOGICAL_TABLE VARCHAR(2000),
LOGICAL_COLUMN VARCHAR(2000),
LOGICAL_TABLE_SOURCE VARCHAR(3000),
LOGICAL_EXPRESSION VARCHAR(4000),
INIT_BLOCK VARCHAR(4000),
PHY_VARIABLE VARCHAR(4000),
PHY_DATABASE VARCHAR(4000),
PHYSICAL_CATALOG VARCHAR(4000),
PHYSICAL_SCHEMA VARCHAR(4000),
PHYSICAL_TABLE VARCHAR(4000),
ALIAS VARCHAR(4000),
PHYSICAL_COLUMN VARCHAR(4000))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY METADATAREPO_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY 0X’09’
MISSING FIELD VALUES ARE NULL
(PRESENTATION_CATALOG CHAR(1000),
PRESENTATION_TABLE CHAR(1000),
PRESENTATION_COLUMN CHAR(1000),
BUSINESS_MODEL CHAR(2000),
DERIVED_LOGICAL_TABLE CHAR(4000),
DERIVED_LOGICAL_COLUMN CHAR(4000),
EXPRESSION CHAR(4000),
LOGICAL_TABLE CHAR(2000),
LOGICAL_COLUMN CHAR(2000),
LOGICAL_TABLE_SOURCE CHAR(3000),
LOGICAL_EXPRESSION CHAR(4000),
INIT_BLOCK CHAR(4000),
PHY_VARIABLE CHAR(4000),
PHY_DATABASE CHAR(4000),
PHYSICAL_CATALOG CHAR(4000),
PHYSICAL_SCHEMA CHAR(400),
PHYSICAL_TABLE CHAR(400),
ALIAS CHAR(4000),
PHYSICAL_COLUMN CHAR(4000)))
LOCATION (‘RepositoryDoc.csv’))
PARALLEL
REJECT LIMIT UNLIMITED

Once this is done, load this table back into the repository and then create a report.

      

      

Simple but very powerful.

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

Oracle BI EE 10.1.3.3/2 – Using Aggregate Tables – Aggregate Persistence Wizard

Posted by Venkatakrishnan J on November 20, 2007

One of the users of BI EE had sent me an email yesterday asking about the various options for improving performance of reports in BI EE. There are lots of options available in BI EE to improve the performance of reports in general. One of the widely used ones is the use of Aggregate Tables. So, lets try to understand what are Aggregate tables and how they can be used. As the name suggests, Aggregate tables are Physical tables that store Aggregates of measures across multiple levels of a Hierarchy. So, for example, say if you have the following hierarchy

      

and say you are analyzing Sales Measure across this hierarchy, you can store the aggregated values of this measure across all the above levels so that the BI Server can use this aggregate table instead of querying against the transactional database. Now, lets see how to go about creating Aggregates. There are 2 ways of doing this. One way is to manually create an Aggregate table and then associate that table with the dimension levels. The other way (recommended one) is to use the Aggregate Persistence Wizard available within BI EE. So, if you go to the Admin Tool -> Utilties you would find a utility called as the Aggregate Persistence Wizard.

      

The first step in using this wizard is to specify an output file name for the resultant script. This script is what we will execute later to create the Aggregate tables and their corresponding mapping to the various levels of the hierarchy

      

The next step in creating the Aggregate script is to specify the measures for which you would like to create the aggregate tables. Remember, you can only use those measures that have some predefined aggregation set.

      

Once that is done, choose the levels across the dimensions which you would like to associate with the aggregation. In our case we shall choose the lowest country level.

      

Once the levels are chosen, choose the names and the connection pool for the target aggregate table.

      

Then complete the wizard. This would create the script in the output directory you specified.

      

If you open the script you would see some UDML based commands that can be recognized only by the BI Server. So, lets see how we can go about executing them. Go to command prompt and navigate to {OracleBI}/Server/Bin and there you would find an executable called as nqcmd.exe. This is what we will use to execute the above script. Use the command below to execute this

nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s “D:\AggregateCountry.sql” -o “D:\Outputfile1.txt”

      

Once this script is executed open the Admin tool and the repository. If you navigate to the connection pool you would find the aggregate tables created.

      

Also, if you double click on any of the levels above the Country level (say SubRegion_Name), you would find that the logical mapping has been automatically created by the script.

      

Before proceeding further lets just go back to the database and see how the data of the aggregate table looks like. Remember, there will be 2 tables. One is for storing the level hierarchy and the other is for storing the aggregates.

      

And, at last lets create a simple report in BI Answers containing the Region, SubRegion and Country Columns. After that look at the query that is fired in the backend. You would notice that the aggregate table will be used instead of the actual transaction table.

      

      

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

Oracle BI EE – Catalog Reports

Posted by Venkatakrishnan J on November 19, 2007

One of the very good features of the catalog manager is its ability to export Catalog related reports. For example, if you would like to have a detailed report containing the frequently accessed reports, last accessed time etc of the reports within a folder you have in the catalog, Catalog Reports are the way to go. This option is available for all the folders that you have in your catalog. So, if you log into Catalog Manager and right click any of the folders that you have in your catalog you would get the “Create Report” option.

      

So, if you click on Create Report, you would get multiple options.

      

So, the list of columns that you see on the left are the columns that are available for the report. For example, one can create a report containing the Created Time, Last Accessed Time, Owner, Request Folder, Request Name. So, what this would basically show is the list of the reports that have been accessed in each folder and their corresponding created times. Also, one has an option to save these reports in a file. Lets create a simple report containing the above said columns and see how the report actually looks like

      

There can be multiple types of reports. They are

1.    Requests
2.    Dashboards
3.    Segments
4.    Segment Trees
5.    List of Formats

      

The first 2 are of importance since they provide catalog related reports for the individual reports and dashboards respectively. I think the remaining 3 formats are related to Siebel CRM but not sure. One other good option is to export these reports into flat files and load them into the database so that you can use this in your dashboard reports. You can use APEX to load these reports into a database table. But the main issue that i found is that if you include columns like RequestXML/ Dashboard Page XML then it becomes almost impossible to load these reports into the database(because of these XML fields). So, while creating the reports (if you do not have any custom load scripts), just exclude the RequestXML column. Also, i have included the following external table script below. You can just create the external table and use that to report in BI EE.

      CREATE OR REPLACE DIRECTORY CATALOGREPORT AS ‘D:\VENKAT\ET’

   CREATE TABLE CATALOGREPORT_ET (
   CREATED_TIME VARCHAR(100),
   LAST_ACCESSED_TIME VARCHAR(100),
   LAST_MODIFIED_TIME VARCHAR(100),
   OWNER_REPORT VARCHAR(20),
   REQUEST_CRITERIA_COLUMN VARCHAR(40),
   REQUEST_CRITERIA_FORMULA VARCHAR(40),
   REQUEST_CRITERIA_TABLE VARCHAR(40),
   REQUEST_DESC VARCHAR(2000),
   REQUEST_FOLDER VARCHAR(200),
   REQUEST_NAME VARCHAR(300),
   REQUEST_PATH VARCHAR(100),
   REQUEST_SQL VARCHAR(400),
   REQUEST_SA VARCHAR(40))
   ORGANIZATION EXTERNAL
   (TYPE oracle_loader
   DEFAULT DIRECTORY CATALOGREPORT
   ACCESS PARAMETERS (
   RECORDS DELIMITED BY NEWLINE
   FIELDS TERMINATED BY 0X’09’
   MISSING FIELD VALUES ARE NULL
   (CREATED_TIME CHAR(100),
   LAST_ACCESSED_TIME CHAR(100),
   LAST_MODIFIED_TIME CHAR(100),
   OWNER_REPORT CHAR(20),
   REQUEST_CRITERIA_COLUMN CHAR(40),
   REQUEST_CRITERIA_FORMULA CHAR(40),
   REQUEST_CRITERIA_TABLE CHAR(40),
   REQUEST_DESC CHAR(2000),
   REQUEST_FOLDER CHAR(200),
   REQUEST_NAME CHAR(300),
   REQUEST_PATH CHAR(100),
   REQUEST_SQL CHAR(400),
   REQUEST_SA CHAR(40)))
   LOCATION (‘Test.Txt’))
   PARALLEL
   REJECT LIMIT UNLIMITED

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