The Data Loader (Webinar 26)

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 you turn into a reporting superhero. So here we are, your smiling hosts. I'm Kathryn.

And I'm Andy.

And just a few housekeeping announcements. We will be recording this session and we'll make it available within a week. The webinar will last about 25 minutes with time for questions, so you can submit those questions to supportlearpointstrategy.com we'll answer as many as we can at the end, and any that we don't get to, we'll follow up with by email. On our last installment, we introduced you to five new wonders of Clearpoint 10.5. So if you weren't able to join us, I'd highly recommend checking out that recording on our Vimeo channel, our support center, and on our blog and 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. And 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, how to download and install it. We'll walk through some examples, demonstrate how it changes content in clearpoint, and we'll make sure that you can manage your packages right from within the application once they're set up. So let's dive right in. We are exploring the friendly streets of Metropolis today. Hey, guys, can you help me with something?

Jenna, we're in the middle of a webinar.

This is more important. Excuse me? More important than a webinar? Guys, 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, like watching a webinar.

Okay, Jenna, we can give you a hand, but we have a webinar to get back to, so we're going to do this in less than five minutes.

Awesome. Thanks.

Guys, can you send us that Excel file?

I already sent it over.

Great.

Ok, pulling it up here. There we go.

Great. It looks like it's formatted correctly for the Data loader with the period names 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 the 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. I need to get all of this data in for the whole quarter. Not a problem. Let's pull up the Data Loader.

And so we upload our budget data this way every month. So we actually have a package locked, loaded and ready to go for you.

That's right. We've mapped the Excel file to measures and series in clearpoint. And now all we need to do is click the green arrow to run that package and upload the data. So that's loading up right now. And once it's done, we'll need to just go into clearpoint and refresh. And there you have it. Wow. My report is completely full of data. It's really that fast? It's that fast. Your calculations and evaluations and even your charts have all been updated too, and we're not done yet. Do you have your template set up for your briefing book? Yeah, it's the one in the templates, the one called Monthly Budget Report. All right, I see it there. So we'll just click to generate for March and that'll be in our inbox in no time. So here you have it. The Data Loader has helped Jenna get all of her information into her report. We'll check here. It was less than five minutes. And this is just one example of how the Data Loader can help you become a reporting superhero. Wow, guys, that was amazing. It usually takes me an hour to get this information into clearpoint. Thanks, clearpoint. Thanks, Data Loader. Bye, guys. Bye, Jenna.

Okay, so we just threw a lot of information out there. What do you say we back up a little?

Definitely. Can you start with a little more info about what the Data Loader is?

Yeah, absolutely. Let's get started. 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 measures or metric data with the click of a button. It's different from the web based version of clearpoint most of you are probably used to, but it is still entering data into the same database. Users create what are called packages that link Excel, CSV or SQL data sources to your ClearPoint measures. Now, file structure is really important here, so let's go ahead and take a look at that before we get too far along. Starting in column one, you will see a list of all of the periods that you want to be able to map data to. Then you list each series in the subsequent columns. Each of these will have a one to one relationship with some series in your clearpoint account.

Right. So, for example, each of these budget columns maps directly to one of Jena's department budget series for her report. Yep.

And please keep in mind that you can use as many series going across here as you want. One of our clients currently maps over 900 series in one package.

Wow.

Yeah, and you can also use as many tabs as you like as well, if you're someone who likes to stay organized. So once you have everything mapped using the data loader, all you need to do is click that green button that we used before, and your data will upload in a matter of seconds. And packages can be reused too. So when the next reporting period comes around, like it did just there for Jenna, the same measures can be updated instantly.

Right. And so this is not to be taken for granted. Thanks to that package we created last month, Jenna was able to upload her data in an instant and produce her report in no time.

Yeah. And additionally, you can even set scheduled uploads, which is a huge perk of the data loader, as it can automate the entire data entry process.

So you might be wondering how you can install it. And if you want access to the data loader, simply reach out to us@supportlearpointstrategy.com or will send you a link to the file you need to download the data loader. And as far as permissions go, if you're an updater, editor, scorecard, admin, or full administrator, the data loader is at your command.

Yeah. So basically you can use the data loader as long as your user type allows for you to make changes in clearpoint.

Right. So, as Andy mentioned, this will be installed right on your computer. You'll just click the link to download the file and run the installer like you would any other program.

