Windows ETL Server: Repository Backup Automation

This blog article covers automating the backup of DAC and Informatica repositories in a Windows ETL Server environment. The batch files which perform the backup tasks have been tested with DAC 10.1.3.4.1 and Informatica 8.6 on a Windows Server 2003 environment.

The batch files utilize Date/Time Windows environment variables which might not be available in earlier versions of Windows, such as NT/2000. The batch files need to reside on the ETL server, and can be scheduled using the default Windows task scheduler located under Programs > Accessories > Scheduled Tasks.

The Informatica batch file connects via command line to Informatica and then executes a backup of the repository.

Informatica Syntax:
pmrep connect -r Repository_name -d Domain_name -n user -x passwd
pmrep backup -o output_file_name (**by default outputs to the infa_shared/Backup folder).

The DAC batch file utilizes the AutomationUtils.bat file located in the DAC folder, and uses the EXPORT command. You can specify specific Source Containers by adding them to the end of the command-line, as shown in the DAC documentation.

DAC Syntax:
EXPORT 'folderName' 'contName1' 'contName2' ...

Below are simple batch files that 1) Create a new folder with the current date/time in a specified location and 2) export INFA/DAC repositories and place them in these folders.


INFORMATICA REPOSITORY BACKUP SAMPLE SCRIPT:
::*****************************************************************************
:: Set variables
::*****************************************************************************

set /a dt_tm_stamp=0
set hour=%time:~0,2%
if %hour% LSS 10 set hour=0%hour:~-1%
set dt_tm_stamp=%date:~-4%%date:~-10,2%%date:~-7,2%%hour%%time:~3,2%

::*****************************************************************************
:: Navigate to backup location, and create new folder with todays datetime stamp.
::*****************************************************************************

CD C:\ETL_BACKUPS\
md INFA_Repository_%dt_tm_stamp%

::*****************************************************************************
:: Connect to Informatica and backup repository
::*****************************************************************************

pmrep connect -r BICG_BI_DW -d Domain_brian-9574f410e -n Administrator -x Administrator
pmrep backup -o BICG_BI_DW_%dt_tm_stamp%.rep

::*****************************************************************************
:: Move Informatica repository into directory previously created with todays date.
::*****************************************************************************

cd C:\Informatica\PowerCenter8.6.0\server\infa_shared\Backup
MOVE C:\Informatica\PowerCenter8.6.0\server\infa_shared\Backup\BICG_BI_DW_%dt_tm_stamp%.rep C:\ETL_BACKUPS\INFA_Repository_%dt_tm_stamp%\

::*****************************************************************************
:: Clear out variables
::*****************************************************************************

set dt_tm_stamp=
:EXIT

DAC REPOSITORY BACKUP SAMPLE SCRIPT:
::*****************************************************************************
:: Set variables
::*****************************************************************************

set /a dt_tm_stamp=0
set hour=%time:~0,2%
if %hour% LSS 10 set hour=0%hour:~-1%
set dt_tm_stamp=%date:~-4%%date:~-10,2%%date:~-7,2%%hour%%time:~3,2%


::*****************************************************************************
:: Navigate to backup location, and create new folder with todays datetime stamp.
::*****************************************************************************

CD C:\ETL_BACKUPS
md DAC_Repository_%dt_tm_stamp%

::*****************************************************************************
:: Export DAC repository
::*****************************************************************************

CD C:\oracleBI\DAC\bifoundation\DAC\
call AutomationUtils.bat EXPORT C:\ETL_BACKUPS\DAC_Repository_%dt_tm_stamp%

::*****************************************************************************
:: Clear out variables
::*****************************************************************************

set dt_tm_stamp=
:EXIT

-----------------------------------------------------

This is the most basic resolution and one should incorporate a backed-up shared drive location for the repositories to be stored on, as well as a way to trim the repositories after a specified amount of time as these files can be several hundred MB each.

Total Columns

Report totals are pretty common, so it makes sense that OBIEE offers many options for totaling. If your report calls for total or sub-total rows, those are best left to the "Grand Total" and "Total By" buttons in Answers. However, if your report calls for Total Columns, those typically need to be created in the repository as opposed to creating them in Answers (on a report by report basis).

