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 220.127.116.11 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/18.104.22.168/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 22.214.171.124.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 126.96.36.199.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 188.8.131.52.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 184.108.40.2069 supports the following Essbase versions:
Issues resolved in version 220.127.116.119:
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. (18.104.22.1681)
- Removed the "/" character as a switch for command line arguments as it causes problems in Linux. (22.214.171.1245)
- 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 (126.96.36.1991)
- Command line operations resulted in an error due to an improper attempt to interact with the GUI progress bar. (188.8.131.521)
- Shared members attributes where not be properly written resulting in a delimiter/column count mismatch. (184.108.40.2065)
- 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.