Uploading Data Automatically to ClearPoint (Webinar 43)

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 Kathryn.

And I'm Andy.

And as a few housekeeping announcements, we will be recording this session and will make it available online within a week. The webinar will last about 25 minutes with time for questions at the end, so please submit your questions to supportlearpointstrategy.com any that we don't get to on air, we'll respond to via email. Now today is going to be all about automatically uploading your data to ClearPoint using the ClearPoint data loader. And for those of you that are unfamiliar, the Data Loader is a tool that clearpoint users can install on their own computers and is essentially a way of updating your measure or metric data with the click of a button.

And that's an understatement. We estimate that there have been over 2 million data points updated via the Data Loader, saving users more than 4,500 hours of data time.

Wow. So much more time for activities.

Yeah, definitely. 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.

I can imagine every single person listening can relate. So we'll start by showing you how the Data Loader plays an integral part of an automated reporting process. Then we'll dive into configuring your packages to upload via the Data Loader. We'll 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 right from within clearpoint. Now, for those of you who have been following the upgrades in Clearpoint 12.0, you've probably seen the ways in which Clearpoint scheduling features are helping you save time and automate your entire reporting process. 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 your 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 their analysis for their data.

And of course, we can assume most users log in and enter data right after seeing this reminder, but maybe there are a few stragglers so that's taken care of too, with a second reminder that will send only to owners who haven't made their updates yet.

Nice. And you can set a deadline for your account to lock down automatically for administrator review. Then, thanks to report scheduling, the admins can get a draft briefing book right to their inbox. And after making any final updates, schedule a briefing book to send out to the leadership team automatically.

You still have to run your meetings in person, but afterwards you can schedule a report of all action items you captured during the meeting to be sent out to attendees. Your reporting process basically runs itself pretty great.

So now that we've seen in context how the Data Loader fits into an automated reporting process, let's go into a little more detail about how it actually works. Andy, how can all of our excited viewers out there get access to the Data Loader?

That is a good question. So 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@supportlearpointstrategy.com we'll send you the link to the file you need to install the Data Loader. And if you contact us through the Data Loader Care package page in the support center, we'll even write the email for you.

Yeah, so we'll send out a link to that care package along with the webinar recording. So what types of clearpoint users can use the Data Loader?

So if you are an updater, an editor, a scorecard, admin, or a full administrator, the Data Loader is at your command.

So basically you're saying that you can use it as long as your user type allows you to make changes in ClearPoint?

Yep, that is correct.

Awesome. So again, the dataloader will be installed right on your computer. You just click the link to download the file and run the installer like you would for any other program. Once it's installed, you'll be able to log in with your clearpoint credentials and you can update upload your file to connect it to clearpoint and begin uploading.

So file structure is important here, so let's take a look at that quick before we get too far along. So we're going to start with an Excel file example, but this is true of any file type you upload with the Data Loader and more on the other file types later. So if we look at this example file here, we can see the way that it's formatted, and all files need to be formatted in the same way. So if we start in column A, you will see a list of all periods that we want to add data to.

And for this period column, you can either enter the period name or the period end date, but either way, it needs to match what's in your clearpoint account exactly.

After the period column, you then list the series you want to map to in the subsequent columns. Keep in mind that 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 name in the spreadsheet has a unique name.

Yeah, and each of these columns is going to be mapped to a single series in clearpoint.

Right. And something else to keep in mind is that you can have as many series as you want in one of these packages. One of our client currently maps over 900 series in one package, and you can use multiple sheets in the workbook if you would like as well.

Great. So now that we've made sure that our file is configured to have the required format, we can go ahead and set up our package in the data loader. And so this setup is going to be the process of pairing or mapping each column of our data from that external data source with the measure series where we want the data to be uploaded to clearpoint. And I've got the data loader open here to the first screen that you'll see when you log in. I'll click where it says click to add a new row in order to get my package started. And I'm going to want to give it a recognizable name. So we're going to be working with our HR metric. And under file name, I can click the three dots here to choose a file from my computer to use as the source of our data. The data loader you can see here under package type automatically recognizes that file as an Excel file. So we'll go ahead and click Next, and this will take me to the scorecard selection screen, which shows me all the scorecards I have access to in my clearpoint account so that I can decide where I want that data to be uploaded. I'm going to check the box next to Upward Airlines Corporate here, and then click Next and I'll move on to the Mapping definitions screen to choose the exact measures and series where the data from each column will fall. So what we're seeing here in the column name column is the name of both the sheet from Excel and the column name that I've given the column in my file. And then for our scorecard measure, and series columns. These are going to be indications in clearpoint of where you want that data to go. So I'm going to look in particular at our turnover rates, which we track by division. So I'm just going to check the box to enable each one of those columns from my spreadsheet, and then I can use the drop downs here to select the scorecard. Measure and series where I want that data to go.

