Data Loader 2.0

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

The new and improved Data Loader is finally here! In this episode of “I Didn’t Know ClearPoint Could Do That!?!”, we cover mapping your packages to upload quantitative and qualitative information, creating new scorecards and strategic elements using the Data Loader, setting up your data to load automatically, and so much more!

Happy Reporting!

Webinar 52 Transcript


  • Introduction (0:00)
  • Agenda (1:14)
  • Data Loader 1.0 vs. 2.0 (1:48)
  • Transitioning to Data Loader 2.0 (4:31)
  • Getting API keys and logging in (7:32)
  • Selecting a file to upload (9:32)
  • Updating and creating strategic elements
    • Selecting a worksheet and mapping the file (12:05)
    • Import and scheduling options (15:48)
    • Preview changes (17:07)
  • Questions (18:52)

Introduction (0:00)

  • Rachel: ‘Ahoy mateys’ and welcome to the “I didn’t know ClearPoint could do that!?!” webinar series
  • Today, we’ll be talking about the new and improved Data Loader 2.0!
  • But before we get started, here are your smiling hosts, I’m Rachel
    • Arah: and I’m Arah!
  • Rachel: As a few housekeeping announcements:
    • We will 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 you can submit your questions to [email protected]
  • Any that we don’t get to, we’ll follow up with over email!
  • Now, on our last installment, we walked through creating dashboards in ClearPoint, so if you’re interested in setting up a super cool, customized landing page that displays your most important data and information, be sure to check out that recording and transcript on our website or Vimeo channel!
  • In addition to the recording being posted, a lot has changed since our last webinar. Our ClearPoint team has been working remotely the past several weeks, and I know many of you are as well. During this challenging time, we just wanted to say thank you all for being part of the ClearPoint Community and for everything you are doing for your communities back at home as well!

Agenda (1:14)

  • With that said, let’s go ahead and ‘hit the deck’! Today, we’ll be focusing on the Data Loader 2.0!
  • We’ll start by explaining the differences between the original Data Loader and the new one, including how to transition between the two!
  • Then, we’ll walk through getting logged into Data Loader 2.0 and setting up your file to upload
  • Finally, we’ll wrap up by actually adding and updating elements using the Data Loader!! It’s going to be awesome.
  • So again, if you have any questions, please reach out to [email protected]!

Data Loader 1.0 vs. 2.0 (1:48)

  • Rachel: So before we set sail on our Data Loading journey, I think we better clear some things up first!
  • Arah: Yeah, I agree! For starters, can you remind us what the Data Loader is even used for? Also, you keep saying Data Loader 2.0, but didn’t we already have a Data Loader?
  • Rachel: Yes, that’s exactly right! Data Loader has been a ClearPoint household name for some time now, but in our most recent release we introduced a new version which we’ve been referring to as ‘Data Loader 2.0’.
    • In general, the data loader is a tool that allows you to automatically upload data into ClearPoint, rather than having to do manual entry every month or quarter.
    • So if the Data Loader is a tool to help you ‘map’ your data on it’s ‘journey’ from Excel files or databases to your ClearPoint account, then your automatically updated strategic elements are like your ‘buried treasure’!
    • For the original Data Loader, this applies to quantitative measure data only. While there is a specific formatting requirement to ensure the upload is successful, once the file is set up and mapping is complete, packages can be set to run automatically on a schedule.
  • Arah: That sounds incredible! How could it get any better?
  • Rachel: I know! It’s hard to believe, but somehow our development team pulled it off!
    • Data Loader 2.0 allows users to update both quantitative and qualitative information related to measures and all other types of strategic elements too – including objectives, initiatives, and action items!
    • And not only can elements be updated using the Data Loader, they can also be created making setting up new scorecards a quick 5-minute task!
  • Arah: Wow, I’ve worked on a lot of account set up during my time at ClearPoint, and I can tell you those time-savings are no joke!!
  • Rachel: Tell me about it, matey! And that’s not all…
  • With Data Loader 2.0, not only can uploads be run manually or set on a schedule, users also have the option to set the data loader to ‘watch’ a file. This means that whenever new data is added to the file and the file is saved, it will immediately be posted to ClearPoint!
  • Arah: Does that mean I don’t have to login to ClearPoint OR the Data Loader in order to have the most up-to-date data for my elements?
  • Rachel: That’s right!!
  • Arah: Amazing! So who on deck has access to this exciting new feature?
  • Rachel: Currently Data Loader 2.0 is only available for those on the Enterprise Plan. However, the original Data Loader is still available to those on both the Professional and Enterprise Plan through the end of 2020.
    • By that point, we will have a version of Data Loader 2.0 available for the Professional Plan – so keep an eye out for announcements on that over the next few months!

