OBIEE 11g: Change Column Formatting default options for all columns in a subject area

OBIEE has multiple column formatting properties. Each property has multiple options. Hence it is quite understandable that it comes with pre-selected default option.

In most cases these default values serves us well and we are mostly happy with it. Most of the time we only change them at report level for few columns.

But there are situations when users of particular subject area wants all columns in that subject area to have a formatting option different than the default.
For eq. All columns should Repeat values rather than suppress.

There are couple of ways in which this can be done.

1. Change the default values in the XML files.

 This is the simplest of all options. But this will change it across board for all subject areas in OBIEE. This might not be desirable in all cases.

2. Manually change the value for each column in the subject area and save as system wide default option.

This option will make sure change is only for one subject area. But imagine doing this manually for 100's of columns. Its very time taking and is prone to mistakes.

Today I will walk you through simple steps you can use to automate this option. And once you have the baseline build for one subject area you can do it for any subject area with any no. of columns in almost no time.

1. Build a simple report with 1 column.
2. Go to column formatting for the column.
3. Change from suppress to repeat and save as system wide column format.
4. Open catalog manager and access online catalog.
5. Go to catalog root/system folders/metadata. How to get access to metadata folder?
6. You should see a new entry for the subject area used to create report in step 1.
7. Open the file, go to Edit XML and copy the XML content.

If you now carefully observe the XML content, there are 3 sections:

• Header - Appears only 1 time at top for each subject area.  
<?xml version="1.0" encoding="UTF-8"?>
<sawsavedformat:metadata xmlns:sawsavedformat="com.siebel.analytics.web/savedformat/v1.1">
  <sawsavedformat:columnSavedFormats>
• Column Format - Appears 1 time for each column.
<sawsavedformat:columnSavedFormat xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="sawsavedformat:regularColumnSavedFormat" columnName="&quot;Subject Area&quot;.&quot;Presentation Table&quot;.&quot;Presentation Column&quot;">
      <saw:displayFormat>
        <saw:formatSpec suppress="repeat"/>
      </saw:displayFormat>
      <saw:tableHeading>
        <saw:displayFormat />
      </saw:tableHeading>
      <saw:columnHeading>
        <saw:displayFormat>
          <saw:formatSpec />
        </saw:displayFormat>
      </saw:columnHeading>
    </sawsavedformat:columnSavedFormat>
• Footer - Appears only 1 time at the bottom for each subject area.
  </sawsavedformat:columnSavedFormats>
</sawsavedformat:metadata>

This is where we can use the power of excel and create XML content for all columns in a subject area automatically.
Before implementing below steps please take a backup of webcatalog. Also please be aware of impacts to any existing reports upon changing the default formats.

1. Generate an excel report of all metadata objects using RPD tool utility (Tools->Utilities->Repository Documentation).
2. We only need Subject Area, Presentation Table and Presentation Column. Copy these columns for any single subject area and paste in a new excel sheet.
3. Create 3 new column in the worksheet. Call them "String1", "String2" and String3". Now we have total 6 columns in the excel sheet.
4. Insert a new row at top and paste Header section content in "String1". All other columns will be blank. 
<?xml version="1.0" encoding="UTF-8"?>
<sawsavedformat:metadata xmlns:sawsavedformat="com.siebel.analytics.web/savedformat/v1.1">
  <sawsavedformat:columnSavedFormats>
5. Insert a new row at bottom and paste Footer section content in "String1". All other columns will be blank.
 </sawsavedformat:columnSavedFormats>
</sawsavedformat:metadata>
6. For the 1st row with non blank subject area value, we will divide Column Format section content in String1, String2 and String3. This is done to easily concatenate all strings together in excel. If you have any other preferred way to concatenate without breaking them down, feel free to do so.

String1 - Constant

<sawsavedformat:columnSavedFormat xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="sawsavedformat:regularColumnSavedFormat" columnName=" 

String2 - Concat Subject Area, Presentation Table and Presentation Column.

