SUPPRESS VALUES IN REPORT BUT REPEAT VALUES IN EXCEL EXPORT

1. Open the instanceconfig.xml file located in

ORACLE_INSTANCE\config\OracleBIPresentationServicesComponent\coreapplication_obipsn

2. Enter the following namespace declaration in the WebConfig element:

Note that the Export element includes the required attribute xsi:type, which specifies the type of export. Valid values are:

• excel (for export to Excel)
• formattedText (for data export)
• pdf (for export to PDF

3. Locate the Download section in which you must add the elements that are described in the table below.Include the elements and their ancestor elements as appropriate, as shown in the following example:

<ServerInstance>
  <Download>
    <Export xsi:type=”excel”>
        <RepeatRows>false</RepeatRows>
    </Export>

    <Export xsi:type=”formattedText”>
        <Delimiter char=”,”/>
    </Export>

    <Export xsi:type=”pdf”>
        <KeepRowsTogether>true</KeepRowsTogether>
    </Export>

  </Download>
</ServerInstance>

4. Save your changes and close the file.

5. Restart Oracle Business Intelligence.

Elements for Manually Configuring for Export:

Element	Description	Default Value
RepeatRows	Specifies whether cells that span rows and cells that span columns are to be repeated when users export tables and pivot tables to Microsoft Excel.Ifset to true, then cells that span rows and cells that span columns are repeated, regardless of theValue Suppression setting in the Analysis editor. For example, in a table that has Year and Month values, Year is repeated for all Month values.If set to false, then the behavior is the same as that defined by the Value Suppression option in the Analysis editor: – If Value Suppression is set to Suppress, then cells that span rows and cells that span columns are not repeated. For example, in a table that has Year and Month values, Year is displayed only once for Month values. – If Value Suppression is set to Repeat, then cells that span rows and cells that span columns are repeated. For example, in a table that has Year and Month values, Year is repeated for all Month values. For more information on the Value Suppression option, refer to this document: Value Suppression. The export type is: xsi:type=”excel”	false
Delimiter	Specifies the column delimiter character for theCSV Format option, for example, a semicolon (;), when exporting raw data from results andviews.The export type is: xsi:type=”formattedText”	“,”
KeepRowsTogether	Specifies whether rows are to be kept together at page breaks when exporting to PDF.If set to true, rows are kept together at page breaks.If set to false, rows are split across page breaks. The export type is: xsi:type=”pdf”	false

SUBTOTALS FOR SOME METRICS AND NOT FOR OTHERS

Hi All,

This will be a quick post. Yesterday, I stumbled upon an issue where I was requested to show subtotal for some metrics in the table where as not to show subtotal for other Metrics.

Thing to highlight was that all of the metrics were aggregated by default in RPD.

Now one might be tempted to:

* Go to the results tab, then edit the table view.
* In the Columns and Measures section, choose if you want the totals to display after or before.
* Click on the mouse icon on the metrics, where you want to display the totals for, and choose aggregation rule as sum, and none for the metrics that you dont want to display the totals for.

Strangely, this might work in some case, while in some cases it won’t. You might see NULL in those columns where you didn’t want subtotals

The trick lies in going to Criteria Tab once again, go to the metric you don’t want the total on, and then select

Aggregation Rule (Totals Row) as None

Run the report once again. You will see few metrics showing totals whereas few metrics not showing subtotals, as intended.

Another thing to note is that this would not have been the issue in Pivot table at all. But I wanted to refrain from using the same lest TMP tables would have been created as my dataset was large.

IMPROVING SPECIFIC REPORT PERFORMANCE

OBIEE 11g analysis has query performance option to “Share query with multiple users”

BiCoach Tip: This option is more to do with BI Presentation Server Cache. This has no impact on settings saved for BI Server Query Cache.

To enable this option,

* Analysis’ Advanced tab

* In the bottom of the page select “Share query with multiple users (may improve performance after initial run)

* Click “Apply” and save your analysis.

VERTICAL CLUSTERING IN OBIEE

Vertical scaling in OBIEE means adding more Business Intelligence Components to the same computer to make use of the hardware resources. There are cases when the middleware hardware is sized with many CPUs and more Memory, so in order to make use of them then we have to do vertical scaling.

Where Vertical Clustering is to do more with one BI components in one server, horizontal clustering deals more with configuring multiple instances of BI server in different boxes. This is useful not only for load sharing, but also at times when one server is down. There are more to the world of Horizontal Clustering. But I will cover this in my later post.

Coming back to Vertical Clustering…

Though there were some workaround available in 10g, Vertical Clustering has been introduced in 11g.

Unlike Horizontal, this does not provide high availability when the server itself goes down. In this type of clustering generally 3 components are clustered

1. BI Server
2. BI Presentation Services
3. Java Host

That is, all the components that can act in active-active configuration can be clustered in Vertical Clustering. Other components like Scheduler, Cluster Controller which work in active-passive fashion are generally not clustered vertically.

* Log Into EM

* Navigate to the deployment section. (Since we are doing vertical clustering, we do not need to have a shared drive for the repository & the web catalogs. But again its better to identify a drive that can potentially be shared in the future with other machines as Vertical Clustering does not provide high availability (for future migration to horizontal clustering)).

* Once the drive is identified (one for RPD and the other for Web Catalog), copy the web catalog to the shared drive. In the deployment section of the Enterprise Manager, enter the shared directory details of both the RPD and the Web Catalog.

* Upload the repository (RPD) into the shared drive using the enterprise manager.

* Access the Fusion Middleware Control 11g.

* Select core application under Business Intelligence

* Select Capacity Management.

* Go to Scalability tab and click Lock and Edit Configuration. Now the Enterprise Manager will allow editing the values.

This will automatically create new instances(within the main instance) in BI EE for both the BI Server and Presentation Server(after Activating the changes). We can validate this by looking at the number of directories under

{ORACLEINSTANCE}/bifoundation/OracleBIServerComponent and

{ORACLEINSTANCE}/bifoundation/OracleBIPresentationServerComponent

Since we selected two, 2 new directories will be created for the new BI Server and Presentation Server that we added through the EM. Lets now start these new components through the capacity management interface

Once the scaling out is completed, opmn will be aware of the newly created components.

Till Next Time!

AUTOMATE GENERATION OF REPOSITORY METADATA

Sometime back, I have posted on creating Catalog Metadata (GUI) and Repository Metadata (again GUI)

What if we have a way to automate this metadata. We code some lines, schedule it – simple!

The trick lies in using the /command switch of the AdminTool to run a script of commands.
Create a command script: BiCoach_RPDMetadata.build and put in the following few lines:

Replace MWHOME with local OBIEE installed path, BiCoach_repository with your own and the repository password with your own.
—-
OpenOffline

<MWHOME>\instances\instance1\bifoundation\OracleBIServerComponennt\coreapplication_obis1\repository\BiCoach_repository.rpd <repository password>

DescribeRepository D:\scriptoutput\BiCoachrepository_report.csv UTF-8

Close

Exit
—-
Now since we have to automate, lets create a script to run the AdminTool and pass in the script file created above.
—-
@echo off

set ORACLE_BI_APPLICATION=coreapplication

set JAVA_HOME=<MWHOME>\Oracle_BI1\jdk

set ORACLE_HOME=<MWHOME>\Oracle_BI

set ORACLE_INSTANCE=<MWHOME>\instances\instance1

call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.cmd

set PATH=%ORACLE_HOME%\common\ODBC\Merant\5.3\Drivers;%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ORACLE_HOME%\bin;%PATH%

<MWHOME>\Oracle_BI1\bifoundation\server\bin\AdminTool.exe /command d:\scripts\BiCoach_RPDMetadata.build
—-
Generated CSV file is what we are looking for. Go ahead and schedule this script in Windows Explorer.

AUTOMATE MIGRATION OF CATALOG IN LINUX

Lets see today how to automate webcatalog migration. I found this more useful in taking daily/ weekly/ monthly back up of catalog

Approach:
1. Write the script to Zip the catalog and move it to different folder or server
2. Schedule this script to run on daily/ weekly/ monthly basis as per requirement.

1. Archiving the catalog

Use the following Script:

tar -zcvf $(date ‘+%d_%m_%y_%H_%M’)_DEVCATALOGBkUp.tar.bz2{{Destination}}>Output_report.txt
echo “Done”

Here

{{Destination}} – represents the full path of folder where archive is to be created
z – This option tells tar to read or write archives through gzip, allowing tar to directly operate on several kinds of compressed archives transparently.
c – Create a new archive
v – Operate verbosely
f – Use archive file
(date ‘+%d_%m_%y_%H_%M’) – this will append the current date before the name of folder.

Save the file as Catalog_Backup.sh

2. Scheduling the Shell script.
Use the cron command to schedule the Catalog_Backup.sh as per intended backup frequency.

crontab [-u user] [-l | -r | -e] [-i]

-u Append the name of the user whose crontab is to be tweaked. If this option is not given, crontab examines “your” crontab, i.e., the crontab of the person executing the command.
-l Display the current crontab.
-r Remove the current crontab.
-e Edit the current crontab, using the editor specified in the VISUAL or EDITOR environment variables.
–i Same as -r, but gives the user a “Y/n” prompt before actually removing the crontab.

OBIEE METADATA LINEAGE

I will talk about a generic topic today (more from Application Maintenance Perspective). Lets say we have created our RPD and various reports.
How we will track if we have to:
1) Change the source/ formula of a column which is coming in the report.
2) We have made the change in column formula in BMM Layer in the Report. How to identify the impacted Reports
3) There is a physical column in the RPD which has been deleted now. Will this be impacting any reports.
4) Who are the owners or who have the permissions to check any particular report, etc

