Next
For other A-Team articles¬†by Richard, click here Introduction This article walks through the steps to set up a Cloud Storage Container, for use with the Fusion SaaS BI Cloud Connector. This may be of particular interest to Oracle Analytics Cloud customers, wanting to use the new Data Replication functionality from Fusion SaaS (for more details, […]
OAC 18.3.3: New Features - 21-Sep-2018 08:58 - Rittman Mead Consulting
OAC 18.3.3: New Features

I believe there is a hidden strategy behind Oracle's product release schedule: every time I'm either on holidays or in a business trip full of appointments a new version of Oracle Analytics Cloud is published with a huge set of new features!

OAC 18.3.3: New Features

OAC 18.3.3 went live last week and contains a big set of enhancements, some of which were already described at Kscope18 during the Sunday Symposium. New features are appearing in almost all the areas covered by OAC, from Data Preparation to the main Data Flows, new Visualization types, new security and configuration options and BIP and Essbase enhancements. Let's have a look at what's there!

Data Preparation

A recurring theme in Europe since last year is GDPR, the General Data Protection Regulation which aims at protecting data and privacy of all European citizens. This is very important in our landscape since we "play" with data on daily basis and we should be aware of what data we can use and how.
Luckily for us now OAC helps to address GDPR with the Data Preparation Recommendations step: every time a dataset is added, each column is profiled and a list of recommended transformations is suggested to the user. Please note that Data Preparation Recommendations is only suggesting changes to the dataset, thus can't be considered the global solution to GDPR compliance.
The suggestion may include:

  • Complete or partial obfuscation of the data: useful when dealing with security/user sensitive data
  • Data Enrichment based on the column data can include:
    • Demographical information based on names
    • Geographical information based on locations, zip codes

OAC 18.3.3: New Features

Each of the suggestion applied to the dataset is stored in a data preparation script that can easily be reapplied if the data is updated.

OAC 18.3.3: New Features

Data Flows

Data Flows is the "mini-ETL" component within OAC which allows transformations, joins, aggregations, filtering, binning, machine learning model training and storing the artifacts either locally or in a database or Essbase cube.
The dataflows however had some limitations, the first one was that they had to be run manually by the user. With OAC 18.3.3 now there is the option to schedule Data Flows more or less like we were used to when scheduling Agents back in OBIEE.

OAC 18.3.3: New Features

Another limitation was related to the creation of a unique Data-set per Data Flow which has been solved with the introduction of the Branch node which allows a single Data Flow to produce multiple data-sets, very useful when the same set of source data and transformations needs to be used to produce various data-sets.

OAC 18.3.3: New Features

Two other new features have been introduced to make data-flows more reusable: Parametrized Sources and Outputs and Incremental Processing.
The Parametrized Sources and Outputs allows to select the data-flow source or target during runtime, allowing, for example, to create a specific and different dataset for today's load.

OAC 18.3.3: New Features

The Incremental Processing, as the name says, is a way to run Data Flows only on top of the data added since the last run (Incremental loads in ETL terms). In order to have a data flow working with incremental loads we need to:

  • Define in the source dataset which is the key column that can be used to indicate new data (e.g. CUSTOMER_KEY or ORDER_DATE) since the last run
  • When including the dataset in a Data Flow enable the execution of the Data Flow with only the new data
  • In the target dataset define if the Incremental Processing replaces existing data or appends data.

Please note that the Incremental Load is available only when using Database Sources.

Another important improvement is the Function Shipping when Data Flows are used with Big Data Cloud: If the source datasets are coming from BDC and the results are stored in BDC, all the transformations like joining, adding calculation columns and filtering are shipped to BDC as well, meaning there is no additional load happening on OAC for the Data Flow.

Lastly there is a new Properties Inspector feature in Data Flow allowing to check the properties like name and description as well as accessing and modifying the scheduling of the related flow.

OAC 18.3.3: New Features

Data Replication

Now is possible to use OAC to replicate data from a source system like Oracle's Fusion Apps, Talend or Eloqua directly into Big Data Cloud, Database Cloud or Data Warehouse Cloud. This function is extremely useful since allows decoupling the queries generated by the analytical tools from the source systems.
As expected the user can select which objects to replicate, the filters to apply, the destination tables and columns, and the load type between Full or Incremental.

Project Creation

New visualization capabilities have been added which include:

  • Grid HeatMap
  • Correlation Matrix
  • Discrete Shapes
  • 100% Stacked Bars and Area Charts

In the Map views, Multiple Map Layers can now be added as well as Density and Metric based HeatMaps, all on top of new background maps including Baidu and Google.

OAC 18.3.3: New Features

Tooltips are now supported in all visualizations, allowing the end user to add measure columns which will be shown when over a section of any graph.

OAC 18.3.3: New Features

The Explain feature is now available on metrics and not only on attributes and has been enhanced: a new anomaly detection algorithm identifies anomalies in combinations of columns working in the background in asynchronous mode, allowing the anomalies to be pushed as soon as they are found.

A new feature that many developers will appreciate is the AutoSave: we are all used to autosave when using google docs, the same applies to OAC, a project is saved automatically at every change. Of course this feature can be turn off if necessary.
Another very interesting addition is the Copy Data to Clipboard: with a right click on any graph, an option to save the underline data to clipboard is available. The data can then natively be pasted in Excel.

Did you create a new dataset and you want to repoint your existing project to it? Now with Dataset replacement it's just few clicks away: you need only to select the new dataset and re-map all the columns used in your current project!

OAC 18.3.3: New Features

Data Management

The datasets/dataflows/project methodology is typical of what Gartner defined as Mode 2 analytics: analysis done by a business user whitout any involvement from the IT. The step sometimes missing or hard to be performed in self-service tools is the publishing: once a certain dataset is consistent and ready to be shared, it's rather difficult to open it to a larger audience within the same toolset.
New OAC administrative options have been addressing this problem: a dataset Certification by an administrator allows a certain dataset to be queried via Ask and DayByDay by other users. There is also a dataset Permissions tab allowing the definition of Full Control, Edit or Read Only access at user or role level. This is the way of bringing the self service dataset back to corporate visibility.

OAC 18.3.3: New Features

A Search tab allows a fine control over the indexing of a certain dataset used by Ask and DayByDay. There are now options to select when then indexing is executed as well as which columns to index and how (by column name and value or by column name only).

OAC 18.3.3: New Features

BIP and Essbase

BI Publisher was added to OAC in the previous version, now includes new features like a tighter integration with the datasets which can be used as datasources or features like email delivery read receipt notification and compressed output and password protection that were already available on the on-premises version.
There is also a new set of features for Essbase including new UI, REST APIs, and, very important security wise, all the external communications (like Smartview) are now over HTTPS.
For a detailed list of new features check this link

Conclusion

OAC 18.3.3 includes an incredible amount of new features which enable the whole analytics story: from self-service data discovery to corporate dashboarding and pixel-perfect formatting, all within the same tool and shared security settings. Options like the parametrized and incremental Data Flows allows content reusability and enhance the overall platform performances reducing the load on source systems.
If you are looking into OAC and want to know more don't hesitate to contact us

This is your opportunity to nominate the person you believe will best provide leadership and policy development for ODTUG. For more information, please click here. All nominees must be paid ODTUG members in good standing.
KScope 18 Speaker Award - 17-Sep-2018 07:04 - Ricardo Giampaoli
Hey guys how are you? It has been awhile since last time I wrote anything here…. and surprise, surprise, it’s because I’m crazy working in a project that was sized small but turn out huge and the size didn’t change…. ūüôā never happened before heheheh ūüėČ This is just a small post to tell how […]


If you’re thinking about migrating your Hyperion Planning environment to the cloud, there are several best practices that help you make the move successful. We’ve talked about how a cloud migration lends itself to the opportunity to consolidate and cleanse your environment. But before deciding what needs consolidation, you have to make a big decision: Oracle Enterprise Planning and Budgeting Cloud Service (EPBCS) or Oracle Planning and Budgeting Cloud Service (PBCS)?

Whether you choose PBCS or EPBCS, the overall benefits an Oracle cloud application are the same: no upfront cost for hardware or software, less IT involvement, and no annual maintenance costs. In this blog post, we‚Äôll take a deeper dive into comparing Oracle‚Äôs cloud planning applications. 

ODTUG Kscope Session Highlights - Part 2 - 13-Sep-2018 13:14 - ODTUG Kscope
Part 2 - Curious about the content you'll see at ODTUG Kscope19? As we look ahead to opening abstract submissions in the coming weeks, we would like to share some of the content highlights from ODTUG Kscope18.

Oracle Autonomous Data Warehouse Cloud does not allow to create indexes. Is this a problem for star schemas because no Star Transformation can be used? Or are the required bitmap indexes automatically created? A look under the hood of ADWC.

A typical recommendation for star schemas in an Oracle database is to create a bitmap index on each dimension key of a fact table. I used (and still use) this index strategy in many data warehouses and recommend it in reviews, trainings and presentations. Why are bitmap indexes so important on a fact table? Because they are required for the Star Transformation, a special join strategy for star schemas. Without explaining all the details, here a short summary of the Star Transformation approach:

  1. For each dimension table with a filter (WHERE condition) in the query, a bit array is built based on the bitmap index of the dimension key in the fact table
  2. All these bit arrays are combined with a BITMAP AND operator. The result is a bit array for all rows of the fact table that fit all filter conditions
  3. This resulting bit array is used to access the corresponding rows in the fact table

But how can this efficient join and access method in a star schema be used, when the database does not allow to create any bitmap indexes or non-unique b-tree indexes? Are bitmap indexes created automatically in ADWC? Or how are these kind of queries on a star schema handled by the optimizer? To find the answer, let’s look at the execution plan of a typical query on the SSB schema (sample star schema benchmark) that is available in every ADWC database. Some example queries can be found in Appendix D of the ADWC documentation.

Sample Star Schema Benchmark

The SSB schema contains one fact table LINEORDER with around 6 billion rows and four dimension tables. The time dimension DWDATE contains all calendar days for 8 years, all other dimensions contains between 2 and 30 million rows. The data model is a typical star schema (the foreign key constraints I added manually in Data Modeler for better readability. The SSB schema constains no PK/FK constraints at all).

SSB schema

 

Execution Plan of a Star Schema Query

A query that joins the fact table with all four dimension tables, each of them containing a filter, leads to the following execution plan. We can see several interesting details in this plan:

  • Parallel Execution: The query runs in parallel (all the PX operators in the execution plan). This is generally the case in ADWC, except for connections with consumer group LOW or for ADWC configurations with only 1 CPU core. The degree of parallelism (DOP) depends on the number of CPU cores. More details about scalability can be found in Christian Antognini‚Äôs blog post¬†Observations About the Scalability of Data Loads in ADWC.
  • Result Cache: The result cache is activated (see green line 1 in execution plan). The parameter RESULT_CACHE_MODE is set to FORCE in ADWC and cannot be changed. This allows very short response times for queries that are executed multiple times. Only the first execution reads and joins the tables, all¬†subsequent executions read the result from the cache. This works only for queries with a small result set. In a star schema, this is usually the case for high aggregated data (i.e. when the facts are aggregated on a high hierarchy level of the dimensions).
  • No Star Transformation: No indexes are used in the execution plan. There are two simple reasons for this: Indexes cannot be created manually, and there is no automatic creation of indexes in the Autonomous Data Warehouse. Because no indexes are available, no Star Transformation can be used here.
  • Vector Transformation: Instead of Star Transformation, an even better approach is used in ADWC: Vector Transformation (see blue lines in execution plan). This is very interesting, because Vector Transformation works only in combination with Oracle Database In-Memory. Although this feature is not supported in ADWC at the moment, this very efficient join approach for star schema queries takes place here.

 

————————————————————————————————————————————————————————————————

| Id  | Operation                                        | Name                        | Rows  |

————————————————————————————————————————————————————————————————

|   0 | SELECT STATEMENT                                 |                             |   708K|

|   1 |  RESULT CACHE                                    | 0v7cjd9tjv4vb78py6r10duh4r  |       |

|   2 |   TEMP TABLE TRANSFORMATION                      |                             |       |

|   3 |    LOAD AS SELECT                                | SYS_TEMP_0FDA1A147_1F199710 |       |

|   4 |     PX COORDINATOR                               |                             |       |

|   5 |      PX SEND QC (RANDOM)                         | :TQ10001                    |     8 |

|   6 |       HASH GROUP BY                              |                             |     8 |

|   7 |        PX RECEIVE                                |                             |     8 |

|   8 |         PX SEND HASH                             | :TQ10000                    |     8 |

|   9 |          KEY VECTOR CREATE BUFFERED              | :KV0000                     |     8 |

|  10 |           PX BLOCK ITERATOR                      |                             |   639 |

|* 11 |            TABLE ACCESS STORAGE FULL             | DWDATE                      |   639 |

|  12 |    LOAD AS SELECT                                | SYS_TEMP_0FDA1A148_1F199710 |       |

|  13 |     PX COORDINATOR                               |                             |       |

|  14 |      PX SEND QC (RANDOM)                         | :TQ20001                    |     1 |

|  15 |       HASH GROUP BY                              |                             |     1 |

|  16 |        PX RECEIVE                                |                             |     1 |

|  17 |         PX SEND HASH                             | :TQ20000                    |     1 |

|  18 |          KEY VECTOR CREATE BUFFERED              | :KV0001                     |     1 |

|  19 |           PX BLOCK ITERATOR                      |                             |  6000K|

|* 20 |            TABLE ACCESS STORAGE FULL             | CUSTOMER                    |  6000K|

|  21 |    LOAD AS SELECT                                | SYS_TEMP_0FDA1A149_1F199710 |       |

|  22 |     PX COORDINATOR                               |                             |       |

|  23 |      PX SEND QC (RANDOM)                         | :TQ30001                    |   249 |

|  24 |       HASH GROUP BY                              |                             |   249 |

|  25 |        PX RECEIVE                                |                             |   249 |

|  26 |         PX SEND HASH                             | :TQ30000                    |   249 |

|  27 |          KEY VECTOR CREATE BUFFERED              | :KV0002                     |   249 |

|  28 |           PX BLOCK ITERATOR                      |                             | 80000 |

|* 29 |            TABLE ACCESS STORAGE FULL             | SUPPLIER                    | 80000 |

|  30 |    LOAD AS SELECT                                | SYS_TEMP_0FDA1A14A_1F199710 |       |

|  31 |     PX COORDINATOR                               |                             |       |

|  32 |      PX SEND QC (RANDOM)                         | :TQ40001                    |  1006 |

|  33 |       HASH GROUP BY                              |                             |  1006 |

|  34 |        PX RECEIVE                                |                             |  1006 |

|  35 |         PX SEND HASH                             | :TQ40000                    |  1006 |

|  36 |          KEY VECTOR CREATE BUFFERED              | :KV0003                     |  1006 |

|  37 |           PX BLOCK ITERATOR                      |                             | 80000 |

|* 38 |            TABLE ACCESS STORAGE FULL             | PART                        | 80000 |

|  39 |    PX COORDINATOR                                |                             |       |

|  40 |     PX SEND QC (ORDER)                           | :TQ50004                    |   708K|

|  41 |      SORT GROUP BY                               |                             |   708K|

|  42 |       PX RECEIVE                                 |                             |   708K|

|  43 |        PX SEND RANGE                             | :TQ50003                    |   708K|

|  44 |         HASH GROUP BY                            |                             |   708K|

|* 45 |          HASH JOIN                               |                             |   708K|

|  46 |           PX RECEIVE                             |                             |  1006 |

|  47 |            PX SEND BROADCAST                     | :TQ50000                    |  1006 |

|  48 |             PX BLOCK ITERATOR                    |                             |  1006 |

|  49 |              TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FDA1A14A_1F199710 |  1006 |

|* 50 |           HASH JOIN                              |                             |   708K|

|  51 |            PX RECEIVE                            |                             |   249 |

|  52 |             PX SEND BROADCAST                    | :TQ50001                    |   249 |

|  53 |              PX BLOCK ITERATOR                   |                             |   249 |

|  54 |               TABLE ACCESS STORAGE FULL          | SYS_TEMP_0FDA1A149_1F199710 |   249 |

|* 55 |            HASH JOIN                             |                             |   708K|

|  56 |             TABLE ACCESS STORAGE FULL            | SYS_TEMP_0FDA1A147_1F199710 |     8 |

|* 57 |             HASH JOIN                            |                             |   708K|

|  58 |              TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FDA1A148_1F199710 |     1 |

|  59 |              VIEW                                | VW_VT_846B3E5D              |   708K|

|  60 |               HASH GROUP BY                      |                             |   708K|

|  61 |                PX RECEIVE                        |                             |   708K|

|  62 |                 PX SEND HASH                     | :TQ50002                    |   708K|

|  63 |                  VECTOR GROUP BY                 |                             |   708K|

|  64 |                   HASH GROUP BY                  |                             |   708K|

|  65 |                    KEY VECTOR USE                | :KV0001                     |  3729K|

|  66 |                     KEY VECTOR USE               | :KV0000                     |  3875K|

|  67 |                      KEY VECTOR USE              | :KV0003                     |    14M|

|  68 |                       KEY VECTOR USE             | :KV0002                     |   247M|

|  69 |                        PX BLOCK ITERATOR         |                             |  5999M|

|* 70 |                         TABLE ACCESS STORAGE FULL| LINEORDER                   |  5999M|

————————————————————————————————————————————————————————————————

 

Note

—–

   Рautomatic DOP: Computed Degree of Parallelism is 8 because of degree limit

   Рvector transformation used for this statement

 

 

 

Vector Transformation

Like with Star Transformation, the base idea of Vector Transformation is similar: Before accessing the (usually much bigger) fact table, the result set is reduced by applying all dimension filters. To do this, the Vector Transformation is executed in two phases:

Phase 1: The following steps are performed for each dimension table with filter criteria (i.e. a WHERE condition in the query):

  • The dimension table is scanned with a full table scan. All rows that do not fit the WHERE condition are ignored
  • A key vector is calculated to determine which rows of the dimension are required for the query (KEY VECTOR CREATE BUFFERED in the plan)
  • The data is aggregated with an ‚ÄúIn-Memory Accum
What to Know Before Moving Hyperion to the Cloud - 12-Sep-2018 15:22 - US-Analytics

There‚Äôs a lot of information out there about moving from Oracle Hyperion to the Oracle EPM Cloud, which makes sense ‚ÄĒ there‚Äôs a lot you need to know. However, that saturation of content can be difficult to sift through and keep organized. This blog post is your solution.

In it you’ll find a high-level overview of what you need to know before moving your on-prem tools to the Oracle EPM Cloud, along with links to more in-depth content. You’ll have a single point of reference to answer your questions about moving to the cloud.

If you are a paid ODTUG member who was unable to attend ODTUG Kscope18, we have great news for you! The ODTUG Kscope18 session presentations and recordings are NOW AVAILABLE to you!
Are you a paid ODTUG member (or will you be as of September 30, 2018)? Do you have a lot to offer the ODTUG community? Do you have a passion for ODTUG and time to commit to serving on the board? If so, then I encourage you to submit your name to be considered for the 2019-2020 BOD.
Next