Category Archives: Rittman Mead
Analysing Social Media Activity with ADW and OAC
Yesterday I wrote a series of tweets talking about my Twitter activity analysis with Oracle's Autonomous Database and Oracle Analytics Cloud. Since the tweet became popular and I didn't share the steps, I thought a blog post should follow, so... here we are!
[1/N]
— Francesco Tisiot (@FTisiot) October 27, 2020
I wanted to analyse my Social Media data.
I exported my @Twitter data and analysed with the #OracleText in the #OracleAutonomousDatabase @OracleDatabase and @OracleAnalytics Cloud
Starting from the easy… which is the tweet with most likes?
hint: Becoming @oracleace #ACED! pic.twitter.com/gUQR0Ur7k7
Getting the Data from Twitter
There are many ways that you can get data from Twitter. For the sake of my exercise I requested a dump from my data from the Twitter website. You can request the same following these instructions.
Once the data is ready, you'll receive a mail or a notification and you'll be able to download the related zip file named twitter-YYYY-MM-DD-hashed_string.zip.
Once unzipped you'll see two folders:
assetscontaining files you attached in your tweets (mostly images)datawhere the actual interesting information is.
Within the data folder I concentrated on the tweet.js file which contains the tweet information. There are other interesting files such as followers.js or like.js but I didn't include those datasets in my analysis as of now.
Warning: the tweet.js dataset contains only the tweets written by you. It doesn't contain all people tagging you in a tweet or replies to your tweets.
The Your archive.html file allows you to browse the content on the folder from a web browser
Importing Data into ADW with SQL Developer Web
The next step in my analysis is to import the data into the Autonomous Data Warehouse. For this task, I used SQLDeveloper Web, available by default in ADW, which has a powerful utility to import JSON documents as rows in a relational table. Jeff Smith's post covers the process in detail.
Unfortunately when trying to import into ADW the file tweet.js I encountered an error due to the fact that the file itself is not a pure JSON file, pretty clear if you check the file itself
window.YTD.tweet.part0 = [ {
"tweet" : {
"retweeted" : false,
"source" : "<a href=\"http://twitter.com/download/iphone\" rel=\"nofollow\">Twitter for iPhone</a>",
.....
"lang" : "en"
}
},
....
{
"tweet" : {
"retweeted" : false,
"source" : "<a href=\"http://twitter.com/download/iphone\" rel=\"nofollow\">Twitter for iPhone</a>",
.....
"lang" : "en"
}
}
]The first item to remove from our file is the window.YTD.tweet.part0 = prefix. I believe this is due to the pagination of the results, but it clearly screws up the JSON formatting.
Once removed I could parse the tweet.js file with SQLDeveloper Web, but the table definition proposed had only one column with containing the whole TWEET JSON document.
At this point, I could either accept this and do further processing using Oracle's SQL JSON parsing functionality or slightly change the shape of the file to be ingested correctly and I opted for the second.
When importing JSON documents into rows, SQLDeveloper Web analyses only the first level or attributes in the document itself. In the case of our tweet.js file was something like
{
"tweet" : {
...
}
},
{
"tweet" : {
...
}
},The first level parsed by SQL Developer Web was correctly only extracting the tweet element and proposing a CLOB (JSON) column to store it. But I wanted the content of the tweet to be parsed. I ended up removing the first layer by substituting in the file any occurrence of },{ "tweet" : { with a simple comma and removing the initial and final parenthesis.
The file now looks like the following
[ {
"id" : "279908827007180800",
"created_at" : "Sat Dec 15 11:20:39 +0000 2012",
"full_text" : "Finally at home after #christmasparty... Looooong travel!",
"lang" : "en"
...
}
,{
"id" : "276794944394498048",
"created_at" : "Thu Dec 06 21:07:12 +0000 2012",
"full_text" : "@mRainey will you be there next week too? Enjoy uk and visit #italy if you can!",
"lang" : "en",
...
}
...
}]We can now parse the file with SQL Developer Web, and the output correctly identifies all the first level entities in the JSON document.
Few more clicks and we have our table FRANCESCO.TWEET_DATA_SQLDEV populated automagically!
Note: The whole process above could be implemented and automated in several different ways, the aim of the blog post is only to demonstrate the feasibility of the analysis.
Text Tokenization with Oracle Text
The Tweet's FULL_TEXT column tokenization is done in the Oracle Database, you can see the full process described by the following video produced by the Oracle Analytics PM team.
If you prefer a short version of it, here it is: I basically created the following index
CREATE INDEX "FRANCESCO"."TWITTER_TWEET_IDX" ON "FRANCESCO"."TWEET_DATA_SQLDEV" ("FULL_TEXT") INDEXTYPE IS "CTXSYS"."CONTEXT";The INDEXTYPE IS "CTXSYS"."CONTEXT" creates an index using Oracle Text. A more accurate description of the procedure can be found in OracleBase's post.
Once the index is created, we can see some new tables appearing with the name DR$INDEX_NAME$LETTER with
$DR$being a fixed prefixINDEX_NAMEthe name of the indexLETTERa single letter betweenI,K,N,U,Rwhich meaning can be found in this Document
For the purpose of our analysis, we'll focus on the DR$TWITTER_TWEET_IDX$I table which contains the tokens of our FULL_TEXT column.
But the token by itself is not very useful, we need to match the token with the Tweet's ID to be able to provide meaningful analysis. Again, this is covered nicely by another video created by the Oracle Analytics PM team.
In order to associate the Token with the original Tweet we can use again the power of Oracle Text and the Index created above with the following query
SELECT
full_text,
score(1) AS text_score,
token_text,
a.id
FROM
tweet_data_sqldev a,
(
SELECT DISTINCT
token_text
FROM
dr$twitter_tweet_idx$i
)
WHERE
contains(a.FULL_TEXT, '/' || token_text, 1) > 0Again for more info about Oracle Text's CONTAINS function please refer to the relevant documentation.
I physicalized the output of the above query in a table ( TWEET_TOKENS), which contains the TOKEN_TEXT together with the Tweet's ID so we can now join this table with the original one containing the list of Tweets in Oracle Analytics Cloud.
Note: One of the next versions of Oracle Analytics Cloud will provide the Tokenization as step of a DataFlow within the Database Analytics options! You'll be able to tokenize your strings without leaving OAC.
Analysis of the Data in Oracle Analytics Cloud
If you're a frequent blog reader, this is probably the easiest part. I just had to:
- Create a connection to my Autonomous DataWarehouse by using the wallet file.
- Create the two datasources: one for
TWEET_DATA_SQLDEVand another forTWEET_TOKENS - Create a project and include both Datasources
Once in the project, the first visualization is about the most "liked" tweet... no surprise is when I become Oracle Ace Director, back in 2019, during KScope Keynote!
This first visualization is ok, but not really using any of the Oracle Text capabilities exposed in the Tokens... so my next analysis was...
Which words do I use more often when tweeting?
Easy... with the two datasources created above!
How does it change when I reply to people?
Well, you can see that words about OUG sessions and Oracle are still there, but there is the emerging topic of Food!
Who do I interact most with?
I plotted the # of Replies to specific Users…
No wonder (at least to me) that I get the most interactions with people that tag me with Italian food abominations…
And... how do I reply to them?
Again, very easy with Oracle Analytics Cloud Trellis Options.
You can spot that Food is the major discussion topic with HeliFromFinland and dw_pete, while my chats with @connor_mc_d and @stewartbryson are covering more topics
What about Nephentur?
One Last mention to my interactions with Nephentur: It’s clear his love for OUGs, Airports, Flights, Hotels… all driven by #TheBergEffect
Hope you liked the story, just an example of what you can do with a dataset you own and tools available in the Oracle Cloud!
OA Summit 2020: OA Roadmap Summary
If you are in the Oracle Analytics space, the OA Summit is a great source of content: from Keynotes, customer stories, Partners deep dives; the website is a collection of stories about Oracle Analytics. We've been part of the initial episode talking about how you can start your journey to Data Science with Oracle Analytics!
In Tuesday's session, Gabby Rubin, VP of Product Management, and Jacques Vigeant, Senior Director Product Strategy, shared a series of insights about the product roadmap that we'll cover in this blog.
Public Roadmap & IdeaLab
This is quite big news in the Oracle space, now there is a clear vision about what's coming in the product, accessible by everyone on a public website.
The public roadmap works well also in conjunction with IdeaLab: a place where everyone in the OA community can suggest new product features and up/downvote or add comments on other people's ideas. These hints are reviewed by Product Managers and, if considered valid, included in future releases of the product!
Converged Analytics
The state of the art in the Analytics space offers a neat separation between self-service tools and centralized IT-governed ones. Even for Oracle Analytics, we have two separate approaches as of now: self-service data preparation via Data Visualization vs enterprise IT-driven via RPD.
What was announced at the OA Summit is that the two approaches will converge: there will be the option to self-create a multi-table and multi-source federated dataset which can be shared. This approach empowers the end-user and works on top of the usual self-service data-source definition workflow enabling for each identity in the diagram, ML-based transformations and enrichment recommendations as well as caching setting definitions.
Empowering the end-user also means enabling best software development practices like versioning, certification and promotion capabilities on top of the asset created. The multi-table federated dataset created by end-users will seamlessly transition into enterprise-level semantic models without IT intervention.
From the enterprise side, as announced during OOW19, we'll see a new web-based semantic modeler which will substitute good old Admin Tool. Unlike the current "Lite Data Modeler", the new product will enable the same level of definition complexity we can find in today's RPDs thus will be compatible with every OAC/OAS prebuilt repository. The new web-based semantic modeler is not only a pure replacement of the Windows-based admin tool, but it also offers a native source control integration with Git and in-built options for Data Lineage explorations.
As mentioned the new tool is web-based; if you're an expert RPD developer and worried about different development methodology slowing down the build process, well, there is big news for you! You will be able to edit Repositories via the new JSON based Semantic Modeling Markup Language! Yep, you'll be able to define all the layers of an RPD in JSON syntax, even outside the web-based semantic modeler tool itself. This opens a huge variety of opportunities for source-control, CI/CD pipelines, as well as automatic (coded) builds of RPD artifacts.
Oracle Analytics as Enterprise Keystone
As briefly described in my Data Virtualization blog post, Oracle Analytics can (and should) be considered the Analytics Keystone in the enterprise: the convergence of IT-driven and self-service metadata models can already be exposed via ODBC in OAS enabling the downstream application to access the data inheriting the security settings defined in the Repository. The OA team is working to offer the equivalent in JDBC format for all Oracle Analytics products!
Oracle Analytics is also enhancing the number of source-system connectors available: we'll soon have the option to connect directly to Oracle EPM Cloud from Data Visualization, and similar connectors are arriving for JDBC sources and Google's Big Query.
Machine Learning in OA
Augmented Analytics and Machine Learning capabilities have existed for a long time in Oracle Analytics. A new important improvement in this area will enable Data Flows to use Machine Learning models created outside OA via the usual GUI.
Data Scientists will be able to use their favourite environment and toolset in order to analyse the data and build models. The models built in external systems like Oracle Databases, Oracle Data Science Services, or 3rd-party services could then be registered within OA and used to score data in Data Flows making the Data Scientist and Data Analyst collaboration much easier increasing the ML ubiquity in enterprises.
Predictions explainability will also be possible directly in OA, with details of each model exposed appropriately depending on the model type.
In addition to the above, OA will also allow the usage of Advanced Database Analytics like Sampling, Un-pivoting, Clustering, Anomaly Detection or tokenization with more to come. This allows the usage of already existing functions (avoiding to reinvent the wheel) that can perform securely on massive amounts of data in the Oracle Database.
Data Visualization New Features
There is also some news which has already been shipped in the latest product or will be available soon. Some examples are:
- Adaptive content: we can enable content scrolling in Data Visualization projects, allowing proper spacing of otherwise crowded visualizations
- Canvas Filter control: a prompt that can filter only a subset of analysis registered to it (similar to the Master-details concept in Analysis)
- OAC Embedding Framework: That allows to integrate OA capabilities into external web applications
- Digitalized Map Layers: create an infographic on top of any image, all directly in OAC
OA on Mobile
What about consuming OA content in mobile devices? Data Visualization content is adaptive, the visualization size and appearance will change depending on screen type, so all created content could simply be accessed via mobile browser. Oracle is also investing in Day by Day, which acts as a personal proactive Data Assistant and now enables the threshold-based notifications with more visualization types coming later in the year.
The new announcement regarding mobile is the new Oracle Analytics Mobile App which substitutes the Oracle BI Mobile HD and will provide a way to use, search and collaborate on curated content exposed by OA with an experience in line with modern apps.
More on this, All the Oracle Analytics Apps will enable collaboration: several people will be able to access and comment on visualizations and data avoiding the need to switch to other tools like emails.
A whole new wave of capabilities is in the Oracle Analytics roadmap, for any doubt or questions feel free to reach us!
Data Virtualization: What is it About?
The fast growth of company's data, data-sources and data-formats is driving an increasing interest in the field of Data Virtualization; what is it about? And what tools can provide this functionality?
Data Virtualization defines an approach to expose data coming from disparate data sources via a common interface to downstream applications.
Do you have your sales data in an Oracle Database and client peculiar information in some Cloud App? Data Virtualization will show them as an unique data source while, in the backend, will fire the proper queries to source-systems, retrieve the data and apply the correct joining conditions. Data Virtualization abstracts the technical aspects of the datasources from the consumer. Unlike the ETL approach, there is no data copy or movement, with source systems accessed in real-time when the query is executed (also called Query in Place (QIP)).
To be clear: Data Virtualization is NOT a replacement of ETL, both paradigms are valid in specific use-cases. Data Virtualization is a perfect scenario when reduced amounts of data coming from various systems need to be joined and exposed. On the other side, when massive amounts of data need to be parsed, transformed and joined and data-retrieval speed is the key, then an ETL (or ELT) approach is still the way to go.
Data Virtualization Components?
So far we had a look at the theory and goals of Data Virtualization, but what are the main components of such a tool? We can summarize them in four main points in our source-to-user path: Data Access Optimization, Business Logic, Data Exposure and Security.
What should each layer do? Let's see them in detail.
Data Access Optimization
In this layer, the connection to data-sources needs to be defined, tuning parameters set and data-points of interest determinate. This query should be responsible for optimizing the query pushdown to source systems in order to retrieve the minimal row-set that needs to be displayed or joined with other datasources.
The Data Access Optimization layer should also be able to handle different datasources with different capabilities (e.g. aggregation functions) and, in case pushdown is not possible, perform transformations after data load. Finally, to ease the stress on the source system, caching options should be available in this layer.
This layer should also dictate the methodology of datapoints access, defining (with the help of the Security layer) if a certain column can be read-only or also written.
Business Logic
The Business Logic Layer should be responsible for translating data-points to company-defined metrics or attributes. Datapoints can come from various data-sources so it should also contain the definitions of joining conditions and data federations.
Metrics and attributes need to have aggregations and hierarchies defined in order to be used by downstream applications. Hierarchies can also help with vertical data federation, defining exactly the granularity of each datapoint, thus enabling query optimization when more aggregated datasources are available.
The Business Logic Layer is also responsible for decoupling Business Logic from datasources: when a change in a datasource happens (e.g. the creation of a datamart or a database vendor change) the metrics and attributes layout will not be altered, all the changes will happen in the Data Access Optimization and hidden in the Business Logic Layer definitions.
Data Exposure
The Data Exposure Layer is the one facing downstream applications or users, in this layer metrics and attributes should be organized in business-driven structures and made accessible via various methods like web-interfaces, ODBC, SOAP or REST.
Datapoints defined at this layer should contain business descriptions and be researchable via a data catalog allowing the re-usage of pre-built content.
Security
Exposing data to downstream applications needs to happen securely, thus datapoint access rules need to be defined. Data Virtualization systems should integrate with the company's Identity management tools to identify and dictate who can access any particular datapoint.
Security should not only work as ON/OFF, but also allow the access to subsets of data based on privileges: e.g. a country manager should only see data from his/her own country.
Security is not only about defining boundaries but also about auditing the correct access to data (GDPR?). Data virtualization systems should allow security evaluations or provide pre-built security checks.
Data Virtualization in Oracle
As you might understand, Data Virtualization is a new, hot and growing topic; what has Oracle to offer since there isn't an official Oracle Data Virtualization tool? Well, check the picture below:
This is an Oracle Analytics repository (RPD)! A tool created in 1998, acquired by Oracle in 2007 and with a long history of successful implementations! The reality is that Data Virtualization is not a new topic (we talked about it in 2008), but simply an old capability, with a new name!
Oracle Analytics RPD's three-layer concept matches exactly the Data Access Optimization, Business Logic and Data Exposure layers mentioned above providing the same functionality. The Security is a key component across the whole Oracle Analytics Platform and successfully integrates with all major identity providers.
Oracle Analytics for Data Virtualization
Oracle Analytics is a tool born for data analytics but solves successfully the problem of Data Virtualization: data from different source systems can be queried, joined and exposed.
OA is capable of firing queries based on fields selected by the downstream application and optimized for the datasource selected. Query pushdown is enabled by default and specific datasource features can be turned ON/OFF via configuration files. Specific transformations can happen in OA's memory if the datasource doesn't allow the pushdown. Results of queries can be cached and served for faster response.
In scenarios where massive amounts of data need to be sourced, probably Oracle Big Data SQL or Cloud SQL could be used, pushing part of the virtualization at the database level.
Vertical and horizontal Data Federation can be defined, so data can span across various tables and aggregated datasources can be used for faster response. Metrics, attributes, hierarchies and joins are defined in the model thus related complexity is hidden from downstream applications.
Data exposed can be accessed via a web browser using the traditional Answers and Dashboard or the innovative Data Visualization. Data can also be extracted via SOAP APIs and via ODBC when the related component is exposed. Data Sources defined in Data Visualization and BI Publisher can also be extracted via REST APIs. There is a plan to extend JDBC access also to RPD defined Subject Areas.
A utility, called Metadata Dictionary, automatically generates a Data Catalog which can be used to expose and share what datapoints are available. In the future, Oracle Analytics Cloud datapoints will also be available via Oracle Data Catalog, a specific offering around this area.
The security included with the tool allows datapoint access definition to specific roles as well as limits on data exports size or access times. Security settings can be exported and audited via external tools. The platform usage can also easily be monitored for performance and security reasons.
Self-service Data Virtualization
All we discussed so far describes the IT-driven Data Virtualization where the access to data is only managed by a restricted group of people (usually the IT department). Oracle Analytics enables it alongside Self-Service Data Virtualization, where new datasources, joins and security layers can be defined directly by Business Users enabling a faster and secure data sharing process which can still be audited and controlled.
Do you want to expose the business unit's forecast data in your Excel file alongside the revenue coming from your datamart? With Oracle Analytics this is only few clicks away including the security options controlling the audience.
Oracle Analytics offers both top-down and bottom-up approaches to Data Virtualization at the same time! IT-Driven, highly secured and controlled data-sources can coexist with user-defined ones. Joins and metadata models can be built from both sides and promoted to be visible by wider audience, all within one environment that can be tightly secured, controlled and audited.
Conclusion
Data Virtualization is a hot topic indeed, with the ever-increasing number of different datasources in a company's portfolio we'll see the rise of data abstraction layers. But don't look at new shiny tools to solve an old and common problem!
As described above, Oracle Analytics (Cloud or Server) can successfully be used in Data Virtualization contexts. Deployed in conjunction with Oracle Big Data SQL or Cloud SQL and Oracle Data Catalog offers an extremely compelling solution with a set of tools and knowledge already available in the majority of companies.
Getting Smart View to work with OAC
I wanted to demonstrate what I thought would be a simple task to a client today, however it turned out to be a little more complex than I first anticipated, so I thought I would publish here. All I needed to do was get Smart View connecting to OAC.
Prerequisites
- Excel installed
- OAC up, running and available
Approach
- Download Smart View (see here) - for reference I downloaded version 11.1.2.5.910
- Double click to install and accept the defaults
Establishing a Connection
When I last did this (not sure how long ago that was), I think the OBIEE extension was installed by default, so I put in the OAC connection string and then couldn't work out why I kept getting an error. Fortunately a colleague of mine pointed out the OBIEE extension wasn't actually installed by default any more, so that's what I needed to do next.
Install the Extension
- Open Excel
- Create a Blank Workbook
- Select the Smart View menu item
- Select Options
Go to Extensions
The OBIEE extension is no longer installed out of the box
You need to check Check for updates when Microsoft Office starts, then you should get a link above saying Check for Updates, New Installs and Uninstalls
Select the OBIEE Extension to install, once successful you should see
Now you can create a connection to OAC
Create a Connection
- Select the Panel
- Choose Private Connection, then Create new connection from the bottom of the screen
Choose OBIEE for the connection type
Enter the URL in the following format:
https://<your-oac-host>/analytics/jbips
My example was
https://dv1dfree-xyzabc-ld.analytics.ocp.oraclecloud.com/analytics/jbips
You'll be asked to logon
You can then give the connection a name to save it as
Then you should see the Catalog displayed on the right
If you select a View you will get a number of options of how to display it, the simplest is insert.
Once selected, this will load the data into the sheet.
Oracle Analytics: Everything you always wanted to know (But were afraid to ask)
The release of Oracle Analytics Server (OAS) on 31st January 2020 has left many OBIEE users wondering what it means for them. Common questions we were asked at Oracle OpenWorld Europe last week included:
- what’s the difference between OAS, OAC and OBIEE?
- where does DV fit into this?
- should I be migrating and if so, when; what are the benefits?
This blog post aims to answer all these questions!
First of all, let’s define each product in order to compare them.
Oracle Analytics Cloud (OAC)
Oracle’s strategic analytics platform, hosted in the Oracle Cloud.
Oracle Analytics Server (OAS)
OAS is the new on-prem version of OAC, and is intended as a bridge between legacy OBIEE platforms and Cloud. It has almost complete feature parity with OAC, including AI-powered, modern and self-service analytics capabilities for data preparation, visualisation, enterprise reporting, augmented analysis and natural language processing and search. If you are an OBIEE customer, you are automatically licensed for OAS, and Oracle Data Visualisation (DV) is included at no extra cost.
OAS vs OAC
The main difference between OAS and OAC is related to hosting and administration. OAC is hosted and managed by Oracle, while OAS needs to be installed, configured and patched by you in your datacenter. This also defines the level of control and customisation: with OAS you have the full control over config files, styles, custom function etc, while in OAC you’ll be able to change only what’s exposed in the cloud console by Oracle.
OAC will receive more frequent updates and new features, with OAS scheduled to have an annual release bringing the cloud features to on-premise customers.
So the choice between the two depends on the amount of customisations needed vs the time spent on supporting the platform.
OBIEE vs OAS
OAS was developed to replace OBIEE, however the two products are not exactly the same. There are one or two OBIEE features that are deprecated in OAS, such as BISQLProvider or Act As, but they are still present in the tool, and they’ll not go away until a proper replacement is in place. On the other side, If you were using Scorecards, this tool is no longer shipped with OAS.
OAS on the other hand, brings almost functional parity with OAC, providing a huge amount of new features especially in the self-service area, more info in the dedicated post.
Can I connect to a Database in my Datacenter with OAC?
Yes you can, Data Visualization offers the option to connect to any datasource which is reachable from the Cloud. If you don’t want to expose your database directly, Oracle Data Gateway enables the connection from OAC (including RPD based connections) to on-prem data-sources without the need to open any firewall port.
Where does DV come into this?
Data Visualization (formerly known as Visual Analyzer) is Oracle’s self-service tool. If you’re an OBIEE 12c user, you may be paying extra license fees to use DV. If you’re already using OAC, you may have noticed DV is included with your license, and this will also be the case for OAS.
Ultimately, Oracle Analytics’ aim is to provide a mix and match offering, where you can choose which components are Cloud and on-prem. For example, you can upgrade to OAC and point it to your on-prem database. Or if you’re Cloud averse for whatever reason, you can migrate to OAS and utilise many of OAC’s features.
What does Rittman Mead recommend you do next?
There is probably a different answer for everyone, depending on where you are in your Oracle Analytics journey, so we’d recommend contacting us for an initial chat to go through your options. Broadly speaking, if you’re using OBIEE 11.1.1.7 onwards and you’re considering upgrading to 12c, you should factor OAS or OAC into your decision making process.
To help you decide which product is best for you, we are offering a FREE two-day Oracle Analytics assessment which aims to help you create a business case for migrating to OAC or OAS based on your pain points and anticipated usage. Contact us for more information.
Rittman Mead also provides OAC, OAS and OBIEE training. Our next OAC bootcamp is taking place in London, March 23rd - 26th 2020. For more information go to our training page or contact Dan Garrod.
