The Data Loader

Webinar 26 in the "I Didn't Know ClearPoint Could Do That!?!" webinar series

Ever wish there was a faster way to get your measure data into ClearPoint? Contrary to popular belief, the Data Loader is not an all-powerful genie, but it can make this particular wish come true! Import data directly from Excel into ClearPoint. Write SQL queries to pull in data from an existing database. Schedule data imports to occur at set intervals and put your data entry on autopilot. As of our 10.5 release, you can even manage your Data Loader packages right from within ClearPoint.

Whether you are new to ClearPoint, or a seasoned user, learn more about this lightning-fast way to upload measure data. Happy reporting!

Webinar 26 Transcript



  • Hello everyone, and welcome to the “I didn’t know ClearPoint could do that!?!” webinar series
  • Today, we’ll be covering how the Data Loader can help turn you into a reporting superhero.
  • Here we are, your smiling hosts, I’m Catherine. And I’m Andy
    • We will be recording this session and will make it available within a week
    • Webinar will last about 25 minutes with time for questions
    • Submit questions to [email protected] (answered at the end)
      • We’ll follow up via email
    • On our last installment, we introduced you to five new wonders of ClearPoint 10.5, and if you weren’t able to join us, I’d highly recommend checking out that recording on our Vimeo channel, our support center, and our blog – just search “Webinar”!



  • Today is all about uploading data into ClearPoint at lightning speed with the Data Loader! Andy is our Data Loader expert here at ClearPoint, so you’ll be learning from the best!
  • We did touch on the Data Loader in our last webinar, so in this installment we’ll be expanding on that to give it the full attention it deserves.
  • We’ll go over what the data loader is, and how to download and install it;
    • Walk through some examples,
    • Demonstrate how it changes content in ClearPoint,
    • And we’ll make sure you can manage your packages right from within the application once they are set up!
    • So, let’s dive right in, we’ll be exploring the friendly streets of Metropolis today.


Five-minute exercise (1:36)

    • Jenna: Hey guys, can you help me with something?
    • Andy: Jenna!! We’re in the middle of a webinar!
    • Jenna: This is more important!!!
    • Catherine: Uh, excuse me, more important than a webinar??
    • Jenna: My manager gave me an urgent deadline to get all of our budget data into ClearPoint ASAP. Like, ALL of our budget data!! I don’t know how I’m going to get it done by the end of the day! I mean I had other things on my plate too!
    • Catherine: Like watching a webinar.
    • Andy: Okay, Jenna, we can give you a hand. But we have a webinar to get back to, so we’re going to do it in less than five minutes
    • Jenna: Awesome, thanks!!
    • Catherine: Can you send us your Excel file?
    • Jenna: Already sent it over.
    • Andy: Great, looks like it’s formatted correctly for the data loader, with the periods matching ClearPoint in the left-hand column, and unique series names in the top row. Now what report in ClearPoint are you trying to populate?
    • Jenna: It’s the one called Monthly Budget Report. The budget data is entered into department scorecards, and then we show the budget measures in this report, along with the calculated total budget. And I need to get all this data in for the whole quarter.
    • Catherine: Not a problem! Let’s pull up the data loader
    • Andy: We upload our budget data this way every month, so we actually have a package, locked, loaded and ready to go.
    • Catherine: That’s right! We’ve mapped the Excel file to measures and series in ClearPoint, and now all we’ll need to do is click the green arrow to run the package and upload the data.
      • Now, we’ll just go back into ClearPoint and refresh.
    • Jenna: Wow! My report is completely full of data! It’s that fast?
    • Catherine: It’s that fast. Your calculations, evaluations, and charts all updated too. And we’re not done yet. You’ve got a template set up for your briefing book, right?
    • Jenna: Yup, it’s the one called Monthly Budget Report.
    • Catherine: Great, so we’ll just click to generate, and that briefing book will be in our inbox in no time.
      • So there you have it, not only does Jenna have her report in less than five minutes, but we’ve just given you an example of how the Data Loader can help you become a reporting superhero.
    • Jenna: Wow, that was amazing! Thanks ClearPoint! Thanks Data Loader! Bye guys!
    • Andy and Catherine: Bye Jenna!
    • Andy: Okay, we just threw a lot of information out there. What do you say we back up a little?
    • Catherine: Definitely. Can you start with a little more background on the Data Loader, Andy?
    • Andy: Absolutely.  Let’s get started!


