Category Archives: Art of BI
It’s the End of the Road for MySQL 5.6
There are as many reasons why MySQL is the world’s most popular, open-source, relational database as there are developers who use it.
Since its launch in 1995, it has supplied the global developer community with the database management tools needed to build and deploy today’s uber-sophisticated software applications and systems. Each subsequent iteration has added more value to the already invaluable resource. As MySQL 5.6 reaches its End of Life cycle (EoL), newer versions – 5.7 and the most recent, 8.0(more than 2yrs) – continue to make the work of MySQL users more manageable while also increasing their productivity with innovative new feature set.
Most providers including Oracle, Amazon RDS and Azure Database for MySQL offer MySQL users upgrades to 5.7 and 8.0. Datavail’s professionals can help you make the best upgrade decision for your organization’s needs.
Welcome to MySQL
Version 5.6
Released on February 5, 2013, the MySQL 5.6 version has maintained, over time, a reliable popularity among the world’s app development community. As an open-source program, it has been tuned and refined by thousands of developers. Its most recent iteration provides users with fast, reliable, and scalable database management tools that facilitate corporate and market growth.
The Oracle community elected to discontinue support for 5.6 on February 5, 2021 (although it doesn’t hit its End of Life in Amazon RDS until August 3, 2021). Note that developers will add no further security patches to the programming through the ‘extended time’ between February 5 and August 3. Users who continue using it will experience a higher risk of intrusions.
Version 5.7
The next major release, MySQL 5.7, again increased the value of the system by providing cutting-edge enhancements and additions to the foundation set by 5.6. More than three times as fast as 5.6, MySQL 5.7 also enhances a number of its features.
End of Life for this version is slated for October 2023 Best practice is always to upgrade to the most current version (8.0) if possible.
Version 8.0
MySQL 8.0 first GA was released on April 19, 2018. Not surprisingly, this newest version of the database management program enhances all the productivity elements of previous versions while eliminating outdated or obsolete features that no longer carry value. It is designed to meet developers’ demands as they are tasked with addressing new challenges generated by the next gen mobile, web-based, embedded, and cloud applications.
Upgrade to Which Version?
Because of the EoL of MySQL 5.6, users now face making a tough choice between MySQL 5.7 and 8.0. Several benchmarks were run against. one industry member ran a series of tests to determine how the two matched up. Their conclusion: MySQL 8.0 significantly outperformed 5.7:
- 8.0 remained highly efficient even when the number of threads increased in each of the read, delete, insert and update functions.
- Transaction processing times also increased significantly in 8.0 versus 5.7.
- 8.0 also adds the ability to functional index and invisible indexes.
MySQL 8.0 is loaded with tons of new features and enhancements. However, testing with applications is a must prior to the cutover as there’s no easy roll back from 8.x after upgrades.
Users of MySQL 5.6 now find themselves in a dilemma. Not only is the DB beyond support in Oracle now (and will lose support in AWS RDS in August 2021), but it’s not recommended to upgrade directly from 5.6 to 8.0. Upgrades from the General Availability (GA) versions of 5.7 are supported, and to enjoy continued support, MySQL requires that upgrades start with version 5.7.9 or higher.
For on-premises and virtual implementations, users can choose an IN-PLACE or a LOGICAL upgrade for switching up from 5.7 to 8.0. An IN-PLACE upgrade involves shutting down the MySQL 5.7 server, replacing the binaries with 8.0 binaries, and then launching the 8.0 server using the previous data directory. A LOGICAL upgrade involves SQL dump/load to the newly installed MySQL version. Replication is supported from 5.7 to 8.0 versions.
In RDS AWS, the upgrade from 5.7 to 8.0 is defined as a major upgrade because each version is a major version of the DB. For this process, users must first change the DB engine through AWS CLI, RDS API, or AWS Management console, then follow the steps to complete the upgrade.
Datavail Endorses MySQL 8.0
The database management professionals at Datavail agree with their industry colleagues that MySQL 8.0 offers users the best available features and tools to improve and enhance their organization’s security and performance. In general, the MySQL series of releases has always provided exceptional services through its robust and timely features, its capacity for granular scalability, and its easy integration with today’s native and web-based applications. Version 8.0 adds more benefits to the overall package. Datavail’s favorite 8.0 enhancements (so far):
- The document store! MySQL 8.0 resolves the SQL and NoSQL riddle by introducing X Dev API, enhancing the uses of schema-less JSON collections and SQL tables. The 8.0 upgrade makes it possible to match relational data with JSON documents within the single DBMS. The document store’s reliability, consistency, high availability, and enhanced Transparent Data Encryption (TDE) authentication protocols give experienced and new users unparalleled control over app development across languages and databases.
- SQL enhancements include Common Table Expressions (CTEs) that can be both recursive and non-recursive. By also integrating Windows functions through a standard OVER keyword, MySQL now competes easily with SQLite and PostgreSQL.
- As noted above, schema-less JSON support returns JSON data as relational data, allowing querying JSON results the same way as querying regular relational tables. Additional JSON functions in MySQL 8.0 include added utilities [JSON_STORAGE_SIZE ()] and JSON_ARRAYAGG (). These added features improve the speed of replication and remove ambiguities.
- More Enhanced HA solutions like InnoDB Cluster with Group Replication, InnoDB Replica Set.
- MySQLShelll loaded with a variety of Utilities and easy to script for DBAs and Support teams
Overall, MySQL 8.0 delivers multiple performance, cost, and development features that will improve the work and workloads of every enterprise.
AWS & Oracle & MySQL 8.0
All things considered, upgrading to MySQL 8.0 now appears to be everyone’s best option. Not only will the 5.6 version lose all support by August, but the 5.7 version will lose its support in October 2023, a scant two years away. Planning for Upgrades to MySQL 8.0 is more advisable.
Users on AWS will be pleased to know that its RDS supports DB instances running both 5.7 and each of the minor upgrade versions of 8.0 (8.0.11 through 8.0.23). When upgrading, users can specify the minor version of their choice or let the RDS select the most current default version.
Oracle users will be pleased to know that the MySQL Connector/J 5.1 series is included in Oracle’s Lifetime Support policy, although it does encourage the upgrade to the MySQL Connector/J 8.0 series. (MySQL 5.6 is also covered by Oracle Sustaining Support, although the upgrade to 8.0 is recommended.)
MySQL Deadlines: What You Need to Know for Amazon RDS & Azure
February 4, 2021
- MySQL 5.6 End of Life
April 1, 2021
- You can no longer create new MySQL 5.6 DB instances for Amazon RDS & Azure
October 2023
- MySQL 5.7 End of Life
- End of support from Amazon RDS & Azure on MySQL 5.7
April 2026
- My SQL 8.0 End of Life
- End of support from Amazon RDS & Azure on MySQL 8.0
Final Thoughts
Datavail’s licensed and accredited database administrators are standing by to answer your questions about both MySQL 5.7 and 8.0 and to assist with your upgrade to either. Their long experience with MySQL and deep skill base with database management also make them the team to manage your organization’s data management and database needs. Contact Datavail today.
The post It’s the End of the Road for MySQL 5.6 appeared first on Datavail.
Transforming Information Into Insights: Reporting, Scorecards and Dashboards
Organizations have more data at their fingertips than ever before—but they also need to transform this information into clear, actionable business insights, especially in a visual format.
Brain scientist John Medina, for example, has found that people will remember 65 percent of information presented as a picture three days later, but only 10 percent of spoken information.
The goal of business intelligence (BI) and analytics tools is to enable smarter, data-driven decisions by converting raw data into meaningful visual and text representations. In this article, we’ll talk about some of the most powerful BI and analytics tools that you have on hand: reporting, dashboards and scorecards.
What Is BI Reporting?
BI reporting uses business intelligence and analytics tools to automatically collect and analyze data and generate reports for human consumption, presenting this information in an easily readable and digestible fashion.
The contents of a BI report will differ depending on the underlying data; they may include both text summaries and visual representations. The most common types of charts and visualizations in BI reports include:
- Pie graphs, for percentages or proportional data.
- Line charts, for showing trends over time between independent and dependent variables.
- Waterfall charts, for showing how positive or negative values evolve over time.
- 3D area charts, for describing the relationship between three variables.
- Decision trees, for modeling a decision-making process.
Importantly, BI reports are usually generated on a recurring, scheduled basis; often, they’re created overnight based on the preceding day’s data, so that they can be on key decision-makers’ desks the next morning. This delimited time window distinguishes reports from other BI tools such as dashboards that operate in real time.
What Are BI Scorecards?
BI scorecards are reports that summarize your performance and progress in terms of one or more business metrics and KPIs (key performance indicators). Scorecards are effective because they provide the most salient, valuable information at a glance for decision-makers who are pressed for time—for example, the number of current active users, the number of orders in the past week or the average monthly revenue over the past six months.
What Are BI Dashboards?
BI dashboards are a data visualization tool that displays the real-time status of one or more business metrics and KPIs. The contents of a BI dashboard are usually customizable by the user and include multiple components, such as line graphs, bar graphs, pie charts, tables and status indicators. For ease of use, these components may come with tooltips, labels and text boxes that explain how to interpret the data.
Importantly, BI dashboards are often interactive: they allow users to drill down into the numbers, doing their own research and coming to additional conclusions beyond the surface-level ones presented in the dashboard panel. For example, users might apply a filter to the data based on a date range or geographic location, helping answer ad hoc queries. BI dashboards are also (near) real-time: they are connected to data sources such as databases and spreadsheets, and then refreshed and updated on a regular basis.
BI Reporting Versus Scorecards Versus Dashboards
BI reports, scorecards, and dashboards all have the goal of helping you make better business decisions and forecasts, but they go about it in different ways. Reports are typically non-interactive and based on a specific time range, while dashboards are interactive and up-to-the-minute. Scorecards are specialized reports that monitor a few select metrics, letting readers quickly understand progress on a particular KPI.
When building reports, scorecards, and dashboards as part of your BI workflow, follow good design principles such as:
- “Form follows function”: The visualizations and infographics you select for a given report or dashboard will depend on how they are intended to be used in the big picture. Should your visualization be static or interactive—explanatory or exploratory?
- Remove unnecessary elements: Explanatory visualizations (those intended to emphasize an argument) should contain only the information they need to communicate a particular point. Simplicity is key: with new reports constantly arriving on their desk, readers don’t want to wade through unnecessary details to get to the main point.
Conclusion
Reports, dashboards and scorecards are all valuable, effective components of a good data storytelling initiative. To build them, however, you’ll need the right tools at your disposal. Want to learn more about building out your organization’s BI and analytics initiative? Check out our white paper “From Raw Data to Insightful Stories: Transform Analytics into Innovation.”
The post Transforming Information Into Insights: Reporting, Scorecards and Dashboards appeared first on Datavail.
Top 3 Challenges of a Hyperion 11.2 Upgrade
Oracle Hyperion is one of the world’s leading application suites for enterprise performance management (EPM), helping tens of thousands of customers around the world run cutting-edge analyses of their business processes and uncover hidden insights.
Yet with the great power of Oracle Hyperion also comes great responsibility—in particular, the responsibility to upgrade your Hyperion deployment at regular intervals. This is especially important now due to the upcoming end of support of Hyperion 11.1.x in December 2021.
In this blog post, we’ll discuss the three most common upgrade challenges companies face when completing an upgrade. If you’re ready for solutions, jump to the end for a valuable resource you can download today.
Challenge #1: Performance Testing
Performance testing is the process of determining how a software application or infrastructure performs in response to established input. Application performance management (APM) heavily depends on performance testing: whereas APM seeks to understand and resolve the source of a software application’s performance issues, performance testing helps uncover these issues in the first place.
The types of performance testing include:
- Baseline testing to see how an application performs under normal load
- Stress testing to see how an application performs under abnormally high load
- Capacity testing to determine the number of possible concurrent users and/or transactions
- Longevity testing to see how an application performs after running for an extended period of time
- Regression testing to verify that recent changes have not affected an application’s performance
Using performance testing during an Oracle Hyperion 11.2 upgrade is essential. Hyperion is both a highly complex and vital component of your business processes. If the upgrade substantially impacts your Hyperion performance, you need to know and swiftly fix the issues, so that you aren’t left with post-upgrade regret.
Challenge #2: Minimizing Downtime
One of the reasons why businesses postpone their Hyperion upgrades as long as possible is because of the downtime that they’ll have to undergo. The longer your systems are down, the less productive and efficient your employees may be and the greater the potential for lost profits. While the impact of downtime can sometimes be lessened by scheduling tasks around the workweek, this often isn’t an option for national or global organizations with offices scattered across different time zones.
Often organizations even choose to undergo downtime voluntarily as a “solution” to root cause analysis during or after an upgrade—if all else fails, just try rebooting the machine. However, rebooting causes a ripple effect in terms of inefficiencies and lost productivity and doesn’t actually help you identify the underlying cause of a performance issue.
Challenge #3: Performing Root Cause Analysis
While we’d all like to imagine that our Hyperion upgrade will go as smoothly as possible, this is rarely the case in reality. And when things do go wrong, you need to diagnose the problem efficiently and take immediate action. That’s where root cause analysis (RCA) comes in. RCA is a term describing the tools and techniques for identifying the underlying cause of a problem or performance issue.
Hyperion unfortunately doesn’t come with good RCA tools out of the box; in many cases, users are only alerted to a problem once the service or application has already failed. Trying to put out fires during the Hyperion upgrade distracts system administrators from their work and delays users from regaining access to the tools they need for their jobs.
If you’re a seasoned Hyperion manager, these top three challenges are familiar; if not, you have a peek at what’s to come. But it’s not all bad news. To learn how Datavail can help you overcome these top three challenges, download our white paper, “3 Ways Datavail’s IP Can Jumpstart Your Hyperion Upgrade.”
Read This Next
4 Ways Datavail Prepares Companies for Oracle Hyperion EPM 11.1 End of Support
Staying on top of your enterprise software licenses and expiration dates is crucial—especially when it comes to mission-critical applications like Oracle Hyperion EPM. Download our white paper to learn how to prepare for your EPM upgrade.
The post Top 3 Challenges of a Hyperion 11.2 Upgrade appeared first on Datavail.
What Responsibilities Do Your DBAs Hold After a Migration to Amazon RDS
Platform as a Service (PaaS) offerings deliver many benefits, but you may be wondering about the role your DBAs play when you move to a Database as a Service (DBaaS) platform such as Amazon RDS for SQL Server. Unlike Infrastructure as a Service (IaaS) solutions, you don’t have access to the platform’s underlying systems. DBA responsibilities will change with this strategy, but they’re just as important as ever to the success of your business.
Benefits of Migrating SQL Workloads to Amazon RDS
Before exploring what DBAs do in an Amazon RDS environment, let’s explore the benefits of moving to this DBaaS service.
- Provisioning speed: It only takes a matter of minutes to get new databases setup. This improved agility allows you to quickly get development teams the resources they need to succeed with their projects. As your capacity requirements lessen or grow, you’re able to scale your usage and costs based on your current requirements.
- Automating essential database tasks: Many categories of database tasks are automated so DBAs don’t need to dedicate as much time to baseline operations. Amazon RDS handles high availability, disaster recovery, and routine maintenance tasks.
- Improved efficiency and technical resource allocation: Since many of the tasks associated with the underlying infrastructure are offloaded to AWS in this configuration, you can make better use of your in-house resources and improve your efficiency.
SQL Server on Amazon RDS DBA Responsibilities
DBAs are not rendered obsolete when you adopt a DBaaS solution. Instead, many of their responsibilities shift to strategic projects rather than being caught up in day-to-day duties. This change also marks a move from reactive approaches to proactive ones, as your DBA team has more time and resources to dedicate towards getting the most out of database performance. Here is an overview of the work duties that DBAs have with Amazon RDS for SQL Server.
Developing and Maintaining Database Backup and Recovery Plans
While Amazon RDS has several tools to help with the disaster recovery process, creating plans specific for your databases falls under your DBAs’ responsibilities. Once they create plans, they need to test them and verify that these recovery plans fulfill your business recovery objectives. As your systems change over time, they must update plans to account for new requirements.
Your DBAs perform the same process with high availability to maintain your business continuity. AWS provides the tools, but you need talented DBAs who can get the most out of them.
Monitoring and Optimizing Database Performance
The majority of your DBA team’s time will be spent making databases run as efficiently as possible. They monitor database performance to identify issues, opportunities for optimization based on application and usage, and workload rebalancing based on monitoring insights. As your DBAs fine-tune your databases, you can better support modernized applications, take advantage of new opportunities, and deliver a quality user experience. With more emphasis placed on optimization measures, you also increase your ROI for your database investments. You’re getting more out of the same level of technical resources you used previously without needing to pay substantially higher costs.
Protecting Databases from Breaches
Cyberattacks take many forms. Keeping up with the latest developments makes the difference between identifying threats in advance and reacting to them after they hit your system. Data breaches and loss are costly, especially if sensitive data is accessed. Depending on your industry, you could be at risk of falling out of compliance and incurring fines. Any unexpected downtime also gets in the way of meeting SLAs and other expectations.
Your DBAs are a valuable part of your cybersecurity team., They have many ways to eliminate common exploits, maintain security best practices, and make your databases a less tempting target for attackers.
Planning Database Capacity
Your DBAs will handle workload I/O, storage, and disk space capacity planning to support provisioning the SQL Server instances. With tight control over the capacity, you can reduce your total cost of ownership without needing to compromise on performance.
Creating Extract, Transform, Load Data Pipelines
For many use cases, you’re working with large volumes of data that come from multiple sources. This data needs to be moved to a centralized repository or data warehouse to get the most use out of it, which requires the right data pipelines. Your DBAs set up Extract, Transform, Load pipelines that automate the process of extracting the data, preparing it for use, and then loading it into its destination at scale. These pipelines change over time as you acquire and generate new data sources and technology.
Troubleshooting Databases Quickly and Efficiently
The DBA team may not be handling issues that impact the underlying infrastructure of the DBaaS, but they do have other support responsibilities. They need to monitor the databases and react quickly to any problems to avoid disruptions.
Now that you have a better understanding of your DBA team’s role in a DBaaS environment, explore more about migrating SQL server instances to Amazon RDS in our white paper. If you have additional questions about this process or need more technical resources to successfully migrate to this platform, contact us to discuss your requirements.
The post What Responsibilities Do Your DBAs Hold After a Migration to Amazon RDS appeared first on Datavail.
Performance Tuning for Oracle EBS
Oracle Enterprise Business Suite (EBS) is a powerful, robust suite of applications that helps thousands of enterprise customers improve their business processes. But no matter how well you think you set up EBS at the outset, it needs to undergo performance tuning on a regular basis to make sure that it’s still operating at peak efficiency.
Just like brushing your teeth helps stave off long-term issues like cavities, doing performance tuning for Oracle EBS is a best practice for your enterprise IT that decreases the likelihood of crashes and instability.
Oracle EBS performance tuning should be both proactive and reactive:
- Proactive performance tuning is done in advance (e.g. during the planning and implementation phases of a new project) to establish a baseline for performance.
- Reactive performance tuning is done in response to a perceived acute problem in order to efficiently resolve the issue.
For best results, Oracle EBS performance tuning should be an iterative, ongoing process that helps resolve performance issues as they crop up, identifying and fixing root causes more quickly. Below, we’ll discuss some concerns of performance tuning as it pertains to your Oracle EBS deployment.
4 Essential Components of Performance Tuning
Underneath the umbrella of “performance tuning” are a number of separate but related activities.
The different components of performance tuning include:
- Performance analysis, collecting data about server and client speeds and response times, which can then be used to identify bottlenecks and targets for optimization.
- Code optimization, rewriting and refactoring an application’s code base in order to improve its speed, scalability and availability.
- Load balancing, distributing requests across multiple servers and systems during times of peak usage to avoid crashes and performance issues.
- Parameter tuning, making changes to application settings and configurations as necessary to adjust to your evolving IT environment.
Performance Tuning for Your Oracle EBS Deployment
Once you get started with performance tuning in Oracle EBS, the difference can be drastic. In many cases, just a few adjustments can make a major difference for your Oracle EBS performance. When doing reactive performance tuning in response to an existing problem, time is of the essence.
Below are the steps to follow to isolate and resolve an Oracle EBS performance issue:
- Clearly define the problem: What is the application or service that is experiencing an issue? Who experienced the issue and at what time(s)? Can you repeat the issue? How does the issue impact the organization as a whole?
- Gather data: Collect as much performance data, statistics, and logs as possible, which may contain hints or explicit statements of the underlying problem. This may include SQL queries, database statistics, operating system logs, etc.
- Identify the root cause: Work to identify the most fundamental issue, following the chain of causes and effects to its source. If a process is slow, for example, which parts of the process are the bottleneck? Can you use a profiler to break this down further?
- Alleviate the issue: Once the root cause is identified, alert the IT support and/or development teams to the issue’s existence. If possible, find a temporary workaround to the issue while you work on a more permanent resolution.
Datavail has developed a patented, time-tested “5S” methodology to help our clients proactively optimize their Oracle EBS deployments. This approach includes:
- Applying best practices for SQL programming.
- Using data table statistics to make SQL engines more efficient and performant.
- Giving your EBS ecosystem enough space through data lifecycle management, including archiving, purging, and deleting data when appropriate.
- Monitoring user sessions to avoid contention and locking issues.
- Scheduling processes to avoid competition for resources and performance issues.
Want more information about how we help clients fine-tune their Oracle EBS deployments? We wrote the book on it—literally. Check out our white paper “The 5S Approach to Improving Database Performance.”
Conclusion
Performance tuning is an essential best practice for your Oracle EBS deployment, ensuring that your IT infrastructure continues to operate smoothly and reliably. Yet performance tuning is just one of the many interlocking concerns that Oracle EBS users need to consider when managing their environment.
As an Oracle Platinum Partner with 17 different specializations, including Oracle EBS, Datavail has helped countless clients tweak and optimize their EBS deployments through careful, smart performance tuning. To learn more about the complexities of using Oracle EBS, and how you can take steps to resolve them, check out our white paper “The Top 6 Challenges of Managing Oracle EBS Environments.”
The post Performance Tuning for Oracle EBS appeared first on Datavail.