Transitioning to Data Loader 2.0 (4:31)

  • Arah: So when the time comes to transition your data loader packages from Data Loader 1.0 to 2.0, what does that process look like? Will users need to recreate all of their PACKAGES and Mappings? I know those took a while to set up in the original Data Loader…and personally, I’d rather walk the plank than re-do those mappings all over again!
  • Rachel: Don’t worry Arah, we’ll spare you from the sharks today! It’s actually super easy to transfer your packages to the new data loader, and the mappings will be completely preserved!
  • Let’s walk through the process together now. If Upward Airlines is ready to start using Data Loader 2.0, the first step will be to email the ClearPoint Support team or their account manager to ask for the download link. Once the new version has been installed, we can start the transition process.
  • In ClearPoint, we’ll go to System Settings > Admin Options > Data Loader
    • Right away, we can see the packages already set up with Data Loader 1.0. Currently Upward Airlines just has one for their quarterly data.
    • Here you can edit, duplicate, and run your existing packages for the original data loader.
    • However, If we click on the Data Loader 2.0 tab, we will see that list of packages repeated, but this time with the ability to ‘download’ the package.
    • We’ll want to click that download icon, which will provide us with a file that contains all of the mapping information for that particular package.
    • Once we have that file, we can click to open it.
  • Arah: Woah. This is getting a little crazy. That looks like robotic language to me! Do you think it’s some secret code for our treasure hunting journey?
  • Rachel: Actually…that’s exactly what it is! The language is called JSON, and while it doesn’t have to be a secret, it does contain the information we need to re-establish our file mappings in the new Data Loader and access our “buried treasure”!
    • It will be important to save this to the same location as the actual Excel file that contains the data, so that they can work together to provide a full ‘map’!
    • I’ll click File and then Save As to save.
    • Then, to keep things really neat and organized, I’ve created a separate folder on my desktop, where I can save all of my data loader files to. We’ll click on Desktop, then Treasure Maps, Open, and Save.
    • And the Excel file with the quarterly data is already saved there as well! So now, when we log into the Data Loader in just a minute, the package will already be there – fully mapped and ready to run!
  • Arah: Wait a minute…That’s it?! All you have to do is download the JSON file and save it to the same folder as the Excel or CSV file??
  • Rachel: Yep! Transitioning to Data Loader 2.0 is really that simple!
  • Arah: That’s a huge relief! I was a little hesitant to make the big switch at first, but this will be a piece of cake!
    • So now what? Can we see the new Data Loader itself? Where to, captain? The anticipation is killing me!

Getting API key and logging in (7:32)

  • Rachel: Of course! First we need to create some API Key in ClearPoint so that we can establish the connection between our ClearPoint account and the Data Loader.
    • We’re already in Admin Options, but let’s scroll down and click on API Keys.
  • Arah: And if you aren’t familiar with API Keys yet, don’t worry. You can simply treat them as your username and password for the Data Loader!
  • Rachel: Exactly! To add a new set of keys, we’ll click on the Plus icon and give them a name like ‘Data Loader 2.0’. Then we’ll need to indicate an expiration date.
    • Let’s set this for around 6 months from now – so October 2020
    • Since API keys are like our username and password, it’s a good idea to update them regularly!
    • Then the user selected will be the person who creates the API Key, but it can easily be changed to another user in the account by selecting them from this dropdown menu.
    • Once we click Save, we can click on the key icon to use it!
  • Arah: For a tool as valuable as the Data Loader, I’m glad we have it secured behind lock and key!
  • Rachel: Agreed! There are two different codes associated with the key for extra security, which we can copy and paste to log into the Data Loader!
    • We already have the Data Loader open here, which has been installed on our desktop.
    • You can see right away where the API keys fit. So we’ll just need to copy and paste them both in… And yo-ho-ho! We’re logged in!
  • Arah: Blimey! That was really easy! And I actually feel like I’m in another part of ClearPoint, not a completely foreign place! This must mean we’re on the right track for our treasure hunt!

