Francesco Tisiot - @FTisiot Analytics Tech Lead Become an Equilibrista Find the Right Balance in The Analytics Tech Ecosystem

Verona, Italy http://ritt.md/ftisiot Over10 Years in Analytics [email protected] @FTisiot Oracle ACE Director ITOUG Board President Francesco Tisiot Analytics Tech Lead

Data Engineering Analytics Data Science www.rittmanmead.com [email protected] @rittmanmead

A bit of History

Excel Hell

https://www.rittmanmead.com/blog/2018/11/where-are-my-users-coming-from-analysing-obiee-connections-methods/

Unique Source of Truth

No content

No content

No content

Calculated from Scratch Calculated from Scratch AGREEM ENT

Sourced from Certified Datasource Calculated from Scratch

Approach to Data Certification

Top-Down Approach Data Model Definition Implementation Analysis Centralized BI OBIEE - RPD LOOKER

Bottom-Up Approach Datasource Creation Analysis Datasource Publication Self Service Oracle Data Visualization Tableau

Top-Down vs Bottom-Up KPI Duplication Security Time To KPI Reusability Performance

Reusability / Security DataSource A DataSource B Shared Dimension(s) Centralised BI

Reusability / Security DataSource A DataSource B Dim X Self Service BI Dim X

Self-Service BI

Self Service Centralised

The Right Tool: OAC

Sourcing from Central System Select "Dim Account"."Account Code", "Fact Churn"."Churn Prediction" from "Churn" No Formula Recalculation No Join, Filtering, Aggregation Knowledge Security Inheritance No Code Duplication

No content

Hybrid

Self Service Centralised

Awareness

Documentation

RPD Metadata Dictionary

Suggestions

Complexity Key Factors Security Data Volumes Interest

Key Factors Centralised Self Service Complexity Low High Interest Narrow/Unique Wide/Repeated Security Easy Complex Data Volumes Small Big

Self Service Centralised ?

Control

Keeping Track Usage Tracking!

Usage Tracking SELECT DISTINCT USER_NAME, NVL(QUERY_SRC_CD, 'RPD ODBC') SOURCE, TRUNC(START_TS) TS FROM S_NQ_ACCT WHERE AND ( QUERY_SRC_CD IS NULL OR UPPER(QUERY_SRC_CD) IN ('SOAP', 'RAWSQL') ) AND QUERY_TEXT NOT LIKE '{CALL%' ORDER BY 3 DESC;

Self Service?

Calculation

”Dim Issue Status"."Issue Status”…> ”Fact Issue KPI"."# of Issues"…> Metadata

https://www.youtube.com/watch?v=mQY2vslle_8 REST Endpoints

https://www.youtube.com/watch?v=mQY2vslle_8 REST Endpoints curl “https:///datasetsvc/ public/api/v4/datasets” \ -u ‘:'

{ "provider": "managed", "requiresImpersonation": false, "semicolonDelimited": false, "customDelimiter": false, "name": "train", "namespace": "[email protected]", "business-model": "\"\"", "description": "Uploaded from train.csv.", "display-name": "train", "locale": "sys", "file-length": 460676, "type": "text", "data-last-modified": "2019-04-08T08:19:34.481+0000", "private-connection": false, "mime-type": "application/csv", "owner-guid": "[email protected]", "metadata-last-modified": "2019-04-08T08:22:20.928+0000", "bdp-profile": "{\"profile\":\"784c4626-ebd6-4a01-b5dc-1a3ccd0fee1a\"}", "is-temporary": false } REST Endpoints - CSV

{ …, "name": "OPEN_INCIDENTS", "namespace": "[email protected]", "business-model": "\"\"", "indexable": "BOTH", "description": "External Data Set", "display-name": "OPEN_INCIDENTS", …, "data-last-modified": "2019-03-19T16:07:22.732+0000", "connection-namespace": "[email protected]", "private-connection": false, "connection-name": "LGI", "provider-type": "db", "provider-name": "oracle", "owner-guid": "[email protected]", … } REST Endpoints - DB

REST Endpoints - DB Details api/v4/datasets/{dataset-uri} {dataset-uri} = ‘’.’’

REST Endpoints - DB Details

{ "id": "LGI", "leaf": false, "otype": "Schema", "name": "LGI", "children": [ { "id": "LGI.OPEN_INCIDENTS", "leaf": false, "otype": "dbtable", "name": "OPEN_INCIDENTS", "allColumns": [ { "datatype": "number", "name": "OPEN_INCIDENTS", "is-supported": "TRUE", "otype": "Column", "leaf": true, "id": "LGI.OPEN_INCIDENTS.OPEN_INCIDENTS" }, { "datatype": "varchar(30)", "name": "ISO_YEAR_WEEK", "is-supported": "TRUE", "otype": "Column", "leaf": true, "id": "LGI.OPEN_INCIDENTS.ISO_YEAR_WEEK" }, REST Endpoints - DB Details

REST Endpoints - Dataflows api/v4/dataflows/{dataflow-uri} {dataflow-uri} = ‘’.’’

{ "datagen-name": "Clean Wine Dataflow", "namespace": "[email protected]", "definition": { "steps": [ { "stepId": "InputDataset_0", "description": "winemag-data_first150k", "type": "InputDataset", "stepDisplayName": "winemag-data_first150k", "stepDescription": "", "datasetRef": "ds_0", "qualifiedTable": "XSA(ds_0).\"Columns\"", "datasetDescription": "Uploaded from winemag-data_first150k.xlsx.", "datasetType": "excel", "datasetDisplayName": "winemag-data_first150k", "inputType": "all", "parameters": [], "promptForInputSource": [] }, REST Endpoints - Dataflow Detail

{ "stepId": "AddColumns_0", "description": "Add Columns", "type": "AddColumns", "stepDisplayName": "", "stepDescription": "", "columns": [ { "name": "TRAIN OR TEST", "expression": "case when RAND() < 0.8 then 'Train' else 'Test' end", "datatype": "varchar(4000)", "aggrule": "none", "srcexpression": "case when RAND() < 0.8 then 'Train' else 'Test' end", "transformationtype": "Calculation" }, { "name": "Length of Description", "expression": "CHAR_LENGTH(\"description\")", "datatype": "integer", "aggrule": "sum", "srcexpression": "CHAR_LENGTH(XSA('[email protected]'.'__CLIENT_FLOW_DATASET__'). \"Columns\".\"description\")", "transformationtype": "Calculation" } ] } REST Endpoints - Dataflow Detail

No content

https://www.oracle.com/solutions/business-analytics/data-visualization/extensions.html

Data Governance Extension

Displays only objects visible to you!

BAR File

BAR File

DEMO

Oracle Data Catalog http://ritt.md/oow19-datacatalog Inventories of Data Assets

Oracle Data Catalog Inventories of Data Assets http://ritt.md/oow19-datacatalog

RM Metadata Explorer

RM Metadata Explorer Use Cases Data Lineage GDPR DataFlow -> ODI Mapping Metadata Search Tool Data Capital

Self Service Analytics

Centralized Reporting

Measure

Tech Days 2020 Milan 29th Jan Rome 31st Jan

Francesco Tisiot BI Tech Lead Become an Equilibrista Find the Right Balance in The Analytics Tech Ecosystem