BI & Warehousing

James came up with a cunning crosstab report question on the forum this week. Does not look that bad right? Sadly the new crosstab builder can not build what we need. Notice there is no summarization in the grid; just... Tim Dexter

The elections are over for the ODTUG (Oracle Development Tools User Group) board of directors. Here are the new board members that will be serving in 2010-2011:

• Jeff Jacobs
• John King
• Monty Latiolais
• Bambi Price
• Mark Rittman

They join the carry-over board members who were serving in 2009-2010:

• John Jeunette
• Barbara Morris
• Marc de Oliveira
• Mike Riley

What the keen eyed among you will immediately notice is that Tim Tow was not re-elected. Apparently, it was a very close election and Tim fell just a couple of votes shy. Oracle EPM fans should not be totally disheartened, though, because Mark Rittman was newly elected. Mark is well known in the EPM/BI community. He will do a fine job representing the EPM/BI/Hyperion needs on the board. I've known Mark for a couple of years and I can vouch that he's a decent human being. (I refuse to be friends with anyone who kicks puppies or kittens. Well, okay, I'll be friends with them on Facebook but not IRL. Point is, Mark doesn't kick any small mammals.)
Tim Tow will serve out the rest of his term through December of this year. I know I speak for not only myself but the entire Hyperion community when I say, thank you, Tim, for all the hard work you've put into ODTUG. Hyperion developers, administrators, and users finally have a place to call home again, and it was your tireless service that helped get us there. Sincerely, we appreciate it.
If you want to say thank you to Tim as well, nothing says you appreciate his service like buying a copy of Dodeca from AppliedOLAP.

Details to follow.

Shout out here for Hussein over on the ADF Dev to Dev blog. Nice article covering some of the BIP APIs and how to use them within an ADF project. http://husaindalal.blogspot.com/2009/11/integrating-bi-publisher-standalone.html Thanks to Jurgen and Klaus for finding Hussein and... Tim Dexter

Just a quick pointer to an answer on the Oracle Forum:  http://forums.oracle.com/forums/thread.jspa?messageID=3912341#3912341 If you are merging into a data warehouse fact table (not so likely) or a large aggregation of a fact table (more likely) then you can improve efficiency by adding a transitive predicate into the USING clause to promote partition pruning on the target table. Unless [...]

As I mentioned previously, this is probably the most important area for administering your APEX instance.


Messages

There are 2 types of messages you can create, one is the Login message and one is the System Message



The Login message appears at the top of the Workspace Login screen:



The System messages appears after you have logged in.



Feature Configuration

• Application Development
  
  
  
  
  1. Allow PL/SQL Program Unit Editing - This pertains to the SQL Workshop. Will you allow end-users (Developers/Administrators), who have access to SQL Workshop, to edit PL/SQL.
  2. Create demonstration objects in new workspaces - By default, when you create a new workspace, the sample application is automatically installed in the schema that it is mapped to. In my case, CJUSTICE. There are database objects included with this object as well. If you would like to see those database objects and need a script to remove them, read here.
     
     I don't like that it gets created automatically so I typically turn it off.


• SQL Workshop
  
  
  
  
  1. SQL Commands Maximum Inactivity in minutes - "Identify the maximum amount of time a transactional command in the SQL Command Processor waits before timing out."
  2. SQL Scripts Maximum Script Output Size in bytes - "Identify the maximum amount of output a single SQL Script can generate. SQL scripts are run from the SQL Workshop."
  3. SQL Scripts Maximum Workspace Output Size in bytes - "Identify the maximum amount of space all scripts within a workspace may consume. SQL Script results are the output generated when running SQL Scripts from the SQL command processor."
  4. SQL Scripts Maximum Script Size in bytes - "Identify the maximum size of a SQL script used within the SQL Workshop."
  5. Enable Transactional SQL Commands - "Enable Transactional SQL Commands for the entire instance. Enabling this feature permits users of SQL Commands to issue multiple SQL commands within the same physical database transaction."


• Monitoring
  
  
  
  1. Enable Database Monitoring - "Enable or disable monitoring within the SQL Workshop."
  
  
  Honestly, I have no idea what kind of monitoring is done here. I haven't been able to find any related reports...which doesn't mean they don't exist, just that I can't find them.

Security

• Security
  
  


1. Set Workspace Cookie
2. Disable Administrator Login - Defaults to No (otherwise, how would you log in?)
3. Disable Workspace Login - Defaults to No
4. Allow Public File Upload
5. Restrict Access by IP Address


• HTTPS
  
  


1. Require HTTPS - Use a comma delimited list of IP Addresses to restrict, can be either 1 or up to 4 values


• Session Timeout
  
  