Yeah, so once you get that data loader installed on your computer and open it up, you will be able to log in with your clearpoint credentials, and it's based on your permissions in your account. So you can only upload data to measures that you already have access to. So I'm going to go ahead and log back into our Metropolis account and this will take us to 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 create a new one together. I'm going to basically recreate the package that we just used for Jena's upload. And to create a new package, all we need to do is click in this top row. We'll give it a name. So I'm going to just call it Budget 2. Next, you select the file that you would like to use. So this is going to give you access to the different folders on your computer. Oops, wrong folder. Let's grab this file. Okay, so then once we have that selected, it knows that we used the Excel file type, but you can always use CSV or SQL as well. We'll come back to SQL later, so we'll go ahead and click Next and this will take us to the scorecard selection screen. From here, you can select which scorecards you want to map this file to. I want to go to each of my departments, so I'm going to select all of them.

So something worth noting here is that Andi is mapping to multiple scorecards. That's good to keep in mind when you're thinking about how you want to construct your packages.

So if we click Next, that is going to take us to the mapping definition screen. This is where you can map your file to the correct measures and series in clearpoint. Through a series of dropdowns, you can select the scorecard, the measure, and the series that your data should go to. So first, what we would do is check off a box to enable this column of data, we would select the scorecard we want to go to, followed by the measure name as well as the series. So if we do another row and you can see that I named my series of data properly in the file so that I can tell where it is I'm supposed to map to in clearpoint.

Yeah, that's a great point. And while this screen does seem to involve a lot of work, please remember that the purpose of the data loader is to have a reusable package that will allow you to quickly and easily upload your data into clearpoint time after time.

That's right. So it's a big. It is an upfront investment, but it reaps rewards in the form of tons of saved time for your team. But I think this gets the point across and how that works. So let's move on to the next page. This 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 the file series that are unchanged, like those that we just uploaded in Jenna's example, 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 before and it is going to remain that way. Red means invalid data. This could be something like text or invalid periods in your file. And then gray could mean a number of things. If a series period or scorecard is locked from updating, the data loader is going to be blocked as well.

Yes. So rest assured that the data loader doesn't get special treatment to your data. It must obey the rules like everyone else.

Yes, exactly. So I'm going to go ahead and click next and this takes us to the final screen, which is the package schedule screen. This is where you can really maximize the time savings for your team. So, as I mentioned before, one huge perk of the data loader is that automatic upload schedules can be created. These aren't required, but are just a nice option to keep in mind. Schedules can be set to run on a specific date or time and you can also change the frequency to meet the perfect timing for your reporting calendar. So if I go ahead and create just a test schedule, show you some of those options so you can pick which date it starts on, what time of day it uploads, you can change the frequency from this drop down. So you could do something like run weekly, every two weeks, on Fridays at 5pm, or maybe you have a monthly upload that you want to take place. You could say, let's run on the second Monday of every one month at 8am so you can really customize this to whatever you need for your team and your reporting calendar.

Yeah, it looks like really any timing is possible.

Yeah, exactly. So once we have this schedule created, I'll go ahead and click finish and this is going to take us back to our data packages or home screen. We can see our old packages as well as our new one that has been listed here. If at any time you want to upload data, all you need to do is log in and it'll take you to this screen and click this little green arrow. And just like that, your data will be uploaded in a matter of seconds.

Yeah. So once you set up that package, this is really a lightning fast way to get your data into clearpoint. And I'm sure that you're aware that entering your data manually has an effect in clearpoint beyond just filling the empty cell. So entering data with the data loader is no different. So let's see what else you can expect to change here. We'll go back to our monthly budget report and click into our Airport department. So we'll see here by clicking on the measure data table that we had two raw series uploaded from the Data Loader. But we also have these two calculated series and these have been calculated based on the data that the Data Loader uploaded. You can also see here further up on the page, these charts that were previously blank now have our Data Loader uploaded data included as well. And on the flip side, if you have locked series or locked periods like we mentioned earlier, the Data Loader is not going to affect these settings even if there's data mapped there in your packages. Okay, so so far we've walked you through an Excel example using an Excel file, but some of you are probably thinking, what if my data is stored in some other system that we use in my organization?

Yeah, and that's not a problem. It works very similar to how Excel does. So let's take a look at that now.

Great.

So many of our clients use the Data loader to SQL query data out of those systems and into ClearPoint using the data loader with the help of your IT team. It is really quite easy. All it takes is a SQL query file and then you need to establish a connection to the database that you want to use. So let's open the day loader back up. So if I go ahead and create a new package by giving it a name and then I can go ahead and select the SQL file that I would like to use, then we would pick from the package type SQL and use the Ellipses button to establish that connection. First we select the data source and the data provider. Go ahead and click ok. Then what we can do is enter the server name and our credentials enter my username and password, then I can select the database as well and you can test the connection if you would like, by using this button down here. And it looks like we are successful.