Selecting a file to upload (9:32)

  • Rachel: Totally matey!! So right away we can see that the Data Loader is looking for files to upload on my desktop.
  • The various folders or locations where Excel and CSV files can be stored are referred to as ‘Directories’. The data loader is currently watching my desktop, but since I have all of my data loader files in my ‘Treasure Maps’ folder, I am going to select to ‘Watch New Directory’.
    • From here I can select the folder that I would like to watch – in this case we’ll allow the Data Loader to view our ‘Treasure Maps’ folder.
    • Now all of the files located in that folder appear as a new Directory below, including the Quarterly Data file that we transferred from Data Loader 1.0.
    • The file we are going to upload to Upward Airlines’ ClearPoint account today is the ‘Northern Division Scorecard’. Upward Air is adding a new division to the organization, and we can easily add the entire scorecard including objectives, measures, numerical data, and text updates all at once using Data Loader 2.0!
    • Before we dive in, let’s take a look at the Excel file together.
  • Arah: I remember from the original data loader that there were some pretty specific formatting requirements for the upload to work. Do those still apply here?
  • Rachel: Good memory! Yes, Data Loader files of the past had to be formatted with the reporting periods listed in the first column, followed by the data for each measure series. This is still how Data Loader 1.0 works, and is perfectly fine for uploading just measure data; however it did not leave much flexibility for anything else!
  • With Data Loader 2.0, your file can look something more like this –
    • We’ve included columns for the scorecard, linked objectives, measures, owner, reporting periods, quantitative data, and qualitative analysis and recommendations.
    • You can see we’ve included a lot more information than we ever could before, and while we have this formatted one way – this information can be organized in a number of different ways that can still be read by the data loader!
    • So now let’s jump into the Data Loader – batten down the hatches and get this treasure hunt started!
  • Arah: Wait! Don’t we need to close out the file for it to work, like we had to for Data Loader 1.0??
  • Rachel: Nope! With Data Loader 2.0, you can actually leave your file open while mapping and running your package, making it easy to reference throughout the process if you’d like!

Updating and creating strategic elements

Selecting a worksheet and mapping the file (12:05)

  • Arah: Oh perfect! So now that we are watching the Treasure Maps Directory, we can select ‘Add Mappings’ for the Northern Division Scorecard.
    • Our first step is to add the sheet or sheets we want to upload into ClearPoint. To do so we can check the box to “Include ‘BSC’ In the import”.
    • It’s important to note that the title of the sheet will vary based on whatever you’ve named it in your file!
    • From here, there are a couple different mapping options – the new way and the old way. For nostalgia’s sake, let’s start with the old way. Basically, we are making a DIY MapQuest, but no printing involved.
  • Rachel: Wow that’s a throwback for sure!
  • Arah: Tell me about it! The old way is for previous data loader users, who already have files formatted specifically for uploading measures using the old data loader. You can check the “Use Legacy Format” box. This will provide steps that some folks are accustomed to from Data Loader 1.0.
  • Rachel: So then, is the ‘new way’ basically the same punching in our destination on our GPS for some more high-tech treasure mapping?
  • Arah: Exactly! So instead of checking this box, we will leave it unchecked and go straight to selecting the scorecard we want to direct our data to.
    • Keep in mind that if you would like to add a brand new scorecard to your account, you will need to select to use the Scorecard column by filling in the bubble and then selecting the correct column in your file from the dropdown menu. Once selected, the scorecard column will be highlighted for you!
    • If you just want to update data to an existing scorecard instead, then you can check off “Single Scorecard” on the right and find the appropriate scorecard from the dropdown. In our case, our final destination is the “Northern Division Scorecard”.
  • Now that we’ve selected the scorecard, we can click into the name of the sheet that we want to map out. You will then be prompted to auto-map the columns.
  • Rachel: Woah auto-map? Are you saying that the Data Loader can read minds too?
  • Arah: Um…not exactly! It can’t read minds, but it can pick up where you want your data to go. Auto-map will analyze your file and map each column accordingly! It can save you a lot of time, so why not click yes and have the Data Loader 2.0 do the work for you?
  • Rachel: That’s still pretty cool!
  • Arah: Yup, automapping is a great starting point to help match the fields in your file to the corresponding fields in ClearPoint.
  • And if you don’t want to automap or if you want to adjust certain parts of the mapping, you can also edit into each column.
    • For the Northern Division Scorecard all of the fields that we are uploading are related to Measures, so we’ll need to double check that the Data Loader auto-mapped correctly!
  • Rachel: Everything looks good to me, except it appears that the Data Loader thought we meant Objective analysis and recommendations, instead of Measure analysis and recommendations.
  • Arah: Luckily that’ll be an easy fix! Starting with the Analysis column, we’ll click on the ‘Edit’ button. In the first dropdown, we’ll select the specific element that we want to map to, which is Measures in our case.
    • Then, we move onto the second drop down and select the field it needs to go to. This column is specifically for Analysis. So we’ll scroll down and look for it. Keep in mind, that this dropdown menu is inclusive of ALL default and custom fields that are available in your account for the element you selected in the first dropdown!
    • Now we can do the same for Recommendations – Click Edit, Measure for element type, and Recommendations for field type.
  • Rachel: Awesome! But what does this check box at the bottom of the page mean?
  • Arah: Great observation, Rachel! This is the “Copy down values to fill blank cell’ function Just like it says, it will copy down any value that is placed above the empty cells in your file.
    • If you intentionally left some cells blank – for example, if you do not have any data for future reporting periods – then be sure to uncheck that before you hit save!’

