OAC Workshop - From A to Z

[email protected] www.rittmanmead.com @rittmanmead Oracle Analytics Cloud Workshop AnD Summit 2019

[email protected] www.rittmanmead.com @rittmanmead !2 Francesco Tisiot BI Tech Lead at
Rittman Mead Verona, Italy Rittman Mead Blog 10 Years Experience in BI/Analytics [email protected] @FTisiot Oracle ACE

[email protected] www.rittmanmead.com @rittmanmead About Rittman Mead !3 Rittman Mead is
a data and analytics company who specialise in data visualisation, predictive analytics, enterprise reporting and data engineering. We use our skill, experience and know-how to work with organisations across the world to interpret their data. We enable the business, the consumers, the data providers and IT to work towards a common goal, delivering innovative and cost-effective solutions based on our core values of thought leadership, hard work and honesty. We work across multiple verticals on projects that range from mature, large scale implementations to proofs of concept and can provide skills in development, architecture, delivery, training and support.

[email protected] www.rittmanmead.com @rittmanmead !4 •OAC Overview •Versions, Licensing and Provisioning
•Migrating to the Cloud •Importing Data Sources •Mode 1 and Mode 2 Overview •Report Writing •Oracle DV •Mashups, Dataﬂow and OAC Integration •Advanced Analytics / Machine Learning Course Agenda

[email protected] www.rittmanmead.com @rittmanmead INTRO 101 - OAC Overview and Features
Comparison

[email protected] www.rittmanmead.com @rittmanmead !6 •OAC Product Overview •OAC Key Components
•OAC Comparison to OBIEE Agenda - OAC Overview

[email protected] www.rittmanmead.com @rittmanmead !7 •Analytics “Platform” •Fully scalable •Can be
used in the cloud, on premise or data centre Oracle Analytics Cloud Service

[email protected] www.rittmanmead.com @rittmanmead !8 • Functionality includes (but not limited
to): ‣ Oracle Cloud ‘My Services’ - Used to create instances (similar to SSI’s in OBIEE 12c) ‣ Oracle Analytics Cloud Console - Used to manage datasources - Manage Users - Manage Backups/Restores via snapshots ‣ Enterprise Reporting (BICS) - OBI Data Models (RPD) - BI Publisher - OBIEE Analysis/Dashboards ‣ Oracle Data Visualiser - Data Discovery, Visualisation and Storyboarding - Mashups - Data preparation Oracle Analytics Cloud Service Functionality

[email protected] www.rittmanmead.com @rittmanmead !9 •Used to manage the platform •Track
Billing •User Metrics •Environment Up/Down Time Oracle Cloud ‘My Services’

[email protected] www.rittmanmead.com @rittmanmead !10 •Web based management tool ‣Manages: -DV
Projects -Data Sources -Data Sets -Users -Application Roles Oracle Analytics Cloud Console

[email protected] www.rittmanmead.com @rittmanmead !11 •OBIEE in the cloud •Lift and
Shift from existing OBIEE instances •Full functional Analysis & Dashboards OAC Enterprise Reporting

[email protected] www.rittmanmead.com @rittmanmead !12 •Dynamically visualise data as you drag
and drop attributes and measures •Quickly create visualisations to distribute to other users •Use custom data sources, e.g. spreadsheets or database •Also includes Data Visualiser Desktop OAC Oracle Data Visualiser

[email protected] www.rittmanmead.com @rittmanmead !13 •Import Data from Files or Database
•Create Relationships •Add Data Filters •Add Calculations OAC Data Visualisation - Data Preparation

[email protected] www.rittmanmead.com @rittmanmead !14 Two Service Options Analytics Cloud Classic
Analytics Cloud Services managed by Oracle: Backup & Recovery Service Monitoring Patching & Upgrades Test & Production instances Based on Oracle Cloud Infrastructure (OCI) Services managed by You: Based on Oracle Cloud Infrastructure Classic

[email protected] www.rittmanmead.com @rittmanmead !15 Three Edition Options Enterprise Edition Data
Lake Edition Standard Edition Data Discovery Data Preparation What-If Planning Big Data Storage Data Transformation via Apache Spark Data Lake Connectivity Enterprise Analysis & Dashboarding Published Reporting Day by Day