There are two methods I use for creating Total Columns in the Repository, depending on whether I want the Total Column to reflect only the filtered values in a report, or if I want the Total Column to aggregate across an entire dimension, indepedent of the values in the report.

Here's an example of each method:

In the following dataset, the Total US column represents one of the top levels in a geographic dimensional hierarchy, whereby Region is the next level down, and all regions are in the US:

Method 1:

To create a Total Column that will aggregate all Dollars across the entire geographic dimension (regardless of the data that is displayed on the report), you would create a logical column in the repository using the dollar column, and you would set the Logical Level for the geographic dimension to the Grand Total Level in the geographic dimensional hierarchy. Here’s the result of your “Grand Total”:

If we filter the report to only display the Central and Eastern Region, the Grand Total column will still calculate the total across all regions: $13,087,529.

Method 2:

To create a Total Column that only aggregates the data that is displayed within this report (thus taking the filters into account), you would create a new report column in Answers, and enter a formula similar to this:

SUM(Paint.”Sales Measures”.Dollars BY Markets."Total US")

Here’s the result of your new “Report Total” column along with the “Grand Total” column, with Region filters applied:



Note the difference between the Report Total (Method 2) and Grand Total (Method 1).

Kaleidoscope 2009 Conference Highlights

The Kaleidoscope 2009 conference was held this week in Monterey, CA (June 21 - 25). Sponsored by ODTUG (Oracle Development Tools Users Group), it offered over 150 technical sessions on all things Oracle. Check out the web site (www.odtugkaleidoscope.com) for session links. This may pique your interest or provide some insight into a new or existing technical area.

Another great resource is the ODTUG web site (www.ODTUG.com) which provides "community, technical resources and support for a wide range of technologists working with the Oracle platforms." I'm told that ODTUG members can download most of the Kaleidoscope presentations from the ODTUG site.

I presented a session titled "OBIEE Answers on Essbase: The Future of Ad-hoc Analysis". My focus was the interaction between Essbase and OBIEE Answers - - where it is now and where it's going. Happy to say that I learned of some companies using OBIEE Answers and Essbase in production settings. Many others are looking at it very closely and anticipating the OBIEE 11g release when this capability will improve dramatically.

One thing is clear - - Oracle has embraced Essbase as its go-forward multidimensional database and is putting a lot of development muscle behind making OBIEE even more Essbase friendly in the 11g release. Send me a note if you have any questions (doug.burke@biconsultinggroup.com). I will make my presentation available online with my next post.

Links:
www.odtugkaleidoscope.com
www.ODTUG.com

OBIEE to Twitter... and Beyond

Have you noticed the extraordinary evolution of Twitter as an entirely unique medium of communication? I have. And I've also been having daydreams (call me an OBIEE geek) about coming up with a simple and brilliant way to create a Twitter feed from a scheduled OBIEE report, using something along the lines of either BI Publisher or iBots. Looking into the Twitter API documentation, I started getting excited by the fact that their API follows the REST protocol which essentially uses HTTP requests and responses for the exchange of data. Wow, I thought, I could use the HTTP Delivery method that is new to OBIEE 10.1.3.4 ! How exciting! (OK fine, I truly am an OBIEE geek.)

So we should be able to try a quick and simple test to send a notification to my Twitter profile upon completion of a scheduled BI Publisher report! Awww, not so fast: BIP's HTTP Delivery option implements the "GET" method -- the REST protocol, and likewise the Twitter API, calls for any data update to be made using the "POST" method. Maybe in future versions of OBIEE we'll be able to configure the HTTP Delivery option, and, who knows, even the parameter/values that are sent. For now, this feature won't work with Twitter.

I wonder what iBots can do for me? Perhaps 10.1.3.4 also added HTTP Delivery to "Destinations"? Argh, no such luck -- I'm sure we'll see something interesting in 11g but until then we're out of luck. But what the heck, let's take a poke at the "Advanced" features... Well isn't that interesting! I can call another iBot, a custom script, a Workflow or -- behold -- a custom Java application! Moreover I can even call one or more of these entities in any combination, both when iBot conditions are satisfied AND when no records are returned by the conditional request. The mind reels! And look at the options I have when calling a Custom Script or Java Program -- in addition to the obvious ability to specify the program itself, but I can also specify whether to pass to this program the results of the conditional report (and what format to use) OR the content itself OR simply nothing at all. Moreover I can send any number of additional custom parameters.