&quot;Subject Area&quot;.&quot;Presentation Table&quot;.&quot;Presentation Column&quot;

String3 - Constant Column Format options. You can add any no. of column format options in this column. Keep only those options which you want to change.

">
      <saw:displayFormat>
        <saw:formatSpec suppress="repeat"/>
      </saw:displayFormat>
      <saw:tableHeading>
        <saw:displayFormat />
      </saw:tableHeading>
      <saw:columnHeading>
        <saw:displayFormat>
          <saw:formatSpec />
        </saw:displayFormat>
      </saw:columnHeading>
    </sawsavedformat:columnSavedFormat>  

      7.  We can now concatenate "String1", "String2" and String3" to create a new column                            "String123".
      8.  Do this for all columns in excel sheet (copy and paste). 

      9.  Copy all values in column "String123" and paste in the Catalog Manager (catalog root/system folders/metadata) subject area file.

That's it. If you go back to the answers now and try to create a report you will see that all columns have the default values defined above.

And once you have this template of excel ready all you need to do is replace with a different subject area content. And copy paste to webcatalog using catalog manager.

Thanks,

OBIEE 11g: Access to User Folders from Answers

OBIEE 11g provides access to a folder for each user (controlled by Privileges) where they can store their private objects (Reports, Dashboard etc.). The objects they do not want to share with other users. These folders are named as the users log-in name.

For eq: if user log-in name is "weblogic", the folder will be:

/Catalog Root/User Folders/weblogic

By default these folders are only accessible by the user and not even Admins can access it (for read or write).

But there are lot of situations where an Admin may need to look at the objects in User Folders. Such as when a user is no longer with the organization.

Here are the steps to get access to an individual user folder from Answers:

1. Log-in to  Answers using Admin account.
2. Go to Catalog.
3. On the top left hand corner switch the view from "User View" to "Admin View".
4. Go to User Folders and select folder you need access to. It should pop up an error message: "access denied for user to path /users/....."
5. From the Tasks pane located at bottom left side, select Permissions. This should pop up same error message. "access denied for user to path /users/....."

Now this is where the below steps are useful where you can access the Permissions option.

1. From the Tasks pane located at bottom left side, select Properties.
2. In the Properties window, Ownership section (at bottom), select "Set Ownership of this item and all subitems". This will make the Admin user owner of the folder. You will again get the same error as above but ignore that and move to the next step.
3. From the Tasks pane located at bottom left side, select Permissions. You should now be able to get to the Permissions window.
4. Add the Admin User or Role with appropriate permissions, check both the boxes as shown below and click OK.                                                                      
5. Try to access the User Folder once again. Hopefully you are in.

If you now want you can make the original user as the owner of the objects. Follow these steps:

1. From the Tasks pane located at bottom left side, select Permissions.
2. For the user Account, select the Owner Radio button, click both check boxes and hit OK.

Thanks,

OBIEE 11g: Access to Webcat System Folders using Catalog Manager

We may want to access OBIEE 11g Webcat System Folders for many reasons.
For example we may access metadata folders to manage default formatting for reports.

For rest of this article I will demo the steps on metadata folder using Catalog Manager Tool.

By default when we try to access this folder we get a "No Access" error.

If we try to assign Permissions to metadata folder, we find that the options are all grayed out.

Please remember to take a backup of the webcat before implementing the steps below.

Here are the steps to enable access to the folder.

1. Open webcatalog using Catalog Manager in Online mode.
2. Go to Catalog Root->System Folders->metadata.
3. Right click on metadata folder and select Properties.
4. Change the owner filed from "System Account" to "weblogic" (or the account you used to log in to catalog manager), check Apply Recursively and click OK.
5. Right click on metadata folder again and select Permissions.
6. Assign Application Role "BI Administrator Role"  Full Control, check Apply Recursively and click OK.
7. Try to open the metadata folder again. Hopefully you are in.

If you now prefer you can change the Owner back to "System Account" using the steps 1 and 2 above.

Thanks,