[email protected] www.rittmanmead.com @rittmanmead !16 Two Purchasing Options Monthly Flex Pay
As You Go Based on Universal Credits model No minimum tenure Payments made in arrears Based on consumption Suitable for: Rapid Prototyping Testing & Sampling Elastic Scalable Based on Universal Credits model 12 month minimum tenure Payments made in advance Unused credits are forfeited Suitable for: Predictable, production workloads Long running platforms

[email protected] www.rittmanmead.com @rittmanmead !17 • OAC instances are provisioned in
Oracle Compute Units (OCPU’s) shapes to satisfy diﬀerent requirements • The shape can be altered after service creation ‣ Flex capacity up (or down) based on immediate need Several Shape Options

[email protected] www.rittmanmead.com @rittmanmead !18 • Mobile Web & BI Mobile
App ‣ All DV projects will auto-render on mobile devices ‣ The heritage mobile app supports all OAC content • Synopsis Mobile App ‣ Automatic Excel/CSV ingestion & analysis ‣ Extending to all DV supported sources • Day by Day ‣ Included within Enterprise Edition ‣ Search driven analytics ‣ Voice recognition allows you to verablise questions ‣ Embedded learning enables a tailored experience Mobile Options

[email protected] www.rittmanmead.com @rittmanmead !19 • Secure Access ‣ Remote hosts
access cloud via SSH ‣ Speciﬁc access rules can be conﬁgured Security & Access • Users & Roles in OAC can be managed via two methods: ‣ Embedded Weblogic LDAP server ‣ Oracle Identity Cloud Service (IDCS) - Shared model across all cloud services - Supports SSO - Enables AD integration ‣ IDCS only relevant for Universal Credit

dimensionality.ch [email protected] [email protected] Architectural Options

dimensionality.ch [email protected] [email protected] !21 Oracle Cloud • Simplest integration architecture:
‣ Data held in Oracle Database Cloud Service (DBCS) ‣ ETL via Oracle Data Integration Platform Cloud (DIPC) • Data acquisition from Cloud Applications: ‣ BI Cloud Connector ‣ Goldengate ‣ OAC’s new Data Replication features • Data acquisition from on-premises applications: ‣ Data Sync ‣ APEX Data Loader ‣ SQL Developer tools ‣ Data Pump

dimensionality.ch [email protected] [email protected] !22 Hybrid • Database remains on-premises ‣
Frequently a staging post for full migration • Data access via Remote Data Connector ‣ Runs on dedicated application server (WLS/Tomcat) ‣ Secure connectivity via public/private SSL key ‣ Supports Oracle, DB2, Teradata, SQL Server • Beware performance challenges ‣ Potential latency due to network bandwidth ‣ Check network capacity and test carefully

dimensionality.ch [email protected] [email protected] !23 Multi-Cloud • Commonly adopted cloud strategy
‣ Spread investment across diﬀerent cloud environments ‣ Mix private and public cloud workloads • Data Sets have a broad support for connectivity ‣ Relational, Big Data, Semi-structured & proprietry • Data Model supports fewer options (via RDC) ‣ Oracle, DB2, Terdata, SQL Server

[email protected] www.rittmanmead.com @rittmanmead Getting Started With Your First Instance

[email protected] www.rittmanmead.com @rittmanmead !25 Two Service Options Analytics Cloud Classic
Analytics Cloud Services managed by Oracle: Backup & Recovery Service Monitoring Patching & Upgrades Test & Production instances Based on Oracle Cloud Infrastructure (OCI) Services managed by You: Based on Oracle Cloud Infrastructure Classic

[email protected] www.rittmanmead.com @rittmanmead !26 Analytics Cloud (OAC)

[email protected] www.rittmanmead.com @rittmanmead !27 Analytics Cloud (OAC)

[email protected] www.rittmanmead.com @rittmanmead !28 Real Provisioning Example - OAC

[email protected] www.rittmanmead.com @rittmanmead !30 Real Provisioning Example - OAC Choose
Edition Choose Feature Set

[email protected] www.rittmanmead.com @rittmanmead !34 Real Provisioning Example - OACC Provisioning
1 - Database 2 - OACC

