Using a local datamart

Installation Installation of Mydatamart has been made as simple as possible. The application itself @@ -194,114 +194,295 @@ image button bar immediately below the menu. If you hover your mouse slowly over these buttons a tooltip window should show the functions of each.

- Creating a new datamart - Now the main purpose of mydatamart is to manage your personal datamart files so - the first thing you will need to do is to create a new datamart. You can create a - new datamart either through the File->New menu option or by pressing the button with - the blue cross. When you create this new datamart you will be prompted for a file - name. The extension .dmart will be appended automatically to the name. Give some - thought to how and where you are going to store this file as it will soon be full of - valuable data so you really do need to look after it. You can create a backup at any - time by simply making a copy of this file, eg. onto a USB memory stick. - - The .dmart file is actually an sqlite3 database file which you can also open - and view or edit with any sqlite3 capable tool, such as sqlitestudio. - - When you create the new datamart, the application will present a dialog like . -

+ Maintaining the local datamart + +

Creating a new datamart - -

- The first thing you must do is to establish a connection with your dhis server. To - do this enter the full url (including http://) of the dhis server into the box - labelled URL. Then enter your online dhis user name and - password. Your password will not be saved anywhere on your machine. To login press - the button labelled Login. If the login was successful you will - be rewarded with a green tick icon next to the login button. -

- Logging in to dhis2 server - - - -

- Populating the datamart with metadata - The local datamart is designed to store aggregated data values from dataelements - and indicators in the online DHIS2 application. But before you can do this, you must - first populate the local datamart with metadata from the remote - dhis2. - Metadata refers to the parts of the database which give the data values meaning. - This includes: - - Organisation units and hierarchy; - - - Dataelement and Indicator names; - - - Datavalue disaggregations (categories and categoryoptions); - - - Various groups and groupsets. - - This information is required to work with pivot tables and to select - important parameters relating to your organisation unit. - Fortunately populating with metadata is a straightforward, if time consuming, - operation. The easiest way to load metadata from DHIS2 is to press the toolbar - button with the - image. It can also be done via the "Datamart->Load metadata from dhis" menu - option. - The Mydatamart application will then contact the remote DHIS2 server and download, - transform and save the metadata into the local database. - - Currently the transform part of the operation is quite slow. Expect it to take - as long as ten minutes. During this time you cannot work with the application so - you have to be patient. The reason for the long time is that it must process all - of the orgunits in the dhis hierarchy, which for many countries can be quite - large. In future editions of Mydatamart we will improve on this by cutting down - the number of orgunits to be processed. - - Once the metadata download is complete the user will be returned to the Settings - interface where she can proceed to enter information about her organisation unit and - the level of analysis to be used. This configuration is covered in the following - section. -

- Choosing Organisation unit and analysis level - -

- Setting analysis parameters - - -

- Downloading data - -

+ Now the main purpose of mydatamart is to manage your personal datamart files + so the first thing you will need to do is to create a new datamart. You can + create a new datamart either through the File->New menu option or by pressing + the button with the blue cross. When you create this new datamart you will be + prompted for a file name. The extension .dmart will be appended automatically to + the name. Give some thought to how and where you are going to store this file as + it will soon be full of valuable data so you really do need to look after it. + You can create a backup at any time by simply making a copy of this file, eg. + onto a USB memory stick. + + The .dmart file is actually an sqlite3 database file which you can also + open and view or edit with any sqlite3 capable tool, such as + sqlitestudio. + + When you create the new datamart, the application will present a dialog like + . +

+ Creating a new datamart + + + The first thing you must do is to establish a connection with your dhis + server. To do this enter the full url (including http://) of the dhis server + into the box labelled URL. Then enter your online dhis user + name and password. Your password will not be saved anywhere on your machine. To + login press the button labelled Login. If the login was + successful you will be rewarded with a green tick icon next to the login + button. +

+ Logging in to dhis2 server + + +

+ Populating the datamart with metadata + The local datamart is designed to store aggregated data values from + dataelements and indicators in the online DHIS2 application. But before you can + do this, you must first populate the local datamart with + metadata from the remote dhis2. + Metadata refers to the parts of the database which give the data values + meaning. This includes: + + Organisation units and hierarchy; + + + Dataelement and Indicator names; + + + Datavalue disaggregations (categories and categoryoptions); + + + Various groups and groupsets. + + This information is required to work with pivot tables and to + select important parameters relating to your organisation unit. + Fortunately populating with metadata is a straightforward, if time consuming, + operation. The easiest way to load metadata from DHIS2 is to press the toolbar + button with the image. + It can also be done via the "Datamart->Load metadata from dhis" menu + option. + The Mydatamart application will then contact the remote DHIS2 server and + download, transform and save the metadata into the local database. + + Currently the transform part of the operation is quite slow. Expect it to + take as long as ten minutes. During this time you cannot work with the + application so you have to be patient. The reason for the long time is that + it must process all of the orgunits in the dhis hierarchy, which for many + countries can be quite large. In future editions of Mydatamart we will + improve on this by cutting down the number of orgunits to be + processed. + + Once the metadata download is complete the user will be returned to the + Settings interface where she can proceed to enter information about her + organisation unit and the level of analysis to be used. This configuration is + covered in the following section. +

+ Choosing Organisation unit and analysis level + The benefit of using mydatamart is that you only need to download the data + from the dhis2 server which you need in your routine analysis. By selecting the + appropriate orgunit and level of analysis you ensure that your regular data + updates will be small and manageable even when bandwidth is limited. + The two concepts to be aware of are your root orgunit and + your analysis level. To illustrate this using an example + from Kenya, let us assume you are based in the district office of Nyeri North in + Nyeri County. You have either been given pivot tables or you will make them to + analyze your data down to facility level. So you require data for all the + facilities within Nyeri North and you also require to see data for your peers + ie. the other districts within Nyeri County. + Once you have created a new datamart and have downloaded metadata as described + in the previous section, you should be able to set these two parameters in the + settings dialog as illustrated below. If the dialog is not visible you can + access it by pressing the + button. +

+ Setting analysis parameters + + + Once you have set these once they will be saved with your datamart file. + Though they can be changed at any time or you might have different datamart + files with different settings. In most cases you will likely be maintaining a + single datamart file which you will have set up like the case of Nyeri North + above. + + You will have noticed from that the + analysis level is converted to a number. So for example, in Kenya, the + facility is at level 5, the district is level 4 etc. These correspond to + orgunit hierarchy levels within dhis2. These numbers will vary from one + country implementation to another. For the most part you do not need to be + concerned with the number of the level, except when you come to selecting + views for analysis. Here you will see that the level number is used as part + of the naming convention for views. + +

Downloading data - - -

- Creating new pivot tables - -

- Linking to shared pivot tables - -

+ Now that you have your local datamart set up you are ready to start populating + it with data from your online dhis2 server. Whereas you will probably only + adjust the settings when creating a new datamart, you will be doing regular + synching of your local datamart with the online server. If data is being + downloaded once a month, then the incremental size of each download will be + small. You can get to the data download screen either by pressing the + button or by navigating to the "Datamart->Update Aggregate + Data" menu item. + , shows the datamart update screen. You can set the + detailed options for your download here. There is not much to be done as the + defaults should match your typical operations. +

+ Downloading data + + + On the left hand side there are check boxes which you should set to indicate + whether you need data weekly, monthly, quarterly or yearly (you can select more + than one). These are independently set for aggregated datavaues as well as + aggregated indicator values, though in most cases you will require the same + periodicity of databvalue and indicator value. + On the right hand side you have the option to set the time period you are + downloading. The application will attempt to select a reasonable period for you. + Other than the very first time you download data, the default should be to + download the previous month's data. + Below the period selection section are the two buttons which actually initiate + the download. The first button is used to download data for orgunits below the + root orgunit at the level which was specified previously. So in the example, it + will download aggregated data at the facility level (level 5) for facilities in + Nyere North. + The second button is used to download aggregate data of our peers. In this + case it would ensure that we had data for all the districts in Nyere County. + This will allow the Nyere North user to analyze the data for all facilities in + Nyere North as well as to compare the performance of Nyere North district with + Nyere South district. + + +

+ Working with Excel +

+ Creating a new Excel workbook for analysis + Pivot tables in Microsoft Excel are commonly used to analyze data in dhis2. + The mydatamart tool does not create pivot tables for you, but it does provide a + simple interface to get you started with an Excel workbook which is already + linked to views in your local datamart. This means that users do not need to + concern themselves with the technical detail of ODBC connection strings, DSNs or + what have you. This section describes how you get started with that. It does not + go into the detail of actually designing the pivot tables as this is described + elsewhere in the dhis2 user manual. + The most important thing to have prepared when designing a pivot table are the + database views you will use. Mydatamart automatically creates some "standard" + views for dataelement values and indicator values for different periodicities - + weekly, monthly, quarterly, semesterly (every 6 months), yearly etc. Expert + users might also want to design additional custom views. Using a 3rd party tool + such as sqlstudio is useful for this. There is also an option to import an + arbitrary sql file into the database which can be used to share custom + views. + To start the process of creating an Excel pivot table workbook, the user + should select "Reports->New Excel file" from the menu bar. She will immediately + be greeted with a dialog for selecting views such as illustrated in below. +

+ Selecting views + + + Mydatamart will present a list of all defined views in the listbox on the + left. These include the automatically generated ones as well as any custom views + which may have been created outside of the tool. The names of the views give + some indication of their content. The auto generated views are named as + follows: + pivotsource_<datatype>_ou_<level>_<periodicity> + In we can see that monthly and quarterly views for + indicators at level 4 (district) and 5 (facility) have been selected. + Double-clicking on a view in the left listbox causes the view to be selected for + inclusion. Similarly, double-clicking on a view in the right listbox causes the + view to be removed from the selection. + + A warning to power users creating custom views. You can name these views + whatever you like, but be sure not to use the name of one of the built in + views. If you do that your custom view will get overwritten when the + metadata is updated. + + Once you have selected the required views you can click on "Create Excel + workbook". This will prompt you for a name and location for your new Excel + file. +

+ Working with your new Excel file + When you create a new Excel file as described in the section above, Excel + should automatically open the new file. This file will have the database + connections to your selected views already populated. To see these connections + (on Excel 2007) first click on the "Data" menu, then select "Existing + Connections" from the toolbar. You should see something like below, showing the views that you selected + in the previous step. +

+ Datamart connections + + + You can use these views to create pivot tables or simply to view the data + within a worksheet. To make use of a view simply double click on it. You will be + presented with a dialog similar to . +

+ Pivot report wizard + + + From this point, creating pivot tables is the same as is described in the + DHIS2 User Manual. + + Remember to define the Indicator value calculation when using indicator + views. This process is as described in the DHIS2 User Manual. + + On completion of your pivot table design you can save it and/or copy it to a + new location. Whenever you have new data in your datamart you can refresh the + pivot tables. Refresh times should be reasonably fast as the data is coming from + your local datamart file. + You can also share your pivot design with other mydatamart users, particularly + those at the same administrative level as yourself, as they will likely be + interested in the same views and analysis. To do this you should first ensure + that the Excel pivot cache is empty so that you are not trying to send enormous + files around by email (Instructions required on doing this - + Ed.). + Remember that the database links in the Excel file are pointing at your + datamart file (the file with a .dmart extension which you create when starting a + new datamart). If you move that datamart file your connections will no longer + work. Similarly if you send the file to someone else, they will need to connect + the Excel file to their own datamart. This process is straightforward and is + described in the next section. +

+ Linking an existing Excel file to a datamart + If you have been sent a pivot table report from another mydatamart user, or you have moved the location of your .dmart file + you will need to recreate the link between your datamart and the Excel file. This is accomplished simply: + + + Open your datamart file using the mydatamart tool; + + + Select "Reports->Connect to existing excel file" or use the + + shortcut + + Select the excel file you wish to connect to in the ensuing dialog. + + The excel file will automatically have its database links repaired to point to your current datamart. +

Troubleshooting - + In the event of things not behaving as expected, or if you wish to report buggy or surprizing behaviour, it can be + useful (particularly for the developer) to have more detailed information than that which is normally displayed to the user. + The mydatamart application creates an internal log with lots of low level information about what the program is doing at any + point in time. To view this internal log you need to display the program console. You can either do this through the "Help" menu + option or by simply pressing the "F2" shortcut key. + It is a good idea to capture the content of this console log when reporting problems to the developers.