BI & Warehousing
I have taken to recording screen grabs to help some folks out on 'how do I' scenarios. Sometimes a 3 minute video saves a couple of thousand words and several screen shots.
So, per chance you need to know:
1. How to add a page border to your output and/or
2. How to add an under line that runs across the page
Watch this! https://www.youtube.com/watch?v=3UcXHeSF0BM
If you need the template, sample data and output, get them here.
I'm taking requests if you have them.
A rather not so great post about an ORA-00600 error i faced on a standby database. Environement was 188.8.131.52 on Sun Super Cluster machine. MRP process was hitting ORA-00600 while trying to apply a specific archive log.
The error message was something like this
MRP0: Background Media Recovery terminated with error 600 Errors in file /u01/app/oracle/product/184.108.40.206/diag/diag/rdbms/xxxprd/xxxprd1/trace/xxxprd1_pr00_6342.trc: ORA-00600: internal error code, arguments: , , , , , , , , , , ,  Recovery interrupted!
Some googling and MOS searches revealed that the error was due to corrupted archive log file. Recopying the archive file from primary and restarting the recovery resolved the issue. The fist argument of the ORA-600 is actually the sequence no of the archive it is trying to apply.
This was my final year in an official capacity which was a lot more difficult to deal with than I had anticipated. Here's my record of service:
- 2010 (2011, Long Beach) - I was on the database abstract review committee run by Lewis Cunningham. I ended up volunteering to help put together the Sunday Symposium and with the help of Dominic Delmolino, Cary Millsap and Kris Rice, I felt I did a pretty decent job.
- 2011 (2012, San Antonio) - Database track lead. I believe this is the year that Oracle started running the Sunday Symposiums. Kris again led the charge with some input from those other two from the year before, i.e. DevOps oriented
- 2012 (2013, New Orleans) Content co-chair for the traditional stuff (Database, APEX, ADF), Interview Monkey (Tom Kyte OMFG!), OOW/ODTUG Coordinator, etc.
- 2013 (2014, Seattle) Content co-chair for the traditional stuff (Database, APEX, ADF), Interview Monkey, OOW/ODTUG Coordinator, etc.
- 2014 (2015, Hollywood, FL) Content co-chair for the traditional stuff (Database, APEX, ADF)
This has been a wonderful time for me both professionally and, more importantly to me, personally. Obviously I had a big voice in the direction of content. Also and maybe hard to believe, I actually presented for the first time. Slotted against Mr. Kyte. I reminded everyone of that too. Multiple times. It seemed to go well though. Only a few made fun of me.
I was constantly recruiting too. "Did you submit an abstract?" "No, why not?" and I'd go into my own personal diatribe (ignoring my own lack of presenting) into why they should present. Sarah Craynon Zumbrum summed it up pretty well in a recent article.
But it was the connections I made, the people I met, the stories I shared (#ampm, #cupcakeshirt, etc), and the friends that I made, that's what has had the most impact on me. Kscope is unique in that way because of it's size...at Collaborate or OOW, you'll be lucky to see someone more than once or twice, at Kscope you're running into everyone constantly.
How could I forget? #tadasforkate! This year was even more special. For those that don't know, Katezilla is my profoundly delayed but equally profoundly happy 10 y/o daughter. Just prior to the conference her physical therapist taught her "tada!" and Kate would hold her hands up high in the air and everyone around would yell, Tada! I got this crazy idea to ask others to do it and I would film it. Thirty or forty videos and hundreds of participants later...
So a gigantic thank you to everyone who made this possible for me.
Here's a short list of those that had a direct impact on me...
- Lewis Cunningham - he asked me to be a reviewer which started all of this off.
- Mike Riley - can't really say enough about Mike. After turning me away a long time ago (jerk), he was probably my biggest supporter over the years. (Remind me next year to you tell you about "The Hug."). Mike, and his family, are very dear to me.
- Monty Latiolais (rhymes with Frito Lay I would tell myself) - How can you not love this guy?
- Natalie Delemar - Co-chair for EPM/BI and then boss as Conference Chair.
- Opal Alapat - Co-chair for EPM/BI and one of my favorite humans ever invented. I aspire to be more organized, assertive, and bad-ass like Opal.
What's the future hold?
I have no idea. My people are in talks with Helen J. Sander's people to do one or more presentations next year, so there's that. Speaking of which...it's in Chicago. Abstract submissions start soon, I hope you plan on submitting. If you're not ready to submit, I hope you take try to take part in shaping the content by finding one of about 10 abstract review committees. Who knows where they may lead you?
Finally, here's the It's a Wrap video from Kscope15 (see Helen's story there). Here's Kscope16's site. Go sign up.
- Fixed an issue where the username and password passed via the command line were improperly logged
- Fixed an issue reading MaxL XML data sources when the alias or UDA contained xml encoded characters such as the ampersand (&) character.
- Updated labels on the Input Source tab of the user interface to clarify their purpose.
One of the new features introduced in 220.127.116.11.0 is WebCenter Content (WCC) Server as a delivery channel. Prior to this release, we could manage delivery to WCC (formerly UCM) server using webDAV as explained by Tim in his blog "BI Publisher and WebDAV... done!". However, there were few restrictions
- No way to include standard or custom metadata. Therefore, there was no description for the documents submitted and searching these documents in WCC was not convenient.
- WebDAV uses folders to store the document and access to these folders have to be pre-configured by WCC Administrator. User can not select security group or account at the time of scheduling.
Moreover, with WCC as delivery channel you have the ability to use idc(s), http(s) and JAX-WS protocols. Refer to the documentation on RIDC protocols for more details on these protocols.
We have now videos in BI Publisher Youtube Channel to demonstrate how BI Publisher integration with WCC works. The videos are split in two parts: Part 1 (Title: BIPublisherWCC Part1) covers an overview and explains some of the WCC concepts, while Part 2 (Title: BIPublisherWCC Part2) walks you through all the steps necessary to make the integration work.
You can find additional details in the documentation guide. Navigate to the Books link and check the Administrators Guide for setting up delivery destinations and the Data Modeling Guide for Custom Metadata & Bursting related information.
I am sure you will find this new feature very easy to configure and very useful for maintaining documents in WCC. Have a nice day !!
I am happy to announce that Oracle BI Publisher 18.104.22.168.0 is released, although I admit that this is almost a week old news now and I am sure some of you may have already known this by now from the BI Publisher homepage in OTN or from other sources. My sincere apologies for the delay here.
Thank you Tim for helping me to get back into this blog membership and being patient to allow me put this word out. My activity in the blog has been so less in the past that I am as good as a new member here. When I tried to login last week, I was greeted with this message -
"Sorry, you do not have the privileges necessary to access the page you requested. This system is available to Oracle Employees only. Oracle Employees who would like to request a blog account should click here."
So I had to start all over and get a fresh access created. Thanks to Tim and Phil from IT support for helping me with this. I will now make sure to use this space more often and share more features, tips and tricks.
Oracle BI Publisher 22.214.171.124.0 was GA on May 19th and you can get the download, documentation, certification matrix and release notes here at the BI Publisher home page in OTN. Here is a quick snapshot of new features in this release. The download is also available at Oracle Software Delivery Cloud site. The documentation page has also been given a fresh new structure where in the left navigation you will notice "Task" and "Books" as two menu items. The task will provide quick reference to role based activities under different sub menu items such as "View & Publish", "Design Reports" etc. The "Books" menu will take you to the complete set of books. You can select Administrator's guide, or Developer's guide, Data Modelling Guide, Report Designer's guide and User's Guide for BI Publisher here. If you are looking for any feature, and do not find information under "Tasks" then check for the same under "Books" or use the search option.
Stay tuned for more updates on new features. Wish you have a good time exploring the new features!!
I have just spent an hour or so working on getting a sample scheduleReport web service working with parameter values. There are a lot of examples out there but none I have found have the parameters being set. Our doc is a little light on details on how to set them up :) In lieu of that, here's this!
// Set the parameter values for the report. In this example we have // 'dept' and 'emp' parameters. We could easily query the params dynamically //Handle 'dept' parameter ParamNameValue deptParamNameVal = new ParamNameValue(); deptParamNameVal= new ParamNameValue(); deptParamNameVal.setName("dept"); // Create the string array to hold the parameter value(s) ArrayOfXsdString deptVal = new ArrayOfXsdString(); // For individual values or multiples, add values to the // string array e.g. 10,20,30 deptVal.getItem().add("10"); deptVal.getItem().add("20"); deptVal.getItem().add("30"); // Asterisk used for a null value ie 'All' //deptVal.getItem().add("*"); // add the array to the parameter object deptParamNameVal.setValues(deptVal); //Handle 'emp' parameter ParamNameValue empParamNameVal = new ParamNameValue(); empParamNameVal= new ParamNameValue(); empParamNameVal.setName("emp"); ArrayOfXsdString empVal = new ArrayOfXsdString(); // For individual values or multiples, add values to the string array // empVal.getItem().add("Jennifer Whalen"); // empVal.getItem().add("Michael Hartstein"); // Asterisk used for a null value ie 'All' empVal.getItem().add("*"); empParamNameVal.setValues(empVal); // add parameter values to parameter array ArrayOfParamNameValue paramArr = new ArrayOfParamNameValue(); paramArr.getItem().add(deptParamNameVal); paramArr.getItem().add(empParamNameVal); //Now add array to values obj ParamNameValues pVals = new ParamNameValues(); pVals.setListOfParamNameValues(paramArr);
The pVals object can then be added to the report request object.
Tim Hall has written some brilliant posts about getting going with writing (blogs, whitepapers etc). This post is the result of inspiration from there only. Tim says that just get started with whatever .
If you are into blogging and no so active or even if you aren’t you may want to take a look at all the posts to get some inspiration to document the knowledge you gain on day to day basis.
Here is an index to all the posts by Tim till now
The OlapUnderground Essbase Outline Viewer is a read-only user interface built specifically to view the contents of an Essbase outline. It is built in Microsoft .NET technology and should run on any Windows-based computer without any special downloads. It also uses the MaxL outline xml format and, as your Hyperion administrators can create and distribute the outline xml file, end users do not need an Essbase login to view the outline.
Here is a screenshot of the Outline Viewer:
Click image to see it full-sizeYou can download the Essbase Outline Viewer from our website at http://www.appliedolap.com/resources/downloads/essbase-outline-viewer. Let us know what you think!
My premise was that if you are depending on DISTINCT to return a correct result set, something is seriously wrong with your table design. I was reminded of this again recently when I ran across Kent Graziano's post on Better Data Modeling: Are you making these 3 beginner mistakes in your data models?. Specifically:
Instead of that, you should be defining a natural, or business, key for every table in your system. A natural key is a an attribute or set of attributes (that occur naturally in the data set) required to uniquely identify a row in that table. In addition you should define a Unique Key Constraint on those attributes in the database. Then you can be sure you will not get any duplicate data into the tables.
CLARIFICATION: This point has caused a lot of questions and comments. To be clear, the mistake here is to have ONLY defined a surrogate key. i believe that even if using surrogate keys is the best solution for your design, you should ALSO define an alternate unique natural key. So why MERGE?
I learned about the MERGE statement in 2008. During an interview, Frank Davis asked me about when I would use it. I didn't even know what it was (and admitted that) but I went home that night and...wait...I think he asked me about multi table inserts. Whatever, credit is still going to Mr. Davis. Where was I? OK, so I had been working with Oracle for about 6 years at that point and I didn't know about it. My initial reaction was to use it everywhere (not really)! You know, shiny object and all. Look! Squirrel!
Why am I considering MERGE a bug? Let me be more specific. I was working with a couple of tables and had not written the API for them yet and a developer was writing some PL/SQL to update the records from APEX. In his loop he had a MERGE. I realized at that moment there was 1, no surrogate key and 2, no natural key defined (which ties in with Kent's comments up above). Upon realizing the developer was doing this, I knew immediately what the problem was (besides not using a PL/SQL API to nicely encapsulate the business logic). The table was poorly designed.
Easy fix. Update the table with a surrogate key and define a natural key. I was thankful for the reminder, I hadn't added the unique constraint yet. Of course had I written the API already I probably would have noticed the design error, either way, a win for design.
Now, there are perfectly good occasions to use the MERGE statement. Most of those, to me anyway, relate to legacy systems where you don't have the ability to change the underlying table structures (or it's just cost prohibitive) or ETL, where you want to load/update a dimension table in your data warehouse.
Noons, how's that? First time out in 10 months. Thanks for the push.
I think that the process of building a data mining scoring engine is similar to develop an application.
We have the requirement analysis, functional design, technical design, coding, testing, deployment, etc. phases.
Version 126.96.36.1999 supports the following Essbase versions:
Issues resolved in version 188.8.131.529:
2015.02.15 - Issue 1355 - All Writers - Add functionality to replace all line feeds, carriage returns, tabs, and extraneous spaces in formulas
2015.02.13 - Issue 1354 - RelationalWriter - Changed the default database name from dodeca to extractor
2015.02.13 - Issue 1353 - RelationalWriter - Added CONSOLIDATION_TYPE_SYMBOL, SHARE_FLAG_SYMBOL, TIME_BALANCE, TIME_BALANCE_SYMBOL, TIME_BALANCE_SKIP, TIME_BALANCE_SKIP_SYMBOL, EXPENSE_FLAG, EXPENSE_FLAG_SYMBOL, TWO_PASS_FLAG, and TWO_PASS_FLAG_SYMBOL columns to the CACHED_OUTLINE_MEMBERS table
2015.02.13 - Issue 1352 - RelationalWriter - Added Server, Application, and Cube columns to the CACHED_OUTLINE_VERSIONS table
2015.02.13 - Issue 1351 - Fixed issue with LoadFileWriter where UDA column headers were incorrectly written in the form UDAS0,DimName instead of UDA0,DimName
In addition, a number of fixes, etc, were put into 2.0.2 and earlier releases and those releases went unannounced. Those updates included the following items:
- There is no longer a default .properties file for the Extractor. This will force a user to specify a .properties file. (184.108.40.2061)
- Removed the "/" character as a switch for command line arguments as it causes problems in Linux. (220.127.116.115)
- Fixed issue when combining MaxL input with relational output where a "not supported" error message would appear due to certain properties were not being read correctly from the XML file (18.104.22.1681)
- Command line operations resulted in an error due to an improper attempt to interact with the GUI progress bar. (22.214.171.1241)
- Shared members attributes where not be properly written resulting in a delimiter/column count mismatch. (126.96.36.1995)
- Added encoding options where a user can choose between UTF-8 and ANSI encodings. The Extractor will attempt to detect encoding from selected outline and, if the detected outline encoding is different from the user selected outline encoding, a warning message appears.
This issue, which often manifested itself with errors in the Essbase error 10420xx range, was caused by how the Essbase Java API communicated with the server. In essence, whenever a piece of information was needed, the Essbase Java API grabbed a port from the pool of available ports, did its business, and then released the port back to the pool. That doesn’t sound bad, but the problem occurs due to how Windows handles this pool of ports. Windows will put the port into a timeout status for a period of time before it makes the port available for reuse and the default timeout in Windows is 4 minutes! Further, the size of the available pool of ports is only about 16,000 ports in the later versions of Windows. That may sound like a lot of ports, but the speed of modern computers makes it possible, and even likely, that certain operations, such as the outline APIs, that call Essbase many, many times to get information would be subject to this issue. Frankly, we see this issue quite often with both VB and the Java Essbase Outline Extractors.
We brought this issue to the attention of the Java API team and assisted them by testing a prerelease version of the Java API jars. I am happy to report the fix was released with Essbase 188.8.131.52.502. In addition, there is a new essbase.properties setting that allows you to turn the optimization on or off:
It is our understanding that this optimization is turned on by default. I also checked the default essbase.properties files shipped with both Essbase 184.108.40.206.502 and 220.127.116.11 and did not see that setting in those files. It may be one of those settings that is there in case it messes something else up. The work of our own Jay Zuercher in our labs and searching Oracle Support seems to have confirmed that thought. There is apparently an issue where EIS drill-through reports don't work in Smart View if socket optimization is turned on. It is documented in Oracle Support Doc ID 1959533.1.
There is also another undocumented essbase.properties setting:
According to Oracle development, this value defaults to 300 ms but there should be little need to ever change it. The only reason it is there is to tune socket optimization in case more than 2 sockets are used per Java API session.
Jay also tested the 18.104.22.168 version in our labs with the Next Generation Outline Extractor. With the default settings, one large test outline we have, "BigBad", with about 120,000 members in it, extracted in 1 minute and 50 seconds. With socket optimization turned off, the same outline was only about 25% complete after 2 hours. In summary, this fix will be very useful for a lot of Oracle customers.
An email came in this morning to an internal mailing list,
We have an Essbase customer with some reporting requirements and we are evaluating BI Publisher as the potential solution. I'd like to ask for your help with any document, blog or white paper with guidelines about using BI Publisher with Essbase as the main data source.
Is there any tutorial showing how to use BI Publisher with Essbase as the main data source?
There is not one to my knowledge but trying to be helpful I came up with the following response
I'll refer to the docs ...
First set up your connection to Essbase
Then create your data model using that Essbase connection
Use the MDX query builder to create the query or write it yourself (lots of fun :)
Add parameters (optional)
Then build layouts for your Essbase query
annnnd your're done :)
Simple, right? Well simple in its format but it required me to know the basic steps to build said report and then where to find the appropriate pages in the doc for the links. Leslie saw my reply and commented on how straightforward it was and how our docs are more like reference books than 'how to's.' This got us thinking. I have noticed that the new 'cloud' docs have How do I ... sections where a drop down will then show maybe 10 tasks associated with the page Im on right now in the application.
Getting that help functionality into the BIP is going to take a while. We thought, in the mean time, we could carve out a section on the blog for just such content. Here's where you guys come in. What do you want to know how to do? Suggestions in the comment pleeeease!
Here is the official notification:
You can read the notification yourself in the Essbase 22.214.171.124 Readme.
Another messaging exchange last week with Leslie ...
OK, so we practised it a bit after our first convo and things got a little cheesy but hopefully you get the message.
Hit this link and you too can give some constructive feedback on the Oracle doc for BI (not just BIP.) I took the survey; its only eight questions or more if you want to share more of your input. Please take a couple of minutes to help us shape the documentation of future.
Today's post comes to you from Brent at STR Software. If you could help out, it would be greatly appreciated, read on ...
First off, if you are not familiar with the term SIG, it stands for Special Interest Group. OAUG facilitates a number of SIGs to bring users together that share common or industries concerning certain Oracle products.
Unfortunately, the BI Publisher SIG has been offline for a number of years and has not been given the attention it needs to be a useful resource for members of OAUG. Well... I'm getting the band back together and I need your help!
The SIG itself was formed to specifically focus on BI Publisher embedded in Oracle EBS, Peoplesoft and JD Edwards. I have put together a survey that is being emailed out to previous members of the SIG to get thoughts on how the SIG can be of service. That list is pretty old and YOU may not be on it, so if you are interested in participating in the SIG (or even if you are not), have a look at the link below and let me know your thoughts. Our first official meeting will be at Collaborate 15 in Las Vegas, hope to see you there!Take the survey -> here!