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.

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.

WORKING THROUGH RPD PATCH FILES

Most of us work in RPD through Admin Tool only – Changes, BMM layer changes, Creation of Variables, Updating variables, etc and what not.
However, there is one thing very interesting which can expedite making small incremental changes in RPD and that is – Patching.

A Patch file is xml file generated from RPD. This xml file can be either of whole Repository or can be a small file of differences between two RPDs.
The second option entice me very much as I think this can be used in multiple scenarios.

Let me illustrate how to work on Patch files by giving one example. There can be multiple applications of this but that I would leave to my readers as food for though for now.

Imagine you have a RPD (BiCoach.RPD) that has variables which define the connection pool details.

Here we have created three static Repository variables that are used in connection pool physical layer.

Now the value of connection pools change very frequently as per your OBIEE Environment. i.e Different database name, userid, password for integration environment or production environment or so on. We are taking here example of small number of variables but this list can be huge depending on your RPD.

Lets create a quick small patch file that has only details of these variables.
1. Copy the same RPD and rename it as BiCoach_Dev.rpd
2. Modify the values of those 3 variables.

3. Compare both the RPDs.
4. Create the Patch as mentioned. Save the file as ‘RPDDifference.xml’

5. This is how the created Patch file looks

6. You might notice that for each element, there is specific id and uid is generated. This remains constant. However, for our exercise, we can delete id and uid for each of the variables but lets retain this.
7. Now, I am making change in this xml file and updating values as per my Integration Environment.

8. Next Lets apply this patch in our original RPD – BiCoach.RPD.

9. Click Next. New RPD ‘BiCoach(1).RPD’ is created. Lets see our updated variables.

This really offer multiple avenue for its application: be it for Support team or Others. I will let this for readers to explore.

Please continue to drop your emails/ suggestions. Till next time,
Happy Exploring!

REFRESH GUIDS

In Oracle Business Intelligence 11g Release 1 (11.1.1), users are recognized by their global unique identifiers (GUIDs), not by their names. GUIDs are identifiers that are completely unique for a given user. Using GUIDs to identify users provides a higher level of security because it ensures that data and metadata is uniquely secured for a specific user, independent of the user name.

Wherever possible, secure access to data and metadata using application roles rather than individual users.

Refreshing GUIDs/ GUID Synchronization/ GUID regeneration

GUID refresh updates any metadata references to user GUIDs in the Oracle BI repository and Oracle BI Presentation Catalog. During the GUID refresh process, each user name is looked up in the identity store. Then, all metadata references to the GUID associated with that user name are replaced with the GUID in the identity store.

When?? If Oracle best practices are not observed and Oracle Business Intelligence repository data is migrated between systems that have different GUIDs for the same users, GUID refresh is required for the system to function. This is not a recommended practice, because it raises the risk that data and metadata secured to one user (for example, John Smith, who left the company two weeks ago) becomes accessible to another user (for example, John Smith, who joined last week). Using application roles wherever possible and using GUIDs consistently across the full development production lifecycle prevents this problem from occurring.

Refreshing User GUIDs

To refresh user GUIDs, perform the following steps on APPHOST1 and APPHOST2. Note that GUID refresh must occur with only one node operating at a time.

1.    Stop Oracle BI Server and Presentation Services on all nodes except where you are refreshing the user GUIDs. For example:
cd ORACLE_BASE/admin/instancen/bin
./opmnctl stopproc ias-component=coreapplication_obips1
./opmnctl stopproc ias-component=coreapplication_obis1

2.    Update the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter in NQSConfig.INI:
Open NQSConfig.INI for editing at:
ORACLE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn
3.    Locate the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter and set it to YES, as follows:

4.    FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES;

5.    Save and close the file.

6.    Update the Catalog element in the instanceconfig.xml file:
Open instanceconfig.xml for editing at:
ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obipsn
Locate the Catalog element and update it as follows:

<Catalog>
<UpgradeAndExit>false</UpgradeAndExit>
<UpdateAccountGUIDs>UpdateAndExit</UpdateAccountGUIDs>
</Catalog>
Save and close the file.

7.    On the node where you are refreshing the GUIDs, start the Oracle BI Server and Presentation Services using opmnctl:
cd ORACLE_BASE/admin/instancen/bin
./opmnctl startproc ias-component=coreapplication_obis1

8.     After you confirm that the Oracle BI Server is running, then start Presentation Services:
./opmnctl startproc ias-component=coreapplication_obips1