What is the data loader? (4:46)

  • 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.
  • It is different from the web-based ClearPoint you are used to, but it is still entering data into the same data base.
  • Users create what are called packages, that link Excel, CSV, and SQL data sources to your ClearPoint measures.
  • File Structure is important so let’s take a look at that quick before we get too far along.
    • All Data Loader packages should be formatted in the same way
      • Starting in column1, you will list all periods that you want to add data to
      • You then list the series you want to map to in the subsequent columns.
      • These will have a one-to-one relationship with some series in your ClearPoint account.
      • Catherine: For example, each of these budget columns mapped directly to one of Jenna’s department budget series for her report.
      • Andy: 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.
    • Once you have everything mapped, all you need to do is click a button and your data will upload in a matter of seconds.
    • Packages can be reused, too, so when the next reporting period comes around, the same measures can be updated instantly.
    • Catherine: This is not to be taken for granted! Thanks to the package we created last month, Jenna was able to upload her data in an instant and produce her report in no time.
    • Additionally, you can set some scheduled uploads, which is a huge perk of the Data Loader as it can automate the data entry process.


How to install Data Loader? (6:44)

  • If you want access to the Data Loader, simply reach out to us at [email protected]! We’ll send you the link to the file you need to download the data loader.
  • As far as user permissions go, if you’re an updater, editor, scorecard admin, or full administrator, the Data Loader is at your command.
    • Andy: Basically, you can use it as long as your user type allows you to make changes.
  • So, as Andy mentioned, 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.
  • It is based on your permissions in your account, so you can only upload data to measures that you already have access to.
  • So I am going to go ahead and log back into our Metropolis account…


Excel Example (7:40)

  • The first screen you see when logging in is the Data Packages screen.  From here, you can see any packages that are tied to your username and your computer.
  • We can see some of the other packages that already exist for this account, but let’s go ahead and show you how to build a new one.
  • I am going to basically recreate the budget package we just used for Jenna’s upload.
    • To create a new package, just click in this bar.
    • All you need to do is give it a name and select a file from your computer.
    • I am going to name it Budget Package Number 2
    • And then select the same file we used before
    • When ready to get to mapping, click Next
  • Scorecard selection screen
    • From here you can select which scorecards you want to map to.
    • I want to go to each of my departments, so I am going to select each of them.
    • Catherine: Something worth noting is that Andy is mapping to multiple scorecards.  This is good to keep in mind when thinking about how to construct your packages.
  • Mapping definitions
    • After I’ve selected my scorecards, I can map my file to the correct measures in ClearPoint.
    • Through a series of dropdowns, we can select the scorecard, measures, and series that the data should go to.
    • First, we check the enable box to turn on a given mapping.
    • Next, we select the scorecard
    • Then, the measure
    • Finally, the series
    • We can continue to do this for the remainder of the package
    • Catherine: And while this screen seems to involve a lot of work, please remember that the purpose of the Data Loader is to have reusable packages that allow you to quickly and easily upload your data to ClearPoint.
    • Andy: That’s right.  This is an up front investment that reaps rewards in the form of tons of saved time.
    • I think that is enough to get the point across, so let’s move on to the next page.
  • Data preview
    • 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.
    • Series that are unchanged, like those that we just uploaded, 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.
    • Catherine: Yes, rest assured that the Data Loader doesn’t get special treatment to your data! It must obey the rules like everyone else.
  • Final schedule setup screen
    • The last screen in the Data Loader is where you can really maximize the time savings for your team.
    • As I mentioned before, one huge perk of the Data Loader is that automatic upload schedules can be created.
    • This isn’t required, but just a nice option to keep in mind.
    • Schedules can be set to run on a specific date or time.
    • Also, schedule frequency can be adjusted to find the perfect time for your reporting calendar.
    • You could do something like every two weeks, on Fridays, at 5.
    • Or maybe you want this upload monthly. You could tell it to upload on the second Monday of every month at 8am.
    • Catherine: Pretty much anything is possible.
    • Andy: Exactly.
  • Back to home screen
    • And this takes us back to our Data Packages or Home screen.
    • We can see our package is now listed here
    • If, at any time you want to upload that data, all you need to do is log in and click this little green arrow.
    • And your data will be uploaded in a matter of seconds!