For questions like above and many more, application owners would like to document a sort of tracker which has details of all of above. We call this metadata lineage or data lineage.

From OBIEE perspective, we keep this lineage at two levels:
1) Catalog Level
2) RPD Level

Catalog Level

To extract all information related to report, its columns, owners, permissions, path, etc. we can use a feature available through catalog manager.
To generate this report, open catalog manager and follow the below steps:

1. Locate the folder in catalog manager whose information is required. To generate this info for entire catalog, one can share the root folder itself.
2. Select Tools > Create Report.

3. In the Create Catalog Report dialog, select the columns that you want to include in the report

4. Click OK
5. Lets save this csv file as CatalogDetails.csv

Repository Level:

All repository objects information can be generated with Repository documentation utility available within OBIEE Administrator tool.
How to use this utility:
1. Open the Oracle BI Administration tool and open the required RPD
2. In the OBIEE Administration tool, select Tools > Utilities.
3. In the Utilities dialog, select the Repository Documentation utility and click Execute

4 Lets save this csv file as RPDDetails.csv

Now a user/ developer/ owner can check these two files back and forth to verify if anything can break due to new changes. I leave to my readers if they want to import back these two files into RPD and play around by creating reports based on our imported metadata lineage.

Till Next Time,
Happy Exploring.

