Uploading Data Automatically to ClearPoint

Webinar 43 in the "I Didn't Know ClearPoint Could Do That!?!" Webinar Series

If you’ve been searching for an easier way to upload data to ClearPoint, look no further than the Data Loader! The ClearPoint Data Loader can help you upload historical data to several measures at once, connect your external databases to ClearPoint, and more. In this episode of the “I Didn’t Know ClearPoint Could Do That!?!” webinar, learn how to upload data from Excel and CSV files, use SQL queries, and manage Data Loader packages and schedules right from within ClearPoint. Data entry has never been more seamless!

Excited to put what you just learned into action? Here are the key steps and linked resources you need to get started:

  1. Download the Data Loader Care Package 
  2. Learn how to create a schedule 
  3. Check out Data Loader tips & tricks and FAQs 

Webinar Transcript

Introduction (0:00)

  • Catherine: Hello everyone, and welcome to the “I Didn’t Know ClearPoint Could Do That!?!” webinar series. Today, we’re talking about uploading data automatically to ClearPoint with the Data Loader.
  • And before we dive into the details, here are your smiling hosts.
  • I’m Catherine
    • Andy: and I’m Andy
  • As a few housekeeping announcements,
    • We’ll be recording this session and will make it available within a week
    • The webinar will last about 25 minutes, with time for questions at the end, so submit your questions to [email protected]. Any that we don’t answer on air, we’ll respond to via email.

Agenda (0:37)

  • Today is going to be all about automatically uploading your data to ClearPoint using the ClearPoint Data Loader.
    • For those of you who are unfamiliar, the Data Loader is a tool that ClearPoint users can install on their own computers.
    • It is essentially a way of updating your measure or metric data with the click of a button.
    • Andy: That’s an understatement! We estimate that there have been over 2 million data points uploaded via the Data Loader – saving users more than 4500 hours of time.
  • Catherine: Wow! So much more time for activities!
  • Andy: Yeah, and in addition to saving you time, it can also save you from the countless headaches that typically follow incorrectly entered data by simple mistakes like fat fingers or errant copying and pasting.
  • Catherine: I imagine every single person listening to the webinar can relate!
  • We’ll start by showing you how the Data Loader plays an integral part in an automated reporting process
  • Then we’ll dive into configuring packages to upload via the Data Loader
  • Talk through when you might choose to use the Data Loader
  • And we’ll look at all the options you need to manage your Data Loader packages in ClearPoint.

An Automated Reporting Calendar (1:40)

  • Now for those of you who have been following the upgrades in ClearPoint 12.0, you’ve probably seen the ways in which ClearPoint’s scheduling features are helping you save time and automate your entire reporting calendar.
    • So we wanted to put the Data Loader into this broader context before diving in.
  • So, picture this – at the beginning of the month or quarter, you use the Data Loader to automatically upload data into ClearPoint from external sources.
    • Then reminders are automatically scheduled to send once the data is uploaded, so that your element owners can log in and write up analysis for their data.
  • Andy: of course, we can assume most users log in and enter data right after seeing this reminder.
    • Andy: But maybe there are a few stragglers – that’s all taken care of, too, with a second reminder that will send only to owners who haven’t made updates yet.
  • Catherine: Nice! And you can set a deadline for your account to lock down automatically for administrator review.
  • Then thanks to report scheduling, admins get a draft briefing book right to their inbox,
    • And after any final updates are made, another scheduled briefing book sends to the leadership team automatically.
  • Andy: You still have to run your meetings in person, but afterwards, you can schedule a report of all the action items you captured to be sent out to attendees.
    • Your reporting process basically runs itself!

