News and Views: Drive Smart Decisions with Cloud Analytics, Machine Learning and More

Recent Posts

Data Visualization

Understand User Behavior Using Oracle Analytics

How would you like to know what your software users are thinking and how they might react in the future? Oracle Analytics Cloud supports the accumulation of Usage Tracking (UT) statistics that help in monitoring system usage and performance to understand and predict user behavior. Knowing in advance what choices a user is likely to make helps increase efficiency and reduce errors. These statistics can be used in a variety of ways such as in system or database optimization, aggregation strategies, or internal billing of users/departments based on the resources that they consume. Usage tracking is particularly useful in determining user queries that are creating performance bottlenecks based on query frequency and response time.  Previous versions of Oracle Business Intelligence Server can track usage at a detailed query level. When Usage Tracking is enabled, it collects data records for every query that is executed and writes them all to database tables. Both logical and physical queries are tracked and logged in separate tables, along with various performance measures:  The following video gives a high-level overview of how to set up Usage Tracking on your Oracle Analytics Cloud environment.     Prepare to Enable Usage Tracking We're going to dive into this process, but first you will need to meet the following conditions to enable Usage Tracking in your environment: Usage Tracking requires a full metadata repository database (RPD) to be enabled. It will track usage on any queries, even outside the RPD, but it only requires an RPD for its configuration. For example, UT cannot be enabled when only a Thin Client Modeler (TCM) such as a web-based admin-tool model is active on your Oracle Analytics Cloud. UT requires access to a database to write data back into tables. A user login with create table privileges on the database schema will be needed during the configuration. Configure Usage Tracking UT configuration involves the following two steps:  Database connection in RPD: Using Administrator tool client (the editor for the RPD repository), define a database connection in the physical layer of your RPD. This connection should point to a database where Usage Tracking tables will be created and maintained. This database can be anywhere on the cloud as long as it is accessible for write access by Oracle Analytics Cloud.  System Settings in Oracle Analytics Cloud: Configure Usage Tracking parameters, such as connection pool name, physical query table name, and logical query table name.   Define a Database Connection in RPD Open the RPD which is uploaded in Oracle Analytics Cloud and create a new database in the physical layer. Provide an appropriate name (e.g., UsageTracking) and choose the database type as Oracle 12c. Under this database, create a new Connection Pool with an appropriate name (e.g., UTConnectionPool). Provide the connection details to the database where UT is to be configured and login credentials to the schema.  Note: This database user needs to have table privileges on the schema.    Next, create a physical schema in the RPD with the same name as the database schema to be used by UT (e.g., UT_demo).  Once these definitions are complete, the physical layer should look like this.    Save the RPD and upload it onto Oracle Analytics Cloud using the Replace Data Model option within the Service Console. System Configuration On Oracle Analytics Cloud, open the System settings screen from the Console Tab.  Scroll down and modify the following properties. 1) Toggle on the Enable Usage Tracking button 2) Usage Tracking Connection Pool: Enter the connection pool name in the format: <Database>.<ConnectionPool>  Example: UsageTracking.UTConnectionPool 3) Usage Tracking Physical Query Logging Table: This is the table where details about the physical queries executed by Business Intelligence Server against the underlying database are tracked. Enter the name in the format:  <Database>.<Schema>.<Table> Example: UsageTracking.UT_demo.PhysicalQueries 4) Usage Tracking Logical Query Logging Table: This is the table where details about the logical SQLs executed by Business Intelligence Server are tracked. Enter the name in the format: <Database>.<Schema>.<Table> Example: UsageTracking.UT_demo.LogicalQueries 5) Usage Tracking Max Rows : Specifies the number of rows after which the UT tables will be purged. The default value is 0 which implies there is no purging. You can set it to any desired value and the records will be purged once that threshold is reached. After entering these details, restart Oracle Business Intelligence Server. Once the server is restarted, Usage Tracking is enabled, and the two UT tables are created in the database. UT Tables Explained Open SQL Developer, log in to the UT database, and observe that the two tables have been created.   To generate some usage tracking data, log in to Oracle Analytics Cloud and click around some Data Visualization (DV) projects, both Extended Subject Areas (XSA) as well as subject area based. Also, open the BI classic home and open a few dashboards to generate some queries. Now observe that the tables for logical and physical queries are populated with usage tracking information.     Important Columns in Logical Queries Table END_TS End time of query execution ERROR_TEXT Error message if the query has errored out ID Primary key QUERY_TEXT Actual query text RESP_TIME_SEC Total response time of query in seconds ROW_COUNT Number of rows returned by the query SAW_DASHBOARD Dashboard name where query is getting generated SESSION_ID Session ID from the user firing the query START_TS Start time of the query execution SUBJECT_AREA_NAME   Subject area used for the query USER_NAME User ID who has executed the query     Important Columns in Physical Queries Table ID Primary key LOGICAL_QUERY_ID  Foreign key from the logical queries table QUERY_TEXT Query text TIME_SEC Time taken by query to complete ROW_COUNT Number of rows returned by the query START_TS Start time of the query END_TS End time of the query   The join between the 2 tables can be performed using the join condition:      LogicalQueries.ID  =  PhysicalQueries.Logical_Query_ID.    Note that not all logical queries will generate a corresponding physical query. For example, some logical queries may hit cache and return results from cache. When this happens, logical queries will not generate a physical query. Analyzing UT Data Once UT is enabled, the system usage can be analyzed from DV. In order to do this, create a DV connection to the UT database, create datasets for the Physical Queries and Logical Queries tables and analyze them within a DV project.  Following is a sample analysis built on the UT tables that shows number of sessions, number of queries, most frequently used subject areas, most frequently used dashboards, and so on.  Conclusion Usage Tracking provides a mechanism for administrators to keep track of the usage of the Oracle Analytics Cloud system. These statistics can be leveraged to take decisions to scale up, scale down, restrict access during certain time periods, pause/resume the system, and so on. To learn how you can benefit from the latest features in Oracle Analytics, visit Oracle.com/Analytics, and don't forget to subscribe to the Oracle Analytics Advantage blog and get the latest posts sent to your inbox.