[email protected] www.rittmanmead.com @rittmanmead !35 Real Provisioning Example - OACC -
Database

[email protected] www.rittmanmead.com @rittmanmead !36 Choose Region Real Provisioning Example -
OACC - Database * Must match the Region Chosen in OACC

Database

[email protected] www.rittmanmead.com @rittmanmead !38 Choose Backup Options Real Provisioning Example
- OACC - Database

Database

[email protected] www.rittmanmead.com @rittmanmead !40 Real Provisioning Example - OACC

[email protected] www.rittmanmead.com @rittmanmead !43 Real Provisioning Example - OACC Choose
Region * Must match the Region Chosen in ODBCS

[email protected] www.rittmanmead.com @rittmanmead !44 Real Provisioning Example - OACC Choose
Edition

[email protected] www.rittmanmead.com @rittmanmead Moving from on-premises OBIEE to OAC

[email protected] www.rittmanmead.com @rittmanmead •Cloud is “in” – management support assured
•No need to involve internal IT and slow processes •Scale up and down as needed •Pay as you go – down to hourly basis •No capital expenses, only operating expenses! •“Fight for budget” a lot smoother Why to move to cloud

[email protected] www.rittmanmead.com @rittmanmead •OAC and OACC contain DV •OBIEE requires
additional license for DV •“Cloud first” strategy means cloud gets new features earlier Why to move to cloud

[email protected] www.rittmanmead.com @rittmanmead •Choose whether to use RPD or thin
client modeller ‣Less dependency on skilled staff ‣Free up skilled staff for core tasks ‣Leave common tasks to super users ‣RPD and Admin Tool still there ‣Leverage existing know-how! Why to move to cloud

[email protected] www.rittmanmead.com @rittmanmead Thin client modeller and Admin Tool

[email protected] www.rittmanmead.com @rittmanmead Your ETL Toolset

[email protected] www.rittmanmead.com @rittmanmead Your ETL toolset or Data Flows

[email protected] www.rittmanmead.com @rittmanmead !58 • Migrating to OAC is relatively
straight forwards ‣ The method depends on your starting point… Migration Options From BICS Create BAR File * Upload Based on Oracle Cloud Infrastructure Classic 11g Run the Migration Tool Deploy Import Bundle to OAC Minimum OBIEE 11.1.1.7 Does not apply to Autonomous version Manual Identity Store configuration 12c Create 12c BAR file Upload Snapshot to OAC or Manually upload Data Model Archive/Unarchive Web Catalog

[email protected] www.rittmanmead.com @rittmanmead !59 • DVCS to OAC/OACC ‣ Both
BICS and DVCS not sold anymore ‣ Must be migrated at some point • OAC/OACC to on-premises ‣ On-premises won’t go away ‣ Allow for easier / cheaper on-boarding and development • OACC has full accessibility • Supports all devops and lifecycle technologies known from OBIEE Migration Options

[email protected] www.rittmanmead.com @rittmanmead •Clean and correct first! •Old 11g was
a lot more permissive and fuzzy on certain rules •Clean on-premises means fluid migration •Fix-in-cloud often a lot harder •Review and reconsider your security •Eliminate legacy burdens •Trim unused excess •Institute standards Migration Considerations

[email protected] www.rittmanmead.com @rittmanmead INTRO 101 - OAC Overview and Features
Comparison

[email protected] www.rittmanmead.com @rittmanmead DATASOURCE101 - Creating New OAC Datasources

[email protected] www.rittmanmead.com @rittmanmead !63 •OAC can access data sources from
the cloud or on premise (depending on licensing) •Some examples of data sources are: ‣Oracle Database ‣Oracle Essbase ‣Hive ‣Impala ‣MySQL ‣SQL Server ‣Amazon Redshift ‣ODBC ‣and many more! Oracle Analytics Cloud Data Sources

[email protected] www.rittmanmead.com @rittmanmead !64 •New Data Sources (Connections) are added
through the Oracle Analytics Cloud Dashboard •Uses a Wizard to create the data sources, and then are available for use by anyone who has access Oracle Analytics Cloud Data Sources