Using the Data Loader (3:04)

  • Catherine: Pretty great! So now that we’ve seen in context how the Data Loader fits in to an automated reporting process, let’s go into a little more detail about how it actually works.
  • Andy, how do all our excited viewers out there get access to the Data Loader?!
  • Andy: Good question! The Data Loader needs to be installed on your computer in order to configure data for uploading, so if you want access, simply reach out to us at [email protected]! We’ll send you the link to the file you need to install the Data Loader.
    • Andy: If you contact us through the Data Loader care package page in the support center, we’ll even write the email for you.
      • Catherine: Yeah, we’ll send that link out with the webinar recording.
      • So what types of ClearPoint users can use the Data Loader?
    • Andy: if you’re an updater, editor, scorecard admin, or full administrator, the Data Loader is at your command.
      • Catherine: So basically, you can use it as long as your user type allows you to make changes.
      • Andy:
    • Catherine: So again, this will be installed right on your computer. You click the link to download the file and run the installer, just like you would any other program.
    • Once you install the DL on your computer, you will be able to login with your ClearPoint credentials.
    • Then, you can upload your file to connect it to ClearPoint and begin uploading.

File Structure (4:24)

  • Andy: File structure is important here so let’s take a look at that quick before we get too far along.
    • We’ll start with an Excel file, but this is true of any file type you upload with the Data Loader. More on that later.
  • If we look at this example file here, we can see the way it’s formatted –
  • All files used in Data Loader packages should be formatted in the same way
    • Starting in column A, you will list all periods that you want to add data to
      • Catherine: You can use either the period name here, or the period end date, but either way, they should match your ClearPoint account exactly.
    • Andy: You then list the series you want to map to in the subsequent columns.
    • Keep in mind the column names do not have to match the series names in ClearPoint! You can use whatever naming convention you like, just so long as each column in the spreadsheet has a unique name.
    • Catherine: Each of these columns will be mapped to a single series in ClearPoint.
    • Andy: Right. And please keep in mind that you can use as many series as you want.  One of our clients currently maps over around 900 series in one package.
    • You can also use multiple sheets in the workbook as well.

Mapping Process (5:50)

  • Catherine: Now that our file is configured to have the required format, we can set up our package in the Data Loader.
    • This is the process of pairing – or mapping – each column of data from your external data source with the measure series where the data will be uploaded to ClearPoint.

Data Packages Screen (6:10)

  • I’ve got it open here to the first screen you’ll see when you log in.
  • Clicking into the first row here, I’ll want to give my data package a recognizable name.
    • We’ll call this Upward Airlines HR Metrics.
  • Then, I need to grab the file location by clicking the three dots in the filename column.
  • Once I’ve got that in place, I’ll see that the Data Loader has automatically identified that it’s an Excel file.
  • So I’ll go ahead and click “Next”.

Scorecard Selection Screen (6:48)

  • I’ll land on the Scorecard Selection screen where I can see all the scorecards I have access to in my ClearPoint account.
    • This lets me decide where to upload the data.
    • So we’ll check the box next to the Upward Airlines Corporate scorecard.

Mapping Definitions Screen (7:05)

  • And then clicking “Next” again, I’ll move on to the Mapping Definitions screen, to choose the exact measures and series where the data from each column will fall.
    • Over here in the right hand column, we can see the names of the columns in our original data source.
    • Then we’ve got the Scorecard, Measure, and Series in ClearPoint.
  • I’m going to look in particular at our Turnover Rates, which I can see are tracked by division.
    • So I’ll start by checking the box to enable these columns,
      • And select the Scorecard, Measure, and Series where I want to put that data. I’ve got a series for each division.
    • Andy: This might take a few minutes to configure, depending how many measures you have included in the package,
      • Andy: But what’s awesome about the Data Loader is that you only have to configure it once – your mappings will be saved as part of the package.  All subsequent uploads of this package will reuse these mappings.
      • These is where the huge time savings come from with the Data Loader.