How would you like to know what your software users are thinking and how they might react in the future? Oracle Analytics Cloud supports the accumulation of Usage Tracking (UT) statistics that help in...


Learn Modern Data Visualization with Oracle Analytics

Are you a business analyst curious about what Oracle Analytics can do? We recommend a new online course designed to provide you with the essentials of augmented data visualization. It's called Modern Data Visualization with Oracle Analytics, and you can find it online here. If you want to check it out, please enroll—it's free. I know it's good because I volunteered to help Product Management build this online series as a Massive Open Online Course (MOOC) for Oracle Analytics. What will you learn? In this course, you will broaden your understanding of modern—or augmented—data visualization concepts through hands-on training with Oracle Analytics. And we designed this course so that you can jump right into a technical, hands-on product experience. You know how sometimes you volunteer for something at work and then almost immediately regret it? This isn't one of those times; in fact, quite the opposite. And why, you might ask, would you care that I have trouble turning down new work projects? Because you get something really useful out of it--a solid hand-on product tour of Oracle Analytics for analysts and business people. It was an intense project to get it all done and published, but as we built it, we came together as a team. I also learned a lot about MOOCs and now have a shiny new skill myself. Then the enrollments started coming in, and we're having a great time seeing who is taking the course, and what feedback they're sharing with us. We're creating a community, and that's so cool. I'd like to invite you to be part of that. Now down to business. Why a MOOC? Why this particular MOOC? How does it work? What's in it? Answering the "why" question is simple. With the breakneck pace of change in the analytics world, you need access to training that's self-paced, accessible, low cost, detailed, scalable, hands-on, and available anywhere: Helloooo, MOOC on Udemy.com. "Why this particular MOOC" became a subject of some heated debate, as we all realized that if we included everything we wanted, it would be far too long. Many discussions were had to finally nail down what topics to include.  We hope we hit the nail on the head with topics ranging from machine learning-driven visualizations, data blending and mash-ups, augmented data enrichment, advanced maps and charts, and how to narrate and present your analyses. "How it works" is simple as well. Just click here and enroll.   And look what's included in the course: 4 hours of learning videos and screencasts 45 lectures 5 business use cases 4 bonus projects Also, did I mention? It's free! We're targeting this first course for analysts and business people who want to see what Oracle Analytics can do first-hand. Every section is packed with both video and screencasts to showcase each analytics capability. There are also demo files and scripts to download so that you can try it yourself, for real, with the actual product, no marketing fluff. You'll master everything from built-in functions for advanced visualizations, machine learning-based visualizations, and how to collaborate and share your discoveries. Check out the course outline for the full list of topics. Since we can't guess which use case you're itching to try out, we've packed the course with different projects like sales analysis, school donation analysis, HR attrition analysis, as well as advanced projects such as machine learning models for predictive analysis. Curious about some other application for analytics? You can try it with your data too. I'll freely admit the full course is a time commitment. To help you get through it, we've split it up so you can do it in sections. Plus, Udemy has this terrific "offline" capability, perfect for long airplane business trips. Just think how well-honed your analytics chops will be once you're done. I hope you enjoy the course and gain many useful skills from it. This was a fun course to build, in a slightly insane kind of way, and I'm looking forward to creating more content in the future. Once you get started, let us know what you think and how we might improve Modern Data Visualization with Oracle Analytics going forward and visit the Oracle Analytics Cloud website.

Are you a business analyst curious about what Oracle Analytics can do? We recommend a new online course designed to provide you with the essentials of augmented data visualization. It's called Modern...