Clearly, with just a little bit of work, one could achieve my original objective by writing a simple Java program that reads the incoming parameters and/or dataset and POSTs the relevant information to the Twitter API.

But the real ramifications are much more profound: By using iBots to invoke Java interfaces into external ERP or CRM systems -- many of which offer a rich set of public Java APIs -- the OBIEE platform can become a powerful and flexible system for an enlightened business to coordinate automated actions in richly nuanced response to the variety of heterogenous data made availble to the user's fingertips by OBIEE. I understand OBIEE 11g is taking active integration between OBIEE and business processes to a whole new level with its Action Framework -- but until then, the Custom Java Program feature gives us plenty of possibilities to chew on.

Now, who's ready to write that Java Twitter interface?

ETL Server Setup: Major Installation Differences between BI Applications versions 7.9.5 and 7.9.6 on a Windows Environment

The most notable difference when setting up an ETL server by installing BI Apps 7.9.6 is the standalone DAC installer. The DAC download is 180MB and can be found on the Oracle website here: http://www.oracle.com/technology/software/products/ias/htdocs/101320bi.html

The BI Apps 7.9.6 file is only 320 MB now as it does not contain the DAC, and the new version of Informatica with this release, 8.6 Hotfix 4 clocks in at 2.1GB. All of these files can be downloaded from the above link.

The following order of operations should be taken when setting up the ETL server:

1. Install BI Apps 7.9.6. This needs to be installed on a machine which already has the Oracle BI infrastructure installed, such as a development environment or the ETL server itself if you wish to have a copy of Oracle BI residing there. Similarly to other versions of the BI Apps, this will generate the DAC and Informatica repositories as well as the necessary files in the dwrep folder needed to complete the ETL Server installation. If BI Apps 7.9.6 is installed on a different server than the targeted ETL server, both the DAC and Informatica repository files as well as the dwrep folder will need to be copied over to the ETL server following installation.
2. Install Informatica 8.6 with Hotfix 4. There are several notable differences with Informatica 8.6 and 8.1.1 installations. Most importantly among them is that the Informatica Repository Service and Integration Service are created after installation through the Admin Console rather than through the installer. This allows for greater flexibility during the install, and can save time if all the necessary database level permissions are not yet setup on your Informatica repository databases.
3. Install Standalone DAC 10.1.3.4.1. The default installation folder is now ORA_HOME\bifoundation\DAC rather than the OracleBI\DAC folder. There are many great benefits to the standalone DAC installer, as it automates many of the setup steps required of prior DAC versions. The DAC 10.1.3.4.1 installer includes its own JDK, so no need to install this ahead of time. Of the many automated setup steps completed by the standalone DAC the most notable are: The DAC Installer copies the pmrep.exe and pmcmd.exe files from the Informatica server to client folder and sets them up for you in the PATH environment variable. The DAC also configures the INFA_DOMAINS_FILE environment variable for you, and sets the DAC_HOME and JAVA_HOME in the DAC config.bat file. When configuring the DAC you will need to specify the Domain Name and Integration Service name now under Setup > Informatica Servers.
   
   This posting of differences is not complete and is a brief summary of my installation experiences. If anyone has more to add please leave them in a comment. Happy installing!

Modeling Average Metrics in Aggregate Tables

OBIEE has some excellent features for incorporating aggregate tables into the metadata modeling, and these tables are usually pretty easy to create and populate in the database using simple SQL scripts. One of the only wrinkles I have ever run into involved the need for average metrics along with aggregate tables. This blog posting explains the issues that were encountered and provides a solution.

SCENARIO

Suppose you have the following Fact Table, which represents patient visits at a hospital:

• VISIT_ID is the Primary Key
• YEAR_KEY is a Foreign Key to the Year Dimension
• PATIENT_ID is a Foreign Key to the Patient Dimension
• CHARGES is the only Fact (metric) in the table currently