9.    Set the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter in NQSConfig.INI back to NO.
Important: You must perform this step to ensure that your system is secure.

10.    Update the Catalog element in instanceconfig.xml to remove the UpdateAccount GUIDs entry.

11.    Restart the Oracle Business Intelligence system components using opmnctl:
cd ORACLE_BASE/admin/instancen/bin
./opmnctl stopall
./opmnctl startall

CHANGING COMMA SEPARATOR

Let’s explore today something related to Locale settings in OBIEE 11g.

We have thousand separators in OBIEE:

What if this comma(,) is to be replaced with underscore ( _ ) or any other symbol:

Open localedefinitions.xml. Usual path (\Middleware\Oracle_BI1\bifoundation\web\display)

Locate the below tag

<property name=”thousandsSeparator”>,</property>

Replace comma with underscore

Save the localedefinitions.xml and restart the services.

Run the same analysis.

We are now having _ as separator.

Continue Exploring !

GROUPING VALUES IN PROMPT

Hi Friends,

Recently, we got a requirement to have grouping in the Dashboard prompts. I remember doing that few years back but thought to blog it as not many details are available. This is pretty easy stuff and should not take long for you to incorporate in your BI kitty.

Let’s say we have a Column ‘Day Name’. We would like to categorize our weekend (Saturday and Sunday) as Holiday so that if user selects ‘Holiday’, prompt will pass both Saturday and Sunday to the Report.

Now since we don’t have ‘Holiday’ included in our database, we are going to create a Group to achieve the same.

• Create an analysis on that subject area and go to the Results tab. Click the icon with 3 blue dots and a star in the menu bar.

• Create your group by choosing a Display Label (“Holiday“) and selecting the column and the value you want to put inside (Saturday and Sunday)

• Expand the Selection Steps pane at the bottom of the screen and click on your group (Holiday) –>Save Group As… and save it under the subject area content folder.

• In your Dashboard prompt add the same column, expend the Options and for Choice List Values select Specific Column Values, then click the Plus sign.

• Add all the possible column values (all 7 days of the week in our case) from Column Values pane and add our group (Holiday) from Catalog pane.

Bi Coach Remarks: The usage of Groups in Prompts is only limited “Specific Custom Value” option only. Thus, the demerit of this option or the best place where Groups should be used is where the prompt values are supposed to remain static over the period of time. Else, all those changing values would have to be included in Selection. (Not the option that I would recommend)

We are done. You can see all the days of the week and our created group in the prompt

Till next time, Happy Exploring!

HIDE COLUMN ONLY FROM ADHOC REPORTING/ ANSWERS

Hi,

Recently, we got a requirement from a client that they want to have certain columns which should be present in the Dashboard Reports but the same should not be available for Adhoc Reporting in Answers. This seems to be a little twisted task as any column which is hidden from Answers is automatically excluded from the Report.

Let me first explain you the wrong approach (for your better understanding J ) and then come up with the ingenious (no rocket science) workaround.

We have a Report ‘BiCoach_HideTest’. Here we have 4 columns: Year, Quarter Name, Month Name and Date. As you can see, all these 4 columns are present in left section for Adhoc Reporting.

Now as per the requirement, let’s try to hide ‘Quarter Name’ from Adhoc Reporting. However, the same should still appear in our report ‘BiCoach_HideTest’.

Making simple change in RPD to hide the column by putting condition 1=1

Post this change, when we see our saved Report:

So, this was the wrong solution. Now, lets see the correct workaround.

1. Create Presentation Folder ‘ParentHide’

2. Create Another Presentation Folder ‘Child’

3. Move the Column ‘Quarter Name’ from ‘Time’ folder to ‘Child’

4. Make the newly created ‘Child’ folder as a Child for ‘ParentHide’ folder:

Right Click on ParentHideèSelect Child Presentation Tables è Then select ‘Child’ Folder

5. Reload the metadata.
6. Go to the saved Report ‘BiCoach_HideTest’ and update the column formula for Quarter Name.

BiCoachTip: It is important that we first include the newly created column in the analysis first and then Hide the Presentation folder (as mentioned in next step). If we hide the column first, OBIEE will throw error.

7. Finally, hide the ‘ParentHide’ folder itself.

8. Again, Reload the Metadata.
9. Finally, lets test the Output.

Quarter Name is now not present in Answers but it is present in Report. Our newly created columns ‘Parent Hide’ and ‘Child’ are also not present.

Till Next time. Happy Exploring!