1. Maximum Session Length in Seconds
2. Maximum Session Idle Time in Seconds


• Excluded Domains
  
  


1. Domain Must Not Contain - "Enter domains (not including the port), separated by colons, that should not be allowed in regions of type URL or accessed as a Web service. "


• Account Login Control
  
  


1. Require User Account Expiration and Locking - "Select Yes to enable Application Express user account expiration and locking features across all workspaces. This selection prevents the same feature from being disabled at the workspace level.
   
   Select No to allow individual workspaces to enable or disable this feature independently.
   
   Application Express user account expiration and locking features apply to end-user accounts created using the Application Express end-user account management interface."
2. Maximum Login Failures Allowed - "This setting applies to accounts used to access the Application Express administration and development environment only. It does not apply to end-user accounts used by applications developed in Application Express.
   
   However, this setting is used as the default workspace-level "Maximum Login Failures Allowed" preference, which workspace administrators can change. The workspace-level preference applies to the end-user accounts within that workspace."
3. Account Password Lifetime (days) - Self Explanatory


• Workspace Password Policy
  
  


1. Minimum Password Length - Self Explanatory
2. Minimum Password Differences - "Enter the number of differences required between old and new passwords. The passwords are compared character by character, and each difference that occurs in any position counts toward the required minimum difference.
   
   This setting applies to accounts for workspace administrators, developers, and end use"
3. Must Contain At Least One Alphabetic Character - Self Explanatory
4. Must Contain At Least One Numeric Character - Self Explanatory
5. Must Contain At Least One Punctuation Character - Self Explanatory
6. Must Contain At Least One Upper Case Character - Self Explanatory
7. Must Contain At Least One Lower Case Character - Self Explanatory
8. Must Not Contain Username - Self Explanatory
9. Must Not Contain Workspace Name - Self Explanatory
10. Must Not Contain - colon seperated list of words that cannot be used in the password, default words are: "oracle:hello:welcome:guest:user:database"
11. Alphabetic Characters - default: "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
12. Punctuation Characters - default: "!"#$%&()``*+,-/:;<=>?_"



• Service Administrator Password Policy
  
  
  
  
  1. Policy - You can either define the Workspace Password Policy (above) or just use the default strong password policy. For my sandbox versions, I tend to make it as lax as possible so I do not have to remember complex passwords when putting things like this together.

Instance Settings

For most organizations, the only 3 areas you need to worry about here are Email, Report Printing and Wallet.

• Self Service
  
  


1. Provisioning Status - 3 options here: Manual, Request and Email Verification. For most organizations Manual will do just fine. That will allow your end-users to follow the normal protocols set forth in your organization. For hosted sites, like apex.oracle.com, Email Verification is the way to go.
2. Development Service URL - Used in conjunction with Request or Email Verification above.



• Email Provisioning
  
  



1. Email Provisioning - By default, is is enabled, but without the other settings it is useless. I turn this off...but it doesn't really matter for me since I use a sandbox.


• Schema
  
  



1. Require New Schema - Do you want to require the user to obtain a new schema or reuse an existing one?


• Email
  
  



1. SMTP Host Address - This is where you set up your internal mail server. All APEX mail calls will use this server by default.
2. SMTP Host Port - Self Explanatory
3. Administration Email Address - Self Explanatory
4. Notification Email Address - Self Explanatory


• Wallet
  
  
  
  
  1. Wallet Path - Path to the wallet on the database server.
  2. Wallet Password - Self Explanatory


• Report Printing
  
  
  
  
  1. Print Server - Standard is the Default. To enable pdf printing you need BI Publisher and this is where you enter the server information.
  2. Print Server Protocol
  3. Print Server Host Address
  4. Print Server Port
  5. Print Server Script
  
  For a How To on setting up APEX and BI Publisher, check out the RittmanMead blog entry Oracle ApEx and BI Publisher.


• New Workspace Request Size
  
  
  
  If using the Self Service module, this is where you'll set the sizes (and the default) of the workspaces to choose from. You can enable them all or just a few.


• Workspace Change Request Size
  
  
  
  Just like the New Workspace Request Size section, you can modify the size of workspaces you are offering.

We’re organizing a drinks reception at the Pitcher and Piano Bar on the Tuesday evening of the UKOUG Tech and EBS Conference, which is open to all BI & DW delegates, speakers and partners who’d like to come along. We’ll be there from 6pm to 9pm on Tuesday, 1st December, and we thought it’d be a good chance for anyone interested in BI and data warehousing to get together and share a few stories.