Let’s say you need to create two CHARGES metrics for your Answers users:

1. Charges (represent a sum of CHARGES)
2. Avg Charges (represents an average of CHARGES per patient visit)

You could satisfy these requirements by creating two metrics in the logical layer of the repository, both mapped to the CHARGES column, with the Default aggregation rules for these metrics set to Sum and Avg respectively. For now, let’s assume you choose this route.

Now suppose you want to use an aggregate table to improve query performance for queries that only utilize the year dimension along with visit Facts. The aggregate table might look like this:

NOTE: (in the above example, there were only 6 records in the original table, so an aggregate table would be trivial, but imagine if the VISITS_FACT table had 6 million rows and your aggregate table reduced the row count from 6 million down to 2).

You could easily use the following query to populate the table above:



Note that I have included a VISIT_COUNT column, which might not have been an initial requirement, but its purpose will soon become apparent.

ISSUES

The above method for creating an average metric in an aggregate table may seem like a good approach, but it falls apart in certain query scenarios. Remember your “Avg Charges” metric? Its Default aggregation rule is set to Avg, and this metric is now mapped to two logical table sources:

1. VISIT_FACT.CHARGES
2. VIST_FACT_AGG_YEAR.AVG_CHARGES_PER_VISIT

When you run an Answers request that contains the Year dimension along with the “Avg Charges” metric, the result is correct; however, what if you run an Answers request that only contains the “Avg Charges” metric and nothing else? There’s a good chance that the Oracle BI Server will still choose to use the aggregate logical table source for this query, and if it does, the results will have an incorrectly calculated “Avg Charges” metric. Why is this? It’s because you can’t average an average. Averaging is not an additive operation. When you try to average a group of numbers that already represent averages, you do not get the proper weighting factors of the original averages and the formula is wrong.

In our example, the correct average for all 6 rows would be (100+100+200+200+850+950)/6 = 400; however, the “average of the averages” would be (900+150)/2 = 525, which is incorrect.

SOLUTION

I suppose you could create two separate “Avg Charges” metrics in the logical layer and map each metric to only one logical table source, and then set the content levels for these metrics, but this solution is impractical, overcomplicated, and does not follow best practices. Besides, wouldn’t you rather have just one “Avg Charges” metric available to end users? The backend aggregate tables and processing should be seamless to the end user anyhow, that’s the whole point of the business layer.

Therefore, here is my proposed solution that should still help you reap the performance benefits of using aggregate tables, and will also allow you to define just one “Avg Charges” metric that will have the correct calculation no matter what the query scenario is:

1. Do not create an AVG_CHARGES_PER_VISIT column in your aggregate table at all.
   
2. Instead, create the VISIT_COUNT column in the aggregate table.
   
3. In the logical layer, create a “# Visits” metric based on the VISIT_COUNT. Set the Default aggregation rule to Sum (this is the appropriate aggregation rule since counts are additive; here we do not use Count as the aggregation rule because it makes no sense to count a count.) But wait, our VISIT_FACT table does not have a VISIT_COUNT column, so how do we map the “# Visits” metric to this logical table source? Here’s the trick, just map this column to a constant number 1:
   
   
   
   (Think about why this works…counting individual rows is equivalent to tallying the rows by assigning a value of 1 to each row and then summing all the 1’s).
   
