Getting Data Out — ODBC and JSON API

I going to talk a little bit about how to get data out of the system um a lot of times these requests come to me where they're they're talking about an obbc interface uh to connect to our database um we want to back it up just a little bit and talk about uh if the goal is to

get data out we have a few different methods of getting data out um the first one I'm going to show is in product um this is definitely the most useful if you are just needing to get to see the data right to be able to see the data in the product that's simple um so I went to

control panel and then I'm going to go to the system report editor and I'm going to add a report and this is our system report editor tool it uh does take a security setting uh as well as um um some security access along with that uh that we verified basically using two Factor kind of the requirement that

we're looking for um and this tool it lets you write SQL so this is querying the database um so you can start off with just you know um uh a test name you give it a name and then you can start writing SQL and we have where it's going to autocomplete you saw that pop up there and

and so I can say something like select star from and if I start typing a table name it will autocomplete it for me so if I know that I'm looking for some data that has to do with an encounter that I had with the patient I start typing encounters it'll fill in encounters for me and even if

I Alias that and then I say where e dot it'll fill in what columns are available on that table um so if you kind of have an idea of what you're looking for but you may not know the exact name uh this will help you fill in uh you know that I hey I want to filter this

out by a certain date I can scroll through here and see uh which which date columns I have so like modified dates a great one I going to look for encounters with a certain modified date if you are perhaps not as sure where you want to start we do have a couple of other tools we have our

little button over here called the model browser um when you click on that that will let you search for tables so if I look for that same one I can look for every table with the word encounter in it right there's a lot of things related to encounters um that you can get into the details on that

I'm just going to stick with the base one the encounters is where it all starts this gives you a little bit more of an expanded view so it does still show you all the columns um but you can click on those things and get a little bit more information I chose pad ID because I know this one's

very interesting that one is going to relate back to the patient or the employee this goes back to the chart um and so this actually does indicate that there is a foreign key so this table this model table the model browser is aware that the pad ID is related to patients. pad ID and so and that's also

a link that will link me out to the patients table and I can browse the patients table and see what Fields there are and this does go in both directions you know I'll show um uh columns that are uh that this thing relates to and I can also say what is referenced by the encounters table so this

uses encounter ID that's the primary key on the encounters table and that will show me all of the tables that are related that have a foreign key back to encounters. encounter ID and I can you know continue browsing those relationships and try to get an understanding of how those things come together so definitely the model browser is

is a great tool I always start people off in the product looking at you know how can I query data in the product even if you do need uh an external interface if you need to get the data into another product if you need to get it into powerbi or or Informatica something else that you want to

correlate with other data sets um or perhaps just you know needing that tool I still do like to start with this because of this model browser I think one of our top questions is you know where's the data um and this definitely helps browse that it helps browse around understand the tables understand how they're related uh some

of these even have notes we definitely do need to put some effort into adding some notes uh do we have any simple ones yeah we just uh you can also add Wiki if you need to make your own notes that that Wiki help is something that can be added some of these do have comments on the columns

unfortunately I'm not hitting any that seem to so um so there are some additional notes hopefully more notes coming um so that's going to be the first thing I want to start with and uh the in um the the in product tool I'm actually just going to leave this off I'm going to I'm going to leave off

the work CLS we're going to get everything from Encounters this is my test system I would not recommend doing this query in a production system if you decide to use this use a smaller table um but if I go ahead I have a name and I hit run on that um built into the tool we can run

this has a filter already on it for me I'm going to go ahead and use that so we're going to clear off the group by encounter ID and now this shows all the records so this is every encounter in my development system all test data course um and I can you know browse through all of the records

and if there's something that I want to understand a little bit better I can do things like grab a column put it up in the group field I chose service date now that one's a fun one because it's a date time we get extra options built into the tool that if I want to understand my encounters by

year by quarter by year and month I can do that and so it's going to group all of this data now you know for J uh June 2000 do we have anything new maybe with some more records here and the test data so go like July of 2010 has more data February of 2010 and so now these

are group by the uh the month and the year uh we also just automatically get a graph when you do group by if you do group by we'll go ahead and we break that up so you can get the counts uh and there's lots of cool things you can do about aggregating the data there's a lot of

different things you can do um on that as well as pivoting if you want to then see well I want to understand that's probably not a great column there's no data in that one um in in for that one performing user ID is a good one who the Performing user ID would be the user that completed this

encounter um or or was the sorry I should say the Performing provider on the encounter not the actual person at the keyboard necessarily um but if you wanted a group by who and then what time you know we can do pivots and things like that so a lot of cool lot of cool things in this tool I

won't go too deep in it there's probably a whole video that could be dedicated just to this tool Alone um but just to give some of the highlights of being able to visualize the data that is in the system um running reports right inside of the system this of course uh you know like I said just scratch

in the surface there's uh definitely going to be lots of questions about I uh how to find the data if you go into the system report editor when you have access the other uh thing that this is a great resource for is looking at examples so again even if you're using an external tool to query the system

but you need examples on the relationship so the data um you can come in and search for just a table name and you can understand you know here here's a report on all Encounters this is out of the box that comes with the system and it just shows you how to find all encounters uh you'll find lots

of very useful information on how do I get from encounters I want to understand the visit type and you can understand that e you know we have an alias EVT for encounter visit types and if we look at that EVT up top well that's the description for the visit type so the encounters table has a code and