Preview Data Screen (8:23)

  • Catherine: Alright, all set! The next screen is known as the Preview Data Screen
    • This is where you can see what would happen if you were to upload your data based on what is currently in that file.
    • Any values that are unchanged are going to show as white.
    • If it is green, that means that new data is going to overwrite, or, that the value was NULL and is going to remain that way.
    • Red means invalid data. This could be something like text or invalid periods.
    • Gray could mean a number of things.
    • If a series, period or scorecard is locked from updating, the Data Loader will be blocked from adding data.
    • Andy: Yes, rest assured that the Data Loader doesn’t get special treatment to your measure data in ClearPoint! It must obey the same permission rules like everyone else entering data manually in ClearPoint.

Package Schedules Screen (9:30)

  • Catherine: Exactly! Clicking next, here’s where we can enter a schedule.
    • Because we now have the option to control this from ClearPoint, I’m gonna wait on it.
    • Regardless of whether you set a schedule, you can always run data on the spot too.
    • So we’ll click Finish, which will take us right back to the home screen,
      • And from here, click the green arrow to run our data.
      • We can see our Turnover Rates measure open in ClearPoint in the background
      • It currently doesn’t have any data in it…but when we run our package, I can see a toaster that our package was uploaded
      • Our chart and data table have calculated automatically based on that data we uploaded.
      • Even the average turnover rate is calculating based on what was in the file.
    • Andy: And again, any locked series or locked periods will remain unaffected. The Data Loader is not going to overstep these rules, even if there’s data mapped there in your packages at the time of upload.
  • Catherine: Yeah! So next time we have new data available, we can just reuse the package we set up. When the next reporting period comes around, the same measures can be updated instantly.
  • Andy: This is not to be taken for granted! Just imagine the time it takes to report on your measure data being cut by 90%–every single month!

CSV and SQL files (11:00)

  • Andy: Some of you are probably wondering what file types you can use and that is a great question.
  • Other than Excel, you can also use CSV or SQL.
  • Both of these are going to be the exact same as the Excel example when it comes to file format, but SQL will just require an extra step in the setup:
    • All it takes is a SQL query file and a database connection.
    • So if I go ahead and create a new package by giving it a name,
    • I can select my SQL file,
    • and then set up the connection using this ellipsis button.
    • First, Select the type of Data Source
    • Next, the provider
    • Enter the server name and credentials, then specify the database
    • You can test your connection to ensure that all was set up correctly.
    • Catherine: I know that this probably looks really complicated to some of you–do not worry!  Database connections with the Data Loader are actually super simple and are something your IT team could help with in a jiffy.
    • Andy: Yep!  And from there, mapping that package will work the exact same way as the Excel file we showed earlier.

Scenarios for Data Loading (13:01)

  • Catherine: You might be wondering about the scenarios where the Data Loader can supercharge your updates in ClearPoint.

Regular updates outside of ClearPoint (13:08)

  • The instance that we’ve touched on so far is one in which your data is being tracked in an outside system alongside your data entry in ClearPoint.
    • One reason this might be the case is that you’re collecting data daily or weekly, and you want to consolidate it into monthly averages or sums before uploading those monthly data points to ClearPoint.
  • Rather than having to copy and paste that data at the end of each month, the Data Loader helps you to ensure that you can easily get all that data into one place for reporting with minimal effort.
  • You set up some basic configurations to establish a connection with ClearPoint, set your data package to upload on a regular schedule, and watch the data roll in.
  • But that’s far from the only use case for using the Data Loader.

Historical Data (13:56)

  • Andy: See if this scenario sounds familiar – you’d like to get another department using ClearPoint, because it would be so much easier for them to create charts and produce reports with their information in the tool.
    • But, they’ve been storing all their data somewhere else, like Microsoft Access.
      • And it’s hard to get them to see past the effort of transferring all that historical data from one system to another.
    • Catherine: Not with the Data Loader!
    • Andy: Right! Rather than having to copy and paste all that data manually, the Data Loader uploads it in one fell swoop, so that they can focus on the more important aspects of analyzing the data and maintaining important qualitative information.
  • Catherine: So, the Data Loader can help you move past any roadblocks around historical data uploads, and your departments can take advantage of all that ClearPoint has to offer.