[email protected] www.rittmanmead.com @rittmanmead !65 •Once you have created a data
source, you can create a Data Set. Which can be: ‣A Database Object (Table, View, etc) ‣File (Excel, CSV, etc) ‣Essbase Application ‣SQL Statements •Data Sets can be ﬁltered to remove data that is not required •Data Sets are managed within Visual Analyzer Oracle Analytics Cloud Data Sets

[email protected] www.rittmanmead.com @rittmanmead !66 •Once you have created a data
set, this can be used inside Visual Analyzer to create a project and visualisations based on that data Oracle Analytics Cloud Data Sets

[email protected] www.rittmanmead.com @rittmanmead !67 •Oracle BICS Remote Data Connector ‣Oracle
Business Intelligence Cloud Service Remote Data Connector (BICS RDC) enables querying data residing in on-premises relational sources without moving data to cloud. ‣Requires a Weblogic/Tomcat Server to host application •Oracle BICS Data Sync ‣Oracle Business Intelligence Cloud Service Data Sync supports loading data from ﬁles, on premise and cloud sources into schema provisioned on Oracle Business Intelligence Cloud Service. ‣Java based service that is installed on a server. Oracle Analytics Cloud Data Sets

[email protected] www.rittmanmead.com @rittmanmead !68 •In Oracle Analytics Cloud Dashboard ‣Navigate
to the data tab ‣Click the ‘Create’ Button ‣Choose ‘Connection’ Example Connection & Dataset: Step 1

[email protected] www.rittmanmead.com @rittmanmead !69 •On the ‘Create Connection’ dialogue ‣Select
the data source type required (e.g. Oracle Database) Example Connection & Dataset: Step 2

[email protected] www.rittmanmead.com @rittmanmead !70 •On the ‘Create Connection’ form ‣Enter
the details of the Connection ‣This will vary depending on the type ‣Click Save Example Connection & Dataset: Step 3

[email protected] www.rittmanmead.com @rittmanmead !71 •This will then be displayed on
the Data tab under Connections Example Connection & Dataset: Step 4

[email protected] www.rittmanmead.com @rittmanmead !72 •Next we need to create our
Data Set •This is done from the data tab, using the same ‘Create’ menu we used before Example Connection & Dataset: Step 5

[email protected] www.rittmanmead.com @rittmanmead !73 •Since we have used an Oracle
connection, we get a list of available schemas. •Double-click on the schema to continue Example Connection & Dataset: Step 6

[email protected] www.rittmanmead.com @rittmanmead !74 •We then get a list of
database objects we can choose and search through •We can also add individual columns •Data Sets are saved as objects in the OAC Dashboard Example Connection & Dataset: Step 7

[email protected] www.rittmanmead.com @rittmanmead !75 •Repeat this for any other data
sets that may be required •We can also add ﬁlters to data sets Example Connection & Dataset: Step 8

[email protected] www.rittmanmead.com @rittmanmead !76 •Once this has been completed we
can see the two new data sets in the Oracle Analytics Cloud Desktop under Data \ Data Sets Example Connection & Dataset: Step 9

[email protected] www.rittmanmead.com @rittmanmead !77 •These data sets can then be
used inside Visual Analyser projects ‣more on this later! Example Connection & Dataset: Step 10

[email protected] www.rittmanmead.com @rittmanmead DATASOURCE101 - Creating New OAC Datasources

[email protected] www.rittmanmead.com @rittmanmead !79 •OAC URL: http://ritt.md/BIWA2019 •User: training •Pwd:
Password01!! •Labs: http://ritt.md/BIWA-Labs Environment

[email protected] www.rittmanmead.com @rittmanmead DATASOURCE101 Lab 1 - Creating a new
Connection and Data Set

[email protected] www.rittmanmead.com @rittmanmead DATAVIZ 101 - OAC Data Visualization

[email protected] www.rittmanmead.com @rittmanmead !82 Exploration: • Scattered data • Uncertainty
• Hypothesis testing • Why did this happen • Detailed analysis • Learning from data • Analysis follows data Oracle Analytics Cloud Data Visualization Explanation: • Consolidated data • Certainty • Hypothesis confirmation • What happened • High level metrics • Teaching with data • Data guides analysis