DEFINING LIMITS FOR THE TABLE OR PIVOT FROM INSTANCECONFIG OR EM

It is rare that our OBIEE reports fetch less number of rows. In most of the cases, built reports fetch huge data set. Add to this scenario where Adhoc Reporting users fire their own queries that fetch thousands of rows.

What we should do?

We apply either Query Limit in RPD or make changes in em or make changes inInstanceConfig.xml files to limit number of rows downloaded or exported or shown by default. Let me explain the second and third option in this blog. :)

THROUGH EM
To Customize number of rows that can be exported,

EM => Business Intelligence => coreapplication => CapacityManagement => Performance => Lock and Edit =>Maximum Number of Rows to Download

Please note that Reducing the maximum number of rows that can be downloaded can improve performance where exports are common

THROUGH INSTANCECONFIG.XML

1. Navigate to
<Middleware>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\instanceconfig.xml
and take Backup Instanceconfig.xml file

2. Change instanceconfig.xml file as follows

3. Locate the below tags (if present) else add the ones (as per applicability) between <Table> and </Table> or between <Pivot> and </Pivot>

<DefaultRowsDisplayed>60000</DefaultRowsDisplayed>
<MaxVisibleColumns>4000</MaxVisibleColumns>
<MaxVisiblePages>2000</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>
<MaxCells>5000000</MaxCells>
<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>

4. Restart Presentation Server from EM. Our changes are ready to be tested.

Some common used tags

MaxCells:
Specifies the maximum number of cells to be displayed in a view. This number should not exceed the product of MaxVisibleColumns times MaxVisibleRows
BiCoach Tip:
If MAX Cells is not set, then following error

View Display Error
Maximum total number of cells exceeded (Configured Limit: 50000).
*Error Details*
*Error Codes: EY692ZW9*

MaxRecords
Specifies the maximum number of records that can be included in the view.

MaxVisibleColumns
Specifies the maximum number of columns to be displayed in a view.

MaxVisibleRows
Specifies the maximum number of rows to be displayed in a view. The value of DefaultRowsDisplayed should not exceed this value.

MaxVisiblePages
Specifies the maximum number of view prompts (or pages in PDF) to be displayed in a view.

MaxVisibleSections
Specifies the maximum number of sections to be displayed in a view. This element does not apply when a slider is in place for a graph.

Till Next post, Happy Exploring :)

Keep posting your comments and your queries.