Attendance is free but it’d be good if you can let us know if you’re going to come along. The Pitcher and Piano is in Brindley Place, just across the road from the ICC, and we’ve reserved an area so just look out for the signs. Drop me a line if you plan to come along, or leave a comment on this blog post, and hopefully we’ll see you in Birmingham.

Here's a simple script to remove the database objects from the default Sample Application from Application Express (APEX). Deleting the application from the interface does not remove the database objects.

There are 30 database objects created by default along with the Sample Application:

OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------
FUNCTION            CUSTOM_AUTH
                    CUSTOM_HASH
INDEX               DEMO_CUSTOMERS_PK
                    DEMO_CUST_NAME_IX
                    DEMO_ORDER_ITEMS_PK
                    DEMO_ORDER_PK
                    DEMO_ORD_CUSTOMER_IX
                    DEMO_PAGE_HIERARCHY_PK
                    DEMO_PRODUCT_INFO_PK
                    DEMO_USERS_PK
LOB                 SYS_LOB0000077614C00007$$
SEQUENCE            DEMO_CUST_SEQ
                    DEMO_ORDER_ITEMS_SEQ
                    DEMO_ORD_SEQ
                    DEMO_PROD_SEQ
                    DEMO_USERS_SEQ
TABLE               DEMO_CUSTOMERS
                    DEMO_ORDERS
                    DEMO_ORDER_ITEMS
                    DEMO_PAGE_HIERARCHY
                    DEMO_PRODUCT_INFO
                    DEMO_STATES
                    DEMO_USERS
TRIGGER             BI_DEMO_USERS
                    DEMO_ORDER_ITEMS_GET_PRICE
                    DEMO_USERS_T1
                    INSERT_DEMO_CUST
                    INSERT_DEMO_ORDER_ITEMS
                    INSERT_DEMO_PROD
                    UPDATE_ORDER_TOTAL

30 rows selected.

If you have nothing else in your schema, you can run this:

DECLARE
  l_sql VARCHAR2(32767);
BEGIN
  FOR i IN ( SELECT object_name, object_type 
             FROM user_objects
             WHERE object_type NOT IN ( 'LOB', 'INDEX', 'TRIGGER' ) 
             ORDER BY object_type )
  LOOP
    CASE 
      WHEN i.object_type = 'TABLE' THEN
        l_sql := 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS PURGE';
      ELSE
        l_sql := 'DROP ' || i.object_type || ' ' || i.object_name;
    END CASE;
    EXECUTE IMMEDIATE l_sql;
  END LOOP;
END;
/

If however, you do have other items in there, use this:

DROP FUNCTION CUSTOM_AUTH;
DROP FUNCTION CUSTOM_HASH;
DROP SEQUENCE DEMO_ORDER_ITEMS_SEQ;
DROP SEQUENCE DEMO_PROD_SEQ;
DROP SEQUENCE DEMO_ORD_SEQ;
DROP SEQUENCE DEMO_USERS_SEQ;
DROP SEQUENCE DEMO_CUST_SEQ;
DROP TABLE DEMO_STATES CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_PAGE_HIERARCHY CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_USERS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_ORDER_ITEMS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_ORDERS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_PRODUCT_INFO CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_CUSTOMERS CASCADE CONSTRAINTS PURGE;

Triggers, LOBs and Indexes will be dropped along with their associated objects.

One intriguing or not-so obvious feature of BI EE is the way it does its Sub-Totals & Pivot Table calculations. As with any reporting tool, we expect almost every part of a report to be pushed back to the database in the form of native SQL. But there are certain cases wherein we cannot push an entire report in the form of SQL (for example Sub-Totals & Pivot table aggregations). In such cases, BI Server does these calculations in its own memory. Lets look at what this “in-memory calculation” means and how BI Server treats totals & Pivot table calculations. Since this is not documented anywhere, this behavior can be different across different releases (though i think it will work the same in all 10.1.3.* releases)

Sub-Totals:

To demonstrate how Sub-Totals work, lets start with a very simple report shown below

As you see, this table view report has a Grand Total. The question is how does BI Server create this Total. Does it bring the entire data into BI Server memory and then does the sub-total or does it push that back in SQL or does it write to any temporary file.

To understand this, lets start with looking at the logical SQL in the advanced tab of this report.

SELECT
Products.PROD_CATEGORY saw_0,
Channels.CHANNEL_DESC saw_1,
Sales.AMOUNT_SOLD saw_2
FROM
"SH - Training Days - Relational"
ORDER BY saw_0, saw_1

As you see this logical SQL does not actually represent the true logical SQL since the aggregation or the total part of the query is not anywhere in this SQL. So, to get the exact SQL we have to go to the Manage->Sessions & extract the logical SQL