[email protected] www.rittmanmead.com @rittmanmead !83 Data Visualization (Explore): • Scattered data
(Data mashups) • Uncertainty (Quick to build new charts, save analysis paths) • Hypothesis testing (Individual uses to test a theory) • Why did this happen (Look for patterns behind the trends) • Detailed analysis (Track multiple analysis paths) • Learning from data (fast workﬂow promotes creativity) • Analysis follows data (Try many things, save false starts, unlimited ﬁlter combos) Oracle Analytics Cloud Data Visualization Answers (Explain): • Consolidated data (RPD) • Certainty (Format dashboards to convey known information) • Hypothesis confirmation (Company gets the single source of truth) • What happened (Show tiles of KPIs) • High level metrics (Present the most relevant information quickly) • Teaching with data (Slower workflow promotes best practice presentation) • Data guides analysis (Dashboard creator guides users with selective use of drill down and filtering)

[email protected] www.rittmanmead.com @rittmanmead !84 • Everything placed on the canvas
is automatically connected • Filtering ‣Global filters affect all charts • Brushing ‣Highlighting data points in one visualization highlights them in all visualizations • Drilling ‣Drilling on one visualization automatically filters the others Oracle Analytics Cloud Data Visualization

[email protected] www.rittmanmead.com @rittmanmead !85 • The subject area view is
similar to Answers Oracle Analytics Cloud Data Visualization

[email protected] www.rittmanmead.com @rittmanmead !86 • The data exploration bar contains
options for chart creation based on the grammar of graphics • A grammar of graphics is a tool that enables users to concisely describe the components of a graphic Oracle Analytics Cloud Data Visualization

[email protected] www.rittmanmead.com @rittmanmead !87 •The canvas displays the chart •Filters
can be added by dropping columns into the ﬁlter view Oracle Analytics Cloud Data Visualization

[email protected] www.rittmanmead.com @rittmanmead !88 • Drag and drop a measure
and an attribute from the subject area onto the canvas Note: Hold the ctrl key to select both at the same time • VA will automatically generate a chart for your single measure • Time attributes automatically generate line charts Oracle Analytics Cloud Data Visualization

[email protected] www.rittmanmead.com @rittmanmead !89 • Select a chart and drag
additional measure or attributes to the data exploration bar to modify the chart’s properties • Add filters by dropping columns into the filter bar • Modify the filter to limit values Oracle Analytics Cloud Data Visualization

[email protected] www.rittmanmead.com @rittmanmead !90 • Visualizations can be dragged and
dropped to create diﬀerent layouts • Each visualization will automatically adjust its size to ﬁll its new placement Oracle Analytics Cloud Data Visualization

[email protected] www.rittmanmead.com @rittmanmead !91 • On the menu header go
to save/save as and give a name to your project Oracle Analytics Cloud Data Visualization

[email protected] www.rittmanmead.com @rittmanmead DATAVIZ 101 - OAC Data Visualization

[email protected] www.rittmanmead.com @rittmanmead DATAVIZ101 Lab 1 - Creating a VA
Project

[email protected] www.rittmanmead.com @rittmanmead MASHUPS101 - Using External Data & Data
Flows

[email protected] www.rittmanmead.com @rittmanmead !95 •Adding your own data is sometimes
referred to as “mash-up” •Data mashups provide the ability to introduce out of band or personal data into a governed data environment. •The expectation is to continue to maintain the self-service model •The goal is to enable the behaviors with a minimum amount of intervention. •Add your own data to analyze on its own. •Add your own data as an extension to an existing subject area. Concepts

[email protected] www.rittmanmead.com @rittmanmead !96 •Create new VA project and select
an existing or new data set and add it to the project Import External Data Source

[email protected] www.rittmanmead.com @rittmanmead !97 • data relationships Import External Data
Source

[email protected] www.rittmanmead.com @rittmanmead !98 •Columns from external subject areas can
be added and manipulated as before •Select measures and attributes from both subject areas then drag and drop to the visualisation and data element area: Combine External and Enterprise Data

