BI & Warehousing

Another tenous and cryptic title, today we're talking connection pooling with BIP.

We have had a question recently on how to set up a connection pool for BIP rather than direct JDBC connections. Pooling has its advantages of course - rather than each report having its own 'personal' connection to the database at runtime it which can get expensive very quickly in terms of resources. Instead, BIP can pick up a connection from a pool, assign it to the report, once the data is fetched the connection is released back to the pool.

Now BIP provides a page to enter the JNDI connection pool information but we are little vague or we at least expect you to know what you are doing when it comes to setting up the pool in the J2EE container. To supplement the doc I thought I'd walk through setting up the connection pool in OC4J and then how to get BIP to use it.

Firstly, you are going to need the oc4jAdmin password to be able to do any of this. If you installed BIP on your server then the installer will have prompted you for a password - hopefully you can remember it. If someone else installed it for you - either talk nicely to them to get the password or buy them a beer and get them to read this blog article and get them to set up the pool. Once you have the password login to the Enterprise Manager, typically:

http://server:port/em

This will get you to the main home page.



Now click on the Administration tab and then on the Services > JDBC Resources icon. You'll end up here:

There is a sample pool and data source set up already for reference. We are going to create a new pool and datasource to use it. Click on the 'Create' button for the Connection Pools section.

Select the BIP application and 'New Connection', this is going to make the pool available to the BIP server. Now hit continue.
Fill out the form providing a name for the pool, etc. Check the documentation if you need to get into Connection Factory Properties for your database.

Before you move on test the connection.

Now move to the Attributes page to fill out the size of the pool, etc. Worth reading the OC4J doc here to get some advice on the numbers to use. It going to depend on your expected reporting load on the system, etc.

The Proxy Interfaces tab is only needed to wrap vendor implementations of java.sql objects when using managed data sources. I dont need it as Im using Oracle objects - check the doc if you need more info.
The pool is now set up so click Finish. Next we need to create a DataSource for the pool - this will manage the pool.

Assign the Application and select Managed Datasource to allow OC4J to 'look after' it for us. 

Fill out a name for the dataource and enter the JNDI location. Check the doc for more details but here's what it states on the JNDI name.

Enter the Java Naming and Directory Interface (JNDI) location (or path) for the data source. Deployed applications use this information to locate the data source. For example, the JNDI location of the OracleManagedDS data source is jdbc/OracleManagedDS.

The one I created has the following location, 'jdbc/BIP10gSource', its a virtual location so you will not find it on the disk. The location is important as we are going to reference it in the BIP JNDI connection setup. Once you're done click Finish, you are taken back to the main data source page where you can test the datasource. We now have a pooled connection we can use in the BIP server to a database.

Now bounce the OC4J instance and bring up the BIP server and get in as an Administrator and navigate to the JNDI Connection page (Admin > Datasources > JNDI Connections) and Add Data Source.

Give it a name and the JNDI location that you defined in the OC4J UI - then hit Test Connection to make sure its all working.

You can now use the connection for your reports - just select the pool name rather than a direct database connection. You're now swimming so gimme 30 laps!

Well, this is what the otn BI EE download page gave me today.

    

10.1.3.3.3 version is about to be released in another 8 to 10 hours(hopefully). I believe this is more of a patch release with some bug fixes. Lets wait and see what new features/bug fixes are being bundled into this release.

The Suncoast Oracle Users Group (SOUG) will be putting on their annual Technology Day on May 13th. If you are in Tampa or the surrounding area, you should go.