Import and scheduling options (15:48)

  • Arah: Great! Now that we have our navigation set up out of the way, we need to make sure to let the Data Loader know if this navigation is a one-time thing or if we plan on frequenting this destination!
    • Starting with the ‘Import Options’, your first option is to create new elements and edit existing elements. ‘Editing elements’ pertains to the ‘edit’ fields in ClearPoint – like changing the name, owner, reporting frequency, or description of an element.
      • For the Northern Division Scorecard, we’ll want to select this option, since we are creating brand new objectives and measures.
    • The second option is to ‘Enable updates only’. This would be used for regularly scheduled updates to fields like analysis and recommendations, or the measure data table.
  • Rachel: Speaking of regularly scheduled…
  • Arah: Yes, that brings me right to my next point! You have three different scheduling options in Data Loader 2.0.
    • You can choose to manually run your uploads, meaning that you can push updates yourself right from the Data Loader. This is what we’ll select for our one-time scorecard creation today.
    • Then we also have a brand new option to have the Data Loader ‘watch’ your file. This means any changes made and saved to the file will automatically be pushed to ClearPoint!
    • And finally, you can also choose to run the package on a schedule so that it updates on a timely basis based on your reporting frequencies.

Preview changes (17:07)

  • Arah: Now it’s time to make sure we’re headed in the right direction – and that our data successfully makes it from point A to point B!
    • To do so, we can click on the Preview Changes tab. You will also be able to see all the reporting periods being updated along with objectives, measures, and series being added.
  • Rachel: ‘Shiver me timbers’! This is incredible. I can’t believe such a large number of changes can all be made in ClearPoint at once!
  • Arah: Agreed! And then new changes, more specifically the newly added data, will be highlighted in yellow. Any errors will be highlighted in red. Once you’re all set, then you can either hit save or run the package directly from here.
  • Rachel: Alrighty! Well, now that our mappings are complete, let’s run this package and see if it takes us to our long-awaited treasure chest!
  • Arah: Rachel, are we there yet?
  • Rachel: Let’s check in ClearPoint and see! And you know what they say about buried treasure – X marks the spot!
    • Oh my gosh – a fully built out balanced scorecard? That’s even better than diamonds and pearls, and much more valuable!
  • Arah: Totally! And none of this would have been possible without Data Loader 2.0!

Questions (18:52)

Rachel: So true! With that, we’d love to use our remaining time today to open up the floor for your questions!

Question 1:  So do you have to have experience with the original Data Loader before you can use 2.0?

  • Arah: Great question! No, you do not need to have packages set up in the original data loader in order to start using Data Loader 2.0. If you are new to data loading, you can go right to using Data Loader 2.0!

Question 2: I’ve noticed there is an additional file that appeared once I saved my mappings and contains a lot of confusing code that I don’t understand…can I delete that?

  • Rachel: I’m so glad someone asked! The short answer here is – ‘when in doubt, don’t throw it out’!
  • So that file is actually the same type of file that we used to transfer the old Data Loader 1.0 package to Data Loader 2.0. The ‘confusing code’ is called JSON and it provides the Data Loader with the mapping information, so that you do not need to remap your file every time you use the Data Loader. Make sure to save this file in a safe place, alongside the file that contains the data. This will help ensure a smooth uploading process going forward!

Question 3: Is the Data Loader 2.0 still compatible with SQL query, just like the original?

  • Arah: The answer to that is yes! The Data Loader 2.0 is compatible with SQL query. If we pop back into the Data Loader, you can set up those database connections underneath the directories here.

Rachel: Alright, looks like that’s all we have time for today! Thanks for taking the time to learn about the Data Loader 2.0 with us! We hope you can now confidently say ‘I DID know ClearPoint could do that!’. See you next time, and Happy Reporting!