SELECT
Products.PROD_CATEGORY saw_0,
Channels.CHANNEL_DESC saw_1,
Sales.AMOUNT_SOLD saw_2,
REPORT_SUM(saw_2 BY )
FROM "SH - Training Days - Relational"
ORDER BY saw_0, saw_1

If you notice, there will be a new function called REPORT_SUM which will be passed to the logical SQL. REPORT_SUM is an aggregation function that BI Server uses to calculate the total (since we had chosen Report Based Total whenever applicable in the table view). One good thing is we can take the exact SQL and fire it in the logical SQL window

As you see BI Server calculates the total as a column based total. There are quite a few functions like REPORT_SUM. I will list some of the common ones here

1. SUM

2. AGGREGATE

3. MIN

4. MAX

5. COUNT

6. COUNTDISTINCT

After calculating the column based total, BI Server will get the first record value in the Report Sum column and display it as Sub-Total. Now that we know what BI Server does for calculating the sub-totals in a table view, lets look at how the BI Server pushes the REPORT_SUM function. If you look at the physical query for the above logical SQL, you will notice that the report level total is not pushed back to the database. So, the question is how BI EE does this total. To determine this lets look at the {OracleBIData}/tmp folder. You will notice that for every query that has a sub-total (with database cache and presentation services cache disabled) BI EE will create temporary files in this tmp directory. The writing of temp files to this directory is governed by 4 settings in the NQSConfig.ini

WORK_DIRECTORY_PATHS	=	"C:\Oracle\OracleBIData\tmp";
SORT_MEMORY_SIZE = 4 MB ;
SORT_BUFFER_INCREMENT_SIZE = 256 KB ;
VIRTUAL_TABLE_PAGE_SIZE = 128 KB ;

So, when we created the above report, BI Server had created a set of temp files in the above directory. These tmp files are actually similar to the cache files but are stored seperately. But how they are handled is unfortunately not documented (though we can arrive at certain conclusions by trying various scenarios). In some complex reports, when the sub-total calculation involves a lot of intermediate rows, you will notice a sizeable increase in the size of the tmp files

Pivot Tables:

Pivot table is probably the most widely used Answers component. Though widely used, many a times it can eventually lead to performance bottlenecks due to a lot of in-memory calculations. Most Pivot calculations are done in the BI Server memory and are not pushed back to the database(though we can control where it is executed to an extent). As a generic rule, in any implementation, if the repository is correctly designed, almost 90 to 95% of the reports can be achieved using normal table views(unless all the reports require a specific feature like Sub-Total at the top etc). The most common reason why pivot table is quite popular is the fact that it offers flexibility to end users in slicing/dicing the data. It is treated as an alternative to a multi-dimensional reporting solution.Pivot table can sometimes hog the temp space so much that even cache writing process might fail due to memory allocation errors. For example, lets take the above report and convert it into a pivot table as shown below

If you notice, all i have done is i have moved a couple of columns to the Excluded section of the Pivot table. If you look at the Physical query, the SQL will still contain Product Id and Channel Desc even though they are excluded.

select T24112.PROD_ID as c2,
     T24112.PROD_CATEGORY as c4,
     T26412.CHANNEL_ID as c6,
     sum(T24170.AMOUNT_SOLD) as c8,
     sum(T24170.QUANTITY_SOLD) as c9
from
     CHANNELS T26412,
     PRODUCTS T24112,
     SALES T24170
where  ( T24112.PROD_ID = T24170.PROD_ID and T24170.CHANNEL_ID = T26412.CHANNEL_ID )
group by T24112.PROD_CATEGORY, T24112.PROD_ID, T24170.CHANNEL_ID, T26412.CHANNEL_ID
order by c6

So what this means is BI Server will do all the aggregations in its own memory or by writing to tmp files. If we look at the tmp file directory

As you see, a sudden 59KB file has come into the tmp file directory due to the amount of custom calculations that the BI Server has to do in its memory. The size of the files directly are related to the amount of calculations & the number of cells retrieved. So, whenever you face an issue like a Write to Tmp directory error etc, then the first place to look at is how much of calculations are being done by the BI Server. Also, as much as possible try to reduce the amount of calculations to be done by the pivot table. As more users start accessing the system, more will the number of tmp files and hence more will be the I/O. This is something to take notice of while creating Pivot Table reports.

Completely off topic today, but something Im sure nearly all of you use. Social networks, whether it be Facebook, Twitter, MySpace, the list goes on, I'm sure you are using at least one. Jake over at the lab has been... Tim Dexter

So, exactly how many man-years have gone into creating Dodeca? A lot as evidenced by a look at the keyboard of Amy Tate, the Dodeca Architect. This is a relatively new Dell Latitude D830 laptop but she has polished off some of the letters in our quest to create great software..