Managing the Data Loader in ClearPoint (14:45)

  • If you are so lucky as to get all your departments on board, our Admins out there are probably wondering how they can keep track of their users’ data loading habits.
  • If we navigate to System Settings, Admin Options, we will see there is a tile for the Data Loader
  • If you click on it, you will see all the packages that exist in your account.
  • For example, here we see Joseph’s customer success package, as well as Ted’s finance package
  • And of course, the package Catherine just made
  • In addition to seeing all the packages on this screen, you can…
    • Delete, duplicate, edit, or run a package using the little blue rocket ship icon here.  This allows you to run the package without even having to log into the Data Loader.

Doing so will queue the package for uploading sometime in the next 10 minutes.

  • So next time Catherine has updates in her file, she can run it from here, no problem.
  • Catherine: But, it’s important to note here that your background service for the Data Loader must have access for this method of uploading to work!
    • We do have a support article that will help with this and this is always something your IT folks can help with too.
  • Andy: If we click on the edit icon we can see what our options are here.
  • We could change the name of the package,
  • And you can change the computer that has access to the package, such as moving it from Catherine’s PC to Andy’s.
  • By changing the owner from Catherine to myself, I will have officially moved the package to my username and computer so that I see it the next time I log in.
    • Catherine: So for example, if you’re out sick or away from your computer and need to upload data in a pinch, Admins can transfer access to a coworker.
  • Andy: You can also change the file path from here if you are transferring the package to a new user.

Setting a Schedule (17:16)

  • You can also see whether the package is set to run on a schedule.
    • One of the awesome new features in 12.0 was of course scheduling of briefing books, and reminders.
    • With this new feature came the ability to edit your schedules from Admin Options so that you don’t need to open the Data Loader every time.
    • To add a schedule, click the plus icon on the upper right.
    • You can name the schedule, select the first date it should run, as well as the time of day it should run.
    • Next, select the frequency–this can be monthly, weekly, or daily, or something in between.  The options with each allow for a lot of flexibility.
    • If we save, we’ll see the next run date for our schedule listed here.

Catherine: Awesome! Now, we’re sure you have some burning questions, so we’d like to open up the floor and answer some of those.

Questions (18:55)

  • Can multiple people use the same package?
    • Packages have a 1:1 relationship with users, meaning there’s one user assigned to each package,
    • But one thing you can do if you want multiple people to upload the same source data is to duplicate the package.
    • So for our HR metrics, I can duplicate the package, assign it to Catherine and her computer, then we can both upload that data now using the same package.
  • What packages is this available in?
    • The Data Loader is available for Professional and Enterprise plans
    • If you’re not sure if that’s your plan, or you just want more information about how to use the Data Loader, just reach out to us! We’re happy to help with any questions that come up.
  • Can I download the link to install the Data Loader?
    • Looks like someone is ready to get moving!
    • For anyone else that wants a link to the Data Loader, email us at [email protected], and we can send that link over to you.
  • Can I upload text with the Data Loader?
    • No, the Data Loader currently uploads to measure series, and that’s quantitative data only.
    • But, we have an API available in the Enterprise plan that can help you upload qualitative data into ClearPoint.
    • Definitely reach out to us with questions about a particular scenario, so we can talk through the use case and figure out what you could accomplish.
  • It looks like this question is asking about the end date field.  I think this is in reference to:
    • I think this person is talking about the End Date on the schedule – so clicking back into our package schedule, we’ll see the end date here is optional.
    • If you want, you could say you only want your package to run for the calendar year – you know that you don’t want measures in this package to receive updates anymore in 2020 for example, so just a way to control when your schedule can run.

Catherine: Perfect! Well that’s all we have time for today – thank you so much for joining us on this episode of the “I Didn’t Know ClearPoint Could Do That!?!” webinar series. We’ll see you next time and Happy Reporting!