this is how to go from the code to the description um likewise how we store revisions on certain things that I mentioned that relationship back to the patient record the chart um they're Mr Numbers right so you can start to understand how all these things relate but just by looking I think these examples are a great resource

to see uh better when you find one with comments of course we're we're working on improving that but um at least you get the joins in there to see how they work and see what we call things um when you get into some of the more challenging relationships we do have a system report FAQ document um I'm

going to jump down I actually jump down to this one a lot of the stuff before that with some of the stuff I covered uh but one of the one of the challenges is how encounters link to other objects did that one have that is encounter documents but doesn't have encounter links encounter links is something that we

get a lot of questions about encounters link is the name of the table this one's not easy to understand just by looking at the database there is a module column on that that's one that I can here if we do let going go back to my right so encounters link don't know to complete that for me and

El dot you know we have those likewise in the model browser it's much more visual right and oh so this is also available in the model browser this is this is new uh we have the magic numbers documented um so this is the magic number for module um this module is basically saying what is the en counter

link to so when I have an encounter ID 1 2 3 4 5 and I get a linked ID of 4 5 6 7 8 I don't know what that linked ID is uh the magic numbers for the module this will tell you if it's medication condition family observations that's a big one observations tables module 83 is

very popular five is very popular if it's a condition that is in the assessment we have a different link the versus you know just a condition on the chart um so yeah a little bit of information on that um so that's available here as well as in the FAQ uh the FAQ has a little bit more uh

some more notes in there about that relationship if writing um SQL uh perhaps isn't the method that you want to get the data out or you know a powerbi connection um we do offer an API it is a Json based API we have our help documentation available out here on the site so I would reference that for

uh uh any updates to that after I make this video when you go out here it does give you some notes on how to access the API in uh in product uh I think it's important to us to be able to learn about these things right in the product right to be able to use them we do

use the API in our product or you know some of our our screens and whatnot we'll use our API in order to um to manage data in the system um so it's not just meant for external use we use it as a part of the product this page that it's showing you here is in product you can

search for API and ends up in control panel interfaces API this is sort of a testing ground for using our uh in product code that we have an M API that is a JavaScript object if you're if you're a programmer familiar with that this will let kind of walk you through how to use our in product uh

API uh that if you if you have something that um you want to run in you know make a screen that queries for data or updates data things like that this will kind of show you it can also be used to get some ideas on the requests that we make so if you are want to be outside

of the product and and interfacing with this API you can use this to write a write a request you know access a patients of a certain P pad ID um or you know an appointment or whatever and uh and see the request that gets made in the browser using our um using our code our in product code

um so that can be kind of an understanding of what's under the hood and how to interface it from outside we do have some other resources though this I believe this does talk about yeah this touches a little bit on a node project a meteor project we have a Jace a a python example of how to interface

with the API so those are not the M API that you would find here in the product uh these are meant to be external you know included to your own project uh use externally web chart interfaces example we've got some things there and one that is very new and has not made an into our documentation yet is

the m web uh on GitHub Mi API tools this one's very new um but I do bring it up because it it looks like it's kind of taking the pieces of some of the other projects uh and making a very comprehensive tool um for you to be able to use on on managing data using the getting data

and managing data using the API um this is the API is a you know it can be read writed depends on how it's configured uh it can be used as for interfaces as well as querying data you interfaces that would update data um so I would reference those if you're interested in a Json API um that's something

that can be talked about kind of on par with what we do with with odbc uh if you if you have the tools and the means to uh interface with a Json API um and then if you if we do want to talk odbc sort of uh what I would call the Classic this is probably the most

common to date um the one that we've had around for quite a while it's it's basically uh we have a flavor of MySQL called Maria DB that is our database backend and we can get a connection opened up to that uh it does require opening up the firewall um so we we need to know where you're coming

from we need an IP address that is on premise for you that we can trust uh to allow even just connections from uh and then you get a credentials and a um an SSL certificate to verify our server uh with those things um you can use these help guides if you use one of these tools I know

powerbi is a really popular one we reference out to powerbi mySQL con connector documentation um you know being that that is their Tool uh and this will walk you through just kind of filling out what you need to do to be able to connect to show mySQL database we do give some notes just about from the data

that we give you we're going to you know the name is up to you we're saying that we want some sort of a MySQL connection uh this is just an example and then we're going to be sending you these files here these are always noted this is uh we we'll require encryption obviously um so use SSL always

need to be enabled um definitely see the product you're using for more details on that um but when we uh do SSL we do offer our our CA certificate as well as is a certificate that we generate for you that will let you verify our server so you're going to get a public key that will let you

verify our server is the the one that you're meaning to connect to to avoid man- in the-middle attacks and so uh if that's the mean the way you want to go that is a way to uh that we can provision that user for you open up our firewall we generate the certificates um and then you can use

whatever tool you like to directly connect to the database uh and then of course it's it's up to that tool how you're going to be querying you know if you need like I I go back to the system report Editor to know what tables to grab um if you need to write SQL we've got examples back there

so all right I think that's uh the summary pretty much everything I wanted to cover um knowing how to how to find the data uh some details on frequently asked questions you know Json API versus uh versus an SQL connection and uh I hope you find this valuable thank you