Obviously you can see how hard we have worked on the product. If you want to really find out how we hit the mark on the greatness aspect, just ask our customers. After all, some of our customers may be your competitors!

More from my journey of Siebel- BIP integration discovery last week. It was a steep learning curve but none the less very useful. Firstly, there are 3 levels of integration available to Siebel 8.1.1 users Level 1 Siebel stores and... Tim Dexter

In the last blog entry i had shown a simple scenario of creating a BMM layer for one physical table. In today’s entry i will be covering a practical use case of creating a BMM layer from one physical table. This is a very interesting use case and can be used to demonstrate the reporting capabilities of BI EE.

Any parent body of a sporting event involving 2 teams/individuals like Tennis/Football etc generally collects a plethora of statistics. In fact stats are more important in a sporting event directly (for placing bets, buying a team, Potential Sponsors, Player analyzing weakness of opponent etc) than any BI implementation for a business(direct impact is more obvious in sports). The stats from a sporting event has immediate impact and actually makes more sense for any person since the end user looking at the stats has more understanding of the game (i always feel that we can relate more to a sporting event than the functioning of a business, but that’s just me ).

Stats for any game involving 2 teams/individuals are collected at the game level. For example, if you consider tennis, the stats like number of shots, number of unforced errors , number of forced errors, Winning Points, Losing Points, set scores etc made by both the individuals are recorded as attributes to the game in a single row. For example, if you consider the screenshot below

this contains actual data of all the games from 2007 in Men’s ATP tennis (i had to choose tennis since i thought that’s the game everyone can relate to). If you notice, every row corresponds to a game played by the winner & loser of the game. Typical stats are analyzed at a player level. For example, a major shoe manufacturer, before sponsoring a tennis player,  would like to know the number of games won & lost by the player in the most commonly played surface (hard courts) for example. But if you look at the way the data is maintained, the KPIs for 2 players are maintained in the same row (winner as well as the loser). Writing reports even in plain SQL can be tricky since either the query has to involve multiple Unions or sub-query joins. All we have is a single table but with a rich set of stats that can be analyzed in multiple ways. The even more trickier part is in modeling this for reporting in BI EE.

For modeling this, we need to determine how any person would be analyzing the stats. As with any game, we always have our favorite players and not so favorite ones. And typically we want to analyze how each one of them performed against each other over a period of time. Also there are stats that we need to look at by not comparing with any other individual like number of matches played in a year, win percentage, loss percentage etc. So there are 3 kinds of stats

1. Metrics when your favorite player is the winner – Example: Number of Matches Won
2. Metrics when your favorite player is the loser – Example: Number of Matches Lost
3. Metrics when your favorite player is the winner as well as the loser – Example: Number of Matches Played

As a first step we start with determining the logical tables that we will be needing in our model. They are

1. Player Dimension – This logical table will contain the following attributes(screenshot). The winner & loser have been transformed to Favorite & Opponent. They do not map 1:1 though. We will be seeing how we map the Favorite & Opponents from Winner & Loser attributes later.

2. Winner Facts
3. Loser Facts
4. All Facts

So, the idea is we enable a capability for any user to analyze the stats (Matches Won, Lost & Played) for their favorite players. But if you remember our data had 2 set of KPIs in a single row. That is our favorite player can be a winner as well as a loser. To accommodate this we create a couple of aliases in the Physical layer. One for Wins and the other for Losses.

There will not be any physical layer joins across these tables/aliases. The aliases that we have created above will act as 2 separate tables providing the 2 sets of KPIs in a single row. Since our perspective of analysis is FAVORITE & OPPONENT, we need to ensure that both the Winner as well as the loser are mapped to both FAVORITE & OPPONENT. So, we start with mapping the dimension to both the aliases. ATP-Winner alias will map Winner to Favorite & Loser to Opponent.

ATP-Loser alias will map Loser to Favorite & Winner to Opponent. Also the other attribute stats like Set Points, Winner points etc will get swapped

By doing this we make the Dimension logical table to have the universal information about a player & his opponent. It will take a while to understand this as this is the key in this modeling scenario. Next we map the individual logical table aliases to their corresponding Fact Tables.

Not only this, we can use the same approach to provide more than one perspective of the data. For example, if you are analyzing football then you can give a Home/Away perspective in addition to Winner/Loser perspective. Also, the same approach above can be used for games where more than 2 teams are involved in a single game. For example, Formula 1, Athletics etc.

To test this, we start with Analyzing Top 5 Players Win record in the last 3 years

And if you look at the query generated, it will contain a universal perspective of all the 5 players above as Winners. So, only one Alias will be used.

