|Oracle Developer Champion
- 12-Jul-2018 12:34 - Brendan Tierney
Yesterday evening I received an email titled 'Invitation to Developer Champion Program'.
What a surprise!
The Oracle Developer Champion program
was setup just a year ago and is aimed at people who are active in generating content and sharing their knowledge on new technologies including cloud, micro services, containers, Java, open source technologies, machine learning and various types of databases.
For me, I fit into the machine learning, cloud, open source technologies, a bit on chatbots and various types of databases areas. Well I think I do!
This made me look back over my activities for the past 12-18 months. As an Oracle ACE Director
, we have to record all our activities. I'd been aware that the past 12-18 months had been a bit quieter than previous years. But when I looked back at all the blog posts, articles for numerous publications, books, and code contributions, etc. Even I was impressed with what I had achieved, even though it was a quiet period for me.
Membership of Oracle Developer Champion program
is for one year, and the good people in Oracle Developer Community (ODC) will re-evaluate what I, and the others in the program, have been up to and will determine if you can continue for another year.
In addition to writing, contributing to projects, presenting, etc Oracle Developer Champions typically have leadership roles in user groups, answering questions on forums and providing feedback to product managers.
The list of existing Oracle Developer Champions is very impressive. I'm honoured to be joining this people.
Click on the image to go to the Oracle Developer Champion website to find out more.
And check out the list of existing Oracle Developer Champions
In the world of self-service analytics, Tableau and Oracle Data Visualization are two tools that are often put on the same discussion platform. In the last year, the conversations surrounding these two tools have increased dramatically — with most of our clients using self-service analytics. In this blog, I am not going to do a comparison rundown between Tableau and Oracle DV. What I do want to show you is two similar tools which introduce exciting new possibilities: Tableau Prep and Oracle Data Flow.
Optimizer statistics are essential for good execution plans and fast performance of the SQL queries. Of course, this is also the case in the Autonomous Data Warehouse Cloud. But the handling of gathering statistics is slightly different from what we know from other Oracle databases.
Since a couple of days, I’m testing several features and behaviors of the Autonomous Data Warehouse Cloud (ADWC) to find out, how useful this Cloud platform solution is for real DWH projects and what has to be considered for the development of a Data Warehouse. To simulate a typical scenario, I’m running incremental load jobs into multiple target tables several times per day. The example I use for this is a Data Vault schema for a craft beer brewery (if you want to know more about the data model, watch this video I recorded last year). It the simulated environment on ADWC, I already sold 68 million beers until today – far away from what we sell in our real micro brewery. But this is not the subject I want to write about in this blog post.
More interesting than the data (which is mostly generated by DBMS_RANDOM) is the fact that no optimizer statistics were gathered so far, although the system is running since more than a week now. I play the role of a “naive ETL developer”, so I don’t care about such technical details. That’s what the Autonomous Data Warehouse should do for me.
Managing Optimizer Statistics in ADWC
For this blog post, I switch my role to the interested developer, that wants to know why there are statistics available. A good starting point – as often – is to read the manual. In the documentation of ADWC, we can find the following statements in the section Managing Optimizer Statistics and Hints on Autonomous Data Warehouse Cloud:
Autonomous Data Warehouse Cloud gathers optimizer statistics automatically for tables loaded with direct-path load operations. … If you have tables modified using conventional DML operations you can run commands to gather optimizer statistics for those tables. …
What does this mean exactly? Let’s look at some more details.
Statistics for ETL Jobs with Conventional DML
The automatic gathering statistics job that is executed regularly on a “normal” Oracle database, does not run on ADWC. The job is enabled, but the maintenance windows of the scheduler are disabled by default:
This is a good decision, because many data warehouses are running ETL jobs in the time frame of the default windows. Statistics gathering in a data warehouse should always be part of the ETL jobs. This is also the case in ADWC. After loading data into a target table with a conventional DML operation (INSERT, UPDATE, MERGE), the optimizer statistics are gathered with a DBMS_STATS call:
Only schema and table name must be specified as parameter. For all other settings, the DBMS_STATS preferences are used. Four of them are defined differently per default in Autonomous Data Warehouse Cloud:
- INCREMENTAL is set to TRUE (default: FALSE). This is only relevant for incremental statistics on partitioned table. Currently, Partitioning is not supported on ADWC, so this preference has no impact.
- INCREMENTAL_LEVEL is set to TABLE (default: PARTITION). This is relevant for partition exchange in combination with incremental statistics and therefore currently not relevant, too.
- METHOD_OPT is set to ‘FOR ALL COLUMNS SIZE 254’ (default: … SIZE AUTO). With the default setting, histograms are only gathered if a column was used in a WHERE condition of a SQL query before. In ADWC, a histogram with up to 254 buckets i calculated for each column, independent of the queries that were executed so far. This allows more flexibility for ad-hoc queries and is suitable in a data warehouse environment.
- NO_INVALIDATE is set to FALSE (default: DBMS_STATS.AUTO_INVALIDATE). For ETL jobs, it is important so set this parameter to FALSE (see my previous blog post Avoid dbms_stats.auto_invalidate in ETL jobs). So, the preference setting is a very good choice for data warehouses.
The configuration of ADWC makes it very easy to gather optimizer statistics in your ETL jobs, but you still have to make sure that a DBMS_STATS call is included at the end of each ETL job.
Statistics for ETL Jobs with Direct-Path Loads
A better approach is to use Direct-Path INSERT statements. This is not only faster for large data sets, but makes it much easier to manage optimizer statistics. The reason is an Oracle 12c feature and two new undocumented parameters.
Since Oracle 12.1, statistics are gathered automatically for a Direct-Path INSERT. This works only for empty tables, and no histograms are calculated, as explained in my previous blog post Online Statistics Gathering in Oracle 12c.
In ADWC, two new undocumented parameters are available, both are set to TRUE by default:
- “_optimizer_gather_stats_on_load_all”: When this parameter is TRUE, online statistics are gathered even for a Direct-Path operation into a non-empty target table.
- “_optimizer_gather_stats_on_load_hist”: When this parameter is TRUE, histograms are calculated during online statistics gathering.
The following code fragment shows this behavior: Before an incremental load into the Hub table H_ORDER_ITEM, the number of rows in the table statistics is 68386107. After inserting another 299041 rows, the table statistics are increased to 68685148 (= 68386107 + 299041).
SELECT table_name, num_rows, last_analyzed
WHERE table_name = ‘H_ORDER_ITEM’;
TABLE_NAME NUM_ROWS LAST_ANALYZED
——————– ———- ——————-
H_ORDER_ITEM 68386107 11.07.2018 09:37:04
INSERT /*+ append */ INTO h_order_item
FROM v_stg_order_details s
LEFT OUTER JOIN h_order_item t
ON (s.h_order_item_key = t.h_order_item_key)
WHERE t.h_order_item_key IS NULL;
299041 rows inserted.
SELECT table_name, num_rows, last_analyzed
WHERE table_name = ‘H_ORDER_ITEM’;
TABLE_NAME NUM_ROWS LAST_ANALYZED
——————– ———- ——————-
H_ORDER_ITEM 68685148 11.07.2018 14:11:09
The column statistics (including histograms) are adapted for the target table, too. Only index statistics are not affected during online statistics gathering – but indexes in ADWC are a different story anyway. I will write about it in a separate blog post.
Statistics gathering is still important in the Autonomous Data Warehouse Cloud, and we have to take care that the optimizer statistics are frequently been updated. For Direct-Path operations, this works automatically, so we have nothing to do anymore. Only for conventional DML operations, it is still required to call DBMS_STATS after each ETL job, but the default configuration of ADWC makes it very easy to use.
After attending Kscope18, one thing is still extremely clear — Oracle continues and will continue to be all about the cloud. For example, the title of the Kscope presentation detailing what’s coming to OBIEE is “Oracle Analytics: How to Get to the Cloud and the Future of On-Premises.”
While that does tell you there’s still a future in on-prem Oracle BI, it's also clear that all the innovation will be put into the cloud. In this blog post, we’ll look at…
Commentary support in Oracle BI tools has been a commonly requested feature for many years. As with many absent features in our beloved tools, the community has come together to develop methods to implement this functionality themselves. Some of these approaches, such as leveraging Writeback, implement out-of-the-box Oracle BI features.
More commonly you’ll find custom-built software extensions or free “open source” applications that provide commentary extensions.
In this blog post, we’ll look at the difference between custom-built extensions and open-source applications. We’ll also consider two different tools — one open source, another a custom-built extension.
In Oracle Autonomous Data Warehouse Cloud, External Tables can be used to read files from the cloud-based Object Storage. But take care to do it the official way, otherwise you will see a surprise, but no data.
Together with my Trivadis colleague Christian Antognini, I currently have the opportunity to do several tests in the Autonomous Data Warehouse Cloud (ADWC). We are checking out the features and the performance of Oracle’s new cloud solution for data warehouses. For the kick-off of this project, we met in an idyllic scenery in the garden of Chris’ house in Ticino, the southern part of Switzerland. So, I was able to work on a real external table.
Testing External Tables in the Cloud on an external table with view to the clouds.
A typical way to load data files into a data warehouse is to create an External Table for the file and then read the data from this table into a stage table. In ADWC, the data files must first copied to a specific landing zone, a Bucket in the Oracle Cloud Infrastructure Object Storage service. The first steps to do this are described in the Oracle Autonomous Data Warehouse Cloud Service Tutorial. The Oracle Cloud Infrastructure command line interface CLI can also be used to upload the files.
The tutorial uses the procedure DBMS_CLOUD.copy_data to load the data into the target tables. The procedure creates a temporary external table in the background and drops it at the end. Another procedure DBMS_CLOUD.create_exernal_table is available to create a reusable External Table on a file in the Object Storage. But is it possible to create an External Table manually, too? To check this, I extracted the DDL command of the table CHANNELS (created with DBMS_CLOUD.create_extrenal_table):
Then, I created a new table CHANNELS_2 with exactly the same definition, only with a different name. It seems to be obvious that both tables should contain the same data. But this is not the case, table CHANNEL_2 returns no data:
First, I was confused. Then I thought it has to do with missing privileges. Finally, I assumed to be dazed because of the heat in Chris’ garden. But the reason is a different one: CHANNELS_2 is not an External Table, but a normal heap-organized table. Even it was created with an ORGANIZATION EXTERNAL clause! Extracting the DDL command shows what happened:
What is the reason for this behavior? The explanation can be found in Appendix B: Autonomous Data Warehouse Cloud for Experienced Oracle Database Users (the most interesting part of the ADWC documentation): Most clauses of the CREATE TABLE command are either ignored or not allowed in the Autonomous Data Warehouse. In ADWC, you cannot manually define physical properties such as tablespace name or storage parameters. No additional clauses for logging, compression, partitioning, in-memory, etc. are allowed. They are either not supported in ADWC (like Partitioning), or they are automatically handled by the Autonomous Database. According to documentation, creating an External Table should not be allowed (i.e. return an error message), but instead, the clause is just ignored. The same happens for index-organized tables, by the way.
External Tables are supported (and even recommended) in the Autonomous Data Warehouse Cloud, but they cannot be created manually – we are in an Autonomoust�Database.
If you follow the steps explained in the documentation and use the provided procedures in package DBMS_CLOUD, everything works fine. If you try to do it the “manual way”, you will get a non-expected behavior and probably loose a lot of time to find your data in the files.
The PL/SQL package DBMS_CLOUD contains many additional useful procedures for file handling in the Cloud, but not all of them are documented. A complete reference of all its procedures with some examples can be found in Christian Antognini’s blog post DBMS_CLOUD Package – A Reference Guide.
Do you get tired of recreating the same PowerPoint decks each month when your numbers change? Wouldn't it be great if you could just push a button and have the numbers in your ppt slide update to what is in the database? Wouldn't it be even better if the data was used in visually rich MS Office objects such as tables and charts? And, wouldn't it be awesome if you could interact with the data in real time during your presentation?
Well, did you know Smart View for Power Point does all of that? That's right I said Smart View for PowerPoint; Smart View is not just for Excel.
Smart View has had the functionality to work with the MS Office suite for some time, but frankly the functionality outside of Excel has been limited and challenging to work with at times. While the Power Point and Word functionality are not 100%, they have come a long way and with some patience you can make some really nice PowerPoint slides that are interactive with your underlying EPM data sources.
At Kscope18 I did a presentation with fellow Oracle Ace and GE colleague Gary Adashek. We did a Shark Tank style pitch to "investors" on why they should help us "Save The Beverage Company". If you don't know what The Beverage Company is, your Essbase street credit is going to take a serious hit. Of course I am referring to the fabled bottling company made famous by the Essbase Sample Basic database. Gary and I figured we could use our Smart View skills to create some really slick ppt slides to convince the investors to help us save this favored institution that had been around since the Arbor days. Besides having some fun trying to convince our panel of investors (see the Photoshop pics at the end of this post) while the audience watched, we wanted to convey the very real message that you can do some interesting things in PowerPoint with Smart View.
In my effort to communicate useful EPM tips across various mediums, this seemed like a good topic for a blog post. In this tutorial, I am going to walk through how to create interactive Smart View objects in PowerPoint. I am working with Essbase 220.127.116.11, Office 2016, and Smart View 18.104.22.168.800. I suggest using this latest version of Smart View since it has some fixes in it specifically associated with PowerPoint.
The first thing you will need to decide is how you are going to link your source data to your ppt. You have three options to create what is referred to in Smart View as a 'function grid'. You can base your function grid on
- A Hyperion Financial Report (HFR) grid
- A Data Form (Hyperion Planning, (e)PBCS, and HFM-presumably-I have not tested)
- An ad hoc Smart View retrieve in Excel.
Each one of these have their pros and cons PBCS data forms seem to have the most functionality while also being the most stable. HFR grids are stable, but they lack the ability to change the POV after they have been turned into a function grid. Excel has the most functionality in terms of different objects, but it is less stable since you are creating the function grid from an ad hoc report in Excel.
So to start off let's take a look at building a ppt slide using a PBCS form as a source for a chart.
First step is to either create a form or select one that has the data you are looking for. Keep in mind if your goal is to make a chart, not all forms are set up correctly to make a nice chart. In my experience so far, I have created a separate folder for forms called 'Forms for PPT' where I save the ones I have created specifically for this purpose.
This is the form I created for demonstration. You can see it is pretty straightforward, but note that I did add a dimension to the Page section of the form; you'll see why in a little bit.
When working with a Data Form or HFR report as a source you can begin directly from Power Point; there is no need for Excel.Steps
- Open Power Point and start with a blank presentation
- Connect to your data source, in this case PBCS, and navigate to Forms folder and select the form you created as the basis for your chart
- At the bottom of the Smart View panel, click on 'Insert Chart'
- Be patient this step may take a minute or so while Office renders the object
- It may also be a good idea to ensure Excel is closed before doing this. I have found that if Excel is open prior to inserting the chart it times out. Technically they are using the Excel chart engine to render the chart and insert it into Power Point
- Once the chart is rendered you can resize it and move it around your slide to desired location. I do not recommend trying to move it to another slide, if you want it on another slide it seems best to repeat the steps.
- Once the chart is created you can now make changes to it as you would a typical Office object. You can go to the chart designer and change the chart type or the color theme or various other options. Smart View does provide some of the options in a pop-up menu you will see if you click on the chart, but the options there are similar to the ones on the chart design ribbon, with the exception of the filter function, which allows you to filter out certain members. The filter function gives the option to potentially use a large form with a lot of data and then filter it in ppt, rather than having to create multiple forms. You can also insert your regular ppt content and wind up with something that looks like this.
- Now that I have a nice chart I can take it one step further and make it interactive. Remember before when I mentioned I put a dimension in the page section of the form? Let's go back to the Smart View panel hit the drop down next to the little house icon and select 'Document Contents'. Click on your function grid and then at the bottom of the panel click on 'Insert Reporting Object/Control'. Now, click on the POV object
- You will see a grey box inserted onto the slide. Note that this POV box will not become active until you enter Power Point presentation mode. While in presentation mode you can hit the drop down next to the dimension member that was placed in the Page section of the form and select a different member; hit the refresh button and your objects will re-render with the new data.
So you can see that I was able to very quickly create a presentation quality image based off my PBCS data. Next time my data changes, I can open this ppt file, go to Smart View refresh and the object will pull in the new data and update the object accordingly.
HFR GridNext, let's look at how to insert a HFR report
The steps for inserting an HFR report are similar but there are a few differences. First, like the data forms, you need to start off with a report that has the data you want. I created an HFR report similar to the data form in previous example.Steps
- Open Power Point and start with a blank presentation
- Connect to your data source, in this case we are still using PBCS but we are going to choose the Reporting provider instead of the EPM provider. Navigate to the folder where you saved your report and select it. Then hit the Open link at the bottom of the Smart View panel
- When you click Open, the Import Workspace Document window will open. By default you can import the report as an image, but we want to hit the drop down and select Function Grid instead
- Click Finish
- You will be taken back to your slide and the Document Contents pane will be active in the Smart View panel. Click on the Insert New Reporting Object/Control link
- A new window pops up, scroll down and select Chart (note there is no option for POV). Your chart is inserted and associated with the function grid, same as above with PBCS form.
You can now work with the chart the same way you did in the steps above. So now, let's take a minute to explore some of the other objects (note these work the same if you are using a form).
- Return to the Document Contents pane, select your function grid connection and insert another object
- This time let's add an Office Table
- Once the table is inserted, click on the table and then go to the PowerPoint Design Ribbon and select a format for the table; you can then repeat for the chart. You may also want to increase the font for the table.
- Insert a new slide into your ppt
- On slide 2, insert a new reporting object/control, select Function Grid
- Note that unlike the Office table, the function grid inserts multiple text boxes, some with labels, and others with active data links to your data source. You can arrange these objects anywhere you would like and again click on the design ribbon to alter the way the object are formatted.
There are a number of options to play with to get the format the way you would like. Note that from time to time I have encountered a few bugs and some inconsistencies in behavior between data sources. I encourage you to log an SR with Oracle for any you come across to get this product working as well as possible.
For the last data source, let's look at an ad hoc from Excel. Note I will use PBCS but this works for other data sources such as Essbase as well.
- Open Excel and start with a blank workbook
- Connect to EPM data source via Smart View
- Using ad hoc analysis, create a basic retrieve
- Select your data range by dragging mouse over the cells with data
- Go to Smart View ribbon and click Copy (note this is not the same as Excel Copy or ctrl + C)
- Open Power Point blank presentation
- Go to Smart View Ribbon and click Paste (note this is not the same as Paste on the Home ribbon or ctrl + V)
- At this point you will see that the function grid is actually placed in the slide. Go ahead and run the refresh
- Now let's add a chart and a POV slider: go to Smart View panel and go to document contents, select your Smart View link, and then click on Insert New Reporting Object/Control.
- Select chart
- Go back to Document Content, select your Smart View link, and then click on Insert New Reporting Object/Control.
- Scroll to bottom and select Slider
- Select the dimension you want the slider for, I am going to choose Years, with members FY18, FY17, and FY16.
- Now when I enter presentation mode, my slider becomes active. I can use my mouse to slide to different year selections
There are an endless number of combinations and examples I could show, but I think this is a good stopping point. If you were able to follow along and complete the steps you now have the basic understanding of how to create Smart View Power Point objects that are linked to your EPM data source. Experiment with different objects, and different data sources; I think you will find some very cool features. Don't be discouraged if you run up against something that doesn't work right, the Oracle team has been very responsive and you just need to log an SR so they become aware of the issue. Sometimes it is what we are doing, sometimes it is bug, but as I said at the beginning of the post, the product has come a long way and I believe it can be very useful in the hands of the right users.
Best of luck, let me know your thoughts in the comment section.Post-Conclusion
Pics from Kscope18 Save The Beverage Company presentation
1. Meet The Sharks, The Founders, and The Presenters!
Typically, issues that arise with your enterprise software require costly solutions, but occasionally you’ll find something that’s both helpful and affordable.
UnifyBI, the connector for Oracle BI and Tableau, is now available in a community edition and free to download. That’s right, free. So, what’s the catch? It doesn’t come with all the bells and whistle of the paid versions, but it will still help the community of Tableau and Oracle BI users solve their biggest problems.
This blog post will cover the issues the Community Edition helps solve, installation, as well as how its features stack up to the UnifyBI Professional Edition and Server Edition.
The July updates for Oracle's Planning & Budgeting Cloud Service (PBCS) and Enterprise Planning and Budgeting Cloud Service (EPBCS) have arrived! This blog post outlines several new features, including a new data integration component, updated vision sample application, considerations, and more.
The monthly update for PBCS and EPBCS will occur on Friday, July 20 during your normal daily maintenance window.
The July updates for Oracle's Account Reconciliation Cloud Service (ARCS) are here. In this blog post, we’ll outline new features in ARCS, including adding power calculation, drag and drop attachments, considerations, and more.
We’ll let you know any time there are updates to ARCS or any other Oracle EPM cloud products. Check the US-Analytics Oracle EPM & BI Blog every month.
The monthly update for Oracle ARCS will occur on Friday, July 20 during your normal daily maintenance window.