What changes and what doesn’t in ClearPoint (13:08)

  • So I’m sure you’re aware that entering data manually has an effect in ClearPoint beyond just filling the empty cell.
  • And entering data with the data loader is no different. So, what else should you expect to change?
    • Any calculations that use the raw data you’ve entered will be updated automatically.
      • So if we click on our Department Budget in the Airport department, and double click on the data table, we’ll see that two of the series are calculated directly from that Data Loader Data.
      • The charts are also going to update, so we have two charts here that were not previously populated, which now have both raw and calculated series displayed.
      • On the flip side, if you have locked series or locked periods, the Data Loader is not going to affect these settings, even if there’s data mapped there in your packages.


SQL example (14:16)

  • Catherine: OK, so far we’ve only looked at examples using Excel files. Some of you are probably thinking that’s great, but what if my data is stored in some other system that we use at my organization?
  • Not a problem. Many of our clients use the Data Loader to SQL query data out of those systems and into ClearPoint.
  • With the help of your IT team, it is really quite easy.
  • All it takes is a SQL query file….And establishing a connection to the database I want to use
    • So if I go ahead and create a new package by giving it a name,
    • I can select my SQL query file,
    • and then set up the connection using this ellipsis button.
    • First, Select the type of Data Source
    • Next, the provider
    • When ready, click OK
    • Enter the server name and credentials, then specify the database
    • You can test your connection to ensure that all was set up correctly.
  • I am going to basically recreate the budget package we just used for Jenna’s upload.
  • Catherine: I know that this probably looks really complicated to some of you–do not worry!  Database connections with the Data Loader are 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.
    • I click next,
    • Pick my scorecards,
    • Map to the correct series,
    • Finish out the package and return the home screen
    • Let’s check this measure out in ClearPoint
      • We can see that it is blank.
    • And then we can run it with the green arrow.
    • And then we can go back and refresh page to see our data
    • Catherine: Easy enough!


Managing from Admin Options (18:00)

  • So as many of you saw on our last webinar, administrators can now manage Data Loader packages from within ClearPoint, and we’ll walk you through how that works.
    • System Settings > Admin Options > and we’ll click on the tile marked Data Loader.
  • This window will show you all the packages that exist in your account.
    • From here you can…
      • Delete, duplicate, edit, or run a package.
        • And, we’ll click on the edit icon to see what our options are here.
        • So, we could change the name of the package,
        • The owner of the package – that is, the user who has access to it when they log into the data loader
        • And you can change the computer that has access to the package as well.
          • Andy: 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
        • And, you can change the file path in case it has moved folders.
      • Then you can also see whether the package is set to run on a schedule.
      • If you want to run a package without even having to log into the Data Loader, we can click on the rocket ship button
        • And doing so will queue the package for uploading sometime in the next 10 minutes.
        • So next time Jenna has uploaded data to that budget file, she can run it from here, no problem.
    • Andy: 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.


Questions (20:12)

  1. Does the Data Loader only work on measure series, or can it also be used to upload comments to the various text boxes?
    1. So, for now, Data Loader only works for the quantitative data in ClearPoint. We do have the idea to add this capability in our road map, however.
    2. Alternatively, our API documentation is now out, and you can use that to get whatever data you need in or out of ClearPoint.
  2. Does the Data Loader work with SharePoint?
    1.  The Data Loader works with files from your network.  Also, SharePoint can be used if you have it configured so that the file is accessed over WebDAV or something similar
    2. Also, looks like we have another question about data sources.  Someone is wondering integrating with MUNIS.
      1. The answer to that is yes as well.  You can use the SQL query functionality to get data out of MUNIS.
    3. How do I ensure I have the most up-to-date version?
      1. If you look at the top header in the Data Loader, you’ll see a version number. It should say 1.2.1, let us know.
    1. Again, here’s how you download it:
  1. Is it possible to install the data loader on a virtual machine?
    1. Yes, this allows multiple users to access the Data Loader.


That’s all we have time for today, thanks so much for joining us, and we’ll see you next time on the “I didn’t know ClearPoint could do that?!?” webinar. Happy reporting!