[email protected] www.rittmanmead.com @rittmanmead !99 •To refresh data you must ensure
that the newer spreadsheet ﬁle contains a sheet with the same name as the original one •The sheet must contain the same columns that are already matched with the subject area. •In the Data Sources pane, or the Subject Areas pane, right-click the data that you want to refresh. •Reload Data Data Refresh

[email protected] www.rittmanmead.com @rittmanmead !100 •To remove a datasource from a
project right-click the data that you want to remove. •Select remove from Project •To permanently delete a data source right-click the data that you want to remove. •Select delete to erase the data from storage. Remove and Delete Data

[email protected] www.rittmanmead.com @rittmanmead !101 •Data ﬂows are used to create
‘curated’ Data Sets ‣ Manipulate data sets to add additional columns ‣ Join Data Sets together ‣ Filter Data Sets ‣ Sentiment Analysis ‣ Machine Learning Training ‣ Create Essbase Cubes Data Flows

[email protected] www.rittmanmead.com @rittmanmead !102 • In Oracle Analytics Cloud, go
to the Data Tab • Select Create -> Data Flow: Creating a New Data Flow: Step 1

[email protected] www.rittmanmead.com @rittmanmead !103 • A new tab will open
and ask you to choose a starting data set (or to create a new one): Creating a New Data Flow: Step 2

[email protected] www.rittmanmead.com @rittmanmead !104 • This will take you to
the Data Flow editor where we can start manipulating our data Creating a New Data Flow: Step 3

[email protected] www.rittmanmead.com @rittmanmead !105 • Click the + icon next
to the data set you imported at the beginning to add a new step to your data ﬂow ‣This will raise a context menu for the diﬀerent types of steps you can use: Creating a New Data Flow: Step 4

[email protected] www.rittmanmead.com @rittmanmead !106 • Each step type has its
own options that can be conﬁgured: Creating a New Data Flow: Step 5

[email protected] www.rittmanmead.com @rittmanmead !107 • You then need to set
an output point to your data set ‣Data can be stored either in the cloud data storage or back to a database that you have created a connection too: Creating a New Data Flow: Step 6

[email protected] www.rittmanmead.com @rittmanmead !108 • You can then schedule the
Data Flow to run: Creating a New Data Flow: Step 7

[email protected] www.rittmanmead.com @rittmanmead MASHUPS101 - Using External Data & Data
Flows

[email protected] www.rittmanmead.com @rittmanmead MASHUPS101 - Lab 1 - Using External
Data

[email protected] www.rittmanmead.com @rittmanmead AA01 - Advanced Analytics

[email protected] www.rittmanmead.com @rittmanmead !112 •Description of Metrics and Attributes Augmented
Analytics

[email protected] www.rittmanmead.com @rittmanmead !113 Key Drivers/Anomalies

What Problem are we Trying to Solve? Supervised Unsupervised “I
want to predict the value of Y, here are some examples” “Here is a dataset, make sense out of it!” Classiﬁcation Regression https://towardsdatascience.com/supervised-vs-unsupervised-learning-14f68e32ea8d Clustering

[email protected] www.rittmanmead.com @rittmanmead !115 •Data Visualization provides one click access
to ‣Trending ‣Clustering ‣Outlier Detection ‣Forecasting BI Analyst Experience

[email protected] www.rittmanmead.com @rittmanmead !116 DataFlow Train Model

[email protected] www.rittmanmead.com @rittmanmead !117 More Detailed ML

[email protected] www.rittmanmead.com @rittmanmead !118 Which Model - Parameters To Pick?

[email protected] www.rittmanmead.com @rittmanmead !119 Select, Try, Save, Change, Try, Save
…..

[email protected] www.rittmanmead.com @rittmanmead !120 Model Evaluation

[email protected] www.rittmanmead.com @rittmanmead !121 Model Evaluation 502/(502+896) = 64.09% 471/(471+866)=64.77%

[email protected] www.rittmanmead.com @rittmanmead !122 Use On the Fly

[email protected] www.rittmanmead.com @rittmanmead !123 Step of a Data Flow

[email protected] www.rittmanmead.com @rittmanmead AA01 - Advanced Analytics

OAC Workshop - From A to Z

OAC Workshop - From A to Z

More Decks by FTisiot

Other Decks in Technology

Featured

Transcript