select T30942.WINNER as c1,
     T30942.YEAR as c2,
     sum(case  when not T30942.LOSER is null then 1 else 0 end ) as c3
from
     ATP T30942 /* ATP - Winner */
where  ( T30942.WINNER in ('Del Potro J.M.', 'Djokovic N.', 'Federer R.', 'Murray A.', 'Nadal R.') )
group by T30942.WINNER, T30942.YEAR
order by c1, c2

Lets include the Loss Statistics as well and see the resulting query.

WITH
SAWITH0 AS (select sum(case  when not T30942.LOSER is null then 1 else 0 end ) as c1,
     T30942.WINNER as c2,
     T30942.YEAR as c3
from
     ATP T30942 /* ATP - Winner */
where  ( T30942.WINNER in ('Del Potro J.M.', 'Djokovic N.', 'Federer R.', 'Murray A.', 'Nadal R.') )
group by T30942.WINNER, T30942.YEAR),
SAWITH1 AS (select sum(case  when not T30972.LOSER is null then 1 else 0 end ) as c1,
     T30972.LOSER as c2,
     T30972.YEAR as c3
from
     ATP T30972 /* ATP - Loser */
where  ( T30972.LOSER in ('Del Potro J.M.', 'Djokovic N.', 'Federer R.', 'Murray A.', 'Nadal R.') )
group by T30972.LOSER, T30972.YEAR)
select distinct case  when SAWITH1.c2 is not null then SAWITH1.c2 when SAWITH0.c2 is not null then SAWITH0.c2 end  as c1,
     case  when SAWITH1.c3 is not null then SAWITH1.c3 when SAWITH0.c3 is not null then SAWITH0.c3 end  as c2,
     SAWITH0.c1 as c3,
     SAWITH1.c1 as c4
from
     SAWITH0 full outer join SAWITH1 On nvl(SAWITH0.c2 , 'q') = nvl(SAWITH1.c2 , 'q') and nvl(SAWITH0.c2 , 'z') = nvl(SAWITH1.c2 , 'z') and nvl(SAWITH0.c3 , 'q') = nvl(SAWITH1.c3 , 'q') and nvl(SAWITH0.c3 , 'z') = nvl(SAWITH1.c3 , 'z')
order by c1, c2

As you see BI EE will automatically make a sub-query join across both the aliases since we have measures coming in from 2 different fact tables. The join is done as both the fact measures conform to the common dimension. There are quite a few other interesting stats that one can derive out of this. Like for example, we would like to know the list of matches that Federer has lost in the last year and also the opponents & the courts.

Interestingly he has lost to a total of 9 players. But Nadal & Murray have beaten him quite a few times more than other players. So, lets narrow this down further to see where Federer lost to Nadal & Murray

There are lots of other stats that we can accomplish out of this. I will stop here & will let FIFA, ATP etc notice this . Interestingly, in any major website there is not a lot of stats that are open for public. Only if we could have a live Web-Service giving us the results of each game & their stats, we can have even more interesting results for other sports (like what makes Usain Bolt faster than any other athlete!!! etc). But the point is BI EE can do these kind of statistical reporting pretty easily if modeled correctly in the repository.

OAUG Connection Point - Enterprise Performance Management
Back on October 30, I wrote about what little information I had on the upcoming regional OAUG conferences on Hyperion and EPM. The name that OAUG is giving to these conferences is way too long and way too hard to remember, so I'm going to start calling them "EPM Connection" conferences. If OAUG doesn't like it, they should come up with a shorter, catchier, more marketable name (like Dimensions, Solutions, Collaborate, Kaleidoscope, or OpenWorld).
I have some new information to impart compliments of the OAUG eNews from November (scroll way to the bottom): http://www.oaug.org/communications/publications/enews/2009/2009-11.htm
Date Change: February 22-23Contrary to earlier information that put the dates slightly later, the conference is now going to be held on Monday and Tuesday (Feb. 22-23) in Jersey City, NJ.
Tracks RevealedThere are going to be 5 tracks at the first EPM Connection conference:

• Budget, Forecasting & Planning
• Financial Consolidations & Reporting
• Business Intelligence & Analytics
• The Office of the CFO
• EPM Product Roadmap