4. Now that we have a proper “# Visits” metric mapped to both of our logical table sources, create a new logical column for “Avg Charges” using the formula “Avg Charges” = “Charges” / “# Visits” (remember that our “Charges” metric already represents a sum of charges):
   
   
   
   
5. No matter what the Answers request looks like, our “Avg Charges” metric will calculate the average correctly, using the appropriate logical table source, and using the following formula, which applies the default aggregation rules in the correct manner: “Avg Charges” = SUM(“Charges”)/SUM(“# Visits”). This formula even works at the most granular level when you are only averaging one row: 100 = 100/1.

This is the best solution I have found for working with averages along with aggregate tables. If anyone has any other ideas, I’d be interested to hear them.

OBIEE Security Enforcement – External Database Table Authorization

Authentication vs. Authorization

Commonly asked question – What is the difference between authentication and authorization? Authentication is the process in which a user id and password is verified to see if the user is a valid user. The process can be compared to logging on to your email or even your laptop. Once the user logs on, authorization takes care of what components or data a user can have access to. To read about OBIEE Authentication click here.

Setting up framework for Authorization

Authorization is most commonly handled by using an external table. The following steps are required after setting up Authentication process:

1. Create a table in the database that would have the Authorization information. If you already have a table from which associates the UserID/Username with Groups, you can use that table. If not, create the following table in your database.

CREATE TABLE WC_USER_AUTH

(

LOGON VARCHAR2(120 BYTE) NOT NULL,

GROUP_NAME VARCHAR2(120 BYTE) NOT NULL,

CREATED_DT DATE DEFAULT SYSDATE

)

TABLESPACE <your tablespace>

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 64K

MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

CREATE UNIQUE INDEX NDX_LOGON_GROUP ON WC_USER_AUTH

(LOGON, GROUP_NAME)

NOLOGGING

TABLESPACE <your tablespace>

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

NOPARALLEL;

2. Now you will have to populate the table with the relevant information. Note that one user can belong to more than one group.

3. Next, you need to create the groups in the repository. The name of the groups should be as they are in the table, if you want these groups to drive web and data security as well. If you have a group called “Power Users” in the table, you would have to create a group with the exact same name.

4. As a best practice, it is recommended that a separate connection pool is created for the execution of Authentication and Authorization Initialization blocks.

5. Now create a session initialization block that would read from the table to assign groups to the user.

 

6. Configure the session initialization block. Give it a name and click on Edit Data Source. In the pop up window, choose Database from the drop down box. Write a SQL statement that would get all the group names of the user that is populated in the USER variable as part of Authentication. The SQL statement used in this example is SELECT ‘GROUP’, R.GROUP_NAME FROM WC_USER_AUTH R WHERE UPPER(R.LOGON)=UPPER(‘:USER’). Choose a connection pool.

7. Now edit the variable target and set it to row wise initialization. What it really means is, it would assign multiple values to a variable, GROUP variable in this case. If a user belongs o multiple groups, multiple rows will be returned by the SQL and this setting would enable GROUP to contain all the values.

8. Now set execution precedence. The authorization process takes place after authentication process. We are using a variable (USER) that authentication process is populating.

9. Now create the Catalog Groups in the presentation services. The group names should match the group names from the table and the repository as in Step 3, if you want them to drive the web and data security.

Go to Settings -> Manage Presentation Catalog Groups and Users

 

10. Click on Create a new Catalog Group. In the new window give the name of the group and as a best practice give it a password.

11. Now when logged I will log in as Kumar Kambam and click on My Account, in here we can see the Kumar.Kambam ‘s group membership. You can join a Catalog Group from here.

12. Now that we have established that Power Users group has at least one user as demonstrated in the Step 12, let us log in as Administrator and go to Power Users Group properties. Don’t panic if you see the message saying “There are currently no members in this Group”. Group assignment to a user is done at session level. When a user logs on and authorization process assigns groups to users. This assignment of users to a group is valid for that session only. Thus no group membership information is stored in the presentation services.

13. One can also create catalog groups in the presentation services and assign users manually, however it is not recommended to do so.

14. One frequently asked question is – Why cannot I see the comprehensive list of users and their group memberships in the presentation services?

In this set up, presentation services cannot be used to maintain or see the comprehensive list of users. A user will appear only after he/she logs on for the firstime. As far as group assigment goes, it is done on the session level and is valid for that session only. So we cannot see the group membership information. Though you can create a catalog group on the presentation services and assign users manually, it not recommended to do so.

Points to ponder

1. Authentication and Authorization are two different processes accomplishing different tasks.

a. Authentication checks valid user and password

b. Authorization assigns security group membership

2. Authorization process is executed after authorization process

3. If you want to control data and web security with the groups defined in the table, the name of the group should be the same in all the three places – table, repository, and presentation services

4. The assignment of a user to a group in this case is done at session level and that information is not stored in the presentation services. Though you can create a catalog group on the presentation services and assign users manually, it not recommended doing so.