So this might take a few minutes to configure the first time, depending on how many measures you have included in the package. 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. And this is where the huge time savings comes from with the data loader.

All right, so we're all set here, and the next screen is going to be our preview data screen, where I can see what would happen to our data in clearpoint if I were to upload the data based on what's currently in my Excel file. So any unchanged values are going to be shown in white. These green cells here indicate that new data is going to be uploaded to clearpoint. That will overwrite the value that was currently there. And red means that the data is invalid. So something like a text format or an invalid period, then gray could mean a number of things. If a series or period or scorecard is locked from updating, the data loader will be blocked from adding data into those gray cells.

Yeah. Rest assured that the data loader doesn't get special treatment to your measured data in clearpoint. It must obey the same permission rules like everyone else. Entering data manually into clearpoint.

Exactly. So on the next screen, when we've made sure that everything looks good in our preview, here's where we could enter a schedule. But because we now have the option to control this from clearpoint, I'm going to wait on that and let Andy preview that later. So regardless of whether you set up the schedule, you can always run data from a Data Loader package on the spot too. We'll click Finish, which will take us back to the home screen, and I can click the green arrow to run my data right away. Now, we can see in the background here that I've got our turnover rates measure open in clearpoint. It currently doesn't have any data in it, but once we click to run our package, we're going to see it refresh here along with the toaster message that our package was uploaded. And I can see that both my chart and data table have populated automatically with several periods of data from our package. And even this calculated series for our divisional average turnover rate has calculated based on that information as well.

And again, any locked series or locked periods will remain unaffected. The day loader is not going to overstep these rules. Even if there's data mapped there in your package at the time of the upload.

Yeah, so next time we have new data available, we can just reuse the same package that we set up. When the next reporting period comes around, the same measure can be updated instantly.

And 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. So some of you are probably wondering what file types you can use, and that is a great question. We looked at Excel already, but other than that, 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 the file format. But SQL will just require an extra step in the setup. And all that it takes is a SQL query file and a database connection. So if we go ahead and open up this text notepad, we can see the file here, and this is just the query that our data loader is going to use. So if I open up the data loader, I can go ahead and create a new package, give it a name, select my query file, and then we can see that it defaults to SQL as the package type. So then when you do that, you will see this ellipses button show up over here on the right and you can click this to set up your connection. And all you do is pick the data source, you select the data provider and then there will be a place to specify the server, provide the credentials to log on to that server, and then the database within there that you are going to use for this upload. And you can always test the connection using the button down here, just to be sure that everything is set up properly.

I know that this probably looks a little bit complicated to some of you, but don't worry, the database connections with the data loader are super simple and they're something that your IT team can help out with in a gsi.

Yeah, and from there mapping that package will work the exact same way as the Excel file we showed earlier. So getting connected to the data source and giving you the query file is all that your IT team will have to help out with. You can handle the rest Great.

So at this point you might be wondering about some scenarios where the Data Loader can help to supercharge your updates in clearpoint. And 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. So one reason for this, that this might be the case is that you are collecting data daily or weekly, and then you want to consolidate it into a monthly average or sum before uploading those data points to be reported out on from clearpoint. So rather than having to copy and paste your data at the end of each month, the data Loader is going to help you ensure that you can easily get all that data into one place for reporting with minimal effort. So as you've seen, you'll just set up some basic configurations to establish the connection with clearpoint to your external file, and then you can set the data package to upload on a regular schedule and just watch your data roll in. But that's far from the only use case for using the Data Loader.

Yeah. So 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 their charts and produce their reports with their information in clearpoint. But they've been storing all their data somewhere else, like Microsoft Access or Excel files, and it's hard to get them to see past the effort of transferring all that historical data from one system to another.

Not with the Data Loader.

Exactly. So 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.

Yeah. So the Data Loader is a great tool to help you move past any roadblocks around historical data uploads and get your departments to take advantage of all that clearpoint has to.

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. And if we go back over to clearpoint and we navigate to system settings and admin options, we will see that there is a tile for the Data loader. If you click on it, you will see a list of all the packages that exist in your account for any of the other users as well. For example, we can see Joseph's customer success metrics and Ted's Finance metrics. We can also see the package that Katherine just made for her HR Metrics on this screen. You can delete or duplicate or edit these packages, or you can click this little blue rocket ship button and run the package automatically from here without even having to log into the data loader. So basically you just click on the rocketship and tell it to run. Doing so will cue the package to run for uploading sometime in the next 10 minutes. So the next time Katherine has updates in her file, she can run it from here, no problem. And she doesn't need to log into the Data loader.

Yeah, and it's important with this option to note that your background service for the Data Loader must have access for this method of uploading to work. And for that we do have a support article that will help. And this is always something that your IT folks can help with too.

Yeah, definitely. So if we click on the edit icon, we can see what our options are here. And first we could change the name of our package. We can change the machine ID or the computer name. So I can change that from Catherine to mine. Now we can change the owner as well. And doing so will officially move that package to my username and my computer so that I'm the one who sees it when I log in. So I'm like the owner of this package.

That's helpful. For example, if you're out sick or you're away from your computer and you need someone else to upload your data in a pinch, if I'm an administrator, I can easily transfer that access to a co worker.

And you can also change the file path from here if you are transferring the package to a new user, for example. And you can also see when you edit each of your packages, what schedules exist for that package. So if we click over here on schedules on the left, this is where we will see no schedules added, because Kathryn did not add one from the Data Loader. But one of the awesome new features in 12.0 was, of course, scheduling for briefing books and reminders. And with this new feature came the ability to edit your schedules from within clearpoint so that you need to edit the open the Data Loader every time that you want to configure that schedule. So to add a schedule, all you need to do is click the plus icon in the upper right and you can give it a name, say seventh at noon. You can specify what that first run date should be, as well as the runtime of day. You can also choose to change up the frequency. So right now it's set to monthly, but you could also do weekly, different combinations or Daily and kind of anything in between too. So we can have it run daily, every seven days at noon, for example. And the options here just give you a lot of flexibility for whatever your schedule needs. So if we save this, we will see that our schedule has been added here for our package and that we can see that run date listed over here on the right.

Fantastic. Now we're sure you have some burning data loader questions and we'd like to open up the floor to get some of those answered. So here's one that's come in. Can multiple users use the same package? Andy, do you want to tackle that one?

Yeah, definitely. So each packages have a one to one relationship with users, meaning there is one user per package. One thing you could do if you want to have multiple people owning the same package or have the ability to upload the same package source data to clearpoint is just duplicate your package. So I could change this to be Katherine.

Sorry, my name's so long.

That's why I changed to Andy. And I can change the machine ID back to Catherine. And now we can see both of our packages listed here. This is mine and this is Kathryn's and we can both upload the same data now if we would like, using the same package.

Awesome. So here's another good question. Which packages is the data loader available in? The dataloader is available for professional and enterprise plans. And if you're not sure that that's your plan or you just want more information about how to use the data loader, definitely reach out to us. We're happy to answer any questions that come up.

And it looks like someone's question is actually to get a link to install the data loader. So glad someone is making a move already and ready to get going. For anyone else who wants to get a link to install the data loader, just email us@supportlearpointstrategy.com and we can send that over to to you.

Perfect. So here's another one. Can I upload text with the data loader? No. So the data loader currently uploads data to measure series, and that's quantitative data only. But we do have an API available in the clearpoint Enterprise plan that will help you upload qualitative data to clearpoint. So definitely reach out to us if you have any questions about particular scenarios so we can talk through that use case and what you might be able to accomplish.

And it looks like someone has a question regarding the end date field, which I believe is in reference on our schedule for our package to this field down here. So as you can see, this field is optional, but if you wanted, you could say, hey, I want my package to only run for the current calendar year. Maybe the metrics that you're uploading with this package, you know that they shouldn't receive their daily updates anymore if that's what you're doing at the end of the year once we go to 2020. So that's just one option out there if you want control that way on when your schedule can run.

Perfect. So we are reaching the end of our time here today. Thank you so much for joining us on this episode of. Thank you. I didn't know clearpoint could do that webinar series. We'll see you next time. And happy reporting.