The speed at which data is flowing on the web, into databases and around us, has changed the way we present it. Static tables and charts are now a thing of the past. Even dynamic and interactive visualizations aren’t enough, because billions of new entries are made every second. We need something better.
You most probably heard of fully autonomous manufacturing plants. In such organizations, one manager walks in, literally “turns the plant on” in the morning, and “shuts it off” when leaving. Every task is handled by robots, even troubleshooting and QA. Machines don’t have excuses, nor do they take breaks; progression is always at where you expect it to be. Applying the same concept to data follows the same idea; you set everything up and it will work without any other input.
As the amount of data available is snowballing, data tasks have started to get complicated. Removing manual update and transfer, will give you a peace of mind by removing human error from the equation, while providing your users with the latest information.
Microsoft Excel is the most common and simple form of a data platform. Pretty much everyone is familiar with it, and anyone can use it to store data. Any kind of CRM, ERP, BI or other data sources; have means to export everything in Excel format or can be connected to an Excel table.
Microsoft also offers two complimentary tools to help professionals with ETL (extract, transform, load) tasks; PowerQuery and PowerPivot. Unorganized data is first scrubbed using PowerQuery, and then everything is placed on a neat table using PowerPivot. Working these two together, combining several tables with identical data structures is a breeze. Capitalizing on the virtue of these tools, Visart brings a new perspective to dynamic reporting.
Visart’s self-service data visualization platform allows you to create all types of charts or diagrams. However, what really makes it shine is the Visart Data Manager (VDM) tool. With this tiny, but versatile software, whole dashboards can be connected to a data source and updated on an interval of your choice. VDM utilizes the power of Excel PowerPivot to connect your Visart dashboards to a great number of data sources.
Data update process follows this logic:
- VDM initiates an update.
- This request goes to the PowerPivot document that is connected to the source data.
- PowerPivot table automatically sends an update request to the source data.
- PowerPivot retrieves the latest results and sends them to VDM.
- VDM updates the VisArt dashboard.
The pipeline looks like this:
Let’s take an example. Here, we are going to use a fictitious school data to demonstrate this process. This dataset includes several properties and 12,126 rows of data. The student data is kept on a CSV file; this is our database.
Next step is putting this data in a format VisArt can use. PowerPivot accepts several types of database or file type connections. Frequently used options are Microsoft Access, SQL, text files and popular CRM software. CSV is the common denominator for all data storage work; all database software can export in CSV format. We used CSV in our example.
In this example, a workbook with a PowerPivot connection to this database is kept on the same machine as the VDM. But in many real life scenarios, CSV or any other data files are located on a network drive. Regardless, this file will act as a median (or a pipeline) to retrieve the data in an organized format and we will set up the VDM connection to this file.
Now, we can establish the connection between VisArt and our PowerPivot file. We will be using the Visart Data Manager (VDM) tool for this. Data can be updated on an hourly, daily, weekly, monthly basis or a custom set frequency. However, sending too many queries to the server can cause unnecessary load on bandwidth and the hardware. Therefore, we will choose a time we believe the server will have the lowest load.
Based on these options, our dashboard is going to be updated every Saturday, at 11PM. Let’s try adding more entries to our database and see what happens.
VDM will automatically hit the PowerPivot file at 11PM on Saturday and PowerPivot will trigger an update. If you refresh your browser after 11, you will see that the dashboard is populated with the latest data.
The total number of students has changed to reflect the latest data that now has 12,217 records. You can find more about setting up a connection in our user guide.
Data collection is getting ahead of our capability to track it. To overcome such limitations and save yourself the trouble, all you need is the VDM tool. Connecting seamlessly to various data sources, and integrating with readily-available data management tools of Excel, Visart offers fully automated dashboards to answer modern data problems.