Vivid ETL (Extract, Transform, Load)
Vivid ETL will allow you to localize your cloud-based data into a Microsoft SQL Server® database that may reside either on-premise or in the cloud on an Azure® server. In this article, we will examine the benefits this provides and how Vivid’s ETL tool can help your organization.
What is ETL?
ETL stands for Extract, Transform, Load. Believe it or not, ETL software has existed since the 70s! Back then, ETL was primarily used for data warehousing purposes to copy and clean data from large (and slow) databases, such as an IBM AS/400 or SAP R/3 based systems, running on mainframe computers. Data from these systems was transformed and loaded into a more simplified database with human readable naming conventions that could be easily accessed and interpreted for reporting and data analysis.
Today, ETL software can be used to better access data that is difficult to access because it is cloud-based. Access to cloud-based data often requires expertise in many different web services and API techniques, or data retrieval is slow to prevent demand overload of the source system. This is especially true on multi-tenant environments. If you need to combine data from multiple objects or across multiple disparate data sources, it might become impossible without ETL. Vivid’s ETL software addresses all these concerns and makes it easy to consolidate your data into a single source that is quick and easy to access and understand!
Some of the benefits of using an ETL software include:
- Faster Performance:
Vivid ETL enables a dedicated on-premise or private-cloud based data source without throttling or data transfer limitations. The data can also be optimized and reindexed for much faster reporting and analysis. For example, “open” and “history” data sources can be combined into a single view enabling near instant multi-year trend analysis.
- Enhanced Data Profiling:
Because most data warehouses involve a wide variety of data sources and targets, it can be difficult to profile this data manually. When all of this data is organized and unified through an ETL solution, data profiling becomes significantly easier.
- Operational Resilience:
Vivid’s ETL will help reduce the chance of human error through automatic data flow that can be scheduled or requested on-demand. This helps ensure that data integrity is maintained, that manual steps are eliminated, and that you have high quality data to work with.
- Better Handle Big Data:
Modern ETL tools are usually capable of handling a combination of structured and unstructured data into one mapping. They may also be able to handle very large amounts of data or data that isn’t necessarily required to be stored in a data warehouse. This promotes increased data organization and an easier search process.
- Organize the Chaos:
In many larger sized, modern organizations, the sheer amount of data and data sources can make effective data organization challenging. By loading all of this data to one destination source, all of this data is neatly organized into one easily accessible source.
How can Vivid ETL help you?
The Vivid ETL tool transfers data from various data sources of your choosing to a localized database. From there, tools such as Vivid Flex can better report on your data and turn your data into actionable information for better and more timely decisions!
Watch the video below for an example of how to use the Vivid ETL tool with Business Central® data.
Vivid ETL can be used with multiple data sources. Below is a list of currently supported data sources.
- Microsoft Business Central®
- QuickBooks Online
- Sage Intacct
- Epicor 10 Cloud
- Insightly CRM (Visit Insightly Reports to learn more)
If you think that your organization could benefit from the Vivid ETL software, please contact us to learn more or for a free trial!*
*Some conditions may apply. Please contact us for more information.
Partner Spotlight – Astute Financial Consulting
Astute Financial Consulting is a team of financial consultants led by Jim Lee out of Fort Collins, Colorado. They have been helping security alarm companies perfect their accounting and reporting processes for over two decades. As a result, these companies experience a consistent and stable platform that allows for accurate reporting and insight into the performance of their operations.
Astute partnered with Vivid Reports in 2012 to leverage Vivid’s advanced reporting and budgeting solutions. This enabled them to translate their expertise into a suite of pre-built report packages tailor-made for the security alarm industry. As part of the report implementation process, they will optimize the accounting system and recommend best practices for reliable reporting. These reports are comprehensive and leverage the vast experience that comes from working with 100+ security alarm companies. Report Packages contain many ratios and metrics specific to the security alarm industry that are easy to understand and connected directly back to live ERP data.
Astute’s report packages touch on all aspects of the operations, including:
- RMR (Recurring Monthly Revenue)
- Financial Reporting
- Service & Technicians
- Time & Billing
- Attrition Management
and so much more.
Scroll down for a sample of what Astute has created using Vivid Reports. The following samples include:
- CFO Reports
- Monthly Business Analysis……………………………….
Owners & Executives
- Monthly Department Analysis………………………….
Executives & Managers
- Bank Compliance……………………………………………..
Executives, Managers, & Supervisors
- Report Packs…………………………………………………………
Managers & Supervisors
- Toolbox Reports…………………………………………………….
Managers, Supervisors, & Staff
- Accounting Reports……………………………………………..
Monthly Business Analysis (MBA)
The MBA is the tell-all month-end recap for security business owners and executives. Comparative and trend data support key financial and operating metrics. A breakdown of new and existing business reveals illuminating insight about company performance. Return on assets and investment, calculated with consideration for change in the value of recurring monthly revenue (RMR), deliver the punch line of your company’s financial story.
Monthly Department Analysis (MDA)
The MDA compiles key performance indicators for straight-forward analysis of installation and service operations at the branch and/or department levels. Trailing twelve-month (TTM) numbers provide averages and show trends for benchmarking. Results are summarized in a chart showing current period performance results versus budget and TTM averages. Questions are quickly answered by drilling down to supporting detail.
Dashboards deliver real-time activity and results for different purposes. Department dashboards display key metrics to keep staff informed of status and progress related to daily tasks. Other dashboards show daily, weekly, monthly, quarterly, and annual trends to provide clear indication of company performance for monitoring progress and ensuring you are heading in the right direction.
Report Packs are the lynchpins behind the integrity and reliability of Astute Reports. Each Report Pack is anchored by a roll-forward report, where beginning values, activity and ending values are displayed in weekly, monthly, quarterly, or annual formats. Pivot tables provide valuable insight by summarizing information instantly for powerful data analysis. Report Packs include RMR, jobs, service, technicians, prospects, cash, accounts receivable, accounts payable, and inventory.
Everyone can find a few favorites in the Toolbox where dozens of reports cover a wide array of subject matter. There are RMR reports for “slice and dice” analysis, trend analysis or simply gathering detailed information. There are job reports, like the Closed Jobs Review and Open Jobs Review, that help streamline daily operations. There are inventory and service reports, including the Contract Analysis for calculating service/inspection contract profitability. Additionally, there are utility-type reports that help keep your database in good order.
The accounting reports streamline month-end accounting processes while ensuring accuracy and compliance with generally accepted accounting principles. Certain reports, like Credit Card Entries and Payroll Entries, simplify what are often tedious tasks. Job accounting reports tackle the most complex area of accounting for security alarm companies, ensuring proper recognition of revenues and matching of costs. Cash Flow Forecast, Commissions Tracking and AR-RMR Aging are additional accounting report examples that empower your financial team to perform at the highest level.
Many Astute Reports can be viewed in a browser using Vivid Web Application. The application works similarly to Microsoft Excel® with the convenience of any time, any device, and any location accessibility through the internet.
To learn more about the Vivid Reports created by Astute, or to take a look at everything Vivid has to offer, please contact us.
Corporate Performance Management vs. Business Intelligence
Corporate performance management and business intelligence…what’s the difference?
Although the two concepts are closely related, there are some differences between the two in functionality, usage, and advantages.
In the following article, we will take a quick glance at some of the basic differences between the two concepts. Hopefully, this information will provide you with a clearer understanding as to why Vivid CPM and Vivid Flex BI are distinguished as different products.
Business Intelligence (BI)
The origin of the term business intelligence is generally attributed to an IBM computer scientist named Hans Peter Luhn. Luhn introduced it in an article he wrote in 1958 aptly titled “A business intelligence system.”
Typically, BI refers to an organization systematically making sense of the information that they collect in order to become more efficient, effective, and ultimately more profitable. Today, BI enables organizations to store, display, analyze, and react to this information more efficiently than even Hans Peter could have ever imagined.
With most BI tools, an organization’s raw data is pulled from their various systems and stored in an intermediate data warehouse. The data warehouse is a staging area where the information is organized and prepared for presentation.
The most common types of presentation formats associated with BI are:
- Flexible user defined (aka “ad hoc”) Reports: where users can drill down into the underlying details
- Dashboards: that provide a snapshot of how an organization is performing from positional or departmental perspectives
Good BI enables management to quickly analyze data, recognize trends, and react to changing conditions within their organization.
Corporate Performance Management (CPM)
Sometimes also referred to as “Business Performance Management,” CPM takes the same concept from a more structured perspective. CPM is often considered a subsection of BI, but we feel it is more accurate to distinguish between the two as many organizations may only need one or the other.
CPM is typically focused on G/L-based analysis, financial reporting, budgeting, and forecasting. Often, CPM data is presented in a Microsoft Excel® report for analysis and review.
Without question, Excel® is still the choice of accounting professionals worldwide for financial reporting, budgeting and analysis. However, utilizing Excel® alone comes with several drawbacks. Creating, consolidating and tracking multiple spreadsheets, needing to export the data from systems, and the inherent security issues when collaborating via email are all issues organizations face when using Excel® alone.
Luckily, solid CPM and BI solutions are here to address all of these issues.
Top 5 Reasons to Love Excel-Based Reporting
1. Most people are familiar with Microsoft Excel®
If you are in the accounting or finance department, chances are you already know how to use Microsoft Excel®. It’s one of the most widely used spreadsheet programs, if you have worked with spreadsheets, most likely it’s been with Excel®. You may not know all the fancy and complex functions, but you can create formulas, insert and delete columns and rows, and add text formatting. If you can do all that, you already know the foundation for Excel®-based financial reporting.
2. You can format Microsoft Excel® to your Heart’s content
Do you want your negative numbers to be in brackets and red? Would you like to highlight certain cells that contain certain values? Or perhaps you would like to insert your logo or make your titles be in bold? All of this and more you can do easily in Excel® without any special training.
3. Data Visualization that’s Easy to Understand
Use the data in Excel® to create pie charts, graphs, and certain conditions to add meaning to your data. These can then be used to add visual emphasis to business reports and to make data analysis easier. Most of us can spot trends and notice discrepancies faster when data is visualized compared to when it is presented on a spreadsheet full of numbers.
4. Easily perform ad-hoc Calculations and Modeling
With Excel®, you can quickly copy areas of a worksheet and various reporting elements, calculations, and other data objects to build new reports, templates, and models in minutes. Reports can be changed on the fly and data viewed from new perspectives. What-If analysis becomes extremely easy, especially if the formats are already created and users just need to change a few drivers. Perhaps, you want to quickly make a copy of an entire report, to experiment, or try something new. This is very easily done in Excel®! Analysis has never been easier!
5. Functions and Logic for advanced KPIs and Operating Metrics
Excel® ships with thousands of baked in functions. Even better, finding out which function you want is just a Google search away. By leveraging Excel®, you immediately have access to advanced functions and without having to spend hours learning a proprietary system.
What does this mean for your business and how can Vivid Reports help?
Vivid Reports is Excel®-based. This means that you can pull in your data from your accounting system (and any other databases) and then use the functionality of Excel® (that you already know!) to make meaningful, presentation-ready reports. With a click of a button, you can refresh reports with new reporting parameters, but the formatting and calculations are already set up for the newly refreshed data! You can then share these reports across your company. With Vivid Reports security, you can be sure that users are only seeing the data they are permitted to access. You can also take your Excel-based reports and publish them through Vivid Reports. There, they can be interacted with and consumed over the internet through browsers, phones, and tablets while still preserving Excel formatting. You truly get the best of both worlds with Vivid Reports.
Cash Flow Report Package – Powered by Vivid Flex
As many organizations have faced financial uncertainty in the wake of COVID-19, cash flow reporting and expense control has become essential.
Why Cash Flow Now?
In simple terms, cash flow management refers to the process of tracking how much money is coming in and out of your organization in real-time. Measuring cash flow is not the same process as measuring profitability. Your organization may still be profitable, but what if you are faced with a situation where expenses are due before revenues are collected? This is where cash flow management comes into play. Business has slowed down around the world, and many organizations are faced with a situation where expenses are due before expected incomes can be realized. Building an effective cash flow management strategy now will help your organization plan for the long-term even while you’re uncertain about how long the current economic disruption may last.
How Vivid Helps
Currently, Vivid Reports is offering our cash flow reporting package for free use* for 90 days. We hope that having free access to an effective cash flow management tool will help organizations plan for the future and get back on their feet stronger. Our advanced, interactive cash flow management reports connect in real-time directly to your ERP through one of our primary products; Vivid Flex. Like all Vivid Flex reports, they can be refreshed in real-time with the click of a button. This allows you to have up to date information anytime you need it. So, if there are any sudden changes, you can rest assured that you will have the information needed to respond. No need for guesswork or estimation as your most recent numbers will always be right at your fingertips.
What is included in the Cash Flow Report Package?
- Cash Activity Report
- Monthly & Weekly Cash Forecasts
- Collections Forecasting
- RMR Forecasting
- AR Aging
- AP Aging
For more details, please contact us. We would be happy to answer any questions and explain how you can leverage this powerful cash flow package for free for up to 90 days. Some terms, conditions, and set up fees may be applicable*.
Click the images below for a closer look at the examples!