I went in 2006 (from Gainesville) and had a great time. Tom Kyte spoke that year. Surprisingly, my favorite speaker was this guy from Sun (I can't remember his name for the life of me). The main thrust of his presentation was the open sourcing of Solaris 10. He said something about DTrace (I think), way over my head. But his enthusiasm was contagious.

I've always wanted to learn a non-Windows OS, but I could never decide what flavor. Just too many choices.

Solaris 10 was free and it ran Oracle. Perfect!

I did recently create a Solaris 10 Virtual Machine, but I still haven't gotten around to attempting to install Oracle on it.

Anyway, it's a good time, especially for those of you starved for "nerd" companionship!

(Yes, another poll, these are pretty cool. Thanks to Mr. Eddie Awad for the polling link. I think he used it a couple of years ago and I've used it every since)

As promised, I am starting a series on the Collaborate 08 presentation by Aneel Shenkar that discussed new features scheduled for the next version of Essbase. This presentation, as any presentation you see from Oracle on unshipped product, has the following disclaimer:

"The following is intended to outline our general product direction. It is intended for information
purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle."

The fact that you hear it from me certainly doesn't change the above disclaimer as I can't/don't speak for Oracle.

That being said, here is a list of the things presented with a brief description of benefits of these features:

1. Varying Attributes (aka Slowly Changing Attributes) - Slowly changing attributes let you change how members rollup based on time. A common usage scenario would be in a corporate reorg where 'Dept 100' rolls up to 'Mgr A' until July 2007, then it rolls up to 'Mgr B'. Without slowly changing attributes, a 'reorg' in the Essbase outline moves all history to roll up to 'Mgr B'.
2. Time Intelligence enhancements in block storage. These were introduced in 9.3 but were ASO only; now some of that same functionality will be available throughout Essbase.
3. Date and Text Measures - Allows you to display text instead of a value as the result of an Essbase query. To me, it looks like an enhanced 'Missing Text String' type of functionality except the 'text' values are controlled by the database designer.
4. Improvements to backup and logging. The thing that stands out for me on this is the ability to audit logging capabilities. It appears Essbase will now track all write operations to the database including outline changes, data load, calculations and lock and send. Further, there will be new 'replay' feature that lets you 'replay' these 'writes' to your database; it looks very useful in the case where you have to restore from a backup.
5. ASO Improvements including the ability to do a 'Partial Clear' of the database and ability for ASO databases to be the target of a partition.
6. Essbase can be a physical database source in the OBIEE BI Server. For those of you that have always wanted to use SQL to go against Essbase, essentially the BI Server layer will allow you to do that; I would guess they are putting a virtual star-schema layer on top of Essbase in the BI Server layer.
7. XOLAP or 'eXtending OLAP on a relational database'. Essentially, XOLAP is dynamically sourcing the cube from a relational datasource. I think this could be a very exciting development that could allow us to write some very cool applications in the future that were difficult before. For example, payroll planning applications where the user wanted to add, on the fly, new employees to plan for may be much easier in XOLAP. I can't wait to get my hands on it so I can see how far we can push it.

The presentation also talked about Essbase Studio and some future Essbase things as well. I plan to post a bit more on this topic and include some screenshots as time allows.

If you want to learn more about Essbase 11.1.1, I would urge you to attend the ODTUG Kaleidoscope conference in New Orleans. The Essbase Symposium on Sunday, June 15 is dedicated exclusively to 11.1.1 and future versions of Essbase; speakers for the day are all from the Essbase development organization and it will give you the unique opportunity to get your questions answered directly from the developers. Information on Kaleidoscope is located at http://www.odtugkaleidoscope.com/hyperion.html.

As i had written previously here, one of the methods of loading Essbase cubes is by using the Hyperion Data Integration Management and its corresponding Essbase Adapter. In that blog entry i had written about the capabilities of Hyperion DIM product (which is nothing but Informatica underneath). Today, lets see how we can go about loading a simple Essbase Cube using the Essbase Adapter. In order to load an Essbase Cube(using the Essbase Adapter) we would have to be aware of 3 different loading mechanisms that are specific to Hyperion DIM. They are

1. Type 1 - Here, all the dimensions are treated normally and the measures are treated as different columns. One would have to have a seperate column populating the lowest level for each dimension and a seperate column for each of the measures. For example, our source should table should look like this

    

In this case, our fact table has 3 dimension keys CITY, PRODUCT and MONTH. And, we also have 2 more columns SALES and COGS where both of them correspond to measures. If you have data in this format then use this load technique to load the data into the Essbase Cube/Database.

2. Type 2 - Here all the dimensions are treated normally and the measures are populated in a single column. And the different measures are identified by another column. For example, our source table populating the cube should look like this.

    

As you see above, our fact table has 4 dimension keys CITY, PRODUCT, MONTH and ACCOUNT. We have only one measure and the type of measure is identified by the ACCOUNT column. So, if you have source data in this fashion, use Type 2 method to load your Essbase cubes.

3. Type 3 - This is for handling value based hierarchies in the source data. So, if your source data contains data with parent child columns (hierarchy is obtained by traversing the parent child columns) then use the Type 3 method to load your essbase cubes.

In our example, lets start with loading a simple Essbase cube/database using the Type1 data load method. Our sample cube contains 2 dimensions LOB and YEAR. There is also another ACCOUNT dimension with only one measure AMOUNT. Our fact table contains data in the below shown format

    

So, import this table into the source. Then import our target Essbase cube (in our case, the dimensions of the cube have already been loaded i.e the essbase cube already has an outline). When you import an Essbase Cube ensure that you are importing it as Type1.

    

    

And finally, this is how your final cube would look like within Hyperion DIM.

    

Now, create a direct one to one mapping between your fact table and your Essbase Cube

    

Once this is done, just execute the above mapping using the Workflow Manager.

    

In the session log you can find out how many rows have been loaded and applied. Now, the question is how does this work. Since, we already have the outline defined, what Essbase Adapter does is it checks the lowest levels of each dimension hierarchy and loads the corresponding data from the fact table. And then it builds up the other summaries based on what summarization has been defined for each of the levels.

    

Well another release looms, 10.1.3.3.3 should be out within the week. Another point point point point release that we have squeezed some new stuff into. Nothing major but useful none the less - bigger things will come with 10.1.3.4 later in the year, will have to save those for now. I must thank Leslie for the majority of the following text, where would we be without her!

So, whats in there?

1. Bundling of DataDirect drivers - we have noticed that folks have been struggling when it comes to setting up connections to databases other than Oracle. The database vendors JDBC libraries do not always work as we expected leading to issues when you are either retrieving data or setting up the scheduler schema. So we are now shipping drivers ourselves for

• IBM DB2 v8.1, v9.1
• Microsoft SQL Server 2000, 2005
• Oracle 9, Oracle 10g

To help on the scheduler setup front the admin page has been updated to help you out a bit more.

2. Link to this report now configurable - I wrote about this feature a while back. Its the ability to get the URL shortcut to a specific report. Its neat but not all of you wanted it available to your users. So it's not configurable on a per report basis.

3. RTF Output Enhancements - as many of you have seen all output formats are not created equal with BIP. PDF outputs get everything we can get into them - the RTF output came in a poor second. We have addressed some of those shortcomings. We now support the following in the RTF output :

• "Keep Paragraph with the Next Paragraph" - The RTF standard includes a command that enables you to "keep a paragraph with the next paragraph." This command prevents a page break between the paragraph for which it is enabled and the following (or next) paragraph
• Keep Paragraph Intact - The RTF standard includes a command that enables you to keep all the lines of a paragraph together ("keep paragraph intact") and prevent the paragraph from breaking across pages.
• Support for Table "Heading Rows Repeat" - The RTF standard includes a command that enables the repeat of the table heading rows when the table breaks across pages.
• Support for "Table Row Keep Together" - The RTF standard includes a command that enables you to prevent a table row from breaking across pages.

3. Support for Multiple Cascading Parameters - Cascading parameters enable you to filter available values for each parameter in a set of parameters that have a relationship. The choice you make from the first parameter in a set of cascading parameters determines the available values for the next parameter. In this way, you can filter choices down to a manageable number for each cascading parameter. Previously only two levels of cascading parameters could be supported. BIP now supports cascading parameters beyond two levels.

4. Support for Vertical Alignment of External Graphics - Support for the attribute "vertical-align" for the FO element "external-graphic" has been added in the 10.1.3.3.3 release of BI Publisher. This enables you to format the alignment of an externally referenced image inline with text. This attribute can be applied when coding an XSL-FO template or an RTF template. The valid values for the attribute are:

• bottom
• top
• middle
• text-top
• text-bottom

5. Support for Multiple Currency Symbol Displays in Report - The format-currency function now accepts a third parameter, "displaySymbolOrNot" to enable the display of multiple currency codes in a single report. The International Standards Organization (ISO) currency code must be present in the report data to pass to the format-currency function.
This is pretty cool, you can now get BIP to include currenct symbols next to your values in a report.

Some nice enhancements for a minor release, once it is released I'll cover some of the features in more detail.

Following on from the previous post, a little glitch in the plans: the instance appeared to crash late last night during the load. Hopefully not some exotic bug associated with dynamic sampling. I modified the delete-and-lock strategy yesterday after remembering that we have a number of indexes that are created with the “compute statistics” option. Since DBMS_STATS.LOCK_TABLE_STATS is [...]

As you might probably be aware, APEX (Application Express) has support for web services. One can call web services and can create several sequential processes out of them. There can be lots of use cases for these web service calls. One of them is to have a custom integration of BI EE within APEX environment. If you had worked on APEX and BI Publisher integration (using Web Services) as documented here excellently by Tyler Muth you would have noticed that APEX can identify BI Publisher web services out of the box since APEX web services have only a single binding i.e all the methods can be identified by using a single root service. But in the case of BI EE, it produces a multiple binding WSDL. APEX does not support multiple binding web services currently. So, the question is how do we go about calling BI EE methods from within APEX? Thats where a tool called SOAP UI comes to our rescue. I came across this tool while going through Tyler’s blog entry above and found this free tool to be very useful to identify end points of SOAP requests. Till now, i was using another tool called Mindreef SOAPScope. Though mindreef is also good, the major drawback was that they did not give a free version. They only provide you an evaluation copy. Use the SOAP UI tool and create a project using the BI EE WSDL below.

http://localhost:9704/analytics/saw.dll?WSDL

If you want you can also include the BI Publisher WSDL to know the difference between a single binding and Multi binding web services.

    

Now, just to get us started, lets try to authenticate into BI EE using the Logon Method of the SAWSession service and then use that session to execute an ibot by using the excuteIBotnow method of the IBotService from within Application Express. Since APEX, does not support multi binding web services, we would have to treat each and every service (different bindings) of the BI EE WSDL as a seperate WSDL in itself. So, go to an APEX application and click on Shared Components -> Logic -> Web Service References. Now click on create and do not search for the WSDL in the UDDI. In the next step, instead of entering the WSDL click on create Web Service Reference Manually. Remember, we would have to create one reference for each and every web service. Following are the URLs that you would have to use for each service

XMLViewService - http://localhost:9704/analytics/saw.dll?SoapImpl=xmlViewService
SecurityService - http://localhost:9704/analytics/saw.dll?SoapImpl=securityService
WebCatalogService - http://localhost:9704/analytics/saw.dll?SoapImpl=webCatalogService
SAWSessionService - http://localhost:9704/analytics/saw.dll?SoapImpl=nQSessionService
JobManagementService - http://localhost:9704/analytics/saw.dll?SoapImpl=jobManagementService
ReplicationService - http://localhost:9704/analytics/saw.dll?SoapImpl=replicationService
ReportEditingService - http://localhost:9704/analytics/saw.dll?SoapImpl=reportService
IBotService - http://localhost:9704/analytics/saw.dll?SoapImpl=ibotService
HTMLViewService - http://localhost:9704/analytics/saw.dll?SoapImpl=htmlViewService
MetadataService - http://localhost:9704/analytics/saw.dll?SoapImpl=metadataService

In our case since we want to authenticate and trigger an ibot, we would have to use 2 services. They are SAWSession service and IBotService. While creating a web service reference for the saw session service, use the below XML (you can obtain this from the SOAP UI tool).

<soapenv:Envelope xmlns:soapenv="<a href="http://schemas.xmlsoap.org/soap/envelope/">http://schemas.xmlsoap.org/soap/envelope/</a>" xmlns:v5="com.siebel.analytics.web/soap/v5">
   <soapenv:Header/>
   <soapenv:Body>
      <v5:logon>
         <v5:name>Administrator</v5:name>
         <v5:password>welcome1</v5:password>
      </v5:logon>
   </soapenv:Body>
</soapenv:Envelope>

In the above XML, Administrator and welcome1 are the username/passwords for my BI EE instance. If you want you can replace the actual values with #ITEMNAME# to populate them dynamically based on page/application items.

    

Remember, to enter a collection name for storing the reponse. This is very important since we would have to extract the session id and pass it on to another service. In my case, i had used WSDL_COLLECTION as the name of the collection (Dont worry about creating these collections. These are automatically created. All you need to do is to enter the name. you can get what is stored in the collection by querying the APEX_COLLECTIONS view). Similarly use the below XML for the executeiBotnow method of the IBotService.

 <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v5="com.siebel.analytics.web/soap/v5">
<soapenv:Header/>
<soapenv:Body>
<v5:executeIBotNow>
<v5:path>/users/administrator/_ibots/Test</v5:path>
<v5:sessionID>#F101_SESSIONID#</v5:sessionID>
</v5:executeIBotNow>
</soapenv:Body>
</soapenv:Envelope>

Here, replace the v5:path tag value with the ibot path that you need to execute. Since session ids are dynamic, we would have to reference an application level item (F101_SESSIONID) which would be populated by the SAWSession service. Again, enter a collection name like WSDL_COLLECTIONSCHED for storing the result. Once these 2 web service references have been created, create a page process to call the Logon Method of the SAWSession service.

    

Make this service to be conditionally called on the submit of a button (Login Button). Whenever each of the service is called from within APEX, the result is stored in a collection. Since this stores the result of a web service response, it would be stored as a CLOB in the CLOB001 column of the APEX_COLLECTIONS view. Typically the response of the 1st service would be like the one shown below.

<?xml version="1.0" encoding="UTF-8" ?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:sawsoap="com.siebel.analytics.web/soap/v5">
<soap:Body>
<sawsoap:logonResult>
<sawsoap:sessionID xsi:type="xsd:string">ihdcqbtmm8q06ii2opb7qhh44npfqh5v0liad5izOr07UFe9W00</sawsoap:sessionID>
</sawsoap:logonResult>
</soap:Body>
</soap:Envelope>

As you see the sessionid would be embedded in the SOAP response. The most common method to extract the session id is to write a simple XPath query with the corresponding namespaces. But in our case, we would not be doing that since the resultant SOAP response is a constant. So, let write a simple query as shown below to extract the session id from the above XML.

SELECT SUBSTR(DBMS_LOB.SUBSTR(CLOB001,4000,1),392,INSTR(DBMS_LOB.SUBSTR(CLOB001,4000,1),'</sawsoap:sessionID>')-392) FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'WSDL_COLLECTION'

Use the above query to populate the F101_SESSIONID application level item. Once this is done, create another page process to call the executeIBot method of the IBotservice. Make this process to be executed after the execution of SAWSessionservice page process. Now this would automatically trigger the ibot.

    

This should give you an idea of how to go about calling BI EE web services from APEX. Lets look at more complex scenarios lin future blog entries.

Almost 70% of you responded that you do not have Oracle installed at home. That was a bit of a shocker to me.

I figured that it would be the other way around; people who read blogs, Oracle blogs specifically, would be more likely to have Oracle installed at home.

So, why not?

I am absolutely sick and tired of dealing with issues where misleading partition statistics (for example) cause ETL queries to go crazy and try to sort-merge tables with tens of millions of rows each. Since we’re about to run a test load on production data I’m going to drop all table and index statistics and lock [...]

Account receivables can have a big impact to the cash flow of an organization. Bad credit can hurt your company’s bottom line. Manging customer credits help the company to manage the risk and avoid the issues.

In this post, I will touch credit check and credit limit, centralized and decentralized credit management, and credit limit currency.  This is a a result of studying various credit management features in the OLTP system. The objective is to understand how the these various system works and understand how the credit management process looks like, and how BI can help in these processses.

Credit Check and Credit Limit

In order to manage the customer credit, an organization should establish the credit policy and enforce credit limit for their customers. Credit limits are normally specified by credit management staff in the individual customer profiles. Credit limits are initially established based on the default policy and may involve in doing a credit check on the customers. The initial credit check typically gets the credit information from external data provider. OLTP may handle this business flow by allowing integrating the external data.

Credit Limit Management: Centralized and Decentralized Process

A OLTP may allows the multiple credit limits to be entered for a customer. A customer may order goods and services from different regions or lines of business of the deploying organization. In JD Edwards (EnterpriseOne, or just E1), the deploying organizations is allowed to use Line of Business processing. A customer can be assigned to one of more “E1 companies”. Multiple E1 companies can share the same customer record but can also track and store their specific information by E1 companies.

In E-Business Suite (EBS), credit limits are held by customer profiles. The EBS “operating unit” can be used to represent the regions or lines of business. Multiple EBS operating units can share the same customer record while each operating units can establish their individual customer profiles. This can be done in two ways: 1- creating a customer account for each EBS operating unit or 2- override the customer profiles at the site use level.

PeopleSoft use set based reference sharing mechanism to share the customer credit information.

SAP offers a sophisticated control by allowing centralized and decentralized credit management process. The idea is similar to the set based reference data sharing in PeopleSoft applications. The customer credit information is entered against the customer master and a credit controlling area. A credit controlling area is an (dummy) organization unit by which the customer credit is monitored and managed. Each credit controlling area can be linked to one or more SAP “company codes”.

Credit Limit Currency

The currency used to control credit limit may be different from the currency used in the organization unit that the transaction is entered against. The currency can also be different from the transaction currency entered in the customer invoice. This rule is applicable to all systems that I examined.

E1allows the customer credit limit shared across all “E1 companies” when the Line of Business process is not used. A currency code entered directly on the customer record determines the currency code in which the credit limit is entered.

In EBS, a customer profile can have multiple credit limit amount in different currency codes. In EBS order management module, a credit usage rule set can be defined to list all the transaction currency codes that share a customer profile credit limit amount. Any transactions entered under the specified list of currencies will be converted to the credit limit currency for credit limit check.

In SAP, each credit controlling area include a currency code which is used to manage the customer credit. The system converts the receivable transactions from the transaction currencies to the currency of the credit controlling area. Each customer for each SAP company code can only have one credit area so the transaction currency only need to be converted to that credit control area currency.

How are the customer credit limit profiles used in Business Intelligence?

Each customer can have the account receivable balances organized by the customer credit profiles. If the credit profile is established by credit controlling area, the customer account balance can also summarized by the credit controlling area.

The account receivable balance can be converted to the customer credit profile amount currency. If the customer profile is estalished by credit controlling area, the currency from the control area will be used to summarized the AR account balance.

A few days ago, Farooq posted some comments on an old post of mine. The final comment in our exchange concerned storing activities in our data warehouse by time (actual hour and minute). This is quite a common requirement, especially where the performance of customer service agents is being measured (as in Farooq’s question)

I would be tempted to split the event date and time into two separate dimensions a ‘date’ dimension with a granularity of day and a ‘time’ dimension that has sufficient resolution to capture the events of interest; there is no date component in the time dimension, for example events that occurred at 09:18:00 on May 6 2008 will share the same time dimension key as events that occurred at 09:18:00 on February 5 2008. This approach keeps the time dimension compact, we only need to key the possible time values that occur in a single day, and more importantly it opens up the possibility of simple time range queries aggregating over many days.

I participated in an interview today. One of the questions I usually ask is "Do you have Oracle installed at home?"

It's more a question to gauge their nerdiness than anything, see if they're obsessed like me. Does it always mean that they aren't smart or capable? No, not really. That will come out through the other technical questions. For me at least, it does indicate a curiousity about how the software works.

So, do you have Oracle installed at home?

Scouring the forum while listening to web demos today and came across a post from 576489, a mystery poster - who are you Sir or Madam?

Anyhoo, the title of the post was 'Limitations in excel output' he/she was asking me to provide some more info on the Excel output we provide. As many of you know or have noticed - our Excel output for all flavors of BIP/XMLP is not true binary Excel output. It's actually XHTML with the mime type set  to get Excel to open it. Understand its not Excel HTML but XHTML - read non lazy HTML. So, there is going to be a limit in what you can generate in the Excel output. Some Q & A:

Q1. Can I have formulae or macros in the Excel ouput?
A1. No

Q2. Can BIP use multiple sheets in the Excel output?
A2. No

Q3. What happens when the number of rows generated exceeds the Excel maximum?
A3. BIP will truncate the data

Q4. Where are the images in the Excel, I had some in my template?
A4. You need to set a couple of properties to allow use to access them. Yes, you need to be connected to the network to get the images.

Base image URI html-image-baseuri Base URI which is inserted into the src attribute of the image tag before the image file name. This works only when the image is embedded in the template. Image file directory html-image-dir Enter the directory for XML Publisher to store the image files that are embedded in the template.

With standalone 10.1.3.4 the Excel output is actually MHTML where we can embed the images directly in the HTML file so you don't need the properties set.

Q5. Can I generate master-detail Excel outputs?
A5. Yes, you can build multi level report outputs in the Excel output. 

Q6. I have an account number in my data but Excel treats it as a numuric value (right aligning, etc) - I want it left as a string value?
A6.  Either add a <space> character next to the field holding the account value or use the concat function to add the space to the value i.e. concat(MYVALUE,' '). This will force Excel to treat the value as a string. 

Q7. When opening an Excel output in IE7 from BIP the browser starts to open a window, then beeps and closes the window?
A7. Thanks to Ed on this one:

1. Select 'Tools' -> 'Internet Options' -> 'Security' (tab) -> 'Trusted Sites' -> 'Custom Level' (button) -> 'Downloads' from the browser menu.
2. Set 'Automatic prompting for file downloads' to 'Enable'.
3. Save the setting and close the browser window.
4. Start a new browser session and try again. 

Q7. Are things going to get better with Excel formats?
A7. 
      a. With standalone 10.1.3.4 the Excel output is actually MHTML where we can embed the images directly in the HTML file so you don't need the properties set.
      b. The standalone release also has the Excel Analyzer that allows you to create a binary Excel template that can be run on the server and delivered to users.
      c. We have Excel templates in EBS R12 and discussions to get things going in 11i.

Our Excel is not true Excel and it has a limited set of functionality but for many users its enough just to get the data out into Excel. If you have more questions, post em in comments and I'll update the document.

As you might probably be aware, JDeveloper 11g Tech Preview 4 is already out for everyone to download. I am not sure how many of you actually noticed this, but in Tech Preview 3 of JDeveloper a new integration between JDeveloper and BI EE was added(Strange that this was not announced anywhere since i found this out by chance) But the problem with that release(not a release per se since it is only a Tech Preview) was it was still buggy at best since the ADF components and BI EE somehow did not work together well (atleast on the instances that i tried). But now that TP4 is out, i thought i would try out the integration again. Alas, this did not work out again!!! There are still some issues with the integration. This blog entry is a compilation of my findings. This integration should give you an idea of how BI EE is going to be integrated into a majority of Oracle’s other suite of products. One can now drag and drop BI EE components like reports, dashboards etc directly into ADF-JSP pages to generate custom pages. For example look at the screenshot below,

    

As you see above, one can add connections directly to a Jdeveloper instance. That connection can automatically recognize the web catalog components of BI EE. One other very interesting feature is that one can model business components with BI EE as a data source. For example, if you are aware of the BI EE and BIP integration, you can query a report and add a template to that report. On the same lines one can use a BI EE report as a base and then one can add multiple views on top of it. These views will use the JDBC driver of BI EE.

    

    

One other very important feature that i noticed was the use of “no authentication” for BI EE reports. If you have worked with BI Publisher or Oracle Portal, you would be aware of the fact that one can expose individual reports/webpages to external users without authentication. Till now BI EE did not have this feature. I was under the impression that this is not available in 10.1.3.3.2 version of BI EE too. But when i looked closely at the integration between BI EE and JDeveloper, i noticed that while rendering BI EE reports there was an usage of a special URL parameter called NoAuthGo for all the reports. This i believe would give the Public Pages/Guest folder feature of Oracle Portal/BI Publisher to BI EE. But when i tried using it directly as below

 http://localhost:9704/analytics/saw.dll?NoAuthGo&path=/shared/Paint%20Demo/Prompts%20Across%20SAs/Report%201

and it gave me the below page

    

What struck me was the error message.

“If you would like to use this powerful capability, please contact this site’s administrator”

I believe there has to be an undocumented parameter in instanceconfig.xml (most probably under the AUTH tags) to enable this feature. If anyone knows what that parameter is, feel free to share it in the comments section. As far as i see, the integration is based on using the URL parameters which is well in line with my observations here. Excellent piece of integration and still more to come in the coming releases!!!.

Oracle Docs New Look

Today I opened my List of Books page, opened up the Java Developer's Guide and voila!, a new look:



I had seen this in the 11g documentation but this was the first time I had seen it spread elsewhere. I can't say I like it either. Too fancy.

That's scary...change scares me? What? Oh well, I'll get used to it and someday wonder how I lived without it, just like the iPhone!

Spent some time this morning with one of our star developers ... well they all are in my opinion. But Incheol has been tasked with coming up with the BIP Fusion integration. Things are changing in Fusion with respect to the BIP architecture and for end users when it comes to reporting but its good change I assure you.

Rather than the embedded approach we have right now in 11i/12, where templates are managed by BIP but report defnitions are in the realm of the concurrent processing UI and schema. In Fusion there will be complete report definitions managed by the BIP server ie extracts, templates, parameters, etc. Im not getting into the details of LOVs here, in 11i/12 LOVs or ValueSets are not just used in report submission but get leveraged all over the place so we're still nailing the 'where, when and how' down for those, so dont ask ... yet!
With the server on the outside of apps the development process will be much much smoother and we think faster, without the need to go to a report definition form in one app, then to an extract definition program, then to a layout definition program, then to the XMLP UI to upload said objects - its all in one place, the BIP server with, data extract building tools, connectivity to the desktop template building tools and even an online template builder so you never need to leave the comfort of your favorite browser!

Getting back to Incheol, he has been working on some of the integration between BIP and Fusion Apps and how developers will create reports and make them available to users and then how users will be able to invoke said reports. There are 3 scenarios that have been identified so far:
1. Ability to schedule a report
2. View an existing instance of a previously run report
3. Run a report from a link ie run it immediately

The first and second are being worked on in conjunction with the scheduling team, Incheol has been looking at the third scenario from an end user perspective but focusing on the developer experience.

The links to reports in this image will not sit in their current position - there are plans to have a common region on the page that will list pertinent reports for the page or the users current role - so ignore the position of the links. Its whats going on under the links thats important. When the link is clicked the data from the associated ADFbc component ie a VO is extracted and the relevant report is called via a BIP API that in turn calls the report on the server via a web service call. The data is passed along with template and output required, the server runs the report and returns the output requested.

This is a little different to other scenarios where the report is called and the BIP server extracts, formats and delivers. Here we are essentially telling the BIP server that we already have the data so just format it for us and pass back the output so dont deliver it.

The returned report will have all of the data present in the VO and not just what you can view on the page itself. The ADFbc will handle all the security required to generate the appropriate data set for the current user. We'll be turning this into a common component that development teams and customers alike can embed in their pages.

A small glimpse of whats coming and as we get more concrete I'll try and share more ...    

If you had gone through my blog entry here, i would have talked about using UDML as a way for automating migration from dev to test/prod environments. But the major problem with UDML is that one cannot update the passwords of Connection Pools, Users etc since the UDML expects them to be encoded. In such a case, for updating passwords alone we can use an undocumented command line switch option for the AdminTool. For example, look at the screenshot below.

    

This is nothing but the connection Pool property of the ORCL database. Here, we would like to change the schema names and its corresponding passwords. For example, we would like to change the schema to SH and also would like to change the password. In order to do that, open a text editor and enter the below command. Save the file in the same directory as the Admin Tool ({OracleBI}\Server\Bin). If not, then you would have to give the full path of the file.

Open Dev_Machine.rpd Administrator Administrator
SetProperty "Connection Pool" "orcl"."Connection Pool" "User" "sh"
SetProperty "Connection Pool" "orcl"."Connection Pool" "Password" "welcome1"
Save
Exit

Now, open up a command prompt and navigate to {OracleBI}\Server\Bin. Typein in the below command,

 admintool /command commandlineswitch.txt

    

This would automatically update the connection pool values.

    

You can use the above to update many objects within the repository. For updating passwords for users, just use the nqschangepassword.exe tool. For more details on this, check my blog entry here. So, on a high level your entire migration process would look as shown below

    

Thanks to Phil for sharing this.

Continued from my previous post.

While discussing external tables with my feisty colleague some time back, I explained that I liked using them but I couldn't figure out how to change the file name to match that of what was defined in the table definition.

Colleage to the rescue:

ALTER TABLE table_name LOCATION ( 'new_file_name.csv' );

Cool!

As I mentioned before, I had 4 files types I had to read: 820 and 835, both of the x12 format and two custom file layouts (flat files essentially). Since no one in the group knows Java yet, I wanted to keep the Java portion of the application as small as possible. So with the two custom files, I decided to use external tables. I could then put into practice the above ALTER TABLE statement.

As I looped through the list of files to be processed, I would issue an EXECUTE IMMEDIATE so that I could then SELECT from the table in the next step. It worked like a charm.

As I was doing some testing, I would issue the ROLLBACK statement to clear the tables for the next run. When I verified, there was still data there. WTF?

Oh wait, there's an EXECUTE IMMEDIATE...which runs DDL...which COMMITs...barnacles!

So I couldn't use that new thing I learned, oh well. Fortunately UTL_FILE does have the ability to rename files so I picked a name like 'external_table_file_name.txt' and rename the incoming file to that, then SELECT. Works like a charm.

Got a good one today that had me a little stumped until I RTFM! To be more precise RTFJD - the JD is the javadoc. The data engine we have does not only consume data templates but you can also throw SQL at it and get XML in return. Neat! One of our customers has been struggling with this, they want to be able to pass parameters to the sql at runtime - we support that but they were having pain getting it to work. Our trusty support guys have been nagging me for some help and I finally got to it last night.

The data engine has a setSql method to allow you to pass the sql and you can then pass parameters as a Hashtable or ArrayList thus:

DataProcessor dataProcessor = new DataProcessor();
 dataProcessor.setConnection(jdbcConnection);
 //Set the query
 dataProcessor.setSql("select * from departments where department_id = :p_DeptNo");
 //Create hash to hold param val
 Hashtable parameters = new Hashtable();
  parameters.put("p_DeptNo","10");
 dataProcessor.setParameters(parameters);
 dataProcessor.setOutput("c:\temp\EmpDetails.xml");
 dataProcessor.processData();

Looks straight forward enough but it would not compile or run. I scratched my head for a while. I know we dont docunment the setSql method that well. I ended up in the javadoc and found my problem. I was using the java.util.Hashtable. Hey, I know it, I use it regularly so why not. However the data engine actually needs the Collections version of the Hashtable - com.sun.java.util.collections.Hashtable .. dang! A quick update on the import statements et voila, I have XML coming out based on parameter values. A good lesson for me to read all the doc and apologies to support and the customer for taking so danged long about it!

If you're interested here's the complete code for the method:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.sun.java.util.collections.Hashtable;
import oracle.apps.xdo.dataengine.DataProcessor;
import oracle.jdbc.driver.OracleDriver;


public class DTSql {
    public DTSql() {
    
        try {
         //get connection
        Connection jdbcConnection = getConnection();
         //Initilization//
         DataProcessor dataProcessor = new DataProcessor();
        dataProcessor.setConnection(jdbcConnection);
          //Set the SQL
          dataProcessor.setSql("select * from departments where department_id = :p_DeptNo");
          //Create hash for parameters
          Hashtable parameters = new Hashtable();
          parameters.put("p_DeptNo","10");
          dataProcessor.setParameters(parameters);
          dataProcessor.setOutput("c:\temp\EmpDetails.xml");
          dataProcessor.processData();
        } 
        catch (Exception e)
         {
         e.printStackTrace();
         }
  
    }
        public static Connection getConnection() throws SQLException {
            String username = "hr";
            String password = "hr";
            String thinConn = "jdbc:oracle:thin:@IPG.US.ORACLE.COM:1525:ora10g";
            DriverManager.registerDriver(new OracleDriver());
            Connection conn =
                DriverManager.getConnection(thinConn, username, password);
            conn.setAutoCommit(false);
            return conn;
        } 
    public static void main(String[] args) {
        DTSql dTSql = new DTSql();
    }
}