The non-product orientation of the tracks seems to imply that this will be more of a business-oriented rather than technical conference. Considering that this is put on by OAUG, we sort of expected that though. To drive this point home, OAUG had this to say about their desired audience:This event is ideal for accounting and finance professionals of all experience levels, internal audit staff with a focus on external and compliance reporting areas, and IT professionals who need to understand what the accounting and finance groups need to achieve.I don't like how EPM is getting pigeonholed as only applicable to finance/accounting types, but you can't win 'em all.
More InformationOAUG is telling people that more information will be coming soon to this website: http://oaug.org/conferencesandeducation/conferences/2010/connectionpoint/
At the moment, there is less at that link than I just discussed in this blog posting, but I'm sure more will be posted soon since EPM Connection is now just 3 months away. They'd better hurry with their call for papers (if they're even going to have one).

Yes, I know it comes pre-installed with 11g. No, I didn't bother to check what the default version was either. I needed practice so I removed it and re-installed it. This follows my recent install of 11gR2 on OEL v5.4.

First up, get the latest version from apex.oracle.com. I unzipped the file in a shared folder (which was then mounted to OEL) in the following directory (from OEL):

/mnt/software/oracle/apex/apex

In another window, I open up the installation doc.

From the database server, I moved to that directory and logged in as SYS. Here's the list of files in that directory:

[oracle@oracledb apex]$ ls -l
apex_epg_config_core.sql
apex_epg_config.sql
apexins.sql
apexvalidate.sql
apxchpwd.sql
apxconf.sql
apxdbmig.sql
apxdevrm.sql
apxdvins.sql
apxe101.sql
apxe102.sql
apxe111.sql
apxldimg.sql
apxpatch.sql
apxrelod.sql
apxremov.sql
apxrtins.sql
apxsqler.sql
apxxemig.sql
apxxepwd.sql
catapx.sql
coreins.sql
devins.sql
endins.sql
load_trans.sql

Since it's already installed, I want to remove it. To do so, I need to run apxremov.sql. I run it and a few minutes later its gone. I confirm this by looking in the DBA_REGISTRY view:

SELECT comp_name, version
FROM dba_registry
ORDER BY comp_name;

There are 4 basic installation scenarios:

1. Downloading from OTN and configuring the embedded PL/SQL gateway (11g)
2. Downloading from OTN and configuring Oracle HTTP Server (9.2.0.3 -- 11gR2)
3. Installing from the database and configuring the embedded PL/SQL gateway (11g)
4. Installing from the database and configuring the Oracle HTTP server (11g)

I'm going with #1.

I've removed the default installation of APEX, now I'm going to install it. I'm still logged in to SYS so I just need to run the apexins.sql script.

SQL>@apexins.sql SYSAUX SYSAUX temp /i/

The first parameter SYSAUX is the tablespace where APEX user will ive. The second SYSAUX is where the APEX files will live. TEMP is the name of the temporary tablespace and /i/ is the image (virtual) directory. Installation on my standalone machine took about 30 minutes.

The installation script logs you out at the end, so for the next step, you have to login as SYS account. Now we'll be setting the ADMIN account password. This password will be used here:



or here if you use INTERNAL as the workspace name (thanks John!):



Script to change the ADMIN password: apxchpwd.sql

Since this is technically an upgrade, I need to run apxldimg.sql

SQL> @apxldimg /mnt/software/oracle/apex

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.
timing for: Load Images
Elapsed: 00:02:33.48

Directory dropped.

Verify that the ANONYMOUS account is unlocked, if it isn't unlock it.

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

We need to verify the EPG port now:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If a 0 (zero) is returned, EPG is not set-up yet. I'm going to use port 8080 because I'm too lazy to figure out, for now, how to set it up on port 80.

EXEC dbms_epg.sethttpport( 8080 );

Finally we have to enable network services. Since this is my own sandbox, security is not a concern. I'm going with the default as provided by the documentation:

DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  SELECT ACL 
  INTO ACL_PATH 
  FROM DBA_NETWORK_ACLS
  WHERE HOST = '*' 
    AND LOWER_PORT IS NULL 
    AND UPPER_PORT IS NULL;

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) 
  INTO ACL_ID
  FROM 
    XDB.XDB$ACL A, 
    PATH_VIEW P
  WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) 
    AND EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200', 
     'connect') IS NULL 
  THEN 
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
    'APEX_030200', TRUE, 'connect'); 
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
      ( 'power_users.xml',
        'ACL that lets power users to connect to everywhere',
        'APEX_030200', 
        TRUE, 
        'connect' );
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

You should be ready to go. The url will be http://localhost:8080/apex/apex_admin

If you are prompted with a XDB login window like this:



You may have forgotten to unlock the ANONYMOUS account. (I did, naturally).

Within the Administrative Services space, you have 4 options:

• Manage Service
• Manage Workspace
• Manage Applications
• Monitor Activity

Of those 4, Manage Service is probably the most important area. This is the location where you will administer your entire APEX instance.



Within the Manage Service space, you have 3 categories:

• Manage Service
• Manage Environment Settings
• Manage Shared Components

Manage Shared Components

This is where you manage your public themes. What are public themes?Public themes allow you to add to the theme repository. They are available when you create an application or when you create a theme for an existing application.

You create a public theme by selecting a theme from within a workspace and application. Once created, it cannot be edited directly. To edit, create an application based off the theme, edit the theme within that application and then you can create a new public theme with the updated application theme (and delete the old, if you no longer want it).Manage Service



In the Manager Service section (of Manager Service, naturally), there are 5 sections:

• Site-Specific Tasks
• Logs
• Session State
• Mail Queue
• Installed Translations

I just now learned what Site-Specific Tasks are (isn't that the whole point of this?). You can either create a task that will display on the Workspace Home or the Workspace Login. I've created 2 tasks, one for each area:



Where do those display?

Workspace Login



Workspace Home



Site-Specific Tasks is helpful to relay messages to your development staff, perhaps a planned outage or a reminder to use constraints. ;)

Next up are Logs. From here you can manually purge SQL Workshop, Page View Acitivity, Developer Activity , External Click, Mail and Login Access logs. The reports on these logs can be viewed from the Workspace Home. Not really a whole lot to see here.

Session State
Not a whole lot to see here either. There is a report to view recent sessions with drill-down capabilities, but that's about it. The other 2 items in this section are "Purge Sessions, by age" and "Session State Statistics."

Mail Queue
Basically just a log of all the pending emails to be sent. You have the option to send them or delete individual emails.

Installed Translations
Finally, not much to see here either. Just a list of, you guessed it, installed translations.



I'm going to take on the final piece of the Manage Service section in a separate post. This is where the guts of your APEX instance are managed. Lots of fun stuff here.

I've been reading up on Edition-Based Redefinition (which I can't link to because the docs seem to be missing right now).

In my readings I ran across DBMS_UTILITY.WAIT_ON_PENDING_DML which appears to be new to 11gR2 (there is no entry for it in the 11gR1 docs, which again, I can't link up currently). Based on my reading (I have the docs locally), this function appears to be used specifically for the new editioning feature.

It's listed as a procedure in the docs (don't ask for the link):



and a function if you do a describe on dbms_utility.

This procedure waits until all transactions (other than the caller's own) that have locks on the listed tables and began prior to the specified SCN have either committed or been rolled back.So I wanted to see how it works.

First, I create the table:

CREATE TABLE s
(
  x NUMBER
);

I then open up a second session. In that session, I will run the WAIT_ON_PENDING_DML function which should monitor session 1.

Session 1:

BEGIN
  FOR i IN 1..100 LOOP
    INSERT INTO s ( x ) 
    VALUES ( DBMS_RANDOM.VALUE );
    dbms_lock.sleep( .5 );
  END LOOP;
  COMMIT;
END;
/

I'm inserting 100 records with a wait of half a second between inserts. This should take a little over 50 seconds.

Session 2

Note I've set the timeout to 30 seconds so that it will finish prior to Session 1

DECLARE
  l_bool BOOLEAN;
  l_scn NUMBER;
BEGIN
  l_bool := dbms_utility.wait_on_pending_dml
              ( tables => 'cjustice.s',
                timeout => 30,
                scn => l_scn );

  dbms_output.put_line( 'scn:  ' || l_scn );
  IF l_bool THEN
    dbms_output.put_line( 'true' );
  ELSE
    dbms_output.put_line( 'false' );
  END IF;
END;
/

scn:  924643
false

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.03

The return value of "false" means that Session 1 is still inserting, which I confirmed visually (I should really create a video for this). Once Session 1 completed, I reran Session 2's anonymous block and received these results:

CJUSTICE@TESTING>/
scn:  924773
true

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

In regards to editioning, you would poll the database using this utility to see if there is, well, any pending DML. One aspect of Editioning allows you to create temporary triggers that will help you to migrate your application to the latest version without worrying about locking or timeouts. Pretty cool stuff.

I'll have more Editioning soon.

Our Dodeca architect, Amy, was having problems with the classic Excel add-in (v11.1.1.3) on her laptop and so I took a look. I decided to look to see if the problem as a rogue copy of the xll file on here systems so I did a search for all of the xll's on her system. Here is what I found:


There are 28 localized versions installed by default. No wonder people are complaining about the size of the download. I didn't check to see but I would guess each of these directories contains a full client (localized) API which would be huge.

I hear they are planning improvements for 11.1.2. For the sake of those still on the classic add-in, I hope so.

Generating HTML from BIP? Trying to set a title for the page? Stuck? Can't find out how? Its not in the documentation yet but its pretty simple to do. In the template in MSWord use File->Properties->Summary->Title. Enter your page title.... Tim Dexter