Awesome. So I know this probably looks pretty complicated to some of you, but don't worry, database connections with the Data Loader are actually super simple and. And there's something your IT team can help with in a jiffy.

Yeah, exactly. And from there, like I said before, mapping the package works the exact same way that SQL Query is going to create a table of data that's going to look just like that Excel file. And I can map that to where it needs to go in clearpoint. So just to run through it quick If I click next, I can select the scorecard I want to go to. I can map out the series that I've made available through that SQL query, tell it where in clearpoint I want these to go. We see that these are starting blank. They're going to be completely replaced with this data. And just like with Excel, you could create package schedules as well. But I'm not going to. We're just going to upload this manually. Okay. So we get back to the home screen and we can see that our package has been added here. So I'm going to go ahead and find that measure in clearpoint, and we can see that it is totally blank. We return to the data loader, we can quickly upload it using the green arrow. It's going to ask for sure, and we are pretty quick. If I return and refresh, we can see that our data has been added both in the table and our chart as well.

Easy enough.

Yep, exactly.

So, as many of you saw on our last webinar, once you've got these packages set up, administrators can now manage data loader packages right from within clearpoint. And once again, we'll walk you through how that works. So if I click over to System Settings and Admin options, I'll click on our Data Loader tile and from here I'll see I can delete, duplicate, edit, or run a package. So let's click on the edit icon to see what we can do here. And I can change the name of the package. For starters, I can change the package owner. So that would be the user who has access to it when they log into the data loader. And I can change the machine ID of the computer that has access to the package as well.

Yeah, and that's something important to keep in mind, that you can change the machine ID and the package owner. So for example, if you're out sick or away from your computer and need to get that data uploaded, in a pinch, clearpoint administrators can transfer access of the package to another co worker.

Yeah, it's really useful. And if your package has changed folders, you can even change the file name right here in clearpoint as well. Then you can also see whether the package is set to run on a schedule. So it looks like we're going to upload this package again tomorrow at 5pm and then as we mentioned, if you want to run a package without even having to log into the data loader, we can click on that Rocketship button and doing so is going to queue that package for uploading sometime in the next 10 minutes. So the next time Jenna has new uploaded data in that Budget Excel file, she can run it from here, no problem.

One thing that's important to note here is that when you install the Data Loader, it's actually installing a background service as well. And that's what's going to allow the services or the schedule to run as well as this rocketship. So that service just needs to be granted permission to the folder where the file is living. And this is something your IT team can help with. Definitely. If you do not have permission to. And we have a help article that walks you through that process as well.

Yes, definitely. So it looks like we're closing in on the end of our time here today and I want to make sure we leave some time for questions.

Yes. So let's go ahead and see what's come in.

Here's one. Does the Data Loader only work on Measure series or could it be also used to upload comments to the various text boxes?

Yeah, so right now the Data Loader can only be used to upload directly to Measure series. So the quantitative data in clearpoint we do have on our roadmap to have the ability, whether it's through the Data Loader or some other tool, to upload data easily into text fields in clearpoint. Alternatively, if you don't want to wait for us, our API documentation is now out and available. You can find that on our website or in our support center, and you could use that to get whatever data you need to in or out of clearpoint.

Great.

Looks like another question. Does the Data loader work with SharePoint? The answer is yes. The Data Loader works works fine with files from your network. And SharePoint can be used if you have it configured so that the file is accessed over something like web dev or something similar like that.

So as far as data sources, what about something like munis?

Yeah, that's a great question. And that is a very popular data source used among our municipal clients. The answer to that is yes as well. You can use the SQL query functionality of the Data Loader that we just showed you to get data out of munis and quickly put into clearpoint.

Okay, so here's another good one. How do I ensure that I have the most up to date version of the Data loader? And if we open up the Data Loader here again, you'll see a number in this top header. It'll say V1.2.1 and 1.2.1 is currently our most up to date version of the data loader. So if you're opening the data loader on your computer screen and you don't see that number there, reach out to us and we'll get you set up with the most recent version.

Here's one more. Let's get this one in. Someone asked basically, is it possible to install a data loader on a virtual machine? The answer to that is yes. We have several clients who also do that. And basically that just allows multiple users in the organization to get access to that data loader. So they can all manage their packages and the one data loader from the same location.

Yeah, that's really useful.

Yeah, saves your IT team a lot of time too.

So again, if you want to download the data loader, reach out to us@supportlearpointstrategy.com.

And we can manage their packages and the one data loader from the same location.

Yeah, that's really useful.

Yeah, saves your IT team a lot of time too.

So again, if you want to download the data loader, reach out to us@supportlearpointstrategy.com and we can send you that link to download the file. That's all we have time